Reporting Monitor information with Configuration Manager

  • Article

*No scripts or tools required on client computers*

Whereas automated inventory of display monitors in Windows XP was a little arduous, starting in Windows Vista, Microsoft introduced the WMI WmiMonitorID class in the “root\wmi” namespace. This class contains monitor information including serial number, manufacturer and friendly name. Inventorying from WMI with Configuration Manager is easy but making information of the monitor data can be challenging because most of the monitor data is stored in WMI as arrays of ASCII numbers. For example, the manufacturer name ‘DEL’ (for Dell) is stored as “68,69,76,0,0,0,0,0,0,0,0,0,0,0,0,0” in the WMI class. Some proposed solutions on discussion forums suggest creating another WMI class on each computer, converting the data using scripts or tools into the new WMI class, and then inventorying the newly created WMI class with the converted data. In this blog post, I will introduce a different approach – inventorying the data in its raw format, and converting it only when viewing a report. This approach requires no custom tools or scripts running on each client computer.

Step 1 – Inventory the raw data

With System Center 2012 Configuration Manager, you can browse WMI and select a class to add. However, as of this writing, it appears the ‘Add’ process does not properly detect array members in a class. The array members (ManufacturerName, ProductCodeID, SerialNumberID and UserFriendlyName) of the WmiMonitorID class are detected as single uint16 fields, resulting in data conversion errors. Therefore, save the following text as a .mof file and import it to Configuration Manager 2012 as follows:

[ SMS_Report (TRUE),   SMS_Group_Name ("Wmi Monitor ID"),   SMS_Class_ID ("MICROSOFT|WMIMONITORID|1.0"),   Namespace ("root\\\\wmi") ] class WmiMonitorID : SMS_Class_Template {     [ SMS_Report (TRUE), key ]     String     InstanceName;     [ SMS_Report (TRUE) ]     Boolean     Active;     [ SMS_Report (TRUE)]     UInt16     ManufacturerName[];     [ SMS_Report (TRUE)]     UInt16     ProductCodeID[];     [ SMS_Report (TRUE)]     UInt16     SerialNumberID[];     [ SMS_Report (TRUE)]     UInt16     UserFriendlyName[];     [ SMS_Report (TRUE) ]     UInt16     UserFriendlyNameLength;     [ SMS_Report (TRUE) ]     UInt8     WeekOfManufacture;     [ SMS_Report (TRUE) ]     UInt16     YearOfManufacture; };

Table 1 – MOF file contents for importing

  1. Save the above text as a .mof file
  2. In the Configuration Manager 2012 Administrators console, in the Administration workspace, under ‘Site Configuration \ Client Settings’, open the properties of ‘Default Client Settings’
  3. In the Default Settings window,
    1. Select the ‘Hardware Inventory’ tab.
    2. Click the ‘Set Classes’ button
    3. In the Hardware Inventory Classes window,
      1. Click the Import button
      2. Browse and select the mof file
      3. On the Import Summary screen, select ‘Import both hardware inventory classes and hardware inventory class settings’. Click Import.
    4. Click OK on each window until you exit the Default Client Settings properties window.
  4. On client computers, run (or wait for the next) Machine Policy Retrieval & Evaluation Cycle so they download policy with the new inventory settings, then run (or wait for the next) Hardware Inventory Cycle.
  5. After inventory has been collected on a client, verify by running Resource Explorer in the Configuration Manager console, that the client reports data for Wmi Monitor ID.

 

Step 2 – Convert the data when reporting

If you have a programming or scripting background, your first thought on converting an ASCII array such as “68,69,76,0,0,0,0,0,0,0,0,0,0,0,0,0” will probably be to use a “split” function to break it up, then convert each number. That is essentially the same process we will use but there are some challenges in doing this with TSQL because SQL Server 2008 does not have a built-in “split” function. You can create your own split function or stored procedure, but that would modify the Configuration Manager database, making it unsupported. One way around is to use the sp_executesql system stored procedure to execute dynamically written statements which do the split and conversion.

So, summarily, what we will do is:

  1. Define the conversion logic/statements that will be passed to sp_executesql to do the split and conversion
  2. Define a table variable with columns for both raw data and converted data
  3. Select the data to be reported on, and insert into the table variable
  4. Define a cursor to loop through the table variable, converting the raw data using sp_executesql and the conversion logic, and writing the converted results back to the table variable
  5. Return converted data from the table variable

The SQL query below is for a report of monitors by collection, using variable @CollID for the collection ID.

set nocount on

