How to obtain recent jobs execution data from Orchestrator Database?

News Knowledge Base
, ,
1

How to get the jobs executed a certain number of days ago up to today, from the Orchestrator database?

Overview: Access to detailed information regarding UiPath jobs executed within their Orchestrator On- Premises setup might be required. This data is vital for enhancing Robot monitoring, understanding process performance, pinpointing bottlenecks, resolving issues, and optimizing resource usage. Additionally, it can help in making informed decisions within organizations.

Use Cases:

  1. Robot Monitoring: Clients deploy monitoring robots to analyze job data and detect anomalies such as failures or delays in the job execution process, triggering alerts for timely intervention.

  2. Performance Analysis: Organizations analyze job execution times, success rates, and error patterns to optimize automation processes for enhanced efficiency and reliability.

  3. Resource Allocation: Accessing job data helps organizations identify underutilized resources and reallocate them to maximize throughput and minimize processing times.

  4. Compliance and Reporting: Detailed job logs provide an audit trail for compliance purposes and facilitate internal or regulatory reporting requirements, ensuring transparency and accountability.

How to get the jobs executed a certain number of days ago up to today from the Orchestrator database?

The following SQL query retrieves essential job information, such as Tenant Name, Robot Name, Job ID, Process Name, Machine Name, Started, Finish, StartTime in UTC, and Job Status. It adjusts time zones for readability, translates numeric job states into human-readable formats, and joins multiple tables for comprehensive data retrieval. The query filters jobs based on tenant IDs and those finished within the last 60 days, prioritizing results by descending job ID.

Expected Result:

Expected Result.jpg


SQL Query:

SELECT 
    T.TenancyName as 'Tenant Name', -- Selecting tenant name, robot name, job id, process name and machine name
    B.[Name] as 'Robot Name', 
    J.Id as JobID, 
    R.[Name] as 'Process Name', 
    M.Name as 'Machine Name', 
    -- Adjusting start time to local time zone
    DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.StartTime) as Started, 
    -- Adjusting end time to local time zone
    DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),J.EndTime) as Finish, 
    J.StartTime as 'StartTime in UTC', -- Selecting start time in UTC
    -- Assigning human-readable job status based on numeric state
    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 'Job Status'
FROM 
    Jobs J -- Selecting from Jobs table
INNER JOIN 
    Robots AS B ON J.RobotId = B.[Id] -- Joining Robots, Machines, Releases, Tenants tables
INNER JOIN 
    Machines AS M ON J.MachineId = M.[Id] 
INNER JOIN 
    Releases AS R ON J.ReleaseId = R.[Id] 
INNER JOIN 
    dbo.tenants AS T ON J.TenantId = T.Id 
WHERE 
    (J.TenantId = 1 OR J.TenantId = 2) -- Filtering by tenant IDs, if you want to add moror remove a tenant modify this section as necessary. In this example tenant ID's 1(default) and 2 (test) are consulted.
    AND J.EndTime >= DATEADD(DAY, -60, GETDATE()) -- Filtering jobs finished in the last 60 days if you want to check a different range of days modify this section as necessary.
ORDER BY 
    JobID DESC -- Ordering by descending job ID


Note:

  1. Consider developing a REST API that authenticates against Orchestrator to retrieve the desired information

  2. UiPath does not recommend any form of database manipulation. Observe the following:

    1. Consult internally with your organization's database expert or DB team before taking any action

    2. Take regular backups and document the actions

    3. Test in a development environment or a copy of the production database

    4. Running complex SQL queries against the Orchestrator database can potentially impact database performance.