Video Screencast Help

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.