Retrieving archive size for multiple users

[OldEmployee]

I need to retrieve the size of current archive for around 360 users.

I understand that usage.asp can be used for this, but the users are on random vault stores and going through all of them individually would be time consuming.

Is there an SQL query that can provide this output?

[GertjanA · Accepted solution]

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

[OldEmployee]

Thank you GertjanA.

I only have the list of email address from the exchange team and we have 27 vault stores across which these archives are spread.

So, do we have to run this script on every vault store database?

EDIT: Just read it again and I see you mentioned it should be run on all databases.

[GertjanA]

Hello again,

Unfortunately yes. I prepare this query at customers, by entering each vsdb in the search, with -- in front.

Something like below. This way, you only have to comment out the one you ran, and uncomment the one you want. (comment = add two -- in front, uncomment = remove -- in front)

FROM   EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.Root R,
       EVVaultStore1.dbo.ArchivePoint AP,
       EVVaultStore1.dbo.Saveset S,
       EVVaultStore1.dbo.SavesetProperty SP
       -- EVVaultStore2.dbo.ArchivePoint AP,
       -- EVVaultStore2.dbo.Saveset S,
       -- EVVaultStore2.dbo.SavesetProperty SP
       -- EVVaultStore3.dbo.ArchivePoint AP,
       -- EVVaultStore3.dbo.Saveset S,
       -- EVVaultStore3.dbo.SavesetProperty SP

[ChrisLangevin · Accepted solution]

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

[GertjanA]

That's awesome! Does that work for any EV version (as of 10?)

[ChrisLangevin]

Gertjan,

The IArchives interface that my example uses has been available since EV 2007 (7.5), actually. I'm not sure you could get it work in PowerShell back then (those were PowerShell 1.0 days and my examples definitely use a lot of 2.0+ functionality), but the interface was there.

EV 10 requires Windows 2008 R2, and therefore PowerShell 2.0, and so these examples should work fine.

--Chris

[OldEmployee]

Thank you Chris. This is perfect.

Could you please tell us, where/how is this script pulling information from?

[sandrine_goetha]

Thank you so much Chris I search for something like that since year and yes SQL is a pain...

The only problem I have is the number of my archives more than 60.000 ( active and inactive ones).
And with the fantastic code you gave us I just get the first 5000. I have to confess I'm far from a Powershell specialist, could you hint me in the right direction to get all of my  archives displayed in this.

Thank you so much

Sandrine

[ChrisLangevin]

czle,

This script is pulling the information from EV using the EV Content Management API. Ultimately the information is stored in SQL, but we're just letting the EV storage service do the hard work of querying all the databases instead of writing our own iterating query.

If you're interested in learning more about the EV CM API, become a Veritas Partner and you'll get the full API documentation.

--Chris

[ChrisLangevin]

Sandrine,

The Archives collection implements a default limit of 5000 results returned. You can override that by setting the Maximum property on the collection before you call Get().

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

There is also a handy More property on the collection, which you can check after Get() returns to see if there were more items available than the maximum size.

$as.More

Hope that helps!

--Chris