08-11-2011 05:49 AM
Hello all,
I am sure I have seen these kind of queries, but cannot locate them.I am (un)fortunately not an SQL-person, so I am unable to compose such a query myself...
I need to get a list that shows the number of items and the size of certain archives.
We currently do not have SQL-reporting available. The usage-report is to slow to be used due to the amount of archives.
Does anyone have a query at hand that will allow me to show this info quickly?
Thanks!
Gertjan
Solved! Go to Solution.
08-11-2011 07:21 AM
A slower, slightly more silly query would be the following (but might be more accurate and what you're looking for
SELECT A.ArchiveName, COUNT(S.IdTransaction) "Num. Items Archived", SUM(S.ItemSize)/1024 "Archived Items Size (MB)", SUM(SP.OriginalSize)/1024/1024 "Original Email Size (MB)" FROM EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, yourVaultStoreDB.dbo.ArchivePoint AP, yourVaultStoreDB.dbo.Saveset S, yourVaultStoreDB.dbo.SavesetProperty SP WHERE S.SavesetIdentity = SP.SavesetIdentity AND S.ArchivePointIdentity = AP.ArchivePointIdentity AND AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity AND A.ArchiveName = 'your User' GROUP BY A.ArchiveName
Remember that if you have users across multiple stores, you will have to run this against each individual vault store, if you want to specify multiple users, do
A.ArchiveName IN ('yourUser1','yourUser2','yourUser3')
08-11-2011 07:12 AM
easiest thing would be to query the ArchivePoint table in the VaultStore database and then link it back to the EnterpriseVaultDirectory database
SELECT A.ArchiveName, AP.ArchivedItems, AP.ArchivedItemsSize FROM EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, yourVaultStoreDB.dbo.ArchivePoint AP WHERE AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity AND A.ArchiveName = 'your User'
08-11-2011 07:21 AM
A slower, slightly more silly query would be the following (but might be more accurate and what you're looking for
SELECT A.ArchiveName, COUNT(S.IdTransaction) "Num. Items Archived", SUM(S.ItemSize)/1024 "Archived Items Size (MB)", SUM(SP.OriginalSize)/1024/1024 "Original Email Size (MB)" FROM EnterpriseVaultDirectory.dbo.Archive A, EnterpriseVaultDirectory.dbo.Root R, yourVaultStoreDB.dbo.ArchivePoint AP, yourVaultStoreDB.dbo.Saveset S, yourVaultStoreDB.dbo.SavesetProperty SP WHERE S.SavesetIdentity = SP.SavesetIdentity AND S.ArchivePointIdentity = AP.ArchivePointIdentity AND AP.ArchivePointId = R.VaultEntryId AND R.RootIdentity = A.RootIdentity AND A.ArchiveName = 'your User' GROUP BY A.ArchiveName
Remember that if you have users across multiple stores, you will have to run this against each individual vault store, if you want to specify multiple users, do
A.ArchiveName IN ('yourUser1','yourUser2','yourUser3')
08-11-2011 11:30 AM
Thanks.
Now, how would I go about if the directorydb is stored on SQL1, and the mailboxdb's are on SQL2?
These are seperate servers.
Thanks.
08-11-2011 12:05 PM
You just set them up as linked servers and then fully qualify the names in the form ofLinkName.DatabaseName.SchemaName(dbo).TableName
09-14-2020 10:00 AM - edited 09-14-2020 10:55 AM
09-14-2020 10:18 AM
you will be publicly flogged for attempting to revitalize a decade old thread with such a question. The simple answer is yes.