How To Get A Report For Added Queue Items That Are Processed (Completed) For Every Job Run Using A SQL Query?

How to get a report for added queue items that are processed (Completed) for every job run using a SQL query?

Issue / Requirement Description: How to get a report for added queue items that are processed (Completed) for every job run using a SQL query?

Resolution:
Note: First, try to achieve the requirement by the available Orchestrator Rest API endpoints (https://ORCHESTRATOR_HOSTNAME/swagger/index.html). It is suggested to get the help of a Database expert as UiPath never recommends any DB interaction.

Use and re-adapt the below query script based on the internal requirements:

SELECT B.[Name] as Robot,

J.Id as JobID,

CONVERT(varchar(200),R.[Name]) + CASE WHEN CHARINDEX('ServerList',CONVERT(varchar(300),J.InputArguments)) > 0 THEN '-'+LEFT(SUBSTRING(CONVERT(varchar(300),J.InputArguments),16,999),LEN(SUBSTRING(CONVERT(varchar(300),J.InputArguments),16,999)) - 2) ELSE '' END as ProcessName,

M.Name as MachineName,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.StartTime) as Started,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.EndTime) as Finish,

J.StartTime as 'StartTime in UTC',

CASE WHEN J.[State] = 0 THEN 'Queued'

WHEN J.[State] = 1 THEN 'Running'

WHEN J.[State] = 2 THEN 'Stopping'

WHEN J.[State] = 3 THEN 'Terminating'

WHEN J.[State]= 4 THEN 'Failed'

WHEN J.[State]= 5 THEN 'Completed'

WHEN J.[State] = 6 THEN 'Canceled'

ELSE CONVERT(varchar(10),J.State) END as JobStatus,

J.InputArguments

FROM Jobs J

INNER JOIN Robots AS B ON J.RobotId = B.[Id]

INNER JOIN Machines AS M ON J.MachineId = M.[Id]

INNER JOIN Releases AS R ON J.ReleaseId = R.[Id]

INNER JOIN QueueItems AS Q ON J.Id = Q.[ExecutorJobId]

WHERE J.TenantId = 1 AND J.[State]= 5

ORDER BY JobID DESC

The following is the list of state codes used in the dbo.jobs table:

  • Pending -> 0
  • Running -> 1
  • Stopping -> 2
  • Terminating -> 3
  • Faulted -> 4
  • Successful -> 5
  • Stopped -> 6

Example results:


====

How to get a report for added queue items for every job run?

Use and re-adapt the below query script based on your internal requirements:

SELECT B.[Name] as Robot,

J.Id as JobID,

CONVERT(varchar(200),R.[Name]) + CASE WHEN CHARINDEX('ServerList',CONVERT(varchar(300),J.InputArguments)) > 0 THEN '-'+LEFT(SUBSTRING(CONVERT(varchar(300),J.InputArguments),16,999),LEN(SUBSTRING(CONVERT(varchar(30 0),J.InputArguments),16,999)) - 2) ELSE '' END as ProcessName,

M.Name as MachineName,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.StartTime) as Started,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.EndTime) as Finish,

J.StartTime as 'StartTime in UTC',

CASE WHEN J.[State] = 0 THEN 'Queued'

WHEN J.[State] = 1 THEN 'Running'

WHEN J.[State] = 2 THEN 'Stopping'

WHEN J.[State] = 3 THEN 'Terminating'

WHEN J.[State]= 4 THEN 'Failed'

WHEN J.[State]= 5 THEN 'Completed'

WHEN J.[State] = 6 THEN 'Canceled'

ELSE CONVERT(varchar(10),J.State) END as JobStatus,

J.InputArguments

FROM Jobs J

INNER JOIN Robots AS B ON J.RobotId = B.[Id]

INNER JOIN Machines AS M ON J.MachineId = M.[Id]

INNER JOIN Releases AS R ON J.ReleaseId = R.[Id]

INNER JOIN QueueItems AS Q ON J.Id = Q.[ExecutorJobId]

WHERE J.TenantId = 1

ORDER BY JobID DESC

Example results:

3.png

====

How to retrieve the job details with Robot and machine names using a database query?

Use and re-adapt the below query script based on your internal requirements:

SELECT B.[Name] as Robot,

J.Id as JobID,

CONVERT(varchar(200),R.[Name]) + CASE WHEN CHARINDEX('ServerList',CONVERT(varchar(300),J.InputArguments)) > 0 THEN '-'+LEFT(SUBSTRING(CONVERT(varchar(300),J.InputArguments),16,999),LEN(SUBSTRING(CONVERT(varchar(300),J.InputArguments),16,999)) - 2) ELSE '' END as ProcessName,

M.Name as MachineName,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.StartTime) as Started,

DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.EndTime) as Finish,

J.StartTime as 'StartTime in UTC',

CASE WHEN J.[State] = 0 THEN 'Queued'

WHEN J.[State] = 1 THEN 'Running'

WHEN J.[State] = 2 THEN 'Stopping'

WHEN J.[State] = 3 THEN 'Terminating'

WHEN J.[State]= 4 THEN 'Failed'

WHEN J.[State]= 5 THEN 'Completed'

WHEN J.[State] = 6 THEN 'Canceled'

ELSE CONVERT(varchar(10),J.State) END as JobStatus,

J.InputArguments

FROM Jobs J

INNER JOIN Robots AS B ON J.RobotId = B.[Id]

INNER JOIN Machines AS M ON J.MachineId = M.[Id]

INNER JOIN Releases AS R ON J.ReleaseId = R.[Id]

WHERE J.TenantId = 1

ORDER BY J.InputArguments, JobID DESC

Example results: