前の記事へのコメントでは、1CではなくExcelでの会計について覚えていました。 さて、Excelをどれだけ知っているかを確認しましょう。 今日は、Active Directoryからデータを取得し、マクロとPowerShellなしで（通常のOfficeメカニズムのみを使用して）データを操作する方法を示します。 たとえば、Microsoft SCOMのようなものがまだない場合は、組織内のオペレーティングシステムの使用に関する分析を簡単に取得できます。 さて、または単にウォームアップしてスクリプトから注意をそらす。

もちろん、PowerShellの1行で、文字通り以下の例のようにデータを取得できます。 しかし、第1に、PowerShellは退屈すぎます。第2に、Excelはデータを動的に更新できます。結果のドキュメントはネットワーク上で公開され、更新を忘れることがあります。

データを操作するには、 Power Queryメカニズムを使用します。 Office 2010および2013では、 プラグインをインストールする必要があります。MicrosoftOffice 2016では、このモジュールは既に統合されています。 残念ながら、標準版では十分ではありません。Professionalが必要です。

このメカニズム自体は、古いODBCファイルやテキストファイルからExchange、Oracle、Facebookなど、さまざまなソースからデータを受信して​​処理するように設計されています。 Habréで既に記述されているメカニズムと組み込みスクリプト言語「M」の詳細については、Power Queryを使用してActive Directoryからデータを取得するいくつかの例を分析します。

ウォームアップ：ユーザーがいつログインしたかを見てみましょう

ドメインデータベース自体へのクエリは、[データ-新しいクエリ-他のソースから-Active Directoryから]タブで作成されます。

データソースを指定します。

ドメイン名を選択し、接続に必要なデータを指定する必要があります。 次に、オブジェクトのタイプ、この例ではuserを選択します 。 プレビューウィンドウの右側では、クエリが既に実行されており、データのプレビューが表示されています。

リクエストを準備し、プレビューをお楽しみください。

以前は、「変更」ボタンをクリックして必要な列を選択することで、リクエストを準備する必要がありました。 基本的に、これらの列はクラスであり、各列にはActive Directoryオブジェクトの特定の属性のセットが含まれていますが、メイン列displayNameはそれ自体が属性です。 user 、 person 、 topおよびsecurityPrincipalクラスに焦点を当てます。 ここで、「拡張」を使用して各クラスから必要な属性を選択する必要があります-列見出しに2つの矢印の付いたアイコン：

• lastLogonTimestampおよびuserAccountControlを選択してユーザークラスを拡張します 。
• 個人で telephoneNumberを選択します。
• トップ -whenCreated ;
• およびsecurityPrincipalで 、 SamAccountName 。

リクエストを展開します。

次に、フィルターを構成します。特に、ブロックされたアカウントを取得しないようにするには、userAccountControl属性の値を512または66048にする必要があります。フィルターは環境によって異なる場合があります。 属性については、 Microsoftのドキュメントをご覧ください。

フィルターを適用します 。

Excelは、データ形式、特にlastLogonTimestamp属性の値を誤って定義することがあります。 そのような不幸が突然起こった場合、「変換」タブで正しいフォーマットを設定できます。

これで、userAccountControl列を削除する必要があります-ディスプレイにはまったく必要ありません。 [ダウンロードして閉じる]をクリックします。

タブレットが判明しました。 たとえば、列の名前を読みやすい名前に変更します。 自動データ更新を設定します。

「プロパティ」の「データ」タブで、テーブルが開いたときまたはタイムアウトによる自動更新が設定されます。

データ更新を構成します。

更新のセットアップが完了したら、人事部またはセキュリティサービスにテーブルを安全に渡すことができます。誰がいつシステムに入ったかを知らせます。

