Forum Discussion

Stormonts's avatar
Stormonts
Level 5
4 years ago

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

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!

Enterprise Vault
  • Marcde's avatar
    Marcde
    Moderator
    4 years ago

    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

    • GertjanA's avatar
      GertjanA
      Moderator
      4 years ago

      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