-- split and conversion logic declare @convCode nvarchar(1023) set @convCode = N'declare @indx int; declare @valToConvert varchar(4); ' + CHAR(13) + N'set @result=''''' + CHAR(13) + N'while LEN(@input) > 0 begin ' + CHAR(13) + N'select @indx = CHARINDEX('','', @input) ' + CHAR(13) + N'select @valToConvert = SUBSTRING(@input, 0, @indx)' + CHAR(13) + N'if (@valToConvert = ''0'') OR (@valToConvert = '''') break' + CHAR(13) + N'select @result = @result + CHAR(@valToConvert) ' + CHAR(13) + N'select @input = SUBSTRING(@input, @indx+2, LEN(@input) - @indx) end' declare @params nvarchar(500) set @params = N'@input varchar(255), @result varchar(255) OUTPUT'

-- table variable declare @convertTab table (     ResourceID int,     Active0 int,     InstanceName0 nvarchar(255),     ManufacturerName0 nvarchar(255),     ProductCodeID0 nvarchar(255),     SerialNumberID0 nvarchar(255),     UserFriendlyName0 nvarchar(255),     UserFriendlyNameLength0 int,     WeekOfManufacture0 int,     YearOfManufacture0 int,     ManufacturerNameConv varchar(255),     ProductCodeIDConv varchar(255),     SerialNumberIDConv varchar(255),     UserFriendlyNameConv varchar(255) ) -- select data to report on, into the table variable insert @convertTab     (ResourceID, InstanceName0, ManufacturerName0, ProductCodeID0, SerialNumberID0,     UserFriendlyName0, UserFriendlyNameLength0, WeekOfManufacture0, YearOfManufacture0) select     ResourceID, InstanceName0, ManufacturerName0, ProductCodeID0, SerialNumberID0,     UserFriendlyName0, UserFriendlyNameLength0, WeekOfManufacture0, YearOfManufacture0 from v_GS_WMIMONITORID where ResourceID in     (select ResourceID from v_FullCollectionMembership where CollectionID = @CollID)

-- cursor to iterate through table variable and convert declare convert_cursor cursor for select ManufacturerName0, ProductCodeID0, SerialNumberID0,UserFriendlyName0 from @convertTab declare @mfg varchar(255), @pcode varchar(255), @snum varchar(255), @fname varchar(255) declare @out varchar(255)

open convert_cursor fetch next from convert_cursor into @mfg, @pcode, @snum, @fname while @@FETCH_STATUS = 0 begin     exec sp_executesql @convCode, @params, @input=@mfg, @result=@out OUTPUT     update @convertTab set ManufacturerNameConv = @out where ManufacturerName0 = @mfg     exec sp_executesql @convCode, @params, @input=@pcode, @result=@out OUTPUT     update @convertTab set ProductCodeIDConv = @out where ProductCodeID0 = @pcode     exec sp_executesql @convCode, @params, @input=@snum, @result=@out OUTPUT     update @convertTab set SerialNumberIDConv = @out where SerialNumberID0 = @snum     exec sp_executesql @convCode, @params, @input=@fname, @result=@out OUTPUT     update @convertTab set UserFriendlyNameConv = @out where UserFriendlyName0 = @fname     fetch next from convert_cursor into @mfg, @pcode, @snum, @fname end close convert_cursor deallocate convert_cursor

set nocount off

-- return converted data select syst.Name0, cnvt.InstanceName0, cnvt.UserFriendlyNameConv, cnvt.UserFriendlyNameLength0, cnvt.ManufacturerNameConv, cnvt.ProductCodeIDConv, cnvt.SerialNumberIDConv, cnvt.YearOfManufacture0, cnvt.WeekOfManufacture0 from @convertTab cnvt join v_R_System syst on cnvt.ResourceID = syst.ResourceID

Table 2 – SQL Query for Report

 

To test the query in SQL Server Management Studio, you first need to define and initialize the @CollID variable before the script. E.g. you can add the following at the top of the query to test it for the All Systems collection.

declare @CollID varchar(8) set @CollID = 'SMS00001'

Table 3 – Variable declaration for testing SQL query in Management Studio

 

To create a report using Report Builder, use the query in Table 2 as the data source query. Do not use the contents of Table 3 in the report.  I will not go into details on how to create a report, but note the following when using the query in Report Builder:

  • Report Builder will automatically identify variables @CollID, @input and @result as report parameters.

    image002
    @input and @result are however internal variables whose values are set during execution.
    To make the report not prompt for values, edit the properties of @input and @result from the list of report parameters.

    • On the General tab of the parameter properties, check ‘Allow null value’ and set the parameter visibility to ‘Internal’. Do this only for @input and @result

      image003