Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

SQL query

Created: 02 May 2013 • Updated: 03 May 2013 | 6 comments
GTK's picture
This issue has been solved. See solution.

Hi

is there a SQL query to get the size of a users archive?

i know i can use a URL to get user archives but i want to select a specific set of users spread across various EV servers

anyone help here?

thanks

Operating Systems:

Comments 6 CommentsJump to latest comment

GTK's picture

thanks Rob

is there a SQL query that will search multiple vault stores rather than me having to select a specific vault store ?

Rob.Wilcox's picture

Suggest doing a UNION of that query, one for each vault store.  Sort of like this (but I haven't tried it)

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
UNION
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,
       yourNextVaultStoreDB.dbo.ArchivePoint AP,
       yourNextVaultStoreDB.dbo.Saveset S,
       yourNextVaultStoreDB.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
SOLUTION
GTK's picture

yep that works Rob, that will do , thanks

John Santana's picture

Thanks for sharing the solution here Rob !

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.