06-03-2021 03:11 PM
We were wondering if anyone has an SQL query that we can run to display the total number of items in each archive and the age of the newest message?
Thanks!
06-03-2021 10:42 PM
Hi there,
not having something handy that fullfills your request but how about bringing the data together from two sources?
1) Store the details from usage.asp (http://localhost/enterprisevault/usage.asp)
2) Execute the following query
USE EnterpriseVaultDirectory
select Archivename, YoungestItemDateUTC
from IndexVolumeView
If your environment is not too big (for example many vaulstores), you should be able to easily match those both data sets toghether.
Regards
Marc
06-03-2021 11:36 PM
For the item count, you can use Powershell (I believe from EV11 and up).
Create a txt file, paste below. Change C:\TEMP to what ever you need. Save the txt as .ps1 When done, open Windows Powershell (as admin). Run the ps1. When done, check c:\temp
There are more parameters in this, like Size, VaultStoreId, ID, but Name and ItemCount are nicest to get quick.
$cmapi = New-Object -ComObject EnterpriseVault.ContentManagementAPI
$as = $cmapi.Archives
$as.Computer = "localhost"
$as.Get()
$as | Format-Table Name, ItemCount > C:\TEMP\archivelist.txt
I also have below query, which gives more info (change YourVaultStore1_1 to your databasename). Archive creation date, oldest item modified date, archived date etc. Like Marc says, stick the 2 in 1 sheet, and you got all you need.
SELECT A.ArchiveName "Archive Name",
AP.CreatedDate "Archive Created",
AP.ModifiedDate "Archived Last Modified",
MIN(S.IdDateTime) "Oldest Sent/Received Item",
MAX(S.IdDateTime) "Newest Sent/Received Item",
MIN(S.ArchivedDate) "Oldest Archived Item",
MAX(S.ArchivedDate) "Newest Archived Item"
FROM EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.Root R,
YourVaultStore1_1.dbo.ArchivePoint AP,
YourVaultStore1_1.dbo.Saveset S
WHERE A.RootIdentity = R.RootIdentity
AND R.VaultEntryId = AP.ArchivePointId
AND AP.ArchivePointIdentity = S.ArchivePointIdentity
AND A.ArchiveName = 'My User'
GROUP BY A.ArchiveName, AP.CreatedDate, AP.ModifiedDate
order by AP.CreatedDate
06-09-2021 08:23 AM
Thank you both!
06-10-2021 11:03 PM
You're welcome.
Can you mark one of the answers as 'solution'? That kind of closes the question, which will assist others with the same question find this, and prevents others in opening the question to answer it too.
Thanks!