Forum Discussion

OldEmployee's avatar
OldEmployee
Level 5
7 years ago

Retrieving archive size for multiple users

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's avatar
    GertjanA
    7 years ago

    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

  • ChrisLangevin's avatar
    ChrisLangevin
    7 years ago

    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

     

10 Replies

Sort By
  • ChrisLangevin's avatar
    ChrisLangevin
    Level 6
    7 years ago

    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's avatar
      GertjanA
      Moderator
      7 years ago

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

      • ChrisLangevin's avatar
        ChrisLangevin
        Level 6
        7 years ago

        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

    • sandrine_goetha's avatar
      sandrine_goetha
      Level 6
      7 years ago

       

      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's avatar
        ChrisLangevin
        Level 6
        7 years ago

        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

  • GertjanA's avatar
    GertjanA
    Moderator
    7 years ago

    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's avatar
      OldEmployee
      Level 5
      7 years ago

      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's avatar
        GertjanA
        Moderator
        7 years ago

        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