let  = ActiveDirectory.Domains("domain.ru"), domain.ru = {[Domain="domain.ru"]}[#"Object Categories"], user1 = domain.ru{[Category="user"]}[Objects], #" " = Table.RemoveColumns(user1,{"organizationalPerson", "shadowAccount", "posixAccount", "msExchOmaUser", "msExchBaseClass", "msExchIMRecipient", "msExchCertificateInformation", "msExchMultiMediaUser", "msExchMailStorage", "msExchCustomAttributes", "mailRecipient", "distinguishedName"}), #"  securityPrincipal" = Table.ExpandRecordColumn(#" ", "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}), #"  top" = Table.ExpandRecordColumn(#"  securityPrincipal", "top", {"whenCreated"}, {"whenCreated"}), #"  person" = Table.ExpandRecordColumn(#"  top", "person", {"telephoneNumber"}, {"telephoneNumber"}), #"  user" = Table.ExpandRecordColumn(#"  person", "user", {"lastLogonTimestamp", "userAccountControl"}, {"lastLogonTimestamp", "userAccountControl"}), #"   " = Table.SelectRows(#"  user", each ([userAccountControl] = 512 or [userAccountControl] = 66048)), #" " = Table.TransformColumnTypes(#"   ",{{"lastLogonTimestamp", type datetime}}), #" 1" = Table.RemoveColumns(#" ",{"userAccountControl"}) in #" 1" 

アドレス帳を作成するか、企業ポータルがADと友達でない場合の対処方法

Active Directoryと組み合わせてExcelを使用する別のオプションは、ADデータに基づいてアドレス帳を作成することです。 ドメインが正常である場合にのみ、アドレス帳が関連することが明らかになります。

ユーザーオブジェクトのリクエストを作成し、 mailのユーザークラスを展開し、 telephoneNumberの 人物クラスを展開しましょう。 distinguishedNameを除くすべての列を削除します。ドメイン構造はエンタープライズの構造を繰り返しているため、 組織単位の名前は単位の名前に対応しています。 同様に、セキュリティグループはユニット名のベースとして使用できます。

次に、 CN =ユーザー名、OU =経理部、OU =部門、DC =ドメイン、DC = ruという行から、部門名を直接抽出する必要があります。 これを行う最も簡単な方法は、「変換」タブの区切り文字を使用することです。

テキストを抽出します。

区切り文字として、 OU =およびOU =を使用します 。 原則として、コンマで十分ですが、安全にプレイします。

区切り文字を導入します。

これで、フィルターを使用して、ブロックされたユーザーやBuiltinなどの不要なOUをカットして、並べ替えを構成し 、データをテーブルに読み込むことができます。

サマリー表のタイプ。

エージェントや他のトレーニングを導入することなく、ワークステーションの構成に関するクイックレポート

次に、コンピューターでデータを受信して​​、便利なテーブルを作成してみましょう。 会社で使用されているオペレーティングシステムに関するレポートを作成します。このためにリクエストを作成しますが、今回はナビゲーターでコンピューターを選択します。

コンピューターオブジェクトを要求します。

コンピューターと最上位の列クラスを残して展開します。

• cn 、 operatingSystem 、 operatingSystemServicePack 、およびoperatingSystemVersionを選択して、 コンピュータークラスを拡張します 。
• 最上位クラスで、 whenCreatedを選択します。

高度なリクエスト。

必要に応じて、サーバーのオペレーティングシステムでのみレポートを作成できます。 たとえば、属性operatingSystemまたはoperatingSystemVersionでフィルターを適用します。 私はこれを行いませんが、作成時間の表示を修正します-私はその年にのみ興味があります。 これを行うには、[変換]タブで必要な列を選択し、[日付]メニューで[年]を選択します。

コンピューターがドメインに入力されたときから年を抽出します。

ここで、不要なdisplayname列を削除し、結果をロードします。 データの準備ができました。 これで、通常のテーブルと同様に作業できます。 最初に、[挿入]タブでピボットテーブルを作成します-[ピボットテーブル]。 データソースの選択に同意し、そのフィールドを設定します。

ピボットテーブルフィールドの設定。

これで、デザインをカスタマイズして結果を賞賛することができます。

ADのコンピューターのピボットテーブル。

必要に応じて、[挿入]タブでもサマリーチャートを追加できます。 「カテゴリ」（または「行」）で、data- cnに operatingSystemを追加します。 「デザイナー」タブで、好きなチャートのタイプを選択できます。円形のチャートをお勧めします。

円グラフ。

継続的な更新にもかかわらず、Windows XPを搭載したワークステーションとWindows 2003を搭載したサーバーの総数が非常に多いことがはっきりとわかります。 そして、努力するものがあります。

 let  = ActiveDirectory.Domains("domain.ru"), domain.ru = {[Domain="domain.ru"]}[#"Object Categories"], computer1 = domain.ru{[Category="computer"]}[Objects], #" " = Table.RemoveColumns(computer1,{"user", "organizationalPerson", "person"}), #"  " = Table.SelectColumns(#" ",{"displayName", "computer", "top"}), #"  computer" = Table.ExpandRecordColumn(#"  ", "computer", {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}, {"cn", "operatingSystem", "operatingSystemServicePack", "operatingSystemVersion"}), #"  top" = Table.ExpandRecordColumn(#"  computer", "top", {"whenCreated"}, {"whenCreated"}), #" " = Table.TransformColumns(#"  top",{{"whenCreated", Date.Year}}), #" 1" = Table.RemoveColumns(#" ",{"displayName"}) in #" 1" 

しかし、それだけではありません

Excelは会計用のお気に入りのタブレットだけでなく、作成できることに注意してください。 熟練したアプローチにより、彼は多次元データ分析（OLAPキューブ）と行列を使用した連立方程式の解法の両方を処理できます。 そして、Microsoftからの証明書を持っている人たちが壁にほこりをかぶっています-3Dゲームでさえ混乱するオプションがあります。 もちろん運命ではありませんが、夜は間違いなくかかります。

Excelを管理ツールとしてどう思いますか？ 上記のいずれかを使用しましたか？