2200 Western Court, Suite 400 Lisle, IL 60532
(888) 351-8324 (TECH)

Reporting in Office 365

Today I will be discussing reporting in Office 365 via the Reporting Web Service. Microsoft, by default, has reporting available in the admin portal but has some limitations. While there is a wealth of information available in the various reports, only 4 of these reports can be scheduled. If you have administrative access to your O365 tenant you can see all the available reports by clicking the admin icon in the app launcher and navigating to the Reports section.

 

Below are the reports that can be scheduled which leaves a lot to be desired so I will show you how you can leverage PowerBI and the Reporting Web Service to create an email analytics dashboard. 

 

PowerShell Reporting

As you can see admins can schedule the Mail Traffic Report, SPAM detections report, DLP policy report, and a report that shows when email matches a rule in Exchange Online. While you can use PowerShell to get this information as well – unless you are familiar with command lines and coding it might be difficult for you to reliably get the data you need. (Below are the commands to get the Mail Traffic SPAM report from PowerShell but as you can probably tell this is not a good way to quickly get the data you need.)

$Cred = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Cred -Authentication Basic -AllowRedirection
Import-PSSession $Session
Get-MailDetailSpamReport | Out-GridView

 

 

Office 365 Reporting Web Service

Microsoft has made the structured data available through a web based service that allows you to pull and display data from your tenant. You can read more about that here. In this demo we will be using the REST Reporting web service to create a customized dashboard. 

  1. Make sure you have an admisitrative level account in your O365 tenant to use for authentication when pulling in the data. 
  2. Access to PowerBI – You can use it free here 
  3. Launch PowerBI Desktop and it will ask you to authenticate.
  4. Now select the option to “Get Data” and click “OData Feed”. 
  5. Based on the report or data you wish to bring into PowerBI the URL will differ. Here is a complete list of all the available reports https://msdn.microsoft.com/en-us/library/office/jj984326.aspx. For this demonstration we will be working with the Mail Traffic Summary Report (https://msdn.microsoft.com/en-us/library/office/jj984307.aspx). Our URL for this will look something like this: 

    https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficSummary?$select=C1,C2&$filter=Category%20eq%20’TopMailRecipient’&$format=Atom

     

    Base URL: https://reports.office365.com/ecp/reportingwebservice/reporting.svc/

    Report : MailTrafficSummary

    Data to bring in: $select=C1,C2 

    Report Filter: $filter=category%20eq%20’TopMailRecipient’

    format of report : $format=Atom 

     

    The base URL will not change but the report you want will change. The data you bring in is dependent on the report you choose and using the msdn link to the mail traffic summary report you can find out what data is held in the C1 and C2 fields. The format is not so important but you also have the option of using Json as well. 

     

  6. Now we will click ok and we are prompted to authenticate. We will use the basic authentication and enter in your admin account credentials (email address & password)
  7. Once you hit connect, you will see a pop-up of a table with data from your query. At this point, you have the option to click edit and manipulate the data like rename the column, specify the data type within the column, etc. I usually rename the query to the report type to keep it easy when you begin adding multiple queries. Now we have a list of email addresses sorted by top mail recipients. 
  8. Now you have the options to load or edit. I would select edit and rename the query to MailTrafficSummary Top Recipients so that you can clearly identify it when you begin adding more and more data sources. I would also rename the column heading to define what data you are looking at. (e.g. C1 renamed to email address, C2 renamed to Received Mail). Lastly, I would change the column type of the received items (C2) column to whole number. To save choose save & apply in the upper left hand corner. 

 

Now that you have data you can create visualizations or bring in more data and relate it to your existing queries to create more usable and valuable dashboards. 

 

We are developing a reporting dashboard in PowerBI that our customers can leverage as part of our O365 cloud helpdesk which includes an unlimited backup for Exchange Online, SharePoint, and OneDrive data. {{cta(‘e357246d-2188-4c82-a103-4d0af5412bc9’)}}

 

Related:

https://www.systechinfo.com/news/2016/03/10/new-office-365-reporting-monitoring 

https://www.systechinfo.com/news/2016/05/05/office-365-unlimited-backup-support 

 

 

 

 

 

Related Posts