08-14-2014 06:47 AM
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
Solved! Go to Solution.
08-14-2014 07:16 AM
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.
08-14-2014 07:16 AM
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.
08-14-2014 12:23 PM
invalid object name 'Saveset'
08-14-2014 04:44 PM
they need to run against the Vault Store database(s)
08-15-2014 05:32 AM
Thank you!!
09-02-2014 03:57 AM
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%'"