Excel instead of PowerShell: AD queries and system reports "on the knee"


In the comments to the previous article, we remembered about accounting in Excel instead of 1C. Well, let's check how much you know Excel. Today I will show how to get data from Active Directory and work with them without macros and PowerShell - only with regular Office mechanisms. For example, you can easily get analytics on the use of operating systems in an organization if you do not already have something like Microsoft SCOM. Well, or just warm up and distract from the scripts.


Конечно, получить данные как в примерах ниже можно буквально одной строчкой на PowerShell. Но, во-первых, PowerShell — это слишком скучно, а во-вторых, Excel умеет динамически обновлять данные ― получившиеся документы можно опубликовать в сети и забыть про их актуализацию.

To work with data, I will use the Power Query mechanism . For office 2010 and 2013, you will have to install the plug-in , in Microsoft Office 2016 this module is already built-in. Unfortunately, the standard edition is not enough for us, we need Professional.


The mechanism itself is designed to receive and process data from a variety of sources - from the old ODBC and text files, to Exchange, Oracle and Facebook. In more detail about the mechanism and the built-in scripting language "M" already wrote on Habré , I’ll analyze a couple of examples of using Power Query to get data from Active Directory.


Warm up: let's see when our users logged in


The query to the domain database itself is created on the tab "Data - New query - From other sources - From Active Directory".



Specify the data source.


You will need to select the domain name, specify the necessary data for the connection. Next, select the type of objects, in this example, user . On the right in the preview window, the query is already running, showing a preview of the data.



We prepare a request, enjoy the preview.


Previously, the request should be prepared by clicking the “change” button and selecting the necessary columns. In essence, these columns are classes. Each of them contains a set of specific attributes of the Active Directory object, except for the main column displayName , which itself is an attribute. I will focus on the classes user , person , top and securityPrincipal . Now you need to select the necessary attributes from each class using the "extension" - an icon with two arrows at the column heading:


  • extend the
    • by selecting lastLogonTimestamp and userAccountControl ;
    • in person choose telephoneNumber ;
    • at top - whenCreated ;
    • and in securityPrincipal , SamAccountName .


    Expanding the request.


    Now let's configure the filter: in particular, in order not to get blocked accounts, you need the userAccountControl attribute to have the value 512 or 66048. The filter may be different in your environment. Read more about the attribute in the Microsoft documentation .



    Apply a filter .


    Иногда Excel неверно определяет формат данных, особенно значения атрибута lastLogonTimestamp. Если вдруг постигла такая беда, на вкладке «Преобразовать» можно выставить верный формат.

    Now the userAccountControl column should be deleted - it is not needed at all in the display. And click "Download and Close."


    It turned out a tablet, which remained quite a bit to bring to mind. For example, rename columns to something readable. And set up automatic data updates.


    Automatic updating when a table is opened or by timeout is configured in the "Data" tab in the "Properties".



    Configure data updates.


    After the update setup is completed, you can safely give the table to the personnel department or the security service - let them know who and when logged in.


    The request code in the language "M" under the spoiler.
    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"

    Create an address book, or what to do when the corporate portal is not friends with AD


    Another option for using Excel in conjunction with Active Directory is to create an address book based on AD data. It is clear that the address book will turn out relevant only if the domain is in order.


    Let's create a request for the user object , expand the user class in mail , and the person class in telephoneNumber . We delete all columns except distinguishedName - the domain structure repeats the structure of the enterprise, so the names of Organizational Units correspond to the names of units. Similarly, security groups can be used as the basis for unit names.


    Now from the line CN = User Name, OU = Accounting Department, OU = Divisions, DC = domain, DC = ru, you need to directly extract the name of the department. The easiest way to do this is to use separators on the Transform tab.



    We extract the text.


    As separators, I use OU = and , OU = . In principle, a comma is enough, but I play it safe.



    Introduce the delimiters.


    Now, using the filter, you can cut off unnecessary OUs , such as blocked users and Builtin , configure sorting and load data into a table.



    Type of summary table.


    Quick report on the composition of workstations, without the introduction of agents and other training


    Now we will try to create a useful table by receiving data on computers. We will make a report on the operating systems used by the company: for this, we will create a request, but this time we will select computer in the navigator .



    We make a request for the computer object.


    Let's leave the computer and top column classes and expand them:


    • extend the
      • by selecting cn , operatingSystem , operatingSystemServicePack and operatingSystemVersion ;
      • in the top
      • class, select whenCreated .


      Advanced request.


      If you wish, you can only report on server operating systems. For example, apply a filter by the attribute operatingSystem or operatingSystemVersion. I will not do this, but I will correct the display of the creation time - I'm only interested in the year. To do this, select the column we need on the “Transformation” tab and select “Year” in the “Date” menu.



      We extract the year from the time the computer was entered into the domain.


      Now it remains to remove the displayname column as unnecessary and load the result. The data is ready. Now you can work with them as with a regular table. First, we’ll make a pivot table on the “Insert” tab - “Pivot table”. We agree with the choice of the data source and configure its fields.



      Pivot table field settings.


      Now it remains to customize the design and admire the result:



      Pivot table for computers in AD.


      If desired, you can add a summary chart, also on the "Insert" tab. In the "Categories" (or in the "Rows", to your liking) add operatingSystem , in the data - cn . On the “Designer” tab, you can choose the type of chart you like, I prefer a circular one.



      Pie chart.


      Now you can clearly see that, despite the ongoing update, the total number of workstations with Windows XP and servers with Windows 2003 is quite large. And there is something to strive for.


      Request code under the spoiler.
      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"

      But that's not all.


      It should be noted that Excel is able to compose not only the accounting favorite tablets. With a skillful approach, he can handle both multidimensional data analytics (OLAP cubes) and solving a system of equations using matrices . And for those who have a certificate from Microsoft gathering dust on the wall - there is an option to get confused even with 3D games . Not Doom of course, but the evening will definitely take.


      What do you think of Excel as an admin tool? Have you used any of the above?