Published on October 27th, 2015 | by Fredrik Knalstad0
From Email to PowerBI desktop Chart in 5 minutes
The other day I got a question regarding counting huge amount of Alert Emails from System Center Operation Manager (SCOM). SCOM had sent out large amount of Alert Emails to an account and in every email there was a line in the Description that showed the automation job that had failed.
The Image bellow shows one of the emails and job that had failed (“Source:”)
Well we solved this in less than 5 minutes, with a little help from Power BI Desktop. Here was the process:
In outlook open a new message and select [Attach Item] then [Outlook Item]
Select all the Emails and insert the items as “Text Only”, this will add all the selected emails to the new message as plain text.
Now I got one huge email with a lot of unstructured information, save the entire mail as a text document.
Open up Power BI Desktop, Click on [Get-Data] and select [Text]
Browse to the location of the text file and open it up in Power BI Designer.
Now we have imported the text file it’s time to filter the data we need. Click on [Edit].
In the Power BI Designer Editor, select [Add Column] -> [Duplicate Column].
Now we got two identical columns with the same information.
Select the new copy column and on the [Home] tab, select [Group by].
We are now going to do a count on the values in the “Column1 – Copy” and we are going to select the operation “Count Rows”.
Then add a name that represent the new values for the column.
We can now see that every unique line has been counted in the new column.
This is great, now we need to filter this down to the information that we want.
The information we want starts with Source, so we need to remove all the lines that don’t start with Source. In the “Activity Line” column, select the drop down arrow to open up the filter and Sort options.
In the text filter area, add “Source:” this will select only the unique lines that start with Source. This is exactly what we need. Click [OK].
Now we only have the unique activity lines and count of how many times the alarm has been triggered.
To create a visual out of this data, select [Close & Apply].
In the Report area, select both the fields, the we will get an overview of all the activities.
We only want to view the alerts that has failed more than 10 times over this period, so we need to add a filter to the Count of Lines column. In the filters area, select the “Count of Lines” -> “is greater than or equal to” “10“. Then click [Apply Filter].
Next step is to upload the report to PowerBI.com and share the report with the team. All done in less than 5 minutes.