【AD管理】Active Directoryの特定グループのユーザー設定を常に最新の状態でExcelに一覧で表示(抽出)させる方法

組織内の監査対応(SOX)のため、定期的にActive Directory(AD)内のユーザーアカウントのステータスチェックが必要なため、ExcelにADからデータを取得するクエリをPower Queryで作成しました。
今回は、AD内の特定のグループに所属しているメンバーを一覧で表示する必要があるので、その手順について説明します。
なお、AD上でCSVファイルで書き出し(エクスポート)する手順についてはこちらに以前の記事がありますのでご参照ください。

作業環境

• オンプレミスのドメイン環境下・同一ネットワーク内にあるPCで実施
• DCはWindows Server 2016
• Officeは365 Proplusを使用

作業手順

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

   ExcelからActive DirectoryのUser DBを開く手順

2. ドメイン名を入力しOKを押下※表示されない場合は、ドメインネットワーク内に接続済かどうか確認してください。
   
   

   ドメイン名を入力

3. Microsoftアカウントなど、認証を求められた場合は、環境に応じて認証できるアカウント情報を入力し、接続を押下
4. ナビゲーター内のドメイン名のツリーを開き、groupを検索し選択(検索できない場合はツリーを展開していき直接選択)
   
   

   検索でgroupプロパティを検索し展開

5. ナビゲーター ダイアログ右下「データの変換」を押下すると、Power Queryエディターが展開されるので、「distinguishedName」列（通常は一番右側）のプルダウンを開き、「テキスト フィルター」→「指定の値を含む」を選択
   
   

   distinguishedName属性から対象グループを絞り込み

6. 行のフィルター ダイアログ内「指定の値を含む」が選択されているプルダウン右側のテキストボックスに対象のグループ名を入力し「OK」を押下
   
   

   テキストボックスに対象groupを入力

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

   memberプロパティを選択

8. group.member列が追加されるので、また展開アイコンをクリックし「新しい行に展開する」を押下
   

   展開アイコン→新しい行に展開する

9. 展開されたら列名のところで右クリックし、「列のピポット解除」を選択
   

   列名を右クリック→列のピポット解除

10. 「値」の列が追加されるので、列の展開アイコンからdisplayNameもしくはsAMAccountNameを選択(画像ではsAMAccountNameを選択)
    

    「値」の列の展開アイコンを押下→displayName、またはsAMAccountNameを選択

11. 該当グループのユーザー名の一覧が表示されます
    

    ユーザー名の一覧が表示

12. 不要な列を削除し、「閉じて読み込む」を押下
    

    列を整理後に読込

13. Excelファイルにクエリの書き出しが始まり、少し待つとデータが書き出されます
    

    ExcelにADのユーザー一覧が読み込まれた状態

14. 上記作業後は「データ」タブ→「すべて更新」を押下するとADの最新の状態を同期することができます。「
    

    データ→すべて更新を押下