Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

SQL Query to find total archived files and size for each Target FSA File server

Created: 14 Nov 2012 • Updated: 15 Nov 2012 | 2 comments
This issue has been solved. See solution.

Hi

Can anyone pelase help with a SQL query

I need a SQL Query to find total archived files and size for each Target FSA File server please.

Not per archive basis, per file server and for all archive points within each file server.

Many Thanks in advance

Comments 2 CommentsJump to latest comment

JesusWept3's picture

I *think* this query should work, its not tested but it works in theory, change "YourVaultStore" to the name of your vault store db that holds FSA Archives 

SELECT FSE.DnsName "File Server",
       COUNT(S.IdTransaction) "Items Archived",
       SUM(CAST(S.ItemSize AS bigint))/1024/1024 "Size of Items (GB)"
FROM   EnterpriseVaultDirectory.dbo.FileServerEntry FSE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeEntry FSVE,
       EnterpriseVaultDirectory.dbo.FileServerVolumeArchiveEntry FSVAE,
       YourVaultStore.dbo.ArchivePoint AP,
       YourVaultStore.dbo.Saveset S
WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND  AP.ArchivePointId = FSVAE.ArchiveVEID
  AND  FSVAE.VolumeEntryId = FSVE.VolumeEntryId
  AND  FSVE.FileServerEntryId = FSE.FileServerEntryId
GROUP BY FSE.DnsName
SOLUTION