Reporting Enterprise Vault data based on date in Archive Shuttle database

Overview

In order to review large Enterprise Vault archives based on dates, Archive Shuttle database may be queried.

Prerequisites

1. ContainerId of the Source Enterprise Vault Archive from Bulk Mapping screen (column Container Id to be added from Grid->Columns)
2. 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

1. 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

2. 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.