Morning Girl

Web API, Windows, C#, .NET, Dynamics 365/CRM etc..

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ピボットテーブルを作成します! 縦軸がユーザー一覧で横軸がセキュリティロール一覧!

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

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

f:id:sugimomoto:20181219211417p:plain

必要なもの

・Dynamics 365 for Customer Engagement アカウント

・CData Excel-Addin for Dynamics 365 for Sales(Customer Engagement用)

インストールとログイン

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

f:id:sugimomoto:20181219211425p:plain

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

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

f:id:sugimomoto:20181219211432p:plain

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

f:id:sugimomoto:20181219211438p:plain

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

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

f:id:sugimomoto:20181219211447p:plain

対象データ

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

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

[ユーザーエンティティ]

f:id:sugimomoto:20181219211455p:plain

[セキュリティロールエンティティ]

f:id:sugimomoto:20181219211501p:plain

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

f:id:sugimomoto:20181219211507p:plain

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

f:id:sugimomoto:20181219211515p:plain

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

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

FROM [systemusers]

f:id:sugimomoto:20181219211525p:plain

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

f:id:sugimomoto:20181219211536p:plain

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

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

FROM 
[roles] 

f:id:sugimomoto:20181219211552p:plain

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

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

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]

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

f:id:sugimomoto:20181219211607p:plain

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

f:id:sugimomoto:20181219211623p:plain

f:id:sugimomoto:20181219211629p:plain

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

f:id:sugimomoto:20181219211637p:plain

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

f:id:sugimomoto:20181219211645p:plain

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

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

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]

f:id:sugimomoto:20181219211702p:plain

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

f:id:sugimomoto:20181219211709p:plain

おわりに

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

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

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

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