cancel
Showing results for 
Search instead for 
Did you mean: 

EV journal archiving rates by Exchange mailbox or archive

goatboy
Level 6

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

View solution in original post

8 REPLIES 8

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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.

goatboy
Level 6

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.

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

are you looking for a rate by day or hour?

goatboy
Level 6

ideally rate between X and Y date/time. Otherwise hourly rate is probably best.

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

https://www-secure.symantec.com/connect/articles/enterprise-vault-useful-sql-queries

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

goatboy
Level 6

Love your work Tony! Thanks, just what I needed.