MDT Monitoring Dashboard using PowerBI, Part 2/2

In my previous post, I described how I managed to populate the MDT Monitoring data using PowerShell and a fully automated MDT Deployment share.

Now, to access the data, you can do that using the powershell module or using the following URL http://MDTSERVER:9801/MDTMonitorData/. You will notice that there are 3 collections: Computer, ComputerIdentities and NextIDs. the one we’re interested in is “Computers”

It is a nicely formatted XML that we can then transform in PowerBI. It took me about an hour to tranform it into a convenient table using the following R request.

 Source = Xml.Tables(Web.Contents("http://MDTSERVER:9801/MDTMonitorData/Computers/")),
 #"Removed Columns" = Table.RemoveColumns(Source,{"title", "id", "updated", "link", ""}),
 #"Expanded entry" = Table.ExpandTableColumn(#"Removed Columns", "entry", {"id", "title", "updated", "author", "link", "category", "content"}, {"id", "title", "updated", "author", "link", "category", "content"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Expanded entry",{"id", "title", "updated", "author", "link", "category"}),
 #"Expanded content" = Table.ExpandTableColumn(#"Removed Columns1", "content", {"Attribute:type", ""}, {"Attribute:type", ""}),
 #"Expanded" = Table.ExpandTableColumn(#"Expanded content", "", {"properties"}, {"properties"}),
 #"Removed Columns2" = Table.RemoveColumns(#"Expanded",{"Attribute:type"}),
 #"Expanded properties" = Table.ExpandTableColumn(#"Removed Columns2", "properties", {""}, {""}),
 #"Expanded" = Table.ExpandTableColumn(#"Expanded properties", "", {"Name", "PercentComplete", "Settings", "Warnings", "Errors", "DeploymentStatus", "StartTime", "EndTime", "ID", "UniqueID", "CurrentStep", "TotalSteps", "StepName", "LastTime", "DartIP", "DartPort", "DartTicket", "VMHost", "VMName"}, {"Name", "PercentComplete", "Settings", "Warnings", "Errors", "DeploymentStatus", "StartTime", "EndTime", "ID", "UniqueID", "CurrentStep", "TotalSteps", "StepName", "LastTime", "DartIP", "DartPort", "DartTicket", "VMHost", "VMName"}),
 #"Expanded PercentComplete1" = Table.ExpandTableColumn(#"Expanded", "PercentComplete", {"Element:Text"}, {"PercentComplete"}),
 #"Expanded Warnings" = Table.ExpandTableColumn(#"Expanded PercentComplete1", "Warnings", {"Element:Text"}, {"Warnings"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Expanded Warnings",{{"Warnings", type number}}),
 #"Expanded Errors" = Table.ExpandTableColumn(#"Changed Type", "Errors", {"Element:Text"}, {"Errors"}),
 #"Expanded DeploymentStatus" = Table.ExpandTableColumn(#"Expanded Errors", "DeploymentStatus", {"Element:Text"}, {"DeploymentStatus"}),
 #"Expanded StartTime" = Table.ExpandTableColumn(#"Expanded DeploymentStatus", "StartTime", {"Element:Text"}, {"StartTime"}),
 #"Changed Type2" = Table.TransformColumnTypes(#"Expanded StartTime",{{"StartTime", type datetime}}),
 #"Expanded EndTime" = Table.ExpandTableColumn(#"Changed Type2", "EndTime", {"Element:Text"}, {"EndTime"}),
 #"Changed Type3" = Table.TransformColumnTypes(#"Expanded EndTime",{{"EndTime", type datetime}}),
 #"Expanded LastTime" = Table.ExpandTableColumn(#"Changed Type3", "LastTime", {"Element:Text"}, {"LastTime"}),
 #"Changed Type4" = Table.TransformColumnTypes(#"Expanded LastTime",{{"LastTime", type datetime}}),
 #"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"ID", "UniqueID", "CurrentStep", "TotalSteps", "StepName", "DartIP", "DartPort", "DartTicket"}),
 #"Changed Type5" = Table.TransformColumnTypes(#"Removed Columns3",{{"Errors", type number}}),
 #"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"Settings"}),
 #"Replaced Value" = Table.ReplaceValue(#"Removed Columns4","3","Completed",Replacer.ReplaceText,{"DeploymentStatus"}),
 #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Running",Replacer.ReplaceText,{"DeploymentStatus"}),
 #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","4","Unresponsive",Replacer.ReplaceText,{"DeploymentStatus"}),
 #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","2","Failed",Replacer.ReplaceText,{"DeploymentStatus"}),
 #"Added Custom" = Table.AddColumn(#"Replaced Value3", "ElapsedTime", each [EndTime]-[StartTime]),
 #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"ElapsedTime", type duration}}),
 #"Inserted Total Minutes" = Table.AddColumn(#"Changed Type1", "ElapsedTimeTotalMinutes", each Duration.TotalMinutes([ElapsedTime]), type number),
 #"Inserted Round Up" = Table.AddColumn(#"Inserted Total Minutes", "Duration", each Number.RoundUp([ElapsedTimeTotalMinutes]), Int64.Type)
 #"Inserted Round Up"

OK, I lied, I didn’t write that myself, as you can tell, the names are automatically generated and it took me about 10 minutes to do it using PowerBI Desktop🙂

I was interested in the status and the duration. the status is a integer and I had to replace the values 1/2/3/4 by Running/Failed/Completed/Unresponsive. That’s easy enough with the “Replace value function.

The duration, was trickier and needed 4 steps:

  1. calculate the time between start and finish, that was done by adding a custom column with the following code : = Table.AddColumn(#”Replaced Value3″, “ElapsedTime”, each [EndTime]-[StartTime])
  2. Transform that column into a duration : = Table.TransformColumnTypes(#”Added Custom”,{{“ElapsedTime”, type duration}})
  3. Insert a new column that calculates the total number of minutes in that duration : = Table.AddColumn(#”Changed Type1″, “ElapsedTimeTotalMinutes”, each Duration.TotalMinutes([ElapsedTime]), type number)
  4. and finally rounding that up so that it would stack nicely in a bar/pie graph in the report: = Table.AddColumn(#”Inserted Total Minutes”, “Duration”, each Number.RoundUp([ElapsedTimeTotalMinutes]), Int64.Type)

Finally, the GUI, as I said, I was interested in the Status, and I wanted to show the amount of computers that succeeded or failed in a pie chart. That’s probably the easiest chart in PowerBI. To create that chart, click on the pie chart icon in the visualization section. then drag the “DeploymentStatus” field in the Details Section and drag the “Name” field in the Values section, and Voila🙂

For the duration, I wanted to group computers that lasted the same time, so that I could try to pinpoint which ones are taking longer. I decided to go with a bar chart, to do so, I dragged the “Duration” field in the Axis section and I dragged the “Name” field in the Value section.

now the magic of PowerBI is that  those charts are linked by default, if you click on a pie section, it will update the bar chart so that you can visualize the data for those selected computers. as you can see below, if I click on the “Completed” section, the bar chart is updated

Now that you have the R query and a few examples, it’s your turn to impress you boss and provide a shiny ((tm) Johan Arwidmark who was waiting for this post) dashboard showing your deployments progress.

I wanted to thank Johan who helped me with an important part of this. By default, the monitoring data is retained 3 days, if you need to monitor the data for a longer period of time, you need to modify the config file as described in Johan’s blog post 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s