cancel
Showing results for 
Search instead for 
Did you mean: 

EV10 FSA - Archive sizes

 

EV 10.0.4, Win 2008R2, MSSQL10

Hi,

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
1 Reply

Hi ratty67, Would this query

Hi ratty67,

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'
 
SELECT 
    Archive.ArchiveName, 
    ArchivePoint.ArchivePointId,
    COUNT(*)Items,
    SUM(CAST(Saveset.ItemSize AS bigint))/1024/1024 "Size of Items (GB)"
FROM
    EnterpriseVaultDirectory.dbo.Archive Archive
    JOIN EnterpriseVaultDirectory.dbo.Root RT on Archive.rootidentity = RT.rootidentity
    JOIN ArchivePoint on RT.VaultEntryId = ArchivePoint.ArchivePointId
    JOIN Saveset on ArchivePoint.ArchivePointIdentity = Saveset.ArchivePointIdentity
--WHERE    
--        Archive.ArchiveName = @ArchiveName
GROUP BY 
    ArchiveName,
    ArchivePoint.ArchivePointId