cancel
Showing results for 
Search instead for 
Did you mean: 

SQL to return how many items are in an archive and what the newest item is

Stormonts
Level 5

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!

4 REPLIES 4

Marcde
Moderator
Moderator
Partner    VIP    Accredited

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

PMCS GmbH & Co. KG - A Serviceware Company
www.serviceware.de

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Regards. Gertjan

Thank you both!

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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!

Regards. Gertjan