Overview
In order to review large Enterprise Vault archives based on dates, Archive Shuttle database may be queried.
Prerequisites
- ContainerId of the Source Enterprise Vault Archive from Bulk Mapping screen (column Container Id to be added from Grid->Columns)
- LinkId of the Source Enterprise Vault Archive from Bulk Mapping screen (column Link Id to be added from Grid->Columns)
ContainerId is needed in queries below, run against ArchiveShuttleItem_LinkId databases, where LinkId is the guiID of the Archive Shuttle Item database.
Solution
- Item count per year for item date
SELECT COUNT(itemid) AS 'ItemCount', LEFT(CONVERT (VARCHAR, dateutc,20),4) AS 'DateUtc' FROM item WHERE ContainerId = 'ContainerId' GROUP BY LEFT(CONVERT (VARCHAR, dateutc,20),4) ORDER BY dateutc
- Item count per year for archived date
SELECT COUNT(itemid), LEFT(CONVERT (VARCHAR, ArchivedDateUtc,20),4) as 'ArchivedDateUtc' FROM itementerprisevault WHERE itemid IN (SELECT itemid FROM item WHERE containerid = 'ContainerId') GROUP B LEFT(CONVERT (VARCHAR, ArchivedDateUtc,20),4) ORDER BY ArchivedDateUtc
In order to count item count per month or per day LEFT statement should be changed as follows:
- Per month
LEFT(CONVERT (VARCHAR, dateutc,20),4) -> LEFT(CONVERT (VARCHAR, dateutc,20),7)
- Per day
LEFT(CONVERT (VARCHAR, dateutc,20),4) -> LEFT(CONVERT (VARCHAR, dateutc,20),10)
Similar results should be achieved by querying Enterprise Vault SQL database directly. Please review knowledge base article for more information.