Querying Against XML and Saved Reports In Notification Server

I recently was presented with a situation where I was asked to begin pulling reports on what changes in our asset environment on a monthly basis. What the executives said they wanted is a snapshot of what our assets and ownership look like at the first week of every month, what they look like at the last week of every month, and then they wanted a report that would give them the difference between the two snapshots. This information should tell them what assets were deployed over the past month, what assets were removed from production over the past month, and which assets simply changed ownership over the past month. My goal, being as lazy as I am, was to automate the whole thing so the snapshots, comparisons, and reporting all happens while I spend my time on other things.

 Immediately, I thought of the Notification Server’s ability to store saved reports. I could set a report of asset ownership to run on a weekly schedule and the results of those reports are stored in the database, retrievable at any time. In case you are unfamiliar with saved reports, you can set them up by opening up a report on the NS and selecting the “Schedule this report to run” link.

I set my report to run every Sunday morning.

That takes care of the snapshot portion of the project.

Now I needed to set up additional reports that would provide the snapshot comparisons. Right off the bat, I didn’t think this would be too difficult. I imagined that the saved reports would be stored somewhere in the Altiris database and I would simply be able to run a SQL query against them to pull the differences between the beginning and the end of the month. As it turned out, I was half right. They are all stored in the Altiris database, but they cannot be easily queried against. Saved reports are stored in the SavedReport table, but not in a format that is friendly for running queries against. Basically, each saved report is stored as a single row of data. Each row also contains various other information about the snapshot such as GUID of the original report, when the snapshot was taken, description information, etc. The results of the snapshot are stored in the “State” column. Imagine! The results of an entire report, thousands of lines long, all stored in a single cell! The good news is that at least snapshots are stored in XML format. This means that the format is going to be consistent from one snapshot to the next. Now the trick is using SQL to query XML
Before we dive in to some hard core SQL, let’s take a look at what report results look like when they are in XML format. If you want to follow along, an easy way to do this is to make sure you have at least one saved report, and then run the following query through Query Analyzer:

Select Top 1 State From SavedReport

I say use Query Analyzer because it will come back with the result in just a couple of seconds, where as if you run that same query through the report builder in Notification Server, it will hang and eventually crash IE (at least it did for me). I have a hunch that NS may not be written to handle single columns that are thousands of lines long. When running the query in Query Analyzer, you will get something like this as the result:

Copy the contents of this cell and then paste them into a new text file and change the extension to .xml. At this point, to look at the XML I would suggest opening your new XML file with Internet Explorer. The reason for using IE is because it formats the XML properly, where as if you open with something like Notepad, it will just look like an unintelligible mess. Your results should look something like this little portion from the top of my file:

Notice how everything is organized in a hierarchical tree with <results> at the top. Looking down the tree a little ways you come to a <Table> tag. These table sections are the ones that hold the information that we are interested in. Between each <Table> and </Table> are the results of one row from your report with a tag specific to each column. In my example above I have a Serial Number column (“_x0020_” translates into a space), an Owner column, a UserID column, and a Location column.

To access our XML data through SQL, we are going to use some commands from the XQuery language. These commands are supported by Transact-SQL, which is what we used in Query Analyzer, and what is understood by Notification Server. The first step is to store the XML snapshot in something we can query against, so let’s take the contents of our “State” cell and store them in a variable.

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)

That should do it. Now we have a variable “@xml” that contains all the data from the first “State” column in the SavedReport table. You can verify that worked by doing a simple Select statement following your declaration:

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)
SELECT @xml As 'state'

Look familiar? 

Typically I would start my query with a the “SELECT” portion of the SQL statement, but in this case, for explanation purposes, I think that it will make more sense to first jump down to the “FROM” portion of the query. We mentioned above that the information we are concerned with is held in the <Table> sections of the XML file. In XML, each of these bracketed items like <results>, <dataset>, and <Table> for instance, are called “nodes”, so we want our “FROM” statement to step down to the <Table> node so our “SELECT” statement can look at the values inside it. Below is an example with the “FROM” line filled in. The t(c) at the end refers to “table” and “column”. This is the Alias of our XML table. You can change it to anything you like as long as you have one to represent the table and one to represent to the column. For example, you could use x(y), or myTable(myColumn) etc. Just make sure you have both.

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)
SELECT …
FROM @xml.nodes('/results/dataset/Saved/Table') t(c)

