31 C
Wednesday, October 20, 2021

Power BI Data Gateway with Active Directory

You can create amazing reports with Active directory information with live refresh . It saves tons of hours rather than digging into SCCM for some useful data.
Anyone with very basic power bi knowledge can do these reports . As you know it requires power bi pro license to create and do data refresh periodically. In Medium and large size enterprises these reports can really help to solve compliance as there is more usage with custom attributes in several environments that if domain is = A use custom attribute 1 and have more that 20 domains with several attributes. you can create conditional columns as well to see the data visually . Also you can create data alerts if required. The possibilities are limit less.

Once you have the report ready and published . You need to map the data set with the data source and enable schedule refresh So that the data is is active and gets refreshed periodically.

It saves millions of hours for any¬† administrator as we don’t need to jump in every time to some get some real data.

I have created numerous reports am not going to detail all the steps but you can get some idea on what you can get out of it.

  • Data with Department Attribute
  • Data with Company Attribute


  • Utilizing Operating System Attribute

  • User Logon timestamp Attribute

  • Utilizing Operating System Attribute with delimiters to extract only windows 10 version ( )


Sample Conditional Column for one of the reports. If you are into power shell or any language . supporting these if statements are nice and cool.which takes the reports to next level.
Power Bi has amazing features and most of the environment will remain hybrid. its going to take some years where machines moving to azure ad join. there report will help to keep the environment safe and removing legacy operating systems seamlessly. Also keeps the active directory super clean with out inactive old objects.

if [mailRecipient.mail] = "azure365pro.com" and [msExchCustomAttributes.extensionAttribute10] = "AZURE365PRO"
then "TRUE"
if [mailRecipient.mail] = "a.com" and [msExchCustomAttributes.extensionAttribute10] = "A"
then "TRUE"
if [mailRecipient.mail] = "b.com" and [msExchCustomAttributes.extensionAttribute10] = "B"
then "TRUE"
if [mailRecipient.mail] = "c.com" and [msExchCustomAttributes.extensionAttribute10] = "C" or [msExchCustomAttributes.extensionAttribute10] = "CCC"
then "TRUE"
if [mailRecipient.mail] = "d.com" and [msExchCustomAttributes.extensionAttribute10] = "d"
then "TRUE"
else "FALSE"
Satheshwaran Manoharanhttps://www.azure365pro.com
Satheshwaran Manoharan is an Microsoft Office Server and Services MVP , Publisher of Azure365pro.com. Specialized in Office365 / Microsoft Exchange / Virtualization , Sathesh is an Messaging Expert supporting/Designing/Deploying many medium size businesses to large enterprises when it comes to Corporate messaging and Virtualization Infrastructure

Related Articles


Please enter your comment!
Please enter your name here