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:
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: