000032492 - SQL query to get details from the named_arguments column for a Calculation Job in RSA Archer

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support on May 1, 2019
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000032492
Applies ToRSA Product Set: Archer
RSA Product/Service Type: Job Engine Manager
ResolutionFrom the Job Engine Manager, it's not possible to get more details about Calculation Jobs.  
The named_arguments column contains details like Level Id, a list of calculated Field Ids, and a list of Content Ids.  
The information is hard to get since the named_arguments type is XML, but is helpful for troubleshooting or before killing the job.

Run the following SQL query against the Instance database to get details on running calculation jobs.
 

;WITH XMLNAMESPACES('http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS a
,'http://www.w3.org/2001/XMLSchema-instance' AS i 
,'http://www.w3.org/2001/XMLSchema' AS x
,'http://schemas.microsoft.com/2003/10/Serialization/' AS z
,'http://schemas.datacontract.org/2004/07/System.Collections.Generic' AS g
,'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS d7p1
)

SELECT j.JobId, j.JobType, j.Endpoint AS Server, j.process_id AS ProcessId, j.JobOrder
, ai.StartTime, ai.EnqueueTime
, j.Priority, j.EnqueuedDate, j.Generation, j.ParentJobId, j.root_job_id AS RootJobId, j.job_num_id AS JobNumId
, mt.module_name AS ModuleName, lt.level_name AS LevelName
, j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:key[1])', 'nvarchar(max)') AS KeyName
, j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:value[1])', 'nvarchar(max)') AS KeyId
, j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[1]/a:_size[1])', 'nvarchar(max)') AS CalcFieldCount
, j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[2]/a:_size[1])', 'nvarchar(max)') AS ContentCount
, j.named_arguments.query('for $v in data(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[1]/a:_items[1]/*) return if ($v!=0) then $v else ""').value('.', 'nvarchar(max)') AS CalcFields
, j.named_arguments.query('for $v in data(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[2]/a:_items[1]/*) return if ($v!=0) then $v else ""').value('.', 'nvarchar(max)') AS ContentIds

FROM tblAsyncJobQueue j WITH (NOLOCK)
LEFT OUTER JOIN tblAsyncInstrumentation ai WITH (NOLOCK) ON ai.JobId = j.JobId
LEFT OUTER JOIN tblLevel l WITH (NOLOCK) ON l.level_id = j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:value[1])', 'nvarchar(max)')
LEFT OUTER JOIN tblLevelTranslation lt WITH (NOLOCK) ON lt.level_id = l.level_id
LEFT OUTER JOIN tblModuleTranslation mt WITH (NOLOCK) ON mt.module_id = l.module_id
 
WHERE j.JobType LIKE '%calculation%' AND Endpoint IS NOT NULL 
ORDER BY ai.StartTime

Attachments

    Outcomes