Enterprise Vault™ Auditing

Example query search for general item delete audit entries

The following simple query retrieves from the audit database all Search and Delete entries within a specified time period.

USE EnterpriseVaultAudit
SELECT *  FROM [EnterpriseVaultAudit].[dbo].[EVAuditView]
WHERE CategoryName in ('Search', 'Delete')
AND AuditDate BETWEEN '2017-10-05 08:27:48' and '2017-10-05 08:32:37'
ORDER BY AuditID desc

The following SQL query extends this simple query to filter on archive also. The archive information is stored in the Enterprise Vault directory.

DECLARE @ArchiveId varchar(112)
DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime

SET @ArchiveId = '1B29F35DAA512AC47A64558FDF7A614571110000example.local'
SET @StartDateTime = '2017-10-05 08:27:48'
SET @EndDateTime = '2017-10-05 08:28:37'

CREATE TABLE #ArchiveFolders
(
       VaultEntryId varchar(112)
)

INSERT INTO #ArchiveFolders
SELECT VaultEntryId 
FROM [EnterpriseVaultDirectory].[dbo].[ArchiveFolderView]
WHERE ArchiveVEID = @ArchiveId

SELECT *  FROM [EnterpriseVaultAudit].[dbo].[EVAuditView] 
auditView LEFT JOIN #ArchiveFolders archFolder
ON archFolder.VaultEntryId = auditView.Vault
WHERE AuditDate BETWEEN @StartDateTime and @EndDateTime
AND CategoryName in ('Search', 'Delete')
ORDER BY AuditID

DROP TABLE #ArchiveFolders

Table: Example audit entry values returned by the SQL query shows example data returned by the SQL query of the audit database. The column titles relate to the database view, EVAuditView, in the audit database. The values in the column, Example values (Search), show an audit entry created by the initial search for the items to delete. The values in the column, Example values (Delete), show an audit entry created when the user, jdoe, deleted an item.

Given the steps in Table: Steps to provide evidence of item deletion, there would also be an audit entry for the final search showing that the item no longer exists. This audit entry is not included in Table: Example audit entry values returned by the SQL query.

See the Appendix to this document for a description of the format of audit database entries, and an explanation of the values in the EVAuditView columns for different types of audit entry.