Statistics of individual 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 of individual 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 av.ArchiveName like '%archive_name%'
AND s.IdDateTime BETWEEN '2015-01-01' AND '2015-12-31'
GROUP BY av.ArchiveName, av.ArchiveTypeName, ve.VaultStoreName
Result:
Metalogix
The following queries can be used for Metalogix. All query output units are in MB.
|
|
NOTE: Query outputs utilize compressed values to determine licensing. |
1.Launch SQL Server Mgmt Studio from the MAM SQL server (or connect to EAS SQL from another machine that has SQL Server Mgmt Studio installed).
2.Expand the list in Object explorer to show the database names.
3.Locate the correct database name .
4.Right-click on that database and select New Query.
5.Copy and Paste the attached query text into the New Query Windows.
6.Hit Execute in the ribbon bar.
Get stats per archive
Use MAMEXCH --(If the database name is not MAMEXCH change that here)
SELECT em.DISPLAYNAME,
CAST(COUNT(ed.DOCSEQID) AS BIGINT) AS CountOfArchivedItems,
SUM(CAST(ed.ARCHIVE_SIZE AS BIGINT))/1024/1024 AS TotalCompressedSizeInMB,
SUM(CAST(ed.UNCOMPRESSED_SIZE AS BIGINT)) /1024/1024 AS TotalUncompressedInMB
FROM [EXCHANGEPAMMAILBOXES] em
LEFT JOIN [EXCHANGEPAMDOCUMENTS] ed ON ed.MBXID = em.MBXID
WHERE em.MBXID > 0
GROUP BY em.DISPLAYNAME
Result:
Get stats per archive with ReceivedDate
Use MAMEXCH --(If the database name is not MAMEXCH change that here)
SELECT em.DISPLAYNAME,
CAST(COUNT(ed.DOCSEQID) AS BIGINT) AS CountOfArchivedItems,
SUM(CAST(ed.ARCHIVE_SIZE AS BIGINT))/1024/1024 AS TotalCompressedSizeInMB,
SUM(CAST(ed.UNCOMPRESSED_SIZE AS BIGINT)) /1024/1024 AS TotalUncompressedInMB,
Min(ed.RECEIVED) AS OldestReceivedDate,
Max(ed.RECEIVED) AS YoungestReceivedDate
FROM [EXCHANGEPAMMAILBOXES] em
LEFT JOIN [EXCHANGEPAMDOCUMENTS] ed ON ed.MBXID = em.MBXID
WHERE em.MBXID > 0
GROUP BY em.DISPLAYNAME
Result:
