Monitoring Exchange Online User Client Access and Usage with Graph, PowerShell and Power BI
Published Jan 21 2019 10:01 AM 25.3K Views

Introduction

As a Tenant Admin of an Office 365 Exchange Online organization, have you ever needed to monitor who, what, and where someone is connecting to your Exchange Online resources, like accessing mailboxes on mobile devices? I ran into this request a few weeks ago, from one of my customers. After hours of research, and testing I became a believer in the power of Microsoft Graph (Graph). By now you’re probably thinking, what is an Exchange engineer working with a graphing tool for? Well last month, that is exactly what I would have thought too. Surprising (to me) Graph is an extremely powerful tool that can interface with a large set of Microsoft services and technologies to pull data and perform tasks within the service/technology. Pulling sign-in data from Azure Active Directory (AAD) is a breeze with Graph. After the data is extracted, using Power BI for visualization brings your reporting capabilities to a new level! Let’s walk thru a scenario setup where as a Tenant Admin you can find out who is accessing mailboxes in your Exchange Online tenant on mobile devices, using Exchange ActiveSync protocol (which is used by default mail apps on Apple & Android devices) from anywhere in the world.

Note: For this procedure to work for you, you need to have two subscriptions: Exchange Online (like E1 or E3) and Azure Active Directory Premium (like P1 or P2).

Allowing Graph Access to AAD Audit Log Data

AAD allows application access through the App Registration feature. To allow Microsoft Graph to query audit log data from AAD you must first create a new app registration. You can do this by logging into https://portal.azure.com and going to Azure Active Directory > App registrations (you may also see one option as ‘App registrations (Preview)’, we will not use that one). Powerbi1 From here simply create a New app registration provide a Name and enter https://localhost as the Sign-on URL. Powerbi2 After you have created the app registration you can now grant the required permissions by going to Settings > API Access > Required permissions. Powerbi3 Now AddMicrosoft Graph’ (under ‘Select an API’) and grant Read all audit log data permission (under ‘Select permissions’). Click ‘Done’ to complete this step. Powerbi4 It will look like this after the above steps. Powerbi5 Next, you will need to commit the change via the Grant permissions button by clicking on Yes, as in the screenshot below. Powerbi6 You’ll see this confirmation message on top right hand corner in the Azure Admin Portal. Powerbi7 Next, we need to create the key secret. This can be done under Settings > API Access > Keys (keep in mind that your key secret will only be displayed once & you need to copy it for later use). Under Passwords section, give the key a short description & set an expiration time, and then click on Save button, which would result in a warning message (as in the screenshot below) asking you to copy the key value, please do so to use it later in the script below. Powerbi8

Pulling the data with PowerShell

To connect to Graph with PowerShell you first need to obtain an OAuth token from logon.microsoft.com. For authentication, your application ID and key secret is used. This is done using the code below: You will need the following parameters for the PS script below. Application ID: Powerbi9 Key Secret: The value that you copied earlier, it would look something like this (example): 6vNGGm5rAB4Zn32rOW9RT+4zEaqcx3l92qyGwb+vT2c= Tenant Domain: The tenant domain that’s registered for your tenant in Office 365, like contoso.com, for example (Admin Portal | Setup | Domains) Directory Path: The path on the local machine to save the output CSV file from this script, where this PS script is being executed by you as Tenant Admin

$ClientID = "[INSERT APPLICATION ID]"
$ClientSecret = "[INSERT KEY SECRET]"
$TenantDomain = "[INSERT TENANT DOMAIN]"
$OutputDirectory = "[INSERT DIRECTORY PATH]"
$loginURL = 'https://login.microsoft.com'
$resource = 'https://graph.microsoft.com'
$body = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
$oauth = Invoke-RestMethod -Method Post -Uri $loginURL/$TenantDomain/oauth2/token?api-version=1.0 -Body $body

Once the OAuth token has been obtained, we can now request the data from Graph using a web request:

$headerParams = @{Authorization="$($oauth.token_type) $($oauth.access_token)"}
$url = 'https://graph.microsoft.com/beta/auditLogs/signIns'
$resultSet = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url)

We can now filter the results and export them to a CSV:

$output = @()
ForEach($event in ($resultSet.Content | ConvertFrom-Json).value) {
If($event.clientAppUsed -eq "Exchange ActiveSync")
{
$output += $event
}
}
$output | Export-CSV "$OutputDirectory\EXOClientAccessUsageReport.csv" -NoTypeInformation

Here is a full example of the PowerShell script:

<###############Disclaimer#####################################################
The sample scripts are not supported under any Microsoft standard support
program or service. The sample scripts are provided AS IS without warranty
of any kind. Microsoft further disclaims all implied warranties including,
without limitation, any implied warranties of merchantability or of fitness for
a particular purpose. The entire risk arising out of the use or performance of
the sample scripts and documentation remains with you. In no event shall
Microsoft, its authors, or anyone else involved in the creation, production, or
delivery of the scripts be liable for any damages whatsoever (including,
without limitation, damages for loss of business profits, business interruption,
loss of business information, or other pecuniary loss) arising out of the use
of or inability to use the sample scripts or documentation, even if Microsoft
has been advised of the possibility of such damages.
###############Disclaimer#####################################################>
#Declare unique instance variables
$ClientID = "[INSERT APPLICATION ID]"
$ClientSecret = "[INSERT KEY SECRET]"
$TenantDomain = "[INSERT TENANT URL]"
$OutputDirectory = "[INSERT DIRECTORY PATH]"
#Declare static variables
$loginURL = 'https://login.microsoft.com'
$resource = 'https://graph.microsoft.com'
#Build OAuth tequest
$body = @{grant_type="client_credentials";resource=$resource;client_id=$ClientID;client_secret=$ClientSecret}
#Request OAuth token
$oauth = Invoke-RestMethod -Method Post -Uri $loginURL/$TenantDomain/oauth2/token?api-version=1.0 -Body $body
#If OAuth was successful request data from Microsoft Graph
If($null -ne $oauth.access_token)
{
#Build Microsoft Graph web request
$headerParams = @{Authorization="$($oauth.token_type) $($oauth.access_token)"}
$url = 'https://graph.microsoft.com/beta/auditLogs/signIns'
#Request data via web request to Microsoft Graph
$resultSet = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url)
#Place all events related to EXO into an array
$output = @()
ForEach($event in ($resultSet.Content | ConvertFrom-Json).value) {
If($event.appDisplayName -like "*Exchange *")
{
$output += $event
}
}
#Export all EXO events to a CSV
$output | Export-CSV "$OutputDirectory\EXOAccessReport.csv" -NoTypeInformation
}
Else
{
Write-Error "Failed to authenticate to OAuth, no token obtained."
}

After running the script above, which you can do thru Windows PowerShell on any Windows machine, you’ll have a csv file in your hands, i.e. EXOAccessReport.csv, in the output directory that you provided in the above script.

Visualizing Data with Power BI

The geniuses that developed Power BI (download the Power BI desktop app from here) have made this next step so easy even I can do it! Launch Power BI desktop app and simply import the data using Get Data > Text/CSV, select your report (if you used the defaults it will be named EXOAccessReport.csv), and click Load. Powerbi10 Once this is complete you can now select your visualization (I recommend ArcGIS Maps, or Maps), and drag and drop the data fields to the visualization fields. Drag location to Location and Size, userDisplayName to Legend, and clientAppUsed and createdDateTime to Tooltips. Note by selecting First (Default) for clientAppUsed this will select the latest login being as the CSV generated by the script is in descending order from most recent to least recent. Powerbi11 Further down the settings pane, you will find Filters the add clientAppUsed, userDisplayName, location, and createdDateTime to Report level filters. Powerbi12 Finally you can review your report and publish it to your workspace in Power BI. Powerbi13 Remember that in order for Power BI to access the data from the cloud your Power BI Gateway must have access to the CSV file generated by the PowerShell script. See On-premises data gateway for information and instructions on how to setup a Power BI gateway.

Keeping it fresh

To ensure that the report is always up to date with the latest data, I recommend you configure the PowerShell script to run at an interval that meets your needs. This task could be easily accomplished by Task Scheduler, System Center Orchestrator, and many other task scheduling solutions. For the purposes of this post we will use Task Scheduler since it is readily available on most versions of Windows. On the machine the script will be running from, launch Task Scheduler by pressing Windows + R then typing taskschd.msc and clicking OK. Click Create Task in the Actions pane on the right hand side of the window. Powerbi14 Name your task, select Run whether user is logged on or not and check Run with highest privileges. Powerbi15 Select the Triggers tab and click New… Check Repeat task every, select 30 minutes for the interval, and Indefinitely for the duration then click OK. Powerbi16 Select the Actions tab, then click New… Type powershell.exe into Program/script and enter the full path to your script into Add arguments (optional) then click OK. Note: If there is a space in the full path to your script you must put a at the beginning and end of the path. Powerbi17 Click OK then provide your credentials or the credentials you are running the task as into the prompt.

Reading the Report

Now that we have created an amazing Power BI visualization how do we view it? Navigate to https://app.powerbi.com/groups/me/list/reports then click on the report we just created. Powerbi18 Once the report launches and populates with the latest data you can see all user logins plotted on the map with circles. The circle gets larger when more users login from the same location. In the report below we can see an anomaly with one users login location. By hovering over the circle we can see the location (where), display name (who), number of times the user logged in, what technology/method (what) was used to login during the most recent sign-in, and the date and time of the latest login. Powerbi19 If there are multiple logins from this location, there could be multiple methods used to login. To determine which methods were used we can use the Filters pane and apply a location and userDisplayName filter. Once this is complete when we expand clientAppUsed only the technology/method of login is left, in this case Exchange ActiveSync. Powerbi20

Conclusion

Using Microsoft Graph, PowerShell, Task Scheduler, and Power BI we created an auto updating report to track Exchange Online user logins. Powerbi21 With our new skills Exchange Online doesn’t mark the end of custom reporting for organizations. With Microsoft Graph and Power BI our ability to generate custom reports is stronger than ever! Happy Graphing! Dana Garcia
23 Comments
Not applicable
Hi,

Using your example script, I needed add some ' '

$loginURL = 'https://login.microsoft.com'

$resource = 'https://graph.microsoft.com'

$url = 'https://graph.microsoft.com/beta/auditLogs/signIns'

Or else, I got the:

Invoke-WebRequest : Cannot validate argument on parameter 'Uri'. The argument is null or empty. Provide an argument that is not null or emp

ty, and then try the command again.

Then it worked great.

Thanks.

Not applicable
Should be fixed now; thanks!
Not applicable
Wondering if you might be able to help me. I am geting a 403 error.

$resultSet = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url)

Invoke-WebRequest : The remote server returned an error: (403) Forbidden.

Not applicable
JFoley2222,

During the creation and testing of this article we encountered this only when the tenant wasn't licensed for Azure AD Premium 1 or 2. You can start a trail on your tenant if you don't already have it by going here: https://azure.microsoft.com/en-us/trial/get-started-active-directory/.

Thanks,

Dana Garcia

Not applicable
I confirmed we are Azure AD Premium P1. I will continue to look at why I might be seeing this.
Not applicable
will it work off365 Azure AD ? I am getting

PS C:\Users\Satya> C:\satyam\scrips\o365loginaccess.ps1

Invoke-WebRequest : The remote server returned an error: (403) Forbidden.

Not applicable
One last thing is, make sure the domain you specified is your default domain I.E. if you have a custom domain contoso.com and your default tenant domain is contoso.onmicrosoft.com you would need to provide contoso.onmicrosoft.com.

Thanks,

Dana Garcia

Not applicable
JFoley2222,

In this case, I would look at the application registration with Azure AD and Graph. Ensure that Graph has been granted permissions to Read all audit log data and that the "Grant Permissions" button was clicked followed by "Yes". Also I would generate a new Key Secret and triple check the values you put in the script for any leading or trailing spaces. Please let me know if this doesn't resolve your issue.

Thanks,

Dana Garcia

Not applicable
Supplementary : @Carolyn Billups - if I change the logon to a user logon rather than logging on as a the app itself, then if the user has suitable privileges it works every time. Trying to do this as set out here - so the person running the script doesn't even need to have their own account in the tenant, but uses app's ID and Secret as a credential to for one scope of tasks (reading logs) seems to be unreliable.

I'm using it with a different mode of logging on and ( @ Dana) I've found 3 or 4 really useful nuggets of knowledge out of this - so thanks for the good work !

Not applicable
I'm getting the same as spadster and Jfoley2222 - I have Azure AD Premium P1, and the first time I ran the script it returned data, then it seemed to be 50/50 whether I would get a 403 error. And then it seemed to be almost every time. I had it working with my tenant guid as the logon name, but it makes no difference if I use [whatever].com

if I download the schema from https://graph.microsoft.com/beta/$metadata it doesn't show auditlogs in the entityContainer object , which makes me wonder if this is quite finished yet.

Not applicable
I believe you have to be a global admin to run the script
Not applicable
In my case, something is weird. Most of the time I am getting the 403 error. Sometimes I do not. I can run the script 100 times in a row and about 90 of the times I get the 403, 10 it works fine. Either way it appears as though even when I do get the 403, it is pulling data.
Not applicable
Hi Satya11,

The requirements to use this solution are to have a tenant with Azure AD Premium 1 or 2, or a trial subscription for either of those.

Thanks,

Dana Garcia

Not applicable
I did make that change earlier as I had a domain other than the default listed. I am still seeing the 403 error but at some point it had to have worked because I have data in my CSV file. Just not sure it is updating.
Not applicable
So in 2019, on a technetium blog, the suggestion is to run a powershell script as task on a Windows server machine to export a CSV to be used with Power BI gateway? Really guys?

Remove all the on-premises using an Azure storage account and a Logic App.

This should be in my opinion what is expected from Microsoft nowdays

Not applicable
I agree with @Francesco Ares Sodano, it does look messy and not right for 2019. Why can't we connect to Graph API straight from Power BI?
Not applicable
Hi Francesco,

As mentioned in the article you can setup the PowerShell to run on a schedule in many different software solutions to achieve the same result. This article guided readers through using Task Scheduler because it is by far the most readily available tool everyone has access to. Your solution of using an Azure logic app is a great idea, I went with an Azure RunBook myself. Thanks for the feedback and have a great day!

Thanks,

Dana Garcia

Not applicable
This looks handy, however, on first run of the script it begins to gather data then fails with a JSON error along the lines of;

Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.

Not applicable
Additionally, half the time the script will gather data, the other half the time it returns;

Invoke-WebRequest : The remote server returned an error: (403) Forbidden.

At D:\Scripts\oauth.ps1:16 char:19

+ $resultSettemp = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri ...

Not sure if the 'https://graph.microsoft.com/beta/auditLogs/signIns' service is throttled?

Not applicable
Spadster,

In regards to your JSON length error it sounds like the length of the results it too great. You might be able to solve this by adding a filter to our GET request. To do this you would need to change the line $url = 'https://graph.microsoft.com/beta/auditLogs/signIns' to $url = 'https://graph.microsoft.com/beta/auditLogs/signIn?&$filter=createdDateTime ge ' + (Get-Date -Format 'yyyy-MM-DD').AddDays(-7). What this will do is only pull back signIns for the last 7 days, if you still experience the JSON length error you might have to shorten it by increasing the date closer to the current date. For the 403 error that you are experiencing, this could be do to either permissions or a claims issue. Do you have any claims access policies that could be effecting this? If the error states insufficient_claims this is likely what is causing this issue.

Thanks,

Dana Garcia

Not applicable
For those who are experiencing the "Invoke-WebRequest: The remote server returned an error: (403) Forbidden." error, I was also getting this error due to the fact that I wasn't using the Azure Premium Subscription. I just assigned the Azure P2 Subscription and the error's gone. By the way, nice tip Dana!
Not applicable
Denis,

Thanks for letting us know this helped you. Happy graphing!

Thanks,

Dana Garcia

Copper Contributor

I have found I have been getting the Invoke-WebRequest: The remote server returned an error: (403) Forbidden error.  This was on a P1 tenant.  I changed to a P2 and this then worked so it looks like this is P2 only.

Version history
Last update:
‎Jul 01 2019 04:35 PM
Updated by: