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.
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.
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:
2 Likes
Dear Marian P.
Thank you for your suggestion i will search about DB data model.
Regards,
Tom
Let us know if the provided queries help you out. If yes, mark the provided details a solution to help others.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.