cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Warren
Level 5
Employee Accredited Certified

All - Awhile back I posted a Query to pull the details and statistics for Vault Cache Content Cache (CC) download status'. I have since added details gathered by the ClientDiagnostics.aspx page introduced in 10.0.3. While you can go to an interactive page to view the ClientDiagnostic results, this may help to compare between the CC download status and the overall health of an individual VC:

As with all SQL Queries, this is presented as is and so must be run with caution.

USE EnterpriseVaultDirectory
SELECT
AV.ArchiveName,
HC.ComputerName AS UserCompName,
HC.OLVer AS OutlookVersion,
CASE WHEN HC.OLVer LIKE '12%' THEN 'Outlook 2007'
     WHEN HC.OLVer LIKE '14%' THEN 'Outlook 2010'
     WHEN HC.OLVer LIKE '15%' THEN 'Outlook 2013'
     End AS OLGeneralVer,
HC.AddInVer,
VCD.HostUserName,
VCD.LogDate AS LastVCUpdate_LogDate,
VCD.MDCState,
CASE WHEN VCD.MDCState = '0' THEN 'Success/Complete'
                WHEN VCD.MDCState = '10' THEN 'Status-None'
                WHEN VCD.MDCState = '19' THEN 'Pending'
                WHEN VCD.MDCState = '20' THEN 'In Progress'
                WHEN VCD.MDCState = '30' THEN 'In Progress-Acquire Slot'
                WHEN VCD.MDCState = '31' THEN 'In Progress-Acquire Slot-Waiting'
                WHEN VCD.MDCState = '2147483645' THEN 'Status-Reset'
                WHEN VCD.MDCState = '2147483646' THEN 'Status-Suspended'
                WHEN VCD.MDCState = '2147483647' THEN 'Status-Shutdown'
     WHEN VCD.MDCState = '-1' THEN 'Failed'
     WHEN VCD.MDCState = '-2' THEN 'Failed-Dirty'
     WHEN VCD.MDCState = '-3' THEN 'Failed-Offline'
     WHEN VCD.MDCState = '-4' THEN 'Failed-CouldNotConnectToWebServer'
     WHEN VCD.MDCState = '-5' THEN 'Failed-Slot'
     WHEN VCD.MDCState = '-6' THEN 'Failed-InsufficientDiskSpace'
     WHEN VCD.MDCState = '-7' THEN 'Prevented'
     WHEN VCD.MDCState = '-8' THEN 'Failed-ServerSynching' 
     WHEN VCD.MDCState = '-9' THEN 'Failed-EmptyArchive'
     END AS MDCStateDesc,
VCD.ContentCacheState AS VCD_CCState,
CASE WHEN VCD.ContentCacheState = '0' THEN 'Initializing'
                WHEN VCD.ContentCacheState = '1' THEN 'Never Downloaded'
                WHEN VCD.ContentCacheState = '2' THEN 'No Download'
                WHEN VCD.ContentCacheState = '3' THEN 'Complete'
                WHEN VCD.ContentCacheState = '4' THEN 'DB Building'
                WHEN VCD.ContentCacheState = '5' THEN 'DB Downloading'
                WHEN VCD.ContentCacheState = '100' THEN 'Retry WebServer'
                WHEN VCD.ContentCacheState = '200' THEN 'BITs Not Available'
                WHEN VCD.ContentCacheState = '300' THEN 'Failed First DBBuild'
     WHEN VCD.ContentCacheState = '301' THEN 'Failed Next DBBuild'
     WHEN VCD.ContentCacheState = '302' THEN 'Failed BITs Download'
     WHEN VCD.ContentCacheState = '303' THEN 'Failed Process Next Archive'
     WHEN VCD.ContentCacheState = '304' THEN 'Failed Adding Archives'
     WHEN VCD.ContentCacheState = '305' THEN 'Failed Web Server'
     WHEN VCD.ContentCacheState = '400' THEN 'Failed-Generic'
     END AS CCStateDesc,
VCD.NumCCItems,
VCD.NumItemsToDownload AS ItemsToDL,
EME.DefaultVaultId AS ArchiveID,
EME.MbxNTUser,
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.JobAuthor AS AJE_JobAuthor,
AJE.UniqueJobId AS AJE_JobID,

AJE.JobType AS AJE_JobType,
CASE WHEN AJE.JobType = '8' THEN 'CC_Reg_Build' 
                 WHEN AJE.JobType = '9' THEN 'CC Inc Build' 
                 END AS AJE_JobTypeDesc,
AJE.JobState AS 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 AJE_CCJobStateDesc,
AJE.JobDescription AS AJE_JobDesc, 
AJE.RetryCount AS AJE_RetryCount
--,AJE.Parameters

FROM AdminJobEntry AJE
RIGHT OUTER JOIN ExchangeMailboxEntry EME on AJE.JobAuthor = EME.MbxNTDomain + '\' + EME.MbxNTUser
LEFT JOIN ArchiveView AV on EME.DefaultVaultId = AV.VaultEntryID
LEFT JOIN ClientVCDiagnostics VCD on AV.RootIdentity = VCD.ArchiveIdentity
LEFT JOIN HostComputer HC on VCD.HostComputerIdentity = HC.ComputerIdentity

WHERE
--Uncomment one of the below clauses based on need.


--VCD.ArchiveIdentity IS NOT NULL
--AV.ArchiveName = 'user1'
--AJE.JobType IN (8,9) 
--AND AJE.Retrycount > 0

 

Comments
Chris_Warren
Level 5
Employee Accredited Certified

Note: Recently found a VCD.MDCState value not previously documented.

For the query above, insert line beneath line 31 (WHEN VCD.MDCState = '-9' THEN 'Failed-EmptyArchive')

Add the following:

WHEN VCD.MDCState = '-10' THEN 'Failed-CouldNotAuthenticate_EV'

 

So this section should read:

     WHEN VCD.MDCState = '-1' THEN 'Failed'
     WHEN VCD.MDCState = '-2' THEN 'Failed-Dirty'
     WHEN VCD.MDCState = '-3' THEN 'Failed-Offline'
     WHEN VCD.MDCState = '-4' THEN 'Failed-CouldNotConnectToWebServer'
     WHEN VCD.MDCState = '-5' THEN 'Failed-Slot'
     WHEN VCD.MDCState = '-6' THEN 'Failed-InsufficientDiskSpace'
     WHEN VCD.MDCState = '-7' THEN 'Prevented'
     WHEN VCD.MDCState = '-8' THEN 'Failed-ServerSynching' 
     WHEN VCD.MDCState = '-9' THEN 'Failed-EmptyArchive'
     WHEN VCD.MDCState = '-10' THEN 'Failed-CouldNotAuthenticate_EV'
     END AS MDCStateDesc

Version history
Last update:
‎10-28-2014 04:19 PM
Updated by: