Forum Discussion

Mario_VU's avatar
Mario_VU
Level 4
18 years ago
Solved

Vault Store usage query for SQL

Hello all,

 

does anyone have an SQL query that give me the total size and number of items per archive (user)?

This can be found with the Vault Store usage report (usage.asp) but we want to run the query every day in SQL to be processed for billing.

 

Thanks Mario

Enterprise Vault
Information Governance
  • MichelZ's avatar
    MichelZ
    18 years ago

    Actually, I was wondering myself, so I came just up with this:

    (Execute in Vault Store Databases EV*)

     

    SELECT [ArchiveName], ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize, ModifiedDate From view_ListVaults INNER JOIN [EnterpriseVaultDirectory].[dbo].[ArchiveView] ON view_ListVaults.ArchivePointId = [EnterpriseVaultDirectory].[dbo].[ArchiveView].[VaultEntryId]

     

    Note: EnterpriseVaultDirectory Database and Vault Store Database have to be on the same Server for this to work.

     

    /Michel

5 Replies

  • MichelZ's avatar
    MichelZ
    Level 6
    18 years ago

    Well....

     

    I've got you this:

     

    Exec in Vault Store DB:

     

    SELECT ArchivePointId, ArchivePointIdentity, ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize, ModifiedDate From view_ListVaults

     

    Then, for each ArchivePointId, exec in EnterpriseVaultDirectory:

    exec GetArchive N'187D056B5BE6C7648BCCF723F36908BE51110000ev-site-a.a.local'

     

    You can probably script this to give you everything.

  • MichelZ's avatar
    MichelZ
    Level 6
    18 years ago

    Actually, I was wondering myself, so I came just up with this:

    (Execute in Vault Store Databases EV*)

     

    SELECT [ArchiveName], ArchivedItems, CAST(ArchivedItemsSize AS decimal(20, 0)) AS ArchivedItemsSize, ModifiedDate From view_ListVaults INNER JOIN [EnterpriseVaultDirectory].[dbo].[ArchiveView] ON view_ListVaults.ArchivePointId = [EnterpriseVaultDirectory].[dbo].[ArchiveView].[VaultEntryId]

     

    Note: EnterpriseVaultDirectory Database and Vault Store Database have to be on the same Server for this to work.

     

    /Michel

  • phak's avatar
    phak
    Level 3
    17 years ago

    Hi,

     

    Thanks very much its just waht I need. Now I need to understand the SQL server as I want to automate it probably using a scheduled task etc to output the report.

     

    Cheers

     

    Pete

  • Liam_Finn1's avatar
    Liam_Finn1
    Level 6
    17 years ago

    Phak,

     

    If you have SQl reporting services installed and you are running EV reporting you can add this script to the existing EV reports and configure a subscription to have this taks run on a schedule and either exported to file or sent via email to yourself

     

     

    I run some reports that check my journal mailbox status and also index status twice a day so i dont have to go looking for the indexmissing.log file the system tells me when somethin is missing so i just run a repair

     

    I have a few good reports that do this for me

     

    Also reports that check my SQL maintainance tasks and lets me know when they suceeed stuff like backing up my SQL to file every morning