SQL Query For EV - Date Range

[JGrilli]

Hi to all,

Inquiring if someone has a SQL Query for EV to find the total amount of archived data for a date range...for example, all data archived from Jan 1, 2017 to Jan 1, 2018.  This would be for all archive types and across all VS DBs if possible (If I have to run it against multiple DBs that's not an issue.)

Many thanks in advance!

[dcVAST · Accepted solution]

Use YouVaultStore

DECLARE @Date1 datetime
DECLARE @Date2 datetime
DECLARE @ArchiveName Nvarchar(36)
/* Set data range*/
SET @Date1 = '1990-01-01'
SET @Date2 = GETDATE()
SET @ArchiveName = NULL /*Add Archive Name if needed*/

 
SELECT
    Archive.ArchiveName,
    ArchivePoint.ArchivePointId,
    @Date1 AS DateFrom, @Date2 as DateTo,
    COUNT(*)Items,
    SUM(ItemSize) AS DVSSize_kb,
    SUM(OriginalSize)/1024 AS OrignalSize_KB
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
    JOIN SavesetProperty SP on Saveset.SavesetIdentity = SP.SavesetIdentity
WHERE    
    Saveset.ArchivedDate > @Date1
    AND Saveset.ArchivedDate < @Date2  
    AND Archive.ArchiveName =  ISNULL(@ArchiveName, Archive.ArchiveName)
        
GROUP BY
    ArchiveName,
    ArchivePoint.ArchivePointId

[JGrilli]

Works great! Many thanks!