以前の記事でActive DirectoryからPower Queryで特定のグループに含まれるユーザー一覧を出力する方法について紹介しましたが、今回は、この一覧から更に各ユーザーのプロパティ(属性)も併せて表示させる方法について紹介したいと思います。例えば、ユーザー名の隣にそのユーザーの部署、役職、上司などを表示する等です。
これらの情報がエクセル上で一覧で表示できれば、部署名や役職名の記述ミスや上司の設定ミスなどを簡単に見つけることができ、ユーザーメンテナンスの効率化につながると思います。
ある程度大きな組織では、監査対応の一環としてこれらのチェックが求められる場合もあると思いますので、参考になれば幸いです。
目次
作業環境:
Windows10 Pro 20H2, ThinkPad X395, Active Directory
ExcelからPower Queryに接続する
- Excelを開き、「データ」タブ→「データの取得」→「その他のデータソースから(O)」→「Active Directoryから(A)」の順に押下
- 組織または所有しているドメイン名を入力しOKを押下※表示されない場合は、ドメインネットワーク内に接続済かどうか確認してください。
- Microsoftアカウントなど、認証を求められた場合は、環境に応じて認証できるアカウント情報を入力し、接続を押下
- ナビゲーター内のドメイン名のツリーを開き、groupを検索し選択(検索できない場合はツリーを展開していき直接選択)
ナビゲーター ダイアログ右下「データの変換」を押下すると、Power Queryエディターが展開され、AD上の情報が参照されます。
Power Queryエディター上でテーブルを加工する
次に、Power Queryエディター上でテーブル(データの一覧)を操作し、必要な情報を取得するための加工を行います。
- 前項の操作より、Power Query エディター上、一覧をスクロールし「distinguishedName」列(通常は一番右側)のプルダウンを開き、「テキスト フィルター」→「指定の値を含む」を選択
- 「行のフィルター」ダイアログが開いたら、ダイアログ内の「指定の値を含む」が選択されているプルダウン右側のテキストボックスに対象のグループ名を入力し「OK」を押下
※3つ以上のグループを指定する場合は、「詳細設定」ラジオボタンをクリック
- 対象のグループにフィルタされて表示されたら、group行右端の展開アイコンをクリックし、「member」をチェック
- group.member列が追加されるので、また展開アイコンをクリックし「新しい行に展開する」を押下
- 展開されたら列名の見出しの上で右クリックし、「列のピポット解除」を選択
- 新たに右端の列に「属性」と「値」の列が追加されるので、列の展開アイコンから任意の項目(属性)を選択※デフォルトでは全部にチェックが入っていますが、一番上の(すべての列の選択)チェックボックスにチェックを入れると全解除できます
必要な情報の列を追加する
- 前項の操作より、必要な値を選択します。今回は例として、各ユーザーの所属部署、ユーザーID、上司の設定が正しいか確認するための一覧を作成します。
値はそれぞれ以下の通りです。
項目 値 部署 department ユーザーID sAMAccountName 上司 manager
検索欄から属性を検索しチェックした場合、チェックした直後に必ず検索窓右の×を押してください。×を押さずにOKを押すと、その画面でチェックした項目だけが開かれてしまいます。項目を複数選択したい場合には注意が必要です。 - 値.manager列の展開アイコンをクリックし、managerを選択
OKを押すと、値.managerの列に値.sAMAccountName(一番右側)の各ユーザーに対応ずる上司が表示されます。
- 必要に応じて、不要な列を削除していきます。Excelと同様の操作で複数列選択できます。
できたものをExcelに読み込む
- 前項の操作に続き、Power Queryエディター左上の「閉じて読み込む」をクリック
- クエリの処理が完了すると、Excel上に読み込まれたデータが表示されます。
以下例では値.departmentの中にいくつか未入力が見られます。
このように、一覧にしたことによって情報が不足しているアカウントを簡単に発見することができるようになりました。
まとめ
Active Directoryに直接アクセスしてPowerShell等で処理する方法も考えましたが、このようにPowerQueryを利用することにより、定期的なチェックだったり、他の人でも手軽に確認出来たりする等のメリットがあるため、今回のような目的ではPowerQueryを利用したほうがよいのではないかと考えます。組織でOffice365が導入されている場合は、PowerAppsやPowerAutomate等を利用すればもっと効率の良い方法があるかもしれません。PowerAppsやPowerAutomateでの開発例についても、別途紹介していきたいと思います。お役に立てますと幸いですm(__)m
コメント