02-06-2015 12:31 PM
Hi All
I need to get journal archiving rates between X and Y dates, by Exchange mailbox, or archive, or journal tasks.
I found a number of SQL queries queries relating to this, but not quit one that works. This is close:
https://www-secure.symantec.com/connect/forums/need-sql-query#comment-7199911
but only works for mailbox archiving tasks, not journaling tasks.
Does anyone have any sample queries?
thanks!
Solved! Go to Solution.
02-09-2015 10:33 AM
Ok, this will give you a rate by hour for last 7days
SELECT ex.MbxDisplayName, "Archived Date" = left (convert (varchar, archiveddate,20),14), count(*) as 'Number of Items', sum (Convert(bigint,itemsize))/1024 as 'Size'
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
WHERE ArchivedDate > dateadd("dd", -7, getdate ())
GROUP BY ArchivePointID,s.ArchivePointIdentity, left(convert (varchar, archiveddate,20),14), ex.MbxDisplayName
ORDER BY "Archived Date" DESC
This will give you a daily archive rate for last 30 days
SELECT ex.MbxDisplayName, "Archived Date" = left (convert (varchar, archiveddate,20),10), count(*) as 'Number of Items', sum (Convert(bigint,itemsize))/1024 as 'Size'
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
WHERE ArchivedDate > dateadd("dd", -30, getdate ())
GROUP BY ArchivePointID,s.ArchivePointIdentity, left(convert (varchar, archiveddate,20),10), ex.MbxDisplayName
ORDER BY "Archived Date" DESC
You can change the WHERE clause and use a date range in either of these.
02-06-2015 12:51 PM
the counts are essentially all based on items archived per vault store. we typically design journaling to go to a seperate vault store from mailbox archiving (especially when we dedicate servers to journaling) so the common archive rate scripts like the ones here: https://www-secure.symantec.com/connect/articles/enterprise-vault-useful-sql-queries should get you what you need.
02-09-2015 09:59 AM
That link does not work. Does anyone else have example queries? I need something more granular than just vault store as I have journaling and mailbox archiving going into a single vault store. Thanks.
02-09-2015 10:14 AM
are you looking for a rate by day or hour?
02-09-2015 10:17 AM
ideally rate between X and Y date/time. Otherwise hourly rate is probably best.
02-09-2015 10:31 AM
https://www-secure.symantec.com/connect/articles/enterprise-vault-useful-sql-queries
02-09-2015 10:33 AM
Ok, this will give you a rate by hour for last 7days
SELECT ex.MbxDisplayName, "Archived Date" = left (convert (varchar, archiveddate,20),14), count(*) as 'Number of Items', sum (Convert(bigint,itemsize))/1024 as 'Size'
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
WHERE ArchivedDate > dateadd("dd", -7, getdate ())
GROUP BY ArchivePointID,s.ArchivePointIdentity, left(convert (varchar, archiveddate,20),14), ex.MbxDisplayName
ORDER BY "Archived Date" DESC
This will give you a daily archive rate for last 30 days
SELECT ex.MbxDisplayName, "Archived Date" = left (convert (varchar, archiveddate,20),10), count(*) as 'Number of Items', sum (Convert(bigint,itemsize))/1024 as 'Size'
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
WHERE ArchivedDate > dateadd("dd", -30, getdate ())
GROUP BY ArchivePointID,s.ArchivePointIdentity, left(convert (varchar, archiveddate,20),10), ex.MbxDisplayName
ORDER BY "Archived Date" DESC
You can change the WHERE clause and use a date range in either of these.
02-09-2015 10:43 AM
Oh, and you should also be able to add MbxDisplayName to the WHERE clause to only see the journal archive if that is all you want.
02-09-2015 11:08 AM
Love your work Tony! Thanks, just what I needed.