Video Screencast Help

SQL Query - for message stats on all users

Created: 14 Aug 2014 • Updated: 15 Aug 2014 | 5 comments
This issue has been solved. See solution.


looking for a sql query to find the following.

user, message retention category , message location (ie folder its in) for all users , message location category


Operating Systems:

Comments 5 CommentsJump to latest comment

TonySterling's picture

Hi ya,

I have two queries that I have used.  This one will give you a count of items by folder for each archive.

SELECT A.ArchiveName
       ,CAST(AF.FolderPath AS NVARCHAR(MAX))
       ,COUNT(S.IdTransaction) "Number of Archived Items"
      -- ,SUM(S.ItemSize) "Total Size (KB)"
FROM   Saveset S,
       ArchivePoint Ap,
       Vault V,
       EnterpriseVaultDirectory.dbo.Root R,
       EnterpriseVaultDirectory.dbo.Root R2,
       EnterpriseVaultDirectory.dbo.Archive A,
       EnterpriseVaultDirectory.dbo.ArchiveFolder AF
WHERE  S.VaultIdentity = V.VaultIdentity
  AND  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointID = R.VaultEntryID
  AND  V.VaultID = R2.VaultEntryID
  AND  R.RootIdentity = A.RootIdentity
  AND  R2.RootIdentity = AF.RootIdentity
  AND  AF.FolderPath Like '%System%'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName

This one will give you the count by retention category for each archive:

SELECT ex.MbxDisplayName, rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY  rc.RetentionCategoryName, ex.MbxDisplayName
ORDER BY ex.MbxDisplayName 

I haven't put them into one query but might be possible, I just don't have time to give it go but using them should give you an idea of what you have going on.

TonySterling's picture

they need to run against the Vault Store database(s)'s picture

Great script Tony, I have a question I only get 46 lines in the first script, should I get at list 1 line per archive?

Sorry, I know, just change this part of the query  "AND  AF.FolderPath Like '%System%'"