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

Comments

  • Anonymous
    January 01, 2003
    Sorry for taking a while to respond folks. This procedure was written for Configuration Manager 2012 and it also works with Configuration Manager 2012 R2.

    The "Parameter validation failed" error happens when report parameters are not properly configured. I suspect that you have not properly configured the "@input" and "@result" parameters. Please re-read the blog section which discusses how to configure those two parameters. They must be set to 'Allow null value' and their visibility set to 'Internal'.

  • Anonymous
    January 01, 2003
    Still doesn't work.

  • Anonymous
    January 01, 2003
    Good to see snapshots

  • Anonymous
    January 01, 2003
    @Andy, Al and others who say it doesn't work, please describe what exactly doesn't work

  • Anonymous
    January 01, 2003
    Hi, Like Andy we also get an error when running the report. We use SCCM2012R2. "Parameter validation failed. It is not possible to provide valid values for all parameters." "Microsoft.ConfigurationManagement.ManagementProvider.SmsException Parameter validation failed. It is not possible to provide valid values for all parameters. Stack Trace: at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters() at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection1 navigationParameters, IResultObject resultObject) at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument) ------------------------------- System.Web.Services.Protocols.SoapException Parameter validation failed. It is not possible to provide valid values for all parameters. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Parameter validation failed. It is not possible to provide valid values for all parameters. Stack Trace: at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.GetParameters() at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ParameterPresenter.LoadParameters(IReport report, Collection1 navigationParameters, IResultObject resultObject) at Microsoft.ConfigurationManagement.AdminConsole.SrsReporting.ReportViewerPresenter.Worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument) -------------------------------

  • Anonymous
    January 01, 2003
    @Marko, thanks for pointing that out. Try the following in report builder:
    1. Expand the Datasets folder. Right-click on the dataset and select 'Dataset Properties'. Go to the Parameters tab, and delete the @input and @result parameters.
    2. Expand the Parameters folder. Delete the @input and @result parameters, if present.
    3. Save the report, and try it from the Configuration Manager console again.
    Please confirm that this works, and I will update the blog.

  • Anonymous
    November 03, 2013
    No longer works in SCCM 2012, is there going to be an update?

  • Anonymous
    November 07, 2013
    Anny updates on SCCM 2012? Would be great.

  • Anonymous
    February 13, 2014
    The comment has been removed

  • Anonymous
    March 18, 2014
    Hi Kan,

    I just wanted to say that I tried to configure everything as explained above, but report won't start in SCCM 2012 R2 console with "Parametar validation failed". Also, report works just fine in Report Builder 3.0.

    Regards,

    Marko

  • Anonymous
    March 19, 2014
    Hi Kan,

    i can confirm that it is working on ConfigMgr 2012 R2 with your recent changes (deleting the Parameters)

    Thanks for this awesome work!

    Ben

  • Anonymous
    September 29, 2014
    Thank You. I tried a dozens of scripts to no avail, this one worked.

  • Anonymous
    October 03, 2014
    I tried the script, it seemed to work on win7 machines. I don't get reports on my XPs? Thanks.

  • Anonymous
    March 02, 2015
    Hi. For all who have problem ""Parameter validation failed. It is not possible to provide valid values for all parameters." Try to import mof file in the CAS Role Server ( in Central Site )

  • Anonymous
    May 22, 2015
    The comment has been removed

  • Anonymous
    June 11, 2015
    Receiving error in the InventoryAgent.log of Windows 7 SP1 machine and they are not showing in the report. "unknown error encountered processing an instance of class wmimonitorid: 8004100c". "Not Supported (0x8004100C)" is the error translation. I've tried to query this class with WBEMTEST and a vbs script, returning the same error code (0x8004100C). Has anyone ran into this before and knows how to move past it?

  • Anonymous
    July 07, 2015
    The comment has been removed

  • Anonymous
    July 22, 2015
    This is working for me , but in my organization we have Dual desktop machines as well. For single monitor i'm getting details with no issue, but for dual desktop monitors i'm getting "16843009" value in serial no tab (some dual desktop machines giving data but 90% failed to get proper serial no of monitor). What can be the issue, any idea folks here.. Thank you in Advance..

  • Anonymous
    September 22, 2015
    My report shows blank columns for:
    1) Manufacturer Name Conv
    2) Product Code IDConv
    3) Serial Number IDConv

    Can anyone tell me why?

  • Anonymous
    October 06, 2015
    Hi,
    same problem as with Mike Compton.... setup is SCCM 2012 R2 SP1

  • Anonymous
    December 23, 2015
    This is brilliant! Does exactly what I needed it do. Many many thanks!

  • Anonymous
    January 08, 2016
    Hi,

    It seems that at least on SCCM 2012 R2 sp1 monitor serial number is in plain text on DB. So you can just use normal queries to get serial number, no need to convert.

  • Anonymous
    February 03, 2016
    Great report but can also see the huge bottleneck once have maybe 5k computers with duplicate monitors. The conversion loop will take awhile

  • Anonymous
    February 05, 2016
    This is awesome. However, like Mike Compton and Tommi, I've noticed that for quite a few monitors there is data missing in the SQL query. For instance, in resource explorer for a desktop with 2 Samsung S22C450 monitors I see values in all fields (I'm showing values for 1 of the 2 monitors below):

    Active 1
    Instance Name DISPLAYSAM09C45&3b1a3fa7&0&UID1048832_0
    Manufacturer Name SAM
    Product Code ID 09C4
    Serial Number ID HCLF505052
    User Friendly Name S22C450
    User Friendly Name Length 13
    Week Of Manufacture 22
    Year Of Manufacture 2014

    But when I run the sql query provided I get this:

    Name0 DESKTOPCOMPUTER123
    InstanceName0 DISPLAYSAM09C45&3b1a3fa7&0&UID1048832_0
    UserFriendlyNameConv VG932 SERIES
    UserFriendlyNameLength0 13
    ManufacturerNameConv
    ProductCodeIDConv
    SerialNumberIDConv
    YearOfManufacture0 2014
    WeekOfManufacture0 22

    Any ideas?

  • Anonymous
    February 05, 2016
    The formatting of my previous post changed when it was published. I want to explain that the sql query returns null values for the following fields:

    ManufacturerNameConv
    ProductCodeIDConv
    SerialNumberIDConv

    Thanks...

  • Anonymous
    February 15, 2016
    Some Monitors or OperatingSystems dont write arrays of ASCII numbers,
    I leave them and convert only arrays:

    update @convertTab set ManufacturerName0 = @out where ManufacturerName0 = @mfg and LEN(ManufacturerName0) > 12


  • Anonymous
    March 03, 2016
    Hi, I got this to work but needed to change in the report: from v_GS_WMIMONITORID to from v_GS_WMI_MONITOR_ID0