02-01-2018 02:20 PM
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!
Solved! Go to Solution.
02-01-2018 02:32 PM - edited 02-02-2018 07:36 AM
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
02-01-2018 02:32 PM - edited 02-02-2018 07:36 AM
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
02-13-2018 01:40 PM
Works great! Many thanks!