Forum Discussion

Always_Somethin's avatar
Always_Somethin
Level 3
10 years ago

Enterprise Vault report for item age.

Hi Everyone

There are many canned Data Analysis reports on space Usage, Storage, and vaults but I need reports on archived item age and count.

We have Expiry turned off and want to turn it on.

We would like to get a general idea/count of vaulted Email and FSA item age and count.

Has anyone created a report or is there a SQL query that can provide me this information possible?

I just to not see reports referenceing age/item count.

 

Any help or direction would be greatly appreciated.

Enterprise Vault
Information Governance
Reporting
Tip-How to
Windows Server (2003-2008)
  • WiTSend's avatar
    WiTSend
    10 years ago

    Try using this query, you will need to make some adjustments:

    Created date, size, etc...

    Dont forget to change EVMailboxVS01 to your relevent Vault Store Database name

    SELECT

    MbxDisplayName AS 'Mailbox',

    ExchangeComputer AS 'Exchange Server',

    MbxItemCount AS '#Items (Mailbox)',

    VS1.ArchivedItems AS '#Items (Archive)',

    MbxSize/1024 AS 'Mbx Size (MB)',

    VS1.ArchivedItemsSize/1024 AS 'Archive Size(MB)',

    (mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',

    VS1.CreatedDate AS 'Archive Created',

    VS1.ModifiedDate AS 'Archive Updated',

    MbxExchangeState AS 'Exchange State'

     

    FROM

    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,

    EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,

    EVMailboxVS01..ArchivePoint AS VS1

     

    WHERE

    EME.DefaultVaultID = VS1.ArchivePointID AND

    EME.MbxArchivingState = 1 AND

    EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity

     

    If you have more than one Vaul Store you will need to join them with UNION and change EVMailboxVS02 to your relevent Vault Store Database name

    UNION

     

    SELECT

    MbxDisplayName AS 'Mailbox',

    ExchangeComputer AS 'Exchange Server',

    MbxItemCount AS '#Items (Mailbox)',

    VS1.ArchivedItems AS '#Items (Archive)',

    MbxSize/1024 AS 'Mbx Size (MB)',

    VS1.ArchivedItemsSize/1024 AS 'Archive Size(MB)',

    (mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',

    VS1.CreatedDate AS 'Archive Created',

    VS1.ModifiedDate AS 'Archive Updated',

    MbxExchangeState AS 'Exchange State'

     

    FROM

    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,

    EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,

    EVMailboxVS02..ArchivePoint AS VS1

     

    WHERE

    EME.DefaultVaultID = VS1.ArchivePointID AND

    EME.MbxArchivingState = 1 AND

    EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity

  • WiTSend's avatar
    WiTSend
    Level 6
    10 years ago

    Even with Expiry turned off you can run it in the Report mode and get a full lisiting of the eligible items.

  • Always_Somethin's avatar
    Always_Somethin
    Level 3
    10 years ago

    I can run Expiry in report mode but we have retention set to 25 years so there will be no qualifying items.

    We would change retention to say five years. That would give me a qualifying count of items, but not vaulted item age, and item count so to speak.

    Looking elsewhere for a potential sql query or custom report I can potentially modify.

    I hope I am not overlooking an easy resolution.

     

     

  • WiTSend's avatar
    WiTSend
    Level 6
    10 years ago

    Try using this query, you will need to make some adjustments:

    Created date, size, etc...

    Dont forget to change EVMailboxVS01 to your relevent Vault Store Database name

    SELECT

    MbxDisplayName AS 'Mailbox',

    ExchangeComputer AS 'Exchange Server',

    MbxItemCount AS '#Items (Mailbox)',

    VS1.ArchivedItems AS '#Items (Archive)',

    MbxSize/1024 AS 'Mbx Size (MB)',

    VS1.ArchivedItemsSize/1024 AS 'Archive Size(MB)',

    (mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',

    VS1.CreatedDate AS 'Archive Created',

    VS1.ModifiedDate AS 'Archive Updated',

    MbxExchangeState AS 'Exchange State'

     

    FROM

    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,

    EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,

    EVMailboxVS01..ArchivePoint AS VS1

     

    WHERE

    EME.DefaultVaultID = VS1.ArchivePointID AND

    EME.MbxArchivingState = 1 AND

    EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity

     

    If you have more than one Vaul Store you will need to join them with UNION and change EVMailboxVS02 to your relevent Vault Store Database name

    UNION

     

    SELECT

    MbxDisplayName AS 'Mailbox',

    ExchangeComputer AS 'Exchange Server',

    MbxItemCount AS '#Items (Mailbox)',

    VS1.ArchivedItems AS '#Items (Archive)',

    MbxSize/1024 AS 'Mbx Size (MB)',

    VS1.ArchivedItemsSize/1024 AS 'Archive Size(MB)',

    (mbxsize+VS1.ArchivedItemsSize)/1024 AS 'Total Size(MB)',

    VS1.CreatedDate AS 'Archive Created',

    VS1.ModifiedDate AS 'Archive Updated',

    MbxExchangeState AS 'Exchange State'

     

    FROM

    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry AS EME,

    EnterpriseVaultDirectory.dbo.ExchangeServerEntry AS ESE,

    EVMailboxVS02..ArchivePoint AS VS1

     

    WHERE

    EME.DefaultVaultID = VS1.ArchivePointID AND

    EME.MbxArchivingState = 1 AND

    EME.ExchangeServerIdentity = ESE.ExchangeServerIdentity

  • Always_Somethin's avatar
    Always_Somethin
    Level 3
    10 years ago

    I was able to use pieces of the SQL query posted above to harvest the information needed.

    Love this forum!

    Thank you WiT Send!