Dynamics 365(CRM)のユーザー・セキュリティロールの組み合わせ一覧表を作ってみる：CData Dynamics 365 Excel Add-In

前回投稿した「Dynamics 365（CRM）の監査ログをExcelに出力する：CData Excel-Addin for Dynamics 365を使用」という記事が思いの外好評でした（一部で）

kageura.hatenadiary.jp

やっぱりこの、かゆいところに手が届かないというのは Dynamics 365 を運用していてよくあると思います。

そこで今回は、個人的 Dynamics 365 めんどくさい案件の第3位、セキュリティロールの確認方法をやってみたいと思います！

セキュリティロール、つまり権限なので、システム次第では結構クリティカルになりがちなんですがあんまりスマートな確認方法が提供されていない、この案件を、CData Excel-Addinを使って、さくっと確認できるようにしてみようと思います。

どんなことができるの？

以下のExcelピボットテーブルを作成します！　縦軸がユーザー一覧で横軸がセキュリティロール一覧！

ぱっと見で、ユーザーのロール付与忘れや、付与しすぎを確認することができます！

あ、ちなみに今回はユーザー軸で作っていますが、チーム軸でも作成可能です。

必要なもの

・Dynamics 365 for Customer Engagement アカウント

・CData Excel-Addin for Dynamics 365 for Sales（Customer Engagement用）

インストールとログイン

予め以下のURLから「CData Excel-Addin for Dynamics 365 for Sales」をダウンロードしておいてください。

https://www.cdata.com/jp/drivers/d365sales/excel/

Excelを立ち上げて、「CDATA」タブから「取得元D365Sales」をクリックします。

すると接続ウィザードが表示されるでの、接続したい組織URL（Organization URL）を入力して、「OK」をクリックします。

クリック後はブラウザが立ち上がり、OAuth認証処理が実施されます。

そのまま対象ユーザーでログインし、アプリの認可をOKすれば、Web APIへのアクセス許可は完了です。

対象データ

今回対象とするデータが格納されているエンティティは、ユーザー（systemusers）とセキュリティロール（roles）

それから、ユーザーとセキュリティロールを紐づけているsystemuserrolescollectionというエンティティになります。

［ユーザーエンティティ］

［セキュリティロールエンティティ］

ちなみに手軽なデータの取得は、以下のようにSelect Table画面でテーブル一覧から対象のエンティティを選択するだけです。

こんな感じで取得できますが、ちょっと項目が多いので

以下のようなクエリを貼り付けて、実行すると

SELECT 
[systemusers].[fullname], 
[systemusers].[internalemailaddress], 
[systemusers].[systemuserid] 

FROM [systemusers]

もうちょっと小奇麗にデータが確認できます。

同じようにセキュリティロール一覧も取得できます。

SELECT 
[roles].[roleid], 
[roles].[name] 

FROM 
[roles] 

ユーザーとロールの設定一覧取得

それでは、実際にユーザーとセキュリティロールのマトリクスを作るためのクエリを実行していきましょう。

CData Excel addinでは複数のテーブルを結合するJOIN句が利用できます。

それを使って以下のようなクエリを実行すると

SELECT 
[Users].[systemuserid],
[Users].[fullname],
[Users].[internalemailaddress],
[Roles].[name],
[Roles].[roleid],
[UserRoles].[systemuserroleid] 

FROM [systemusers] as [Users] 

LEFT OUTER JOIN [systemuserrolescollection] as [UserRoles] 
ON [Users].[systemuserid] = [UserRoles].[systemuserid]

LEFT OUTER JOIN [roles] as [Roles] 
ON [UserRoles].[roleid] = [Roles].[roleid]

以下のように、それぞれのユーザーにどのロールが付与されているのか？　というデータを取得することができます。

あとはExcelの世界です。ピボットテーブルを実行して

こんな感じで、縦軸にユーザー名、横軸にロール名、値は適当にロール名をカウントするようにしてあげれば

マトリクスを作成することができます。

チームとメンバーの組み合わせ一覧取得

ちなみに、以下のようなクエリを使えば、各チームに所属するメンバーの一覧も取得できます。

SELECT 
[Users].[systemuserid],
[Users].[fullname],
[Users].[internalemailaddress],
[Teams].[name],
[Teams].[teamid],
[TeamMembers].[teammembershipid] 

FROM [systemusers] as [Users] 

LEFT OUTER JOIN [teammemberships] as [TeamMembers] 
ON [Users].[systemuserid] = [TeamMembers].[systemuserid]

LEFT OUTER JOIN [teams] as [Teams] 
ON [TeamMembers].[teamid] = [Teams].[teamid]

ちょっとチームが少ないので、微妙なデータですが、以下のように取得できました。

おわりに

こんな感じで、通常ならSSRSとかでレポートを作らないといけないようなものも、

そのままExcelの機能ベースでデータの取得と処理ができるようになります。

ちなみに、特定のエンティティは作成や更新もサポートしているので、Excelで作ったマトリクスを元に、データをそのままExcel上で更新、みたいなことも可能です。

是非いろいろと活用してみてもらえればと思います。