Orchestrator Managerを使ってジョブ実行履歴を集計する

UiPathのRPAを活用している企業等で、社員にAssistant実行用のライセンスを払い出しているが、実際に使っているのか棚卸ししたい。という方向けの記事です。

-------------
Orchestrator画面でジョブの実行履歴は見れるけど
  - ジョブの実行履歴が多すぎると量が多すぎて集計も手間だ
  - 誰がどれだけジョブを実行しているのかがよく分からない
という悩みがあると思います。

エクセルで集計作業、RPAで集計するにしても、1000万件超えると時間がかかりすぎたり、メモリが溢れて集計できないなど、結構しんどかった経験がありました。MSアクセスに取り込んで、SQLで集計する方法をご紹介します。

こんなイメージの集計結果が出来上がります
|ユーザー|最終実行日|実行時間（分）|実行時間（秒）|実行回数|成功回数|成功率|種類|実行合計日数
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|cb29\admin_1|2023-12-14T07:18:06.113Z|0|52|1|1|100|Unattended|1
|autogen\pocuipath-coe01@local|2023-12-19T14:45:56.98Z|1|62|1|1|100|Attended|1
|poc\sasaki2|2023-12-20T07:34:16.463Z|668|40109|547|457|83.5|Unattended|3
|poc\sasaki|2024-01-12T07:56:40.257Z|77|4653|6|3|50|Unattended|3

:::note warn
MSアクセスでは、データベース容量上限が2GBという制約があります
:::

***
###### 必要なもの
 - Orchestrator
 - Studio 2023.4.1
 - Orchestrator Manager 2.5.1
 - Microsoft Excel
 - Microsoft Access


Orchestratorはオンプレ、AutomationCloudどっちでも可能ですが、この記事ではAutomationCloudで書いていきます。

***
###### １　Orchestratorでの事前設定
　Orchestrator ManagerからOrchestratorに接続するために必要な設定作業です。

１－１
　AutomationCloudの「設定」>「外部アプリケーション」
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/3cb771ef-52f3-5870-eb19-1919ca8e7008.png)
１－２　「外部アプリケーション」から「OAuthアプリ」の中の「アプリケーションを追加」を押します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/5ce3fdef-9466-e21d-ded7-9e55119f34d5.png)

1-3
アプリケーション名を入力し
「機密アプリケーション」を選択します。「機能を追加」を押した後、
リソースは「Orchestrator API Access」,アプリケーションの範囲で「すべてを選択」を押して、「保存」を押します

:::note warn
必ず「保存」を押してください
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/963e1e93-c694-a2f6-bb0f-92bbe866ef28.png)
1-4
「追加」ボタンを押します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/04ad73cd-d20c-b4d8-ad38-1b5b13fc7c42.png)
1-5
アプリIDとアプリシークレットが表示されるので、コピーして保存しておいてください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/13577f56-e85f-e4cd-683a-0e912c6fc385.png)

1-6
保存忘れてしまった。。でも大丈夫です
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/9a967859-640c-3fb3-b457-46c36c59b8ed.png)



###### ２　Orchestrator Managerのダウンロード
Orchestrator Managerをダウンロードして、パソコンの適当なフォルダに解凍します。

:::note info
Orchestrator Managerのダウンロードはこちら
https://marketplace.uipath.com/ja/listings/orchestrator-manager

Orchestrator Managerのバージョンと対応するStudioのバージョンもご確認ください
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/39c89b9b-a5a4-e3fc-2357-7903f12a56e9.png)



:::






###### ３　Main.xamlを実行

　３－１　Main.xamlを実行
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/862da8eb-2ceb-bb35-9411-a18d89ec5bf8.png)

　３－２　接続情報を入力
 しばらくすると、下のような画面が出てくるので項目を入力して「OK」を押します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/b0f03354-985b-cb28-ae8d-6484409abb32.png)

| 項目 |設定内容 |
|:-:|:-:|
|①| AutomationCloudを使うので「クラウド」を選択  |
|②| 「１　Orchestratorでの事前設定」で取得した「アプリシークレット」の値 |
|③| 「１　Orchestratorでの事前設定」で取得した「アプリID」の値 |
|④|ご使用のOrchestratorのテナントのURL　https://cloud.uipath.com/xxxxxxxx/yyy/orchestrator_/の xxxxxxxxの値  |
|⑤|ご使用のOrchestratorのテナントのURL　https://cloud.uipath.com/xxxxxxxx/yyy/orchestrator_/の yyy の値  |
|⑥|デスクトップが指定されます。アクセス権のあるフォルダならどこでも好きな場所に変更して結構です。 |
|⑦|「OAuthフローを使用します」はON  |
 
　３－３　ジョブの取得を実行する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/f5642cc8-90ae-44a4-56a8-7f8e78608bcc.png)

