Retrieving archive size for multiple users

Hello

Something like this. In the A.ArchiveName = 'User', you add all users you need, then run the query against each database. You will need to change EVVaultStore1 in the 3 lines for each vault store you need. If an A.ArchiveName is not in the database, it won't be listed. Put everything in Excel, then see if you have them all.

SELECT A.ArchiveName "Archive Name",
       COUNT(S.IdTransaction) AS ArchivedItemCount,
       SUM(S.ItemSize)/1024 "Compressed Size (MB)",
       SUM(SP.OriginalSize)/1024/1024 "Uncompressed Size (MB)"
FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVaultStore1.dbo.ArchivePoint AP,
       EVVaultStore1.dbo.Saveset S,
       EVVaultStore1.dbo.SavesetProperty SP
WHERE  A.RootIdentity = R.RootIdentity
  AND  R.VaultEntryId = AP.ArchivePointId
  AND  AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND  S.SavesetIdentity = SP.SavesetIdentity
  AND (A.ArchiveName = 'User1'
 OR A.ArchiveName = 'User2'
 OR A.ArchiveName = 'User3')
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

Because you have so many Vault Stores, it's going to be a pain to do this via SQL. Luckily, the EV Content Management API makes this super easy with its Archives collection. Just do this in PowerShell on your EV server:

$cmapi = New-Object -ComObject EnterpriseVault.ContentManagementAPI
$as = $cmapi.Archives
$as.Computer = "localhost"
$as.Get()

Easy as that, now you have the most salient properties of every archive in your whole site stored in that $as variable, and you can manipulate them the same as any other PowerShell object.

Make a nice table:

$as | Format-Table Name, Id, ItemCount, Size

You say you have only a specific subset of archives you care about. You can filter that out with PS too:

$as | Where-Object {$_.Name -in (Get-Content "C:\temp\ArchivesICareAbout.txt")} | Format-Table Name, Id, ItemCount, Size

The Size property reports in KB. If you want other denominations, you can do the division in some custom columns:

$as | Where-Object {$_.Name -in (Get-Content "C:\temp\archivelist.txt")} | Format-Table Name, Id, ItemCount, @{n="Size (KB)";e={$_.Size}}, @{n="Size (MB)";e={$_.Size / 1024}}, @{n="Size (GB)";e={$_.Size / 1024 /1024}}

Output looks like this:

Output can also be exported to Excel by piping to the Export-Csv cmdlet.

If it's a requirement that you use SQL, do it Gertjan's way, but if you just want the data, then this is easier.

--Chris