Useful SQL Query (Vault Cache CC Download Status)

[Chris_Warren]

All - Been awhile but I figured I'd provide this query.  In later versions of Enterprise Vault, the ClientDiagnostics.aspx page has been included to provide admin-level information on Vault Cache/Virtual Vault for an environment.  Prior to this however, it was necessary to sift through VCView, IIS logs and the EnterpriseVaultDirectory for details on how Vault Cache was working for end users.  I put together this query recently to parse through the AdminJobEntry table to get Content Cache (CC) job details for users.  It was useful to me so I present to you!

I plan to eventually go through the ClientDiagnostic info to see if I can tie it into this, but I felt this was helpful as a standalone query.

Notes:
a. As we cannot take into consideration every environment, please run these queries with caution as they MAY cause performance issues while being run.
b. The AdminJobEntry Table tracks Migrator jobs (V-Content Cache jobs and PST jobs).  This table does not track Header updates (MDC) details.

-- Start Query
-- Prerequisite : Must be run on SQL instance with EnterpriseVaultDirectory DB

USE EnterpriseVaultDirectory
SELECT
AV.ArchiveName,
IV.IndexedItems,
EME.DefaultVaultId,
EME.MbxNTUser,
AJE.JobAuthor,
AJE.UniqueJobId,
CASE WHEN AV.ArchiveStatus = '1' THEN 'Archive Enabled'
  WHEN AV.ArchiveStatus = '3' THEN 'Archive Disabled/Closed'
  WHEN AV.ArchiveStatus = '4' THEN 'Marked for Deletion'
  ELSE 'Other' END AS ArchiveStatus,
AJE.JobType,
CASE WHEN AJE.JobType = '8' THEN 'CC_Reg_Build'
  WHEN AJE.JobType = '9' THEN 'CC Inc Build'
  END AS TypeDesc,
AJE.JobState,
CASE WHEN AJE.JobState = '0' THEN 'NONE'
     WHEN AJE.JobState = '1' THEN 'Completed'
     WHEN AJE.JobState = '2' THEN 'Failed'
     WHEN AJE.JobState = '3' THEN 'Queued'
     WHEN AJE.JobState = '4' THEN 'Busy'
     WHEN AJE.JobState = '5' THEN 'Incomplete'
     WHEN AJE.JobState = '6' THEN 'Aborted'
     WHEN AJE.JobState = '7' THEN 'New'
     WHEN AJE.JobState = '8' THEN 'Suspended' END AS StateDesc,
AJE.JobDescription,
AJE.RetryCount
,AJE.Parameters

FROM AdminJobEntry AJE
LEFT JOIN ExchangeMailboxEntry EME on AJE.JobAuthor = EME.MbxNTDomain + '\' + EME.MbxNTUser
LEFT JOIN ArchiveView AV on EME.DefaultVaultId = AV.VaultEntryID
LEFT JOIN IndexView IV on EME.DefaultVaultId = IV.VaultEntryId

WHERE AJE.JobType IN (8,9)
ORDER BY AJE.JobState
--AND Retrycount > 0

Information Gathered:

a. Archive Name
b. IndexItems - I added this to give a genral idea how many total items are in the associated archive.
NOTE: The Parameters value can provide a 'totalitems' value.  This will not match with the IndexItems as the CC Job will build in 'chunks'. 
c. DefaultVaultID (Archive ID for the Archive)
d. MbxNTUser associated with Archive
e. JobAuthor - User account which requested the CC download.
f. UniqueJobId - Shows that it is a VaultCacheJob.
g. ArchiveStatus - pulled from the ArchiveView, translated to the archive state (Enabled/Closed/Marked for Deletion).
h. JobType - The type of CC job it is; CC Regular (Initial) build (8) or Incremental build (9).
i. TypeDesc - Translates JobType.
j. JobState - The current state of the CC job.
k. StateDesc - Translation of JobState.
l. JobDescription - Description of the CC job.
m. RetryCount - How many times the job was retried. (Note: When RetryCount = 3, then the JobState will become Aborted - 6.)
NOTE: A Good way to identify problematic CC updates is to include "AND RetryCount > 0' in the WHERE clause. High retrycounts can indicate a problem.
n. Parameters - Shows the latest xml provided to determine the current CC download information.
NOTE: Since CC Jobs are incremental, the Parameters value gives information on the current job being performed, not the total for the entire Content Cache.

Enjoy!