How to retrive three months data from orchestrator

[patomchai.ka]

Dear All

Currently, I have Ui Version 2018.4 and want to export job process, status, and details from some 2 months ago. Please suggest to me what I should to do? 

Regards,
Tom.

[marian.platonov]

If you cannot achieve your requirement through API calls, and if you have SQL skills, you can query your Orchestrator database.

Some examples:

Disclaimer: UiPath is not responsible for running the below queries in your Orchestrator database. Run them at your own risk.

https://uipath-survey.secure.force.com/CaseView/articles/Knowledge/How-to-get-a-report-for-added-queue-items-that-are-processed-completed-for-every-job-run-using-a-SQL-query

https://uipath-survey.secure.force.com/CaseView/articles/Knowledge/How-to-get-a-report-for-User-Tenant-Robot-Roles-and-Permissions-using-a-SQL-query-in-the-Orchestrator-database

Query 1 → Display the Number of logs per process and process name on a specific day

SELECT JobKey,JB.Id AS JobID, JB.ReleaseId as JobReleaseId,L.ProcessName as ProcessName, COUNT(1) AS TotalLogs FROM Logs as L
INNER JOIN Jobs as JB ON JB.[Key] = L.JobKey
WHERE JB.StartTime > '2022-07-03 00:00:00.000' AND  JB.StartTime < '2022-07-04 00:00:00.000'
GROUP BY JobKey,JB.Id,JB.ReleaseId,ProcessName
ORDER BY JB.Id DESC

Example:

Query 2 → Duration of a process with Process name in seconds

SELECT DISTINCT(J.Id) as JobId,J.ReleaseId,L.ProcessName, DATEDIFF (SECOND,J.StartTime,J.EndTime) AS ProcessDurationInSeconds
   from Jobs AS J
   INNER JOIN Logs as L ON J.[Key] = L.JobKey
   WHERE  J.StartTime > '2022-07-03 00:00:00.000' AND  J.StartTime < '2022-07-04 00:00:00.000'
   ORDER BY J.Id DESC

Example:

Query 3 → For counting Logs added the previous day. Modified so it will count logs for a specific period of time (from…till).

Select CONVERT(DATE, TimeStamp, 101),CONCAT(RobotName,'-',ProcessName,'-',WindowsIdentity) as [Unique Key] ,  RobotName , ProcessName, WindowsIdentity ,  Count(1) as [No. of Logs Added]   
from Logs 
WHERE TimeStamp > CONVERT(DATE,getdate() -1,101)
and ProcessName not like 'RPA0000%'
Group By  CONVERT(DATE, TimeStamp, 101),RobotName , ProcessName , WindowsIdentity
Order By Count(1) desc

Example:

Query 4 → Queue Item Processed the previous 30 days. Modified to display the QueueItems being processed for a specific period of time (from…till)

Select R.Name,CONVERT(DATE, EndProcessing, 101) AS 'Date',  Count(1) as [No. of QueueItem Processed] 
from  QueueItems QI 
Inner  Join  Robots R On R.ID = QI.RobotId
Inner Join QueueDefinitions QD on Qd.ID = QI.QueueDefinitionId
WHERE EndProcessing >  CONVERT(DATE,getdate() -30,101)
Group By R.Name,CONVERT(DATE, EndProcessing, 101)
Order BY Date DESC

Example:

Query 5 → Get JobId,ExecutorJobId,ReleaseId,ProcessName,AverageItemProcessInMs,TotalQueueItemsAdded

SELECT JS.Id, QI.[ExecutorJobId], R.Id AS ReleaseId, R.[Name], AVG(DATEDIFF (MILLISECOND,QI.StartProcessing,QI.EndProcessing)) AS 'Average queue item processing time in ms', COUNT(*) as TotalQueueItemsAdded FROM Jobs as JS
INNER JOIN QueueItems AS QI ON JS.Id = QI.[ExecutorJobId]
INNER JOIN Releases AS R ON JS.ReleaseId = R.[Id]
WHERE JS.StartTime > '2022-07-03 00:00:00.000' AND  JS.StartTime < '2022-07-04 00:00:00.000'
GROUP BY QI.[ExecutorJobId], JS.Id, R.Id, R.[Name]

Example:

Query 6 → Display the Number of logs per process on a specific day

SELECT JobKey,JB.Id AS JobID, JB.ReleaseId as JobReleaseId, COUNT(1) AS TotalLogs FROM Logs as L
INNER JOIN Jobs as JB ON JB.[Key] = L.JobKey
WHERE JB.StartTime > '2022-07-03 00:00:00.000' AND  JB.StartTime < '2022-07-04 00:00:00.000'
GROUP BY JobKey,JB.Id,JB.ReleaseId
ORDER BY JB.Id DESC

Example:

Query 7 → Number of times the process executed on a specific day

SELECT R.[Name],J.[ReleaseId], COUNT(1) AS TotalProcessExecution FROM Jobs as J
INNER JOIN Releases as R ON J.ReleaseId = R.[Id]
WHERE J.StartTime > '2022-07-03 00:00:00.000' AND  J.StartTime < '2022-07-04 00:00:00.000'
GROUP BY R.[Name],J.[ReleaseId]

Example:

Query 8 → Duration of a process in seconds

  SELECT J.Id, DATEDIFF (SECOND,J.StartTime,J.EndTime) AS ProcessDurationInSeconds
   from Jobs AS J
   WHERE  J.StartTime > '2022-07-03 00:00:00.000' AND  J.StartTime < '2022-07-04 00:00:00.000'
   ORDER BY J.Id DESC

Example:

---

We recommend to use the UiPath Insights features.

UiPath Insights is an RPA analytics solution that enables your business to track, measure, and manage the performance of your entire automation program—so you can scale your automation journey to the next level.

Some references regarding Insights:

About Insights

Insights Data Model

Overview

Architecture

Dashboards

RPA Analytics Tool - Measure RPA Operations | UiPath.

[patomchai.ka]

Dear Marian P.

Thank you for your suggestion i will search about DB data model.

Regards,
Tom

[marian.platonov]

Let us know if the provided queries help you out. If yes, mark the provided details a solution to help others.