Power BI and Active Directory for System Administrators

Active Directory and Power BI

One of the most interesting things about Power BI is that it covers a wide range of areas. Therefore, it can help a wide range of different users to analyse and understand their businesses easily. For instance system administrators can use Power BI to analyse  their Microsoft Windows Active Directory. As a matter of fact, Power BI and Active Directory can work together very nicely so that a system administrator can create high level reports and dashboards.

In this , we’ll create a report of the following charts:

  • Total number of computers by Operating System/Service Pack
  • Total number of  computers by year and Operating System
  • Total number of computers
  • Print pages per minute by printer
  • Total number of printers by year and driver name

As a system administrator you can create heaps of other useful reports.

Get Data

  • On Power BI Desktop click “Get Data” then click “More”

Power BI and Active Directory 01

  • Click “Other”, click “Active Directory” then click “Connect”

Power BI and Active Directory 02

  • Enter a Domain name then click OK

Power BI and Active Directory 03

  • As you can see there are 374 tables you can select to create heaps of reports. In this post I use “Computer” and “PrintQueue”

Power BI and Active Directory 04

  • After selecting the desired tables you can either click “Load” or click “Edit” to modify the queries using Query Editor. I click “Load”.
  • Expand both tables from “Fields” pane

Power BI and Active Directory 05

  • As you can see both tables have just two columns which don’t look to be helpful. So to get some more informative data out of those tables we need to do some works in the next steps.
  • Click on “Edit Queries” from the ribbon

Power BI and Active Directory 06

  • As you can see Power Query engine detected lots of related columns with an expand icon (Expand column icon) next to them. These columns, are called Complex Columns. Complex columns are basically the columns that do not exist in the original table, but, exist in a related table. We can expand the complex columns to reveal the containing values.
  • Click on “Computer” table from the “Queries” pane

Power BI and Active Directory 07

  • Click on the expand button of the “computer” column
  • Scroll down the list and tick the following columns:
        1. OperatingSystem
        2. OperatingSystemServicePack
        3. OperatingSystemVersion

Power BI and Active Directory 08

  • As you can see you can search for desired column names
  • You can unselect the “The Original Column Name as Prefix” if you don’t need it. Click OK.

Power BI and Active Directory 09

  • As you can see there are some rows with Null value in OperatingSystem column. We are not interested on those rows. So we can simply filter the table to hide the rows of data with Null on OperatingSystem.

Power BI and Active Directory 10

  • We also need to expant the column “top” and select “CreateTimeStamp” then click OK

Power BI and Active Directory 11

  • In our sample we need to have Year part of the “CreateTimeStamp” column. To extract Year, click on Date-> Year-> Year from “Transform” tab

Power BI and Active Directory 12

Power BI and Active Directory 13

  • Click on “PrintQueue” from the Queries pane
  • Expand the “PrintQueue” column with the following columns then click OK
          1. DriverName
          2. PrintMaxResolutionSupported
          3. PrintPagesPerMinute

Power BI and Active Directory 14

  • We also need to expand the “top” column and select “CreateTimeStamp” column
  • Extract Year part of the “CreateTimeStamp” column. We discussed how to extract Year part in previous steps.
  • Now we have everything we need to create our report. Click on “Close & Load” button from the ribbon

Power BI and Active Directory 15

Power BI and Active Directory 16

Create Reports

Total Number of computers by Operating System/Service Pack

  • Switch to Report view
  • Click on column chart from “Visualisation” pane
  • From “Fields” pane drag and drop “OperatingSystem” on Axis area
  • Drag and drop “DisplayName” on Value area
  • Make sure that “Count” is selected as aggregation for Value

Power BI and Active Directory 17

  • Switch “Data Labels” to ON from format tab

Power BI and Active Directory 18

  • Drag and drop “OperatingSystemServicePack” on the Legend area

Power BI and Active Directory 19

You can replace chart name and titles by something more meaningful. You can also change the look and feel of the chart. I leave these changes to you. If you are new to Power BI Desktop I encourage you to read this post to learn how to create data visualisations in Power BI Desktop on a step-by-step basis.

Total Number of  Computers by Year and Operating System

  • From Fields pane expand “Computer” table
  • Drag and drop “CreateTimeStamp” on the report area
  • Move it from Value are to Axis area
  • Select “OperatingSystem” and “DisplayName” respectively
  • Change the chart type to Area Chart

Power BI and Active Directory 20

  • Change the Legend Position to Right
  • Switch “Data Labels” to ON

Power BI and Active Directory 21

Total Number of Computers

This one is the easy one. You just need to expand “Computer” table from the Fields pane then click on “DisplayName” then change the chart type to Card.

Power BI and Active Directory 22

I don’t like the chart name which is “Count of displayName”. If you click on the Format tab from Visualisation pane you’ll see there is no format settings for this type of chart. Although you cannot change the chart title from Power BI Desktop you can change it when you published it to Power BI Website. I’ll explain how to this later in this post.

If you don’t want to publish the report to the Power BI Website for now, a workaround is to rename the corresponding column to something more meaningful.

I personally don’t think renaming the column is the right way to modify the chart’s title. So I hope Microsoft adds this feature in the next versions of the product.

Print Pages Per Minute by Printer

  • Click Waterfal Chart from the Visualisation pane
  • Expand “PrintQueue” table from the Fields pane
  • Put “DriverName” on Axis
  • Put “PrintPagesPerMinute” on Value
  • Put “PrintPagesPerMinut” on Colour Saturation area

Power BI and Active Directory 23

  • Go to Format
  • From Data Colours switch “Diverging” to ON
  • Set Minimum colour to Red
  • Set Centre colour to Blue
  • Set Maximum colour to Green

Power BI and Active Directory 24

Total Number of Printers by Year and Driver Name

  • Click on Table from Visualisation pane
  • From Fields pane click  “Driver Name”
  • Click “CreateTimeStamp”
  • Select “Do Not Summarise”

Power BI and Active Directory 25

  • Drag and drop the “Driver Name” to the Values area again
  • Select “Count” for the aggregation

Power BI and Active Directory 26

  • Resize the table

Power BI and Active Directory 27

Publish the report to Power BI Website

We created some reports and it’s time to publish the reports to Power BI Website. It’s super easy to publish the reports. You just need to click Publish from the ribbon and pass your Power BI credentials.

Power BI and Active Directory 28

Create Dashboards on Power BI Website

  • Open your browser and login to your Power BI account
  • Make sure that report is published correctly by clicking on the report

Power BI and Active Directory 29

  • Now create a new dashboard and name it Active Directory

Power BI and Active Directory 30

  • Click on the report again and pin the charts to the dashboard

Power BI and Active Directory 31

  • Click on the “Active Directory” from the dashboards again
  • As you can see you have all the charts on the dashboard

Power BI and Active Directory 32

  • Now you just need to reorganise the dashboard as desired

Power BI and Active Directory 33

Changing a Chart Title

As I stated before in some charts we cannot modify the chart title. But, luckily we can change them from the dashboard.

  • Open the dashboard
  • When you hover over a chart a “Title Details” (Power BI and Active Directory 34)and “Delete Title” (Power BI and Active Directory 35) buttons appear.
  • Click on “Title Details”
  • Enter a desired title then click “Apply”

Power BI and Active Directory 35

As you can see, we are actually modifying a “Tile Title” not a chart title. Each space on the dashboard that we put the dashboard objects on it is a tile. Each tile can have a title which is a chart title by default. We can also setup a custom link (URL) for a tile. So if users click on a tile they will be redirected to the URL.


Discover more from BI Insight

Subscribe to get the latest posts sent to your email.

20 thoughts on “Power BI and Active Directory for System Administrators

  1. Hello,

    Thank you very much for the tutorial. It is very helpfull.

    Now, I want to realize the following thing though powerBi, would you please help me
    a. Number of disabled users
    b. Number of enabled but stale users
    c. Number of enabled but locked-out users
    d. Number of looped nested groups
    e. Number of users with passwords that never expire
    f. Number of users with removal recommendations

    1. Hi Dominique

      I know this was posted a few years back but, where you able to find the AD Attribute Enabled, LastLogon, AD Object Creator, and password expiration for Power BI AD computer and users objects?

  2. Hi Dominique

    I do this with a Powershell script and I pull specific attributes and then manipulate the data set in PowerBI…

    The process is not much different to what this blog describes

    1. Hi Peter Johnson
      I’m spending several days trying to put powershell commands in power BI and I’m not succeeding.
      Do you have any tip?
      Thank you
      Vinnie

  3. Hi,
    I took same approach to get AD groups for Power BI row level security but it seems like it requires a gateway. Did you have to get gateway installed for your report?
    We also have Active directory in Azure but I am not sure how to build same report (same as yours) through Cloud. Do you know how to approach that in cloud.
    I would appreciate your help.

  4. Hi to every one, the contents existing at this web
    site are genuinely awesome for people knowledge, well, keep
    up the nice work fellows.

  5. Hi to everyone. I’m creating a report, with all the list of users with 2FA is enabled. is it possible here? Can you give the right table to use? Thank you.

  6. Great Walkthrough however, I got stuck at the beginning entering the domain. I have entered both domains listed in my Office 365 Admin panel but neither seems to work? All I get is domain couldn’t be found.

    I looked at another walkthrough that said the domain should be auto populated if you are logged into your tenant? Any assistance would be greatly appreciated. I’m looking to report on active licenses by day and month to tie back to our billing from our MS partner.

  7. Hi,

    Thanks for this post – I found it very useful.

    I would love to use Power BI to monitor our windows servers for Patch Updates. Can the Active Directory data source help with this ??

    We have a number of workstations and servers in our organisation, that for one reason or another are not monitored by our patching server, and I’d really like to capture this information.

    Cheers,

  8. Thanks for this content. I tried connecting to AD but could not get through. I’m getting authentication error. Could you please suggest what could be the cause? I do have read access to AD

  9. power bi desktop allows me to choose only the domain I belong to, say I belong to Asia then asia.company.com is only available when I choose get-data–>active directory.
    I would like to show users of different domain say europe.company.com then how should I achieve that???

    1. Hi Amit,

      This is only possible if there is a trust relationship between the two domains, which something in the AD level settings not Power BI.
      Hopefully that helps.

      Cheers.

  10. Is it possible to have a service principal credentials accessing Active Directory from Power BI? If yes, what permissions need to be assigned?

  11. Hi,
    Thank you Soheil for sharing very information. Could you please suggest which table/column we can refer from Active directory to import all resources list under specific manager/managers ?

    This needs to include direct/indirect all resources.

    1. Hi Rajender,

      Welcome to BIInsight.com.
      This is not a very straightforward process as every Active Directory is configured differently.
      I am no expert in Active Directory, so, unfortunately, I am not that familiar with the way the entities relate from an Active Directory perspective.
      The reason I wrote this blog post was to help system admins who know a lot about Active Directory to create their own analytical reports in Power BI.
      So, here is the point, if you are a system admin, you may want to start with the entities in which have links to the User entity (table).
      The User entity has UserPrincipalName and Manager attributes (columns).
      From there, you may find the resources under each manager, but I am not certain about that.
      I suppose it depends on your AD configuration though.
      Hopefully this helps.

      Cheers.

  12. Olá, bom dia

    Criei o relatório porém fica em branco e não aparece nenhum sistema operacional

    POde me ajudar ?

    1. Hi Leandro,

      Welcome to biinsight.com and thanks for your question.
      Look, this is a fairly old blog post and I am sure many things have changed in Active Directory since I published this post.
      In addition, I no longer have access to an Active Directory test environment.
      So unfortunately I am unable to help.
      But I am sure many of my website visitors will be able to help.

      Cheers.

  13. Thanks Soheil for publishing this awesome blog. I see scheduled refresh for this report being grayed out. Any reason for that? Also, do you have a way to create and automate a PBI report to display PBI content such a workspace and reports underneath?

    1. Hi Nishi,
      One of the most common reasons for the schedule refresh being disabled is your credentials. You should expand the Data source credentials section in the Dataset Settings page and reenter your credentials.
      About your other question on getting a list of all Reports and Workspaces, you should use Power BI Rest APIs.
      Using the Rest APIs in Power BI requires a little effort, especially if you want to enable the schedule refresh to automatically fetch the data from the APIs.
      To do so, you have to register an App on Azure AD, you also need to create a service principal, you need to generate access token to use the APIs etc…
      Hopefully you find the above links helpful.
      Good luck!

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.