Now that we know where the data will be coming from, we can start working on our “SELECT” statement. Our “SELECT” statement will use the “value” command to retrieve the results for each of the rows in the XML file that we care about. I will start with my “UserID” column. We start the statement with our the table and column alias that will tell it where we are getting the data, then we use the “value” command which takes two arguments. The first is the location of the value we want to pull along with the number of values per row to return. The number of values per row should always be “[1]”. The second argument is the data type that should be returned. You can name your column as I have if you like just as you normally would. Pulling the “UserID” column from my XML would look like this:

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)
SELECT t.c.value('./UserID [1]','varchar(10)') as 'User ID'
FROM @xml.nodes('/results/dataset/Saved/Table') t(c)

You can now continue this process to return as many values as you like. Here is an example of what mine would look like if I wanted to return all the values from my XML table:

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)
SELECT 	t.c.value('./Serial_x0020_Number [1]','varchar(50)') as 'Serial',
	t.c.value('./Owner [1]','varchar(50)') as 'Owner',
	t.c.value('./UserID [1]','varchar(10)') as 'User ID',
	t.c.value('./Location [1]','varchar(50)') as 'Location'
FROM @xml.nodes('/results/dataset/Saved/Table') t(c)

This should give you the basics of querying XML. You can now query saved reports, and tie one saved report to another, or even tie them to other data sources. For example, let’s say I have a saved report that gives me the information above, but I also want to know the model information for each asset. I can tie my saved report to the Inv_AeX_HW_Serial_Number table and retrieve model information:

DECLARE @xml AS XML
SET @xml = (Select Top 1 State From SavedReport)
SELECT 	t.c.value('./Serial_x0020_Number [1]','varchar(50)') as 'Serial',
	t.c.value('./Owner [1]','varchar(50)') as 'Owner',
	t.c.value('./UserID [1]','varchar(10)') as 'User ID',
	t.c.value('./Location [1]','varchar(50)') as 'Location',
	sn.[Computer Model]
FROM @xml.nodes('/results/dataset/Saved/Table') t(c)
Left Join Inv_AeX_HW_Serial_Number sn
On sn.[Serial Number] = t.c.value('./Serial_x0020_Number [1]','varchar(50)')

That's it in a nutshell. Using this method and expanding on the SQL, I was able to create the comparison reports that were requested. In case you are interested, I have posted and commented one of my comparison reports below.

--SAVED REPORT IS RUN ON A WEEKLY BASIS, SO 4 REPORTS = 1 MONTH
--ASSETS THAT HAVE CHANGED OWNERSHIP IN THE PAST 30 DAYS
DECLARE @oldXml xml
DECLARE @newXml xml
--PULL THE XML FROM THE SAVED REPORT 4 WEEKS AGO
SET @oldXml = (Select top 1 [State] from
		(Select top 4 *
		From SavedReport
		Where ReportGuid = '81305921-9102-40ce-8efe-e3b341b1fa84'
		order by [CreatedDate]desc
	     ) savedReports
	     order by [CreatedDate])
--PULL THE XML FROM THE MOST RECENT SAVED REPORT
SET @newXml = (Select top 1 [State] from
		(Select top 4 *
		From SavedReport
		Where ReportGuid = '81305921-9102-40ce-8efe-e3b341b1fa84'
		order by [CreatedDate]desc
	    ) savedReports
	    order by [CreatedDate]desc);
/*USE A COUPLE OF WITH STATEMENTS TO STORE MY NEW AND OLD RESULTS IN MEMORY FOR EASIER COMPARISON LATER ON*/
--OLD SAVED REPORT TABLE
With old([Owner's Cost Center],[Owner],[Asset Type],[Charged To Cost Center],[Serial Number],[Name/Mobile Number],[UserID],[Location],[Model/Carrier],[Data],[Voice],[Text]) 
AS( 
	SELECT isnull(x.y.value( '(./Owner_x0027_s_x0020_Cost_x0020_Center)[1]', 'VARCHAR(20)' ),'') 'Owner''s Cost Center', 
	x.y.value( '(./Owner)[1]', 'VARCHAR(100)' ) 'Owner', 
	x.y.value( '(./Asset_x0020_Type)[1]', 'VARCHAR(30)' ) 'Asset Type', 
	isnull(x.y.value( '(./Charged_x0020_To_x0020_Cost_x0020_Center)[1]', 'VARCHAR(20)' ),'') 'Charged To Cost Center', 
	x.y.value( '(./Serial_x0020_Number)[1]', 'VARCHAR(50)' ) 'Serial Number', 
	x.y.value( '(./Name_x002F_Mobile_x0020_Number)[1]', 'VARCHAR(20)' ) 'Name/Mobile Number', 
	isnull(x.y.value( '(./UserID)[1]', 'VARCHAR(20)' ),'') 'UserID', 
	x.y.value( '(./Location)[1]', 'VARCHAR(20)' ) 'Location', 
	x.y.value( '(./Model_x002F_Carrier)[1]', 'VARCHAR(20)' ) 'Model/Carrier', 
	x.y.value( '(./Data)[1]', 'VARCHAR(20)' ) 'Data', 
	x.y.value( '(./Voice)[1]', 'VARCHAR(20)' ) 'Voice', 
	x.y.value( '(./Text)[1]', 'VARCHAR(20)' ) 'Text' 
	FROM @oldXml.nodes('/results/dataset/Saved/Table') x(y) 
), 
--NEW SAVED REPORT TABLE
new([Owner's Cost Center],[Owner],[Asset Type],[Charged To Cost Center],[Serial Number],[Name/Mobile Number],[UserID],[Location],[Model/Carrier],[Data],[Voice],[Text]) 
AS( 
	SELECT isnull(x.y.value( '(./Owner_x0027_s_x0020_Cost_x0020_Center)[1]', 'VARCHAR(20)' ),'') 'Owner''s Cost Center', 
	x.y.value( '(./Owner)[1]', 'VARCHAR(100)' ) 'Owner', 
	x.y.value( '(./Asset_x0020_Type)[1]', 'VARCHAR(30)' ) 'Asset Type', 
	isnull(x.y.value( '(./Charged_x0020_To_x0020_Cost_x0020_Center)[1]', 'VARCHAR(20)' ),'') 'Charged To Cost Center', 
	x.y.value( '(./Serial_x0020_Number)[1]', 'VARCHAR(50)' ) 'Serial Number', 
	x.y.value( '(./Name_x002F_Mobile_x0020_Number)[1]', 'VARCHAR(20)' ) 'Name/Mobile Number', 
	isnull(x.y.value( '(./UserID)[1]', 'VARCHAR(20)' ),'') 'UserID', 
	x.y.value( '(./Location)[1]', 'VARCHAR(20)' ) 'Location', 
	x.y.value( '(./Model_x002F_Carrier)[1]', 'VARCHAR(20)' ) 'Model/Carrier', 
	x.y.value( '(./Data)[1]', 'VARCHAR(20)' ) 'Data', 
	x.y.value( '(./Voice)[1]', 'VARCHAR(20)' ) 'Voice', 
	x.y.value( '(./Text)[1]', 'VARCHAR(20)' ) 'Text' 
	FROM @newXml.nodes('/results/dataset/Saved/Table') x(y) 
) 
--PULL DATA FROM MY TABLES IN MEMORY WHERE SERIALS MATCH, BUT OWNERS DO NOT
Select old.[Serial Number], 
	old.[Owner's Cost Center]'Old Cost Center', 
	old.[Owner]'Old Owner', 
	new.[Owner's Cost Center]'New Cost Center', 
	new.[Owner]'New Owner', 
	old.[Asset Type]'Type' 
from old full outer Join new on old.[Serial Number] = new.[Serial Number] 
Where old.[Owner] <> new.[Owner] 
order by old.[Serial Number]