cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query - for message stats on all users

hello,

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

thanks

 

1 Solution

Accepted Solutions
Accepted Solution!

Hi ya, I have two queries

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))
       ,AF.FolderName
       ,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.

https://www.linkedin.com/in/awsterling/

View solution in original post

5 Replies
Accepted Solution!

Hi ya, I have two queries

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))
       ,AF.FolderName
       ,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.

https://www.linkedin.com/in/awsterling/

View solution in original post

invalid object name

invalid object name 'Saveset'

 

they need to run against the

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

 

https://www.linkedin.com/in/awsterling/

Thank you!!

Thank you!!

Great script Tony, I have a

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%'"