Forum Discussion

Sarah_Seftel1's avatar
Sarah_Seftel1
Level 6
10 years ago

SQL query - number of archived items per archive

Hi,

Need an assistance with an SQL query that will bring the following result:

list of all archives, number of total items archived for archive, number of items archived per archive for 2014 only, and size of all items in the the archive.

I found different queries here, but I do not know SQL that good, so I was unable to join them :(

Thanks,

Sarah

Enterprise Vault
Information Governance
Reporting
Tip-How to
  • Rob_Wilcox1's avatar
    Rob_Wilcox1
    10 years ago

    This might work:

     

    SELECT
    EME.MbxDisplayName "Mailbox Name",
    PTG.DisplayName "Provisioning Group",
    COUNT(S.ItemSize) "No. Items Archived",
    SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
    SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)",
    SUM(CASE when CAST(Year(ArchivedDate) as int) = 2014 then 1 else 0 end) as 'Num in 2014'
    FROM
    dbo.Saveset S,
    dbo.SavesetProperty SP,
    dbo.ArchivePoint AP,
    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
    EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
    WHERE
    S.SavesetIdentity = SP.SavesetIdentity
    AND S.ArchivePointIdentity = AP.ArchivePointIdentity
    AND AP.ArchivePointID = EME.DefaultVaultId
    AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
    GROUP BY EME.MbxDisplayName, PTG.DisplayName
    ORDER BY PTG.DisplayName, EME.MbxDisplayName

  • Rob_Wilcox1's avatar
    Rob_Wilcox1
    Level 6
    10 years ago

    This might work:

     

    SELECT
    EME.MbxDisplayName "Mailbox Name",
    PTG.DisplayName "Provisioning Group",
    COUNT(S.ItemSize) "No. Items Archived",
    SUM(S.ItemSize)/1024 "Archived Item Size (MB)",
    SUM(SP.OriginalSize)/1024/1024 "Original Item Size (MB)",
    SUM(CASE when CAST(Year(ArchivedDate) as int) = 2014 then 1 else 0 end) as 'Num in 2014'
    FROM
    dbo.Saveset S,
    dbo.SavesetProperty SP,
    dbo.ArchivePoint AP,
    EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
    EnterpriseVaultDirectory.dbo.PolicyTargetGroup PTG
    WHERE
    S.SavesetIdentity = SP.SavesetIdentity
    AND S.ArchivePointIdentity = AP.ArchivePointIdentity
    AND AP.ArchivePointID = EME.DefaultVaultId
    AND EME.PolicyTargetGroupEntryId = PTG.PolicyTargetGroupEntryId
    GROUP BY EME.MbxDisplayName, PTG.DisplayName
    ORDER BY PTG.DisplayName, EME.MbxDisplayName

  • Sarah_Seftel1's avatar
    Sarah_Seftel1
    Level 6
    10 years ago

    Hi Rob, 

    Thanks as always :)

    I encountered some problems when accessing the second SQL instance.

    The scenario is:

    Instance ilsqlev01\ev01 holds the following DB's:

    EnterpriseVaultDirectory
    EnterpriseVaultMonitoring
    EVVSPTVMBXVS01_1
    EVVSPTVMBXVS02_2
    EVVSPTVMBXVS03_3
    EVVSPTVMBXVS04_4
    EVVSGPTVMBXVSG01_1_1

    Instance ilsqlev02\ev02 holds the following DB's:

    EVVSPTVMBXVS05_5
    EVVSPTVMBXVS06_6
    EVVSPTVMBXVS07_7

    The query worked perfectly on instance ilsqlev01\ev01, but when I connected to the second instance and run the query I get an error saying : 
    Msg 208, Level 16, State 1, Line 1, Invalid object name 'EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry'.

    which makes sense since it cannot see the EnterpriseVaultDirectory DB.

    How can I force it to read the EnterpriseVaultDirectory DB details from the 1st instance and still run it on the second instance for the rest of 3 storage groups DB's?

     

    Many many thanks in advanced!

    Sarah

     

  • Sarah_Seftel1's avatar
    Sarah_Seftel1
    Level 6
    10 years ago

    Hi,

     

    Linked the servers, added the first instance name before the EnterpriseVaultDirectory DB and it worked like a charm :)

     

    Rob, thanks a lot! as always, your help is priceless.

     

    Sarah