対象のフォルダを選択します（デフォルトだと全フォルダが選択）
 ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/9f4965f6-cff4-785e-b72c-7bea1c81ecf2.png)

　３－３　結果保存
　実行結果は、「６」ブックのパスの場所にエクセルに保存されてます。

###### ４　MSアクセスで集計作業
　　
　４－１　データインポート
MS　Accessで新しいデータベースを作り、３－３のエクセルをインポートします

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/25a76b0f-27e6-f7df-f0bf-7ff52ce68262.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/00aac67e-ef4f-eac0-4fbc-1a0f8910611b.png)

:::note warn
- インポートする際「先頭行をフィールドとして使う」にチェックをつけて「完了」を押してインポートします。
- 列に対して型の指定は何もしていません。
:::

「取得」というテーブルがインポートされました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/9e1acd99-fc57-2d29-796c-650d60d74187.png)


 ４－２　SQL実行

作成＞クエリデザインを押してSQLビューを押して、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/f82dab88-00a5-3d23-21c1-ba591ee2ebef.png)

以下の、SQLをコピペで貼り付けて実行します。

:::note info

- サブクエリAは、ユーザーごとの、最終実行日、実行回数、実行時間を集計しています。"成功　Successful", "失敗　faulted", "停止　Stopped"のステータスが対象で、実行中など確定していない中間ステータスは除いています。
参考　[ジョブのステータスの種類](https://docs.uipath.com/ja/orchestrator/automation-cloud/latest/user-guide/job-states)

- サブクエリBは、ユーザーごとの、最終実行日、実行回数、実行時間を集計しています。（"成功 Successful"のみ）
- サブクエリB.[成功回数]/サブクエリA.[実行回数]で成功率を算出しています。
- サブクエリCはユーザーごとに、実行した日数の合計を集計しています。100回のジョブを1日で実行した場合「1日」、20回ずつ5日で実施した場合「5日」です。特定日に集中して使ったのか、まんべんなく使ったのかを把握するために集計しています。
:::


```SQL:ジョブ実行履歴集計-実行時間集計.sql
SELECT 
A.[ユーザー] as ユーザー, A.[最終実行日] as 最終実行日, 
Int(A.[実行時間]/60/60*10/10) as 実行時間（時間）,
Int(A.[実行時間]/60*10/10) as 実行時間（分）,
Int(A.[実行時間]) as 実行時間（秒）,
A.[実行回数] as 実行回数, B.[成功回数] as 成功回数,
Int(B.[成功回数]/A.[実行回数]*100*10)/10 as 成功率,
A.[種類],
C.実行日数 as 実行合計日数
FROM
(
 (SELECT [ユーザー], max([開始]) AS 最終実行日, count(1) AS 実行回数,  
 SUM(
 	DateDiff(
 				"s", 
				CDate( mid([開始],1,10) & " "&mid ([開始],12,8) ) ,
				CDate( mid([終了],1,10) & " "&mid ([終了],12,8) ) 
 			 )
 ) as 実行時間, [種類]
 FROM 取得  WHERE  ステート in ("Successful", "faulted", "Stopped")    GROUP BY [ユーザー], [種類])  AS A
 
 inner  JOIN

 (SELECT [ユーザー], max([開始]) AS 最終実行日, [種類],   count( 1 ) AS 成功回数 FROM 取得 GROUP BY [ユーザー], [種類],[ステート] 
 HAVING [ステート]='Successful' )  AS B
 ON A.[ユーザー]=B.[ユーザー] and A.[種類]=B.[種類] 
)

inner JOIN 

 (select [ユーザー], [種類],count(tmp実行日数)  as 実行日数 from
  ( SELECT  [ユーザー], count(  (mid([開始],1,10)) ) as tmp実行日数,[種類] from 取得 group by [ユーザー], [種類],mid([開始],1,10) )  group by [ユーザー], [種類]
)as C
 ON C.[ユーザー]=A.[ユーザー] and C.[種類]=A.[種類] 

order by A.[最終実行日] asc



```




４－３　実行結果
実行を押すと以下のような形で集計されるので、後はエクセルなどに出力してください。

Attended,Unattendedの両方表示されていますが、Assistant実行用のライセンスを実際に使っているのか棚卸ししたいという目的なので、Attendedに絞ってみればよいと思います。
最終実行日や実施回数、実行時間（分）を見て、この人使ってないな。とあたりをつければよいかと。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/7160b9f3-cdca-a034-0366-3682dc6528ed.png)



:::note warn
OrchestratorManagerでジョブの履歴を取得していますが、Automation Cloud の場合、Job実行履歴の保持期間がデフォルトだと30日で削除です。最大で180日に変更したり、削除ではなくてアーカイブにすることもできます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3495721/f24f200b-4422-9059-0924-85c96b736a88.png)

:::



以上です:smile: