cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Veritas.com
Support

EV10 FSA - Archive sizes

ratty67
Not applicable

‎12-15-2014 07:41 PM

 

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
0 Kudos
1 REPLY 1

plaudone
Level 5
Employee Accredited Certified

‎12-16-2014 05:59 AM

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

 

0 Kudos