Customised SQL Reporting - Displaying Driver Details & XML field types
I recently had a need to produce a full report to show the full INF driver details from within SCCM. We can see an original “Driver Source” folders from within the SCCM console as the path represents a field within the SCCM database.
The complete “Driver Source” (including driver file name) is not a simple field within any of the SCCM tables. As it turns out, driver information is embedded within an XML field called SDMPackageDigest that itself is part of the CL_ConfigurationItems table.
Thankfully, I was pleasantly surprised to discover that data with embedded XML files may also be accessed with a standard SQL call to produce useful lists from SQL Reporting.
My first task was to export an XML example of the data I needed from the SDMPackageDigest Field using SQL Server Management Studio. What I specifically needed to retrieve was the physical name of an INF file.
The first line in the XML file declares the namespace for the “DesiredConfigurationDigest” as http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/10/DesiredConfiguration. Knowing the namespace will allow SQL to selectively extract components out of the xml object.
From the XML displayed above, we can see that “INFFile” is an attribute hanging off the Nodal structure of:
DesiredConfigurationDigest -> Driver -> DriverIdentity
We can display this structure as:
This is enough to use SQL Reporting Services to create a report against the production database.
Creating a Reporting Services SQL Report
Most organisations running SCCM 2012 will also have SQL Reporting Services installed within their environment. Make sure you use Internet Explorer & open a URL to your Reports installation (in this case http://sccmserver/Reports ).
Select the Report Builder from the Reporting Services home menu.
The New Report Wizard will prompt on the type of report to be created. In this instance, I’m after a table of Drivers with source location details.
I have to create a data source – which requires an SQL connection to the SCCM database.
Once a working Data Source has been created you are able to design the SQL query that will retrieve Driver source details.
Because I know the table details that I do want to query, I’ll create the SQL query as text.
I’m going to primarily use data from the XML files embedded within the SDMPackageDigest field of the CI_ConfigurationItems table. I am going to also use the DisplayName property for each item from the CI_LocalizedProperties table. Note that the first line sets the default namespace to that used within by the XML itself. Also note that the “at” symbol “@” precedes an attribute while square brackets at the end of the various lines define the names of the destination columns.
The SQL syntax is:
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/07/1...' )
,CI_ConfigurationItems.SDMPackageDigest.value('(/DesiredConfigurationDigest/Driver/DriverIdentity/@Provider) ', 'nvarchar(MAX)') [Provider]
,CI_ConfigurationItems.SDMPackageDigest.value('(/DesiredConfigurationDigest/Driver/DriverIdentity/@Date) ', 'nvarchar(MAX)') [Date]
,CI_ConfigurationItems.SDMPackageDigest.value('(/DesiredConfigurationDigest/Driver/DriverIdentity/@Version) ', 'nvarchar(MAX)') [Version]
,CI_ConfigurationItems.SDMPackageDigest.value('(/DesiredConfigurationDigest/Driver/DriverIdentity/@INFFile) ', 'nvarchar(MAX)') [INFFile]
,CI_ConfigurationItems.ContentSourcePath + '\' + CI_ConfigurationItems.SDMPackageDigest.value('(/DesiredConfigurationDigest/Driver/DriverIdentity/@INFFile) ', 'nvarchar(MAX)') [FullPath]
INNER JOIN CI_LocalizedProperties
ON CI_ConfigurationItems.CI_ID = CI_LocalizedProperties.CI_ID
CI_ConfigurationItems.ContentSourcePath != N''
When run (the red exclamation mark), the example data is displayed below the query.
With the Data Source and Data Set defined, the report layout can be created. I’ll create a table using the Table Wizard from the toolbar.
The Table Wizard prompts over which dataset should be used to create the report table.
The desired fields have to be specified in the table as values.
The “next” button displays the layout of the table which may be customised.
The completed report can be saved back to the report server and run at any stage to produce a full list of driver details and file locations