Reporting on 1E Nomad Enterprise activity in SCCM

Most 1E products have dedicated reporting, however Nomad Enterprise does not.
This may be because adverts are already well reported on by SCCM via Status Messages, and usually the buck stops here. However you may still want to know more
about how it actually got there…
I order to “see the Nomad story” we can actually take advantage of Status Messages as well, and hence this will allow us to report on Nomad directly in SCCM!

First we need to configure Nomad to send the status messages.
You can do this at install time using the MSI property STATUSMSGEVENTS, or after the install directly in the registry or via GPO.
The possible values for this can be found in the Admin guide, I am not going to copy paste this massive document here :)
Be careful not to simply select “Everything” as this can be quite spammy! The report I wrote uses these messages:
Evt_Rqst_Started >>> I use this to identity the Package that we download and the start time
Evt_MasterChanged >>> This is being used to see from which machine this client actually downloads.
Evt_Completed >>> If Nomad finished successful this is used to record the end time.
Evt_Error >>> If Nomad errors for any reason this is used to record the end time as well as the detailed error message.
This means I have to set my STATUSMSGEVENTS to 0x0000002064

Now onto the report itself
This is actually an older report I did for a customer back in SMS 2003 times. I was surprised to find out the query still worked fine for SCCM and the latest Nomad,
so I thought let me polish it back up and share it on my blog.
This really answers who on the subnet became the master? How long did the master take to download it from the DP, and how soon did everyone finish downloading it,
of course it also is a good tool to find machines that had any kind of error.


Here I have chosen to display one advert but for all sites and packages. I was cheeky and hard coded (non existing) DP path in SCCM halfway through to get some error messages.

SQL Challenges
There where numerous challenges in this one!
First of all the Status messages are split into many rows, I joined them back up via FOR XML as a kind of rows concatenation trick.  Let me show you.
Say you have a sales territory team: Tom, Jerry, Micky and Mouse.  (Copyright expired on these names, right? Damn you Walt)
I am going to aggregate the whole team and display the sum. However I also would like to display each Team members name in order of sales.

SELECT TerritoryID
, SUM(SalesYTD) AS TerritorySales
, Accounts = REPLACE((            SELECT
SalesPersonID AS [data()]
FROM
[AdventureWorks].[Sales].[SalesPerson] XMLSales
WHERE
XMLSales.TerritoryID = Sales.TerritoryID
ORDER BY
SalesYTD
FOR XML PATH ('') ), ' ', ',')
FROM [AdventureWorks].[Sales].[SalesPerson] Sales
GROUP BY TerritoryID

This can be run against the good old sample Microsoft DatabaseAdventureWorks.
In effect this is like a glorified subquery. The REPLACE function is added to add commas between the sales reps. The MIN and MAX function can be used to similar effect but only for the largest and smallest of the aggregated values. Some of the status messages here contain 4 rows, so concatenation them together seemed like the best course of action. Later on I can get to my desired value via the SUBSTRING command.

The next challenge was the fact that only the Evt_Rqst_Started message contained the PackageId. This is made worse if you consider that each advert may really copy down multiple packages.
The way I solved it is to first define for each Package and Advert the start and end time, and then update the previous table with the PackageId between those timeframes.
I usually do not like using temp tables, but in this case it made it a whole lot easier. Due to this it’s advisable to filter by AdvertID – if filtration is needed – instead of PackageId.

Download

Followup 11/28/2012
This report has been further developed to have additional features such as reporting on BITS and TS downloads as well.
Additionally 1E has created 7 further reports on Nomad and bundled them into a reporting pack that you can freely request from 1E.
Here are some screenshots from the improved report(s). I am leaving this original report intact as this one still works fine and is a classic report, whereas the report pack is SSRS based.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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