Office 365 supplies a web service that enables you to extract a lot of information on email and spam, antivirus activity, compliance status and Lync Online activities – you can read more about this here.
I am one of the administratosr of our companys Office 365 account , and decided to see if I could use the Power BI tools to extract the information.
The web service contains many different reports where you can extract a lot of information.
In this example, I will show you how to extract information about inbound and outbound mail traffic per mailbox.
Using Power Query
So open a blank workbook and switch to the Power Query tab and select “From Web”
The web service report I want to use is called “MailTrafficSummary” – details about Fields and OData options can be found here
https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom
As specified in the parameters I want it returned as Atom meaning that we will get an xml file back from the web service. Because I am the administrator I will not get prompted for a username and password.
The file contains information about the message count per user and the structure can be seen below.
So right click the binary file and choose to open the file as XML
The XML is returned and we can begin to drill down for find the entry element we are interested in.
So after expanding and removing columns I have the nodes I am interested in
Then I use the transformation of the data to the proper datatypes and add columns for date and time values that could be relevant to group the data by.
Here are the steps applied
let
Source = Xml.Tables(Web.Contents(“https://reports.office365.com/ecp/reportingwebservice/reporting.svc/MailTrafficTop?$select= Date,Name,Direction,MessageCount&$filter=AggregateBy%20eq%20’Hour’&$format=Atom”)),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“id”, type text}, {“updated”, type datetime}}),
#”Expand entry” = Table.ExpandTableColumn(#”Changed Type”, “entry”, {“content”}, {“entry.content”}),
#”Expand entry.content” = Table.ExpandTableColumn(#”Expand entry”, “entry.content”, {“Attribute:type”, “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}, {“entry.content.Attribute:type”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”}),
#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” = Table.ExpandTableColumn(#”Expand entry.content”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, {“properties”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”}),
#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”, {“http://schemas.microsoft.com/ado/2007/08/dataservices”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”}),
#”Removed Columns” = Table.RemoveColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties”,{“id”, “title”, “updated”, “link”, “entry.content.Attribute:type”, “http://www.w3.org/XML/1998/namespace”}),
#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices” = Table.ExpandTableColumn(#”Removed Columns”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, {“Date”, “Name”, “Direction”, “MessageCount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”}),
#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”}),
#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount” = Table.ExpandTableColumn(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date”, “entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”, {“Element:Text”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”}),
#”Renamed Columns” = Table.RenameColumns(#”Expand entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount”,{{“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.MessageCount.Element:Text”, “Messagecount”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Direction”, “Direction”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Name”, “Mailbox”}, {“entry.content.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata.properties.http://schemas.microsoft.com/ado/2007/08/dataservices.Date.Element:Text”, “DateTime”}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“DateTime”, type datetime}, {“Messagecount”, Int64.Type}}),
#”Inserted Day of Week” = Table.AddColumn(#”Changed Type1″, “DayOfWeek”, each Date.DayOfWeek([DateTime]), type number),
#”Inserted Hour” = Table.AddColumn(#”Inserted Day of Week”, “Hour”, each Time.Hour([DateTime]), type number),
#”Inserted Week of Year” = Table.AddColumn(#”Inserted Hour”, “WeekOfYear”, each Date.WeekOfYear([DateTime]), type number)
in
#”Inserted Week of Year”
After all these steps I get a nice clean table
This is returned to the data model of the workbook.
Then I add a measure to the model to summarize the MessageCount field.
And then we can start visualize the mail traffic in Excel …
So as you can see its fairly straight forward to get data from the usage of your Office 365 subscription into Excel and use the Power BI tools to analyze the data.
So check out all the other options/reports in the Office 365 Reporting web service and see if there is something you can use in your reporting.
Erik Svensen - Blog about Power BI, Power Apps, Power Query 