36 C
Thursday, May 23, 2024

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 Manoharan
Satheshwaran Manoharanhttps://www.azure365pro.com
Award-winning Technology Leader with a wealth of experience running large teams and diversified industry exposure in cloud computing. From shipping lines to rolling stocks.In-depth expertise in driving cloud adoption strategies and modernizing systems to cloud native. Specialized in Microsoft Cloud, DevOps, and Microsoft 365 Stack and conducted numerous successful projects worldwide. Also, Acting as a Technical Advisor for various start-ups.

Related Articles


Please enter your comment!
Please enter your name here

× How can I help you?