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.

Print Friendly, PDF & Email