Jobs Data Retrieval With Robot Name Using SQL Query

News Knowledge Base
, , ,
1

How to retrieve all Jobs data from UiPath Orchestrator DB using SQL Query ?

Jobs data, retrieval with Robot name:

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,

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

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

J.StartTime,

CASE WHEN J.State = 0 THEN 'Queued'

WHEN J.State = 1 THEN 'Running'

WHEN J.State = 2 THEN ''

WHEN J.State = 3 THEN ''

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 B ON J.RobotId = B.ID

INNER JOIN Machines M ON B.MachineId = M.Id

INNER JOIN Releases R ON J.ReleaseId = R.Id

WHERE J.TenantId = 1

ORDER BY J.InputArguments, 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

Note:

  • These statuses may change with the Orchestrator version.
  • It is not recommended to directly interact with the UiPath DB. Expertise the above query with the local DB team experts.


====

For a v2022.4.0 Orchestrator version, use this query,

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

image.png

1 Like