Connecting Splunk to Office 365 – Part 2: Microsoft Office 365 Reporting Add-On for Splunk

Connecting Splunk to Office 365 – Part 2: Microsoft Office 365 Reporting Add-On for Splunk

4.5/5 - (6 votes)

In Part 1 of this blog series, I went through the setup of the Splunk Add-On for Microsoft Cloud Services, which you can use to extract, query, and analyze data provided by the Office 365 Management Activity API.  In this particular post, we’re going to explore the Microsoft Office 365 Reporting Add-On for Splunk, which you can use to review message trace data from Office 365.

Obviously, you’ll need Splunk installed and configured, so if you haven’t done that already, you’ll want to get on that.

Prepare Office 365

In order to retrieve the logging data necessary, you need to grant a user object the ability to read the message tracking logs.  By default, Exchange Online doesn’t have a role with only that as its permission (or anything really close), so we’re going to:

  • Create a user account
  • Create a role group
  • Add some roles to it (Message Tracking, View-Only Audit Logs, View-Only Configuration, View-Only Recipients)
  • Add the newly created user to it

Note: Only the ViewOnlyRecipients role is needed for the add-on to work, as that is what the reporting services API requires.  I’ve found it’s useful, though, to have the others so you can check the message trace, message tracking, transport configuration, and message audit data with one account.  If you are going for a least-privilege configuration, remove the MessageTracking, ViewOnlyAuditLogs, and ViewOnlyConfiguration lines.

To do this, connect to Office 365 with PowerShell as a global administrator, and run the following commands:

$TenantDomain = (Get-MsolAccountSku).AccountSkuId[0].Split(":")[0] + ""
$UserName = "splunkreporting@"+$tenantdomain
New-MsolUser -UserPrincipalName $UserName -DisplayName "Splunk Reporting" -PasswordNeverExpires $True -UsageLocation US
$MessageTrackingRoles = @()
$MessageTrackingRoles += (Get-ManagementRole -RoleType "MessageTracking").Guid.ToString()
$MessageTrackingRoles += (Get-ManagementRole -RoleType "ViewOnlyAuditLogs").Guid.ToString()
$MessageTrackingRoles += (Get-ManagementRole -RoleType "ViewOnlyConfiguration").Guid.ToString()
$MessageTrackingRoles += (Get-ManagementRole -RoleType "ViewOnlyRecipients").Guid.ToString()
$RoleGroup = New-RoleGroup -Name 'Splunk Reporting Add-On' -Roles $MessageTrackingRoles -Members $UserName

I’d recommend changing the password to something other than what is generated.  Store the password with your other service accounts and manage it accordingly.

Install the Add-On

  1. From your Splunk server dashboard (in this example, I’m using Splunk Enterprise 7.0.3), click on Splunk Apps.
  2. In the search box, type Office 365, and then click the Install button next to the Reporting Add-On.
  3. If prompted, confirm credentials and terms, and then select Login and install.
  4. Wait while the plugin is installed.
  5. Click the Restart Now button.


  1. After Splunk has restarted, log in.
  2. From the home page, select the Microsoft Office 365 Reporting Add-on for Splunk tile.
  3. On the Add-on home page, click Configuration.

  4. Click the Add button.
  5. Enter a display name and credentials for the account created previously, and then click Add.
  6. Verify that the account has been added to the console.
  7. Click Inputs.
  8. Click Create New Input.

  9. Give the input a friendly name, select an interval in seconds for the input to gather data, an index, an Office 365 account (from the previous step), and a start time.  Click Add when finished.  While Message Trace data can be returned from the portal for up to 90 days, the reporting API can only return it back 7.  This is not an error with Splunk, but a design limitation of the Office 365 Reporting API.
    Note: The UI doesn’t report the date limitation to you; you will just not see data appearing the portal.  You can verify this error by checking %ProgramFiles\Splunk\var\log\splunk\ta_ms_o365_reporting_ms_o365_message_trace.log for the following data:

    2018-05-06 12:57:07,204 ERROR pid=6992 tid=MainThread | HTTP Request error: 400 Client Error: Bad Request for url:$format=json&orderby=Received%20asc&$filter=StartDate%20eq%20datetime'2018-04-01T00:00:00Z'%20and%20EndDate%20eq%20datetime'2018-05-06T12:54:04.804000Z'

Looking for data in all the right places

  1. From the dashboard, click Search.
  2. Under What to Search, click the Data Summary button.
  3. Select the Sourcetypes tab.  You should see a source type for ms:o365:reporting:messagetrace, which will contain message trace data.
  4. Click on the ms:o365:reporting:messagetrace link to view collected data.

Until next time, keep on Splunking.

Published by Aaron Guilmette

Helping companies conquer inferior technology since 1997. I spend my time developing and implementing technology solutions so people can spend less time with technology. Specialties: Active Directory and Exchange consulting and deployment, Virtualization, Disaster Recovery, Office 365, datacenter migration/consolidation, cheese.

Reader Comments

  1. Hi ,

    I see there is always 24 hrs delay for source type “ms:o365:reporting:messagetrace” . See here the recentTime and lastTime. Deifference s 24hrs . Why so ? Please acknowledge .

    firstTime lastTime recentTime sourcetype
    1589553571 06/27/2020 18:16:43 06/28/2020 18:17:15 ms:o365:reporting:messagetrace

  2. Hi Aaron

    How will this work when MS will disable basic auth in October 2020?
    The reporting web service will then become obsolete.

    Any ideas what to do then?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.