【AD管理】Active Directory上の各ユーザーの複数の属性プロパティ(Attribute)をPower Queryで一括チェックする方法

以前の記事でActive DirectoryからPower Queryで特定のグループに含まれるユーザー一覧を出力する方法について紹介しましたが、今回は、この一覧から更に各ユーザーのプロパティ（属性）も併せて表示させる方法について紹介したいと思います。例えば、ユーザー名の隣にそのユーザーの部署、役職、上司などを表示する等です。
これらの情報がエクセル上で一覧で表示できれば、部署名や役職名の記述ミスや上司の設定ミスなどを簡単に見つけることができ、ユーザーメンテナンスの効率化につながると思います。
ある程度大きな組織では、監査対応の一環としてこれらのチェックが求められる場合もあると思いますので、参考になれば幸いです。

ExcelからPower Queryに接続する

Excelを開き、「データ」タブ→「データの取得」→「その他のデータソースから(O)」→「Active Directoryから(A)」の順に押下

ExcelからActive DirectoryのUser DBを開く手順
組織または所有しているドメイン名を入力しOKを押下※表示されない場合は、ドメインネットワーク内に接続済かどうか確認してください。

ドメイン名を入力
Microsoftアカウントなど、認証を求められた場合は、環境に応じて認証できるアカウント情報を入力し、接続を押下
ナビゲーター内のドメイン名のツリーを開き、groupを検索し選択(検索できない場合はツリーを展開していき直接選択)
ナビゲーターダイアログ右下「データの変換」を押下すると、Power Queryエディターが展開され、AD上の情報が参照されます。

Power Queryエディター上でテーブルを加工する

次に、Power Queryエディター上でテーブル(データの一覧)を操作し、必要な情報を取得するための加工を行います。

前項の操作より、Power Query エディター上、一覧をスクロールし「distinguishedName」列（通常は一番右側）のプルダウンを開き、「テキストフィルター」→「指定の値を含む」を選択

distinguishedName属性から対象グループを絞り込み
「行のフィルター」ダイアログが開いたら、ダイアログ内の「指定の値を含む」が選択されているプルダウン右側のテキストボックスに対象のグループ名を入力し「OK」を押下
※3つ以上のグループを指定する場合は、「詳細設定」ラジオボタンをクリック

テキストボックスに対象groupを入力
対象のグループにフィルタされて表示されたら、group行右端の展開アイコンをクリックし、「member」をチェック

memberプロパティを選択
group.member列が追加されるので、また展開アイコンをクリックし「新しい行に展開する」を押下
展開アイコン→新しい行に展開する
展開されたら列名の見出しの上で右クリックし、「列のピポット解除」を選択
列名を右クリック→列のピポット解除
新たに右端の列に「属性」と「値」の列が追加されるので、列の展開アイコンから任意の項目(属性)を選択※デフォルトでは全部にチェックが入っていますが、一番上の(すべての列の選択)チェックボックスにチェックを入れると全解除できます

必要な情報の列を追加する

前項の操作より、必要な値を選択します。今回は例として、各ユーザーの所属部署、ユーザーID、上司の設定が正しいか確認するための一覧を作成します。
値はそれぞれ以下の通りです。

項目値

部署 department

ユーザーID sAMAccountName

上司 manager

部署としてdepartmentを選択

ユーザーIDとしてsAMAccountNameを選択

上司(所属長)としてmanagerを選択

検索欄から属性を検索しチェックした場合、チェックした直後に必ず検索窓右の×を押してください。×を押さずにOKを押すと、その画面でチェックした項目だけが開かれてしまいます。項目を複数選択したい場合には注意が必要です。

検索して選択した場合、チェックしたら検索窓の×を押下する
値.manager列の展開アイコンをクリックし、managerを選択

上司(manager)の、どの属性を表示させるかを選択する

OKを押すと、値.managerの列に値.sAMAccountName（一番右側）の各ユーザーに対応ずる上司が表示されます。

ユーザーに対応する上司が表示される
必要に応じて、不要な列を削除していきます。Excelと同様の操作で複数列選択できます。

項目	値
部署	department
ユーザーID	sAMAccountName
上司	manager

できたものをExcelに読み込む

前項の操作に続き、Power Queryエディター左上の「閉じて読み込む」をクリック
クエリの処理が完了すると、Excel上に読み込まれたデータが表示されます。
以下例では値.departmentの中にいくつか未入力が見られます。
このように、一覧にしたことによって情報が不足しているアカウントを簡単に発見することができるようになりました。

Excelに出力されたクエリ

まとめ

Active Directoryに直接アクセスしてPowerShell等で処理する方法も考えましたが、このようにPowerQueryを利用することにより、定期的なチェックだったり、他の人でも手軽に確認出来たりする等のメリットがあるため、今回のような目的ではPowerQueryを利用したほうがよいのではないかと考えます。組織でOffice365が導入されている場合は、PowerAppsやPowerAutomate等を利用すればもっと効率の良い方法があるかもしれません。PowerAppsやPowerAutomateでの開発例についても、別途紹介していきたいと思います。お役に立てますと幸いですm(__)m