Monitor Serial Number Report for SCCM 2012

    It is possible, however it is not a straight-forward task, to report on the serial number of the screens attached to the computers in your organization. It is done with the usage of WmiMonitorID Hardware Inventory Class, which resides in root\wmi namespace of the computer. You can review the entries present at the computer with the following command:

wmic /namespace:\\root\wmi PATH WmiMonitorID GET * /FORMAT:list

    You will receive the output similar to the following one:

wmic GET WmiMonitorID output



    The property, that is of our interest here obviously is mainly the SerialNumberID one, but you can make use of all the other ones if you find it beneficial for your organization's needs. As you will probably notice unfortunately the data stored in this WMI class is not in human-readable format, therefore it will need to be converted on the T-SQL query level in order to make a nice report out of it. It can be done with the use of the T-SQL query provided below 

Usage:

    First step is to modify Hardware Inventory Client Settings. In order to do so, you have to go to Administration pane in SCCM Console, choose the Default Settings and then click on the Set Classes ... button

    Next click on the Add ... button in order to add the new class. Then Connect ... and type in the root\wmi namespace to connect to.


    From the list choose the wmiMonitorId class and click on OK button.

    The class will appear now on the list of classes available for Hardware Inventory

    The next step depends on your intent. You can tick the box in here, and thus start collecting this WMI class from all the systems in your organization right away. I would however recommend to leave it unticked here and create additional Custom Client Device Settings, where you add this particular class to Hardware Inventory process. Then deploy this custom setting to the collection of computers, that you would like to obtain the serial numbers for. Next step is where the T-SQL query comes in play. With the use of Report Builder installed on your SCCM server you can. again depending on your needs, either create a brand new report, or, which is what I decided to do, use one of the built-in Microsoft reports, preferable the one with a drop-down list of device collections and swap the T-SQL query with the one shown below. If you choose to do the later remember to modify the description and tile fields as well as all the sorting rules/names of the data fields and al the other references to the values from the old report you decided to use as a template. The outcome looks as follows:

 

Monitor Details report in action

    Below you can find the code to put in two Datasets, that will be necessary to be included in the SCCM report

Additional Notes:

    I am not the author of the original version of the T-SQL query provided, meaning mainly the conversion logic. It had to be re-written though, as when implemented in real-life scenario it turned out, that many monitors store all the data shown on the first screen directly in human-readable format, which is where conversion failed leaving empty fields in the report. I included few CASE statements to mitigate the problem. It has been implemented and tested, and it works properly in the form provided. The printscreens provided show the real life implementation's outcome

Code:

DataSet0

SET NOCOUNT ON
DECLARE @input nvarchar(1023)
DECLARE @result nvarchar(1023)
-- 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,
 CASE WHEN cnvt.UserFriendlyNameConv = ''
  THEN cnvt.UserFriendlyName0
  ELSE cnvt.UserFriendlyNameConv
 END AS UserFriendlyName,
 CASE WHEN cnvt.ManufacturerNameConv = ''
  THEN cnvt.ManufacturerName0
  ELSE cnvt.ManufacturerNameConv
 END AS ManufacturerName,  
 CASE WHEN cnvt.ProductCodeIDConv = ''
  THEN cnvt.ProductCodeID0
  ELSE cnvt.ProductCodeIDConv
 END AS ProductCodeID,
 CASE WHEN cnvt.SerialNumberIDConv = ''
  THEN cnvt.SerialNumberID0
  ELSE cnvt.SerialNumberIDConv
 END AS SerialNumberID
FROM @convertTab cnvt
JOIN v_R_System syst ON cnvt.ResourceID = syst.ResourceID
ORDER BY SerialNumberIDConv DESC

DataSet1

SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs)  ORDER BY Name