Useful SQL Query - Move Archive Details (Intra-site moves)

[Chris_Warren]

All - I know it's been awhile, but this took a bit to sort out. The purpose of this query was to get all necessary information from the EnterpriseVaultDirectory when pertaining to EV Move Archive. This was tested and should be very helpful with reviewing the details of multiple single site (Intra-site) moves.

(Note: As we cannot take into consideration every environment, please run this query with caution)

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

USE EnterpriseVaultDirectory
SELECT AV2.ArchiveName AS "NewSrcArchiveNameAV"
,AV1.ArchiveName AS "DestArchiveNameAV"
,CASE WHEN Progress IS NOT null THEN 'MoveArchiveSubtask' ELSE 'ShortcutUpdateSubTask' END AS "TaskType"
,CASE WHEN EME1.MoveArchiveShortcutSubtask IS NOT null THEN EME1.MoveArchiveShortcutSubtask ELSE NULL END AS "MAShortcutSubtask_EME"
,STP.State
,CASE WHEN STP.State = '0' THEN 'Stopped'
      WHEN STP.State = '1' THEN 'Running'
      WHEN STP.State = '2' THEN 'Paused'
      WHEN STP.State = '3' THEN 'Disabled'
      WHEN STP.State = '4' THEN 'Loading'
      WHEN STP.State = '5' THEN 'Stopping'
      WHEN STP.State = '6' THEN 'Failed'
      WHEN STP.State = '7' THEN 'Refreshing'
      WHEN STP.State = '8' THEN 'SvcNotRunning'
      WHEN STP.State = '9' THEN 'Error'
      WHEN STP.State = '10' THEN 'Completed'
      WHEN STP.State = '11' THEN 'CompletedWithErrors'
      WHEN STP.State = '12' THEN 'Queued'
      WHEN STP.State = '13' THEN 'NotInSched'
      WHEN STP.State = '14' THEN 'WaitingForUser'      
      ELSE 'N/A' END as StateDesc          
,SubTaskEntryid
,STP.*, AV1.ArchiveStatus AS "DestArchiveStateAV", AV1.VaultEntryId AS "DestArchiveID_AV"
,AV2.ArchiveStatus AS "SrcArchiveStateAV", AV2.VaultEntryId AS "SrcArchiveID_AV"
,CASE WHEN EME1.DefaultVaultId IS NOT null THEN 'Assigned' ELSE 'N/A' END AS "Dest_Assigned"
,CASE WHEN EME2.DefaultVaultId IS NOT null THEN 'Assigned' ELSE 'N/A' END AS "Src_Assigned"
FROM (SELECT 
cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/sourceArchiveName)') as varchar(50)) as sourceArchiveNamePtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/srcArchiveVEID)') as varchar(50)) as srcArchiveVEIDPtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destVaultStoreId)') as varchar(50)) as destVaultStoreIdPtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/srcSiteEID)') as varchar(50)) as srcSiteEIDPtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destSiteEID)') as varchar(50)) as destSiteEIDPtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/sourceArchiveType)') as varchar(50)) as TypePtr
,REPLACE (REPLACE (cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/sourceArchiveType)') as varchar(50)),'9','ExchMbx'),'17','ExchJrnl') as SrcArchiveTypePtr
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/srcServername)') as varchar(50)) as srcServernamePtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destServername)') as varchar(50)) as destServernamePtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destSiteName)') as varchar(50)) as destSiteNamePtr
,REPLACE (REPLACE (cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destArchiveName)') as varchar(50)),'&lt;','<'),'&gt;','>') as DestArchiveNamePtr
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/srcVaultStore)') as varchar(50)) as srcVaultStorePtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/destVaultStore)') as varchar(50)) as destVaultStorePtr 
,cast(CAST(Parameters as XML).query('data(/MoveArchiveSubTaskParameters/startTime)') as varchar(50)) as startTimePtr

,PhaseProg = substring(Progress, (charindex(';', Progress)+1),
      (charindex(';', Progress)-(charindex(';', Progress)))+7)
,DestVEIdProg = SUBSTRING(Progress,(CHARINDEX('DestVEId=',Progress) + LEN('DestVEId=')), 
    CHARINDEX(';',Progress,CHARINDEX('DestVEId=',Progress) + LEN('DestVEId=')) - (CHARINDEX('DestVEId=',Progress) + LEN('DestVEId=')) )
,TotalItemsProg = SUBSTRING(Progress,(CHARINDEX('TotalItems=',Progress) + 11), 
    CHARINDEX(';',Progress,CHARINDEX('TotalItems=',Progress) + 11) - (CHARINDEX('TotalItems=',Progress) + LEN('TotalItems=')) )
,ProcessedProg = SUBSTRING(Progress,(CHARINDEX('Processed=',Progress) + LEN('Processed=')), 
    CHARINDEX(';',Progress,CHARINDEX('Processed=',Progress) + LEN('Processed=')) - (CHARINDEX('Processed=',Progress) + LEN('Processed=')) )
,CopyErrorsProg = SUBSTRING(Progress,(CHARINDEX('Errors=',Progress) + LEN('Errors=')), 
    CHARINDEX(';',Progress,CHARINDEX('Errors=',Progress) + LEN('Errors=')) - (CHARINDEX('Errors=',Progress) + LEN('Errors=')) )
,SCToUpdateProg = SUBSTRING(Progress,(CHARINDEX('SCToUdate=',Progress) + LEN('SCToUdate=')), 
    CHARINDEX(';',Progress,CHARINDEX('SCToUdate=',Progress) + LEN('SCToUdate=')) - (CHARINDEX('SCToUdate=',Progress) + LEN('SCToUdate=')) )
,SCUpdatedProg = SUBSTRING(Progress,(CHARINDEX('SCUpdated',Progress) + 10), 
    CHARINDEX(';',Progress,CHARINDEX('SCUpdated',Progress) + 10) - (CHARINDEX('SCUpdated',Progress) + 10) )
,SCFailedProg = SUBSTRING(Progress,(CHARINDEX('SCFailed',Progress) + 9), 
    CHARINDEX(';',Progress,CHARINDEX('SCFailed',Progress) + 9) - (CHARINDEX('SCFailed',Progress) + 9) )
,Description
,Progress
,Parameters
,SubTaskEntryId
,State
From SubTask ST) STP
LEFT OUTER JOIN
EnterpriseVaultDirectory.dbo.ArchiveView AV1 ON AV1.VaultEntryId = STP.DestVEIdProg LEFT OUTER JOIN
EnterpriseVaultDirectory.dbo.ArchiveView AV2 ON AV2.VaultEntryId = STP.srcArchiveVEIDPtr LEFT OUTER JOIN
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME1 on STP.DestVEIdProg = EME1.DefaultVaultId LEFT OUTER JOIN
EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME2 on STP.srcArchiveVEIDPtr = EME2.DefaultVaultId
WHERE Description LIKE 'Move Archive%'
Order by STP.sourceArchiveNamePtr

-- End Query

Information Gathered:

a. NewSrcArchiveNameAV - The source archive name, which should have the open-close date appended (From ArchiveView)
b. DestArchiveNameAV - The destination archive name (From ArchiveView).
c. TaskType - Whether or not the Subtask entry is a Movebox move task or a ShortcutUpdateSubtask
d. MAShortcutSubtask_EME - The SubtaskEntryID associated with the enabled user in the ExchangeMailboxEntry table. This MUST match the SubtaskEntryid for the moves ShortcutUpdateSubtask entry.
e. State - Number value for Task State (1,6,10, etc)
f. StateDesc - State Description of Number from State (Running, Failed,Completed, etc)
g. SubTaskEntryid - Unique Task Entry ID as referenced in Subtask table.
h. srcArchiveVEIDPtr - Source Archive ID as referenced in the Parameters XML in Subtask table (SrcArchiveVEID).
i. destVaultStoreIdPtr - Destination VaultStoreId as referenced in the Parameters XML in Subtask table (destVaultStoreId).
j. srcSiteEIDPtr - Source Site Entry ID as referenced in the Parameters XML in Subtask table (srcSiteEID).
k. destSiteEIDPtr - Destination Site Entry ID as referenced in the Parameters XML in Subtask table (destSiteEID).
l.TypePtr - Number value of Archive Type being moved as referenced in the Parameters XML in Subtask table (Type).
m. SrcArchiveTypePtr - Translation of Archive Type. Will state if ExchMbx or ExchJrnl.
n. srcServernamePtr - Source Servername as referenced in the Parameters XML in Subtask table (srcServername).
o. destServernamePtr - Destination Servername as referenced in the Parameters XML in Subtask table (destServername).
p. destSiteNamePtr - Destination Site Name as referenced in the Parameters XML in Subtask table (destSiteName).
q. DestArchiveNamePtr - Destination Archive Name as referenced in the Parameters XML in Subtask table. Note: If New Archive was selected, rather than an existing destination archive, this will return "<New Archive>".
r. srcVaultStorePtr - Source Vault Store Name as referenced in the Parameters XML in Subtask table (srcVaultStore).
s. destVaultStorePtr - Destination Vault Store Name as referenced in the Parameters XML in Subtask table (destVaultStore).
t. startTimePtr - Original Start Time of Move as referenced in the Parameters XML in Subtask table (startTime).
u. PhaseProg - Phase of Move (1-5) as referenced in the Progress value (Phase).
v. DestVEIdProg - Destination ArchiveID as referenced in the Progress value (DestVEId).
w. Progress Statistics (Total Items, Processed, CopyErrors, SCToUpdate, SCUpdated, SCFailed)
   TotalItemsProg - Total Items in source archive as referenced in the Progress value.
   ProcessedProg - Number of items processed (copied) as referenced in the Progress value.
   CopyErrorsProg - Number of items which failed to copy as referenced in the Progress value.
   SCToUpdateProg - Number of Shortcuts identified in mailbox to be updated as referenced in the Progress value.
   SCUpdatedProg - Number of shortcuts updated to new archive as referenced in the Progress value.
   SCFailedProg - Number of shortcuts which failed to be updated as referenced in the Progress value.
x. Description - Description of Task in Subtask table.
y. Progress - Full semi-colon delimited entry of Process value in Subtask table (Note: A Shortcut Update Subtask does not have a Progress entry).
z. Parameters - Full XML value of Move and Shortcut Update Parameters value in Subtask table.

i. State - See value 'e' above.
ii. DestArchiveStateAV - Destination Archive State as referenced in ArchiveView (Normally will = 1 (open)).
iii. DestArchiveID_AV - Destination Archive ID as referenced in ArchiveView.
iv. SrcArchiveStateAV - Source Archive State as referenced in ArchiveView (Normally will = 3 (closed)).
v. SrcArchiveID_AV - Source Archive ID as referenced in ArchiveView.
vi. Dest_Assigned - Will reference 'Assigned' if the Destination Archive is assigned to the user (ExchangeMailboxEntry Table).
vii. Src_Assigned - Will reference 'Assigned' if the Source Archive is assigned to the user (ExchangeMailboxEntry Table)

I apologize if this is information overload, but I found this very useful when troubleshooting Move Archive issues.

Enjoy!

[GertjanA]

Hello Chris,

I assume this needs to be run on the source EV server (if for intra-site moves)?

If so, I recieved an error when running it. (EV11.0.1 CHF2) (using SQL2008R2)

Msg 537, Level 16, State 3, Line 5

Invalid length parameter passed to the LEFT or SUBSTRING function.