Overall statistics with date filter
|
|
NOTE: This query should be run against the Enterprise Vault Vault Store Database. |
-- compressed size - SaveSet.ItemSize - Default size is in KiloBytes
-- uncompressed size - SavesetProperty.OriginalSize - Default size is in Bytes
-- Run this query over EV Item database
-- overall statistics with date filter
SELECT COUNT(*) AS TotalNumberOfArchives, SUM(tmp.CountOfarchivedItems) AS TotalCountOfItems, SUM(tmp.TotalCompressedSizeMB) AS TotalCompressedSizeMB,
SUM(tmp.TotalUncompressedSizeMB) AS TotalUncompressedSizeMB
FROM (
SELECT av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName, COUNT(s.SavesetIdentity) AS CountOfarchivedItems, SUM(CONVERT(BIGINT, s.ItemSize)) / 1024 AS TotalCompressedSizeMB,
SUM(CONVERT(BIGINT, sp.OriginalSize)) / 1024 / 1024 AS TotalUncompressedSizeMB
FROM dbo.ArchivePoint ap WITH(NOLOCK)
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView av ON ap.ArchivePointId = av.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry ve ON av.VaultStoreEntryId = ve.VaultStoreEntryId
LEFT JOIN dbo.Saveset s ON s.ArchivePointIdentity = ap.ArchivePointIdentity
LEFT JOIN dbo.SavesetProperty sp ON s.SavesetIdentity = sp.SavesetIdentity
WHERE s.IdDateTime BETWEEN '2015-01-01' AND '2015-12-31'
-- Enable row in case you want stats per single vault store
-- AND ve.VaultStoreName LIKE '%EV14VSG01VS01%'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
) AS tmp
Result:
Statistics per Enterprise Vault archive
|
|
NOTE: This query should be run against the Enterprise Vault Vault Store Database. |
-- compressed size - SaveSet.ItemSize - Default size is in KiloBytes
-- uncompressed size - SavesetProperty.OriginalSize - Default size is in Bytes
-- Run this query over EV Item database
-- statistics per EV archive
SELECT av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName, COUNT(s.SavesetIdentity) AS CountOfarchivedItems, SUM(CONVERT(BIGINT, s.ItemSize)) / 1024 AS TotalCompressedSizeMB,
SUM(CONVERT(BIGINT, sp.OriginalSize)) / 1024 / 1024 AS TotalUncompressedSizeMB
FROM dbo.ArchivePoint ap WITH(NOLOCK)
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView av ON ap.ArchivePointId = av.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry ve ON av.VaultStoreEntryId = ve.VaultStoreEntryId
LEFT JOIN dbo.Saveset s ON s.ArchivePointIdentity = ap.ArchivePointIdentity
LEFT JOIN dbo.SavesetProperty sp ON s.SavesetIdentity = sp.SavesetIdentity
-- Enable row in case you want stats for concrete archive(s)
-- WHERE av.ArchiveName like '%archive_Name%'
-- Enable row in case you want stats for single vault store
-- WHERE ve.VaultStoreName LIKE '%EV14VSG01VS01%'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
Result:
Statistics per Enterprise Vault archive with date filter
|
|
NOTE: This query should be run against the Enterprise Vault Vault Store Database. |
-- compressed size - SaveSet.ItemSize - Default size is in KiloBytes
-- uncompressed size - SavesetProperty.OriginalSize - Default size is in Bytes
-- Run this query over EV Item database
-- Statistics per EV archive with date filter
SELECT av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName, COUNT(s.SavesetIdentity) AS CountOfarchivedItems, SUM(CONVERT(BIGINT, s.ItemSize)) / 1024 AS TotalCompressedSizeMB,
SUM(CONVERT(BIGINT, sp.OriginalSize)) / 1024 / 1024 AS TotalUncompressedSizeMB
FROM dbo.ArchivePoint ap WITH(NOLOCK)
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView av ON ap.ArchivePointId = av.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry ve ON av.VaultStoreEntryId = ve.VaultStoreEntryId
LEFT JOIN dbo.Saveset s ON s.ArchivePointIdentity = ap.ArchivePointIdentity
LEFT JOIN dbo.SavesetProperty sp ON s.SavesetIdentity = sp.SavesetIdentity
WHERE s.IdDateTime BETWEEN '2015-01-01' AND '2015-12-31'
-- Enable row in case you want stats for concrete archive(s)
-- AND av.ArchiveName like '%archive_Name%'
-- Enable row in case you want stats for single vault store
-- AND ve.VaultStoreName LIKE '%EV14VSG01VS01%'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
Result:
Statistics of individual archive
|
|
NOTE: This query should be run against the Enterprise Vault Vault Store Database. |
-- compressed size - SaveSet.ItemSize - Default size is in KiloBytes
-- uncompressed size - SavesetProperty.OriginalSize - Default size is in Bytes
-- Run this query over EV Item database
-- statistics of individual archive
SELECT av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName, COUNT(s.SavesetIdentity) AS CountOfarchivedItems, SUM(CONVERT(BIGINT, s.ItemSize)) / 1024 AS TotalCompressedSizeMB,
SUM(CONVERT(BIGINT, sp.OriginalSize)) / 1024 / 1024 AS TotalUncompressedSizeMB
FROM dbo.ArchivePoint ap WITH(NOLOCK)
INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveView av ON ap.ArchivePointId = av.VaultEntryId
INNER JOIN EnterpriseVaultDirectory.dbo.VaultStoreEntry ve ON av.VaultStoreEntryId = ve.VaultStoreEntryId
LEFT JOIN dbo.Saveset s ON s.ArchivePointIdentity = ap.ArchivePointIdentity
LEFT JOIN dbo.SavesetProperty sp ON s.SavesetIdentity = sp.SavesetIdentity
WHERE av.ArchiveName like '%archive_name%'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
Result:
