Azure AD Geolocation by sign-in activity using Power BI
If you’re an Office 365 customer or even an Azure customer then you’re probably familiar with Azure Active Directory (or Azure AD). Azure AD is the core identity provider that the majority of Microsoft services rely on for authentication. For today’s post I thought it would be interesting to pull sign-in activity into Power BI and show how simple it is to display a dashboard of geolocated sign-ins by user and device.
The user creating Power BI reports has an Azure AD Premium and Power BI licenses assigned
- Note, if a new user account was recently created, I recommend waiting a day for the sign-in data to fully populate otherwise no sign-in data will be present. Check the Azure AD Premium admin portal for sign-in activity for the user periodically. Once the sign-in data is present, refresh the Power BI dataset connection to pull it into Power BI. More details here: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-reporting-faq
First we’ll need to sign into Power BI and pull in the Azure AD Activity Logs Content Pack. Do this in Power BI by selecting Get Data, Services (Get), then search for Azure. Select Azure Active Directory Activity Logs (Preview) from the search results and provide your Azure AD domain name and then select next.
Once the Azure Active Directory Activity Logs (Preview) content is added we can begin to create a dashboard. From the Power BI UI find the “Azure Active Directory Activity Logs” under Dataset and select it. Under “Visualizations” select Map and under “Fields” expand “Signin Activity” and select City, Country, Name, and Total Signins. Without any further modifications your map should look similar to the following:
Feel free to play around with the data to get the information you find most interesting or better yet, what your security team will find most interesting. Hover over the data circles to display additional information about the data point.
Now a map of sign-ins may be all that is required, however I went a step further and created two slicers to drill in on certain data points. To add slicers, select the Slicer image from under Visualizations from under Fields expand “Unique Users” and then select “Details.Name”.
To add another slicer, repeat the process from above, only instead of expanding Unique Users, expand “Signin Activity” and then select “Device Information”
Adding slicers enables me to check mark interesting information and drill down on that specific data point. Pulling it all together the final dashboard looks like the following:
If I want to hone in on a specific data point, all I need to do is select either a data point under one of the slicers as shown in the gif below:
Add a slicer for date and time to show time based sign-in activity:
This was just a simple method of creating a Power BI report that show’s a lot of rich data points that may help you understand where your users are logging in across the globe from what browser or device. In addition, use the Azure AD Premium to create conditional access policies to protect user identities, corporate information, and block malicious devices, apps, and browsers from unsecure locations.