EV 10.0.4, Win 2008R2, MSSQL10
Looking for an SQL query that will provide the filesystem archivepoints and sizes of archived items related to each AP. The AP needs to be the name of the folder in question (not EV identification string). This is for billing purposes.
I realise this can be done through the usage reporter but want to automate a task (a powershell script) to gather this and other relevant billing/usage information from other sources.
I have tried using the following query as a base (as found at https://www-secure.symantec.com/connect/forums/sql-query-find-total-archived-files-and-size-each-target-fsa-file-server) but have had no joy in trying to modify it to my needs.
I guess the real question is what is the SQL query behind the usage reporter for a specific vaultstore ?
Any help would be greatly appreciated.
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
Would this query work for you?
You can use for one archive or for all as it is currently configured.
Run against the vault store db.
--DECLARE @ArchiveName Nvarchar(36)
--SET @ArchiveName = 'Archive1'
SUM(CAST(Saveset.ItemSize AS bigint))/1024/1024 "Size of Items (GB)"
JOIN EnterpriseVaultDirectory.dbo.Root RT on Archive.rootidentity = RT.rootidentity
JOIN ArchivePoint on RT.VaultEntryId = ArchivePoint.ArchivePointId
JOIN Saveset on ArchivePoint.ArchivePointIdentity = Saveset.ArchivePointIdentity
-- Archive.ArchiveName = @ArchiveName