Custom WMI Class in SCCM 2012 for Hardware Warranty Information Part 2

In part 1 I covered how to create the new WMI class on each device and the powershell script used to interrogate the HP site and obtain warranty details and save to CSV files.

Part 2 here now covers using these CSV files and using a baseline item to read the CSV file and put the values back into the devices.

On the Primary Server I created a folder under INETPUB\WWWROOT called WarrantyDetails. Since IIS is always running on the CAS and Primary Servers, and to cater for any devices talking to SCCM, whether they are domain joined or DMZ/remote machines, we decided to use BITS for the transfer of the CSV file back from the SCCM servers to the individual devices.

So, as per Part 1, we want to create a new baseline item that gets the CSV file from the server, reads it, and fills in the blank WMI Class fields for Warranty Info and Warranty Date. Follow the instructions in Part for the creation of the baseline item. The detection and remediation scripts are below:


Discovery Script (Script Language : Powershell)

# If virtual then just say its compliant, there won't be any warranty details, otherwise if physical, force it to try warranty check

if ((Get-WmiObject Win32_ComputerSystem).model -match "VMWare")
{
    $WarrantyCheck = 1
}
else
{
    $WarrantyCheck = 0
}

$WarrantyCheck

Remediation Script (Script Language : Powershell)

$ComputerName = $ENV:COMPUTERNAME

Import-Module BITSTransfer
$WarrantyFile = $ComputerName + ".csv"
$SourceFile = "http://<NameofyourCASorPrimaryServer>/WarrantyInformation/$WarrantyFile"
$DestFile = "C:\Windows\temp\$WarrantyFile"

Start-BitsTransfer -source $SourceFile -destination $DestFile

if (get-childitem $DestFile)
{
    $WarrantyInfo = Import-Csv $DestFile

    Get-WmiObject WarrantyDetails | Remove-WmiObject

    [void](Set-WmiInstance -Path \\.\root\cimv2:WarrantyDetails -Arguments @{SerialNumber=$WarrantyInfo.SerialNumber;ProductNumber=$WarrantyInfo.ProductCode;WarrantyDate=$WarrantyInfo.WarrantyDate;WarrantyInfo=$WarrantyInfo.WarrantyInfo})
    Remove-Item $DestFile -force
}

And finally, we need to specify a Compliance Rule

• Make sure the Rule Type is Value, and the the Value Equals 1
•  Tick the box for Run the specified remediation script...and save

 And to test, run Powershell and run the command Get-WMIObject -class WarrantyDetail and you should now see the stored warranty information...ah, mine has expired :(

 

SCCM 2012 and returning Registry Keys to Hardware Inventory

If you've read my previous posts re custom WMI classes, you could create WMI classes via scripts and store any information you want in them, including registry entries.

If however you want to specifically return registry keys, then this is also possible.

What we'll end up with is this:

1st thing to do is create the new class. So, on your central CAS server (or your Primary server if you only have this) browse to your SCCM 2012 Installation Folder (usually C:\Program Files\Microsoft Configuration Manager\inboxes\clifiles.src\hinv. Locate the file called configuration.mof and edit this (flat text file)

Browse to the end of the file and add in the following:

#pragma namespace ("\\\\.\\root\\cimv2")
#pragma deleteclass("XXXRegistryValues", NOFAIL)
[DYNPROPS]
Class XXXRegistryValues
{
[key] string KeyName="";
String String;
Uint32 Integer;
};

So here we're creating the 3 fields that will show up in the HW inventory screen above, Keyname (string and the key), String (string field) for any text values and Integer (uint32 field) for any numerical data

Next we want to add 1 or more entries for each registry key you want to retrieve.

For example:

//Return the installed version of Powershell
[DYNPROPS]
Instance of XXXRegistryValues
{
    KeyName="PS_HKLM\\SOFTWARE\\Microsoft\\PowerShell\\1\\PowerShellEngine|PowerShellVersion";
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\PowerShell\\1\\PowerShellEngine|PowerShellVersion")
    ,Dynamic,Provider("RegPropProv")] String;
};

//Return the registered WSUS server for the device
[DYNPROPS]
Instance of XXXRegistryValues
{
    KeyName="WSUS_HKLM\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUServer";
    [PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUServer")
    ,Dynamic,Provider("RegPropProv")] String;
};
and so on... add as many as you need.

Before you save the file, load up CMTRACE and watch the log file
<SCCMInstallFolder>\Logs\dataldr.log and you'll see the configuration.mof file be processed once its saved. And again, as with the custom WMI classes, you'll see new tables appear in the SQL database, in the case above dbo.XXXRegistryValues_DATA and dbo.XXXRegistryValues_HIST, as well as views referencing these tables.

We now need to create a MOF file to import into SCCM to make it available via the Hardware Inventory.

Create a new text file and add the following:

#pragma namespace ("\\\\.\\root\\cimv2\\SMS")
#pragma deleteclass("RLGRegistryValues", NOFAIL)

[SMS_Report(FALSE),SMS_Group_Name("XXXRegistryValues"),SMS_Class_ID("XXXRegistryValues"),
SMS_Context_1("__ProviderArchitecture=32|uint32"),
SMS_Context_2("__RequiredArchitecture=true|boolean")]
Class XXXRegistryValues: SMS_Class_Template
{
[SMS_Report(FALSE),key] string KeyName;
[SMS_Report(FALSE)] string String;
[SMS_Report(FALSE)] Uint32 Integer;
};
Safe this as a file with a .MOF extension somewhere. We will now add the class to the Default Client Settings Hardware Classes - almost identical process to the one used for Custom WMI Classes.


Updating SCCM Client Base Classes for Hardware Inventory

Run the SCCM Management Console

• Click on Administration Section
• Click on Client Settings
• Right click on Default Client Settings, then properties (Has to be the default one to select the new class). If you have additional client settings you can enable and disable the class for collection afterwards, but it must first be selected via the Default Client Settings
• Click Hardware Inventory
• Click Set Classes and you'll see the following screen

 

• This time, click the Import button, and browse to the .MOF file we've just created above and click Open and you should see the results below

• Click Import to complete
• As a default the class won't be selected on the Hardware Inventory Classes screen. Either select the box to enable it for all devices (since we're editing the Default Client Settings) or OK out of this screen and edit your relevant Client Settings and enable as desired.

And thats it - the next time the Hardware Inventory runs, you'll start to see your Registry Keys returned and available via resource explorer (and more importantly they're all available for creating custom reports).

Powershell and SCCM 2012

It can be useful sometimes to kick off SCCM functions on remote desktops that may not be available through the SCCM Console. Here's a couple of examples.

Remotely Run a Baseline on a Device

$ComputerName = "A name of some device on your network"

#Retrieve all the baselines available on the device
$Baselines = Get-WmiObject -ComputerName $ComputerName -Namespace root\ccm\dcm -Class SMS_DesiredConfiguration

#Search these for a specific baseline name to obtain Baseline Name and Version

if ($Baselines)
{
    $BaselineName = $Baselines | Where-Object {$_.DisplayName -match "xxxxxxxx"} | Select-Object -ExpandProperty Name
    $BaselineVersion = $Baselines | Where-Object {$_.DisplayName -match "xxxxxxxx"} | Select-Object -ExpandProperty Version
    $MC = Get-WmiObject -List -Namespace root\ccm\dcm -ComputerName $ComputerName | Where-Object { $_.name -eq "SMS_DesiredConfiguration" }
 
    $R = $MC.TriggerEvaluation($BaselineName, $BaselineVersion, $true, $true)
    $R | Format-Table
}

Doing it all via WMI calls also allows you to specify Credentials on the Get-WMIObject if needed, and if you link this to a SQL query looking directly at your SCCM database (see post http://sccmshenanigans.blogspot.co.uk/2013/11/useful-sccm-sql-queries.html re SQL queries) then you can run a block refresh of Baselines across devices.

Remotely run Hardware Inventory

$ComputerName = "A name of some device on your network"

$MC = Get-WmiObject -List -Namespace root\ccm -ComputerName $ComputerName | Where-Object { $_.name -eq "SMS_Client" }

$SMSCli.TriggerSchedule("{00000000-0000-0000-0000-000000000001}")

As with the above example, if you link it to a SQL query you can bulk execute the HW Inventory scans.

Other schedules that you can kick of are:

Name	Schedule ID
HW Inventory	{00000000-0000-0000-0000-000000000001}
SW Inventory	{00000000-0000-0000-0000-000000000002}
Discovery Data Record	{00000000-0000-0000-0000-000000000003}
Machine Policy Retrieval & Evaluation	{00000000-0000-0000-0000-000000000021}
File Collection	{00000000-0000-0000-0000-000000000010}
SW Metering Usage Report	{00000000-0000-0000-0000-000000000022}
Windows Installer Source List	{00000000-0000-0000-0000-000000000032}
Software Updates Scan	{00000000-0000-0000-0000-000000000113}
Software Updates Store	{00000000-0000-0000-0000-000000000114}
Software Updates Deployment	{00000000-0000-0000-0000-000000000108}

A definitive list can be found via the ConfigMgr SDK under the section titled TriggerSchedule Method in Class SMS_Client

Custom WMI Class in SCCM 2012 for Hardware Warranty Information Part 1

Based on my previous post re Custom WMI Classes (http://sccmshenanigans.blogspot.co.uk/2013/11/custom-wmi-classes-and-reporting-into.html) here's another custom class and scripts for obtaining hardware warranty information.

Creating the new class

As with the previous example, run Powershell and run the command Get-WMIObject -class WarrantyDetails and you should get an error re Invalid Class

So, same as with the MonitorDetails class, the code below will create the WarrantyDetails custom WMI class

function Create-Wmi-Class()
{
    $newClass = New-Object System.Management.ManagementClass("root\cimv2", [String]::Empty, $null);

    $newClass["__CLASS"] = "WarrantyDetails";

    $newClass.Qualifiers.Add("Static", $true)
    $newClass.Properties.Add("SerialNumber", [System.Management.CimType]::String, $false)
    $newClass.Properties["SerialNumber"].Qualifiers.Add("key", $true)
    $newClass.Properties["SerialNumber"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("ProductNumber", [System.Management.CimType]::String, $false)
    $newClass.Properties["ProductNumber"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("WarrantyInfo", [System.Management.CimType]::String, $false)
    $newClass.Properties["WarrantyInfo"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("WarrantyDate", [System.Management.CimType]::String, $false)
    $newClass.Properties["WarrantyDate"].Qualifiers.Add("read", $true)
    $newClass.Put()
}

# Check whether we already created our custom WMI class on this PC, if not, create it
[void](Get-WMIObject WarrantyDetails -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror)
if ($wmiclasserror)
{
    try { Create-Wmi-Class }
    catch
    {
        "Could not create WMI class"
        Exit 1
    }
}

$sSerialNumber = ((Get-WmiObject -Class win32_bios).serialnumber).trim()
$sProductNumber = $null
$sProductNumber = (( Get-WmiObject -ComputerName $ComputerName -namespace "root\hp\InstrumentedBIOS" -Query "select * from HP_BIOSSetting" | Where-Object { ($_.Name -eq "SKU Number") -or ($_.Name -eq "Product Number") } ).Value).trim()

if ($sproductnumber -eq $null)
{
    $sProductNumber = (Get-ItemProperty HKLM:\\HARDWARE\Description\System\BIOS).SystemSKU
}

# Clear WMI
Get-WmiObject WarrantyDetails | Remove-WmiObject

# And store the data in WMI
[Void](Set-WmiInstance -Path \\.\root\cimv2:WarrantyDetails -Arguments @{SerialNumber=$sSerialNumber; ProductNumber=$sProductNumber})

You may need to customise the $sProductNumber information - the example above is specifically for HP kit.

Once the above class has been created, retest on your machine and you should see information

We'll come back to the blank WarrantyDate and WarrantyInfo later.

Following the steps in the previous post, add the new class to the Hardware Inventory classes on Default Client Settings.  Again, watching the dataldr.log file you should see new SQL tables and views created.

When creating the baseline item, the discovery script is almost identical to the previous post:


Discovery Script (Script Language : Powershell)
$ClassInfo = Get-WMIObject WarrantyDetails -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror

if ($wmiclasserror) { $ClassFound = 0 } else { $ClassFound = 1 }

$ClassFound

Obtaining Warranty Information (currently HP only)

If you do a Google search for any combination of script, warranty information, powershell, vb etc you'll find loads of posts with examples.  For HP unfortunately, all the posts I found had links for warranty lookup that no longer worked, either due to HP removing the link or redesigning their websites. It proved to be quite difficult to track down a working weblink, but I finally managed to work one out for desktop machines at least (have yet to work out or find a direct link that can be used for server kit)

The script below is specifically for HP kit but other links may/will exist for other manufacturers. The script reads the SQL table directly from SCCM for all devices with missing warranty information, scrapes the webpage for the relevant information, then stores the returned information in CSV files for later use.

#Get the relevant records directly from SQL for any reported devices with no warranty information
$SQLSelect = "SELECT ass.ResourceID, ass.Name0, ass.Model0, ass.Domain0, ass.Manufacturer0, war.SerialNumber0,
war.ProductNumber0, war.WarrantyInfo0, war.WarrantyDate0
FROM v_GS_Computer_System ass
INNER JOIN v_R_System sys on ass.ResourceID=sys.resourceID
INNER JOIN v_GS_PC_BIOS bios ON ass.ResourceID = bios.ResourceID
INNER JOIN v_GS_WARRANTYDETAILS war ON ass.ResourceID = war.ResourceID
WHERE ass.Manufacturer0 NOT LIKE 'VMWare%' and war.WarrantyInfo0 is null
AND sys.client0=1 AND sys.obsolete0=0 AND sys.Active0=1"

#SQL Server connection information
$SQLServer = "xxxxxxxx" # Name of SCCM SQL server (usually the CAS, or primary if only 1 primary)
$SQLDatabase = "CM_xxx" # Name of database with site code
$SQLAuth = "uid=xxxxxxxx;pwd=xxxxxxxx" # A SQL account with at least R/O access


$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString="Server=$SQLServer;Database=$SQLDatabase;$SQLAuth"
$SQLConnection.Open()

$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.CommandText = $SQLSelect
$SQLCmd.Connection  = $SQLConnection

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SQLCmd
$SQLDataset = New-Object System.Data.DataSet

$SQLAdapter.Fill($SQLDataset) | Out-Null

if ($SQLDataset -ne $null)
{
    $ComputerList = $SQLDataset.Tables[0]

    $RowCount = 1

    $ComputerList | % {
        $ComputerName = $_.Name0
        $sSerialNumber = $_.SerialNumber0
        $sProductNumber = $_.ProductNumber0
        $sManufacturer = $_.Manufacturer0

        $Rows = ([array]($ComputerList)).length

        Write-Host "Processing Row $RowCount of $Rows...Checking $ComputerName..." -NoNewline
        $RowCount++

        switch ($sManufacturer)
        {
            "HP" {
                $sWebLink = "http://h10025.www1.hp.com/ewfrf/wc/weResults?cc=us&dlc=en&lc=en&tmp_weCountry=gb&X-view=mobile&tmp_weCountry=gb&tmp_weSerial=$sSerialNumber&tmp_weProduct=$sProductNumber&product=&lc=en&dlc=en&cc=us&tmp_weDest=&tmp_track_link=ot_we%2Fsubmit%2Fen_us%2Fentitlement%2Floc%3A0"
            }
            "Hewlett-Packard" {
                $sWebLink = "http://h10025.www1.hp.com/ewfrf/wc/weResults?cc=us&dlc=en&lc=en&tmp_weCountry=gb&X-view=mobile&tmp_weCountry=gb&tmp_weSerial=$sSerialNumber&tmp_weProduct=$sProductNumber&product=&lc=en&dlc=en&cc=us&tmp_weDest=&tmp_track_link=ot_we/submit/en_us/entitlement/loc:0"
            }
        }

        try
        {
            $OutputFile = ".\$ComputerName.csv"

            if (Test-Path $OutputFile)
            {
                Write-Host "File already exists..."
            }
            else
            {
                $webClient = new-object System.Net.WebClient
                $output = ($webClient.DownloadString($sWebLink)).tolower()

                $WarrantyInfo = ($output.Substring($output.IndexOf("warranty status"),250))
                $WarrantyDate = ($output.Substring($output.IndexOf("warranty end date"),250))

                if ($WarrantyInfo -match "warranty has expired") { $Warranty = "Expired" }
                if ($WarrantyInfo -match "covered under warranty") { $Warranty = "Covered under warranty" }

                $WarrantyDateFound = [regex]::Match($WarrantyDate,"[0-9]{4}-[0-9]{2}-[0-9]{2}").Groups[0].value

                write-host "$ComputerName, Warranty [$Warranty], Warranty End Date [$WarrantyDateFound]" -NoNewline

                $ComputerInfo = "" | select ComputerName, SerialNumber, ProductCode, WarrantyInfo, WarrantyDate

                $ComputerInfo.ComputerName = $ComputerName
                $ComputerInfo.SerialNumber = $sSerialNumber
                $ComputerInfo.ProductCode = $sProductNumber
                $ComputerInfo.WarrantyInfo = $Warranty
                $ComputerInfo.WarrantyDate = $WarrantyDateFound

                if (Test-Path $OutputFile) { Remove-Item $OutputFile -Force }

                Write-Host "Writing file for $ComputerName..."

                $ComputerInfo | Export-Csv $OutputFile -NoClobber -NoTypeInformation
            }
        }
        catch
        {
        }
    }
}The CSV files that are output contain warranty information for each device.

Other weblinks i've come across on google searches re other manufacturers (NOTE i've NOT tested these as I have none of these devices to test!  Feel free to try them out)

Dell : http://support.dell.com/support/topics/global.aspx/support/my_systems_info/details?servicetag=' + (Machine Serial Number)

IBM / Lenovo :  http://www-307.ibm.com/pc/support/site.wss/warrantyLookup.do?type=' + LEFT (Machine Model, 4) + '&serial=' + (Machine Serial Number) + '&country=897'

Acer : http://secure3.tx.acer.com/FindSystem/findsystem.aspx?Title=Information&sn=' + (Machine Serial Number)

I'll cover reading these files back and storing the information back on each device's custom class in a later post.

Custom WMI Classes and reporting into SCCM 2012

Using Powershell, its possible to create CUSTOM WMI Classes and then include these in the Hardware Inventory for all devices.   This example will cover obtaining monitor information for desktop machines.

Creating the new class

To start, run Powershell and run the command Get-WMIObject -class MonitorDetails and you should get the error:

The script below will create the class, and populate it with the monitor details for your machine
:

#
# This script reads the EDID information stored in the registry for the currently connected monitors
# and stores their most important pieces of identification (Name, Size, Serial Number etc) in WMI
# for later retrieval by SCCM
#

# Reads the 4 bytes following $index from $array then returns them as an integer interpreted in little endian
 
function Get-LittleEndianInt($array, $index)
{
    # Create a new temporary array to reverse the endianness in
    $temp = @(0) * 4
    [Array]::Copy($array, $index, $temp, 0, 4)
    [Array]::Reverse($temp)
   
    # Then convert the byte data to an integer
    [System.BitConverter]::ToInt32($temp, 0)
}

# Creates a new class in WMI to store our data
function Create-Wmi-Class()
{
    $newClass = New-Object System.Management.ManagementClass("root\cimv2", [String]::Empty, $null);

    $newClass["__CLASS"] = "MonitorDetails";

    $newClass.Qualifiers.Add("Static", $true)
    $newClass.Properties.Add("DeviceID", [System.Management.CimType]::String, $false)
    $newClass.Properties["DeviceID"].Qualifiers.Add("key", $true)
    $newClass.Properties["DeviceID"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("ManufacturingYear", [System.Management.CimType]::UInt32, $false)
    $newClass.Properties["ManufacturingYear"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("ManufacturingWeek", [System.Management.CimType]::UInt32, $false)
    $newClass.Properties["ManufacturingWeek"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("DiagonalSize", [System.Management.CimType]::UInt32, $false)
    $newClass.Properties["DiagonalSize"].Qualifiers.Add("read", $true)
    $newClass.Properties["DiagonalSize"].Qualifiers.Add("Description", "Diagonal size of the monitor in inches")
    $newClass.Properties.Add("Manufacturer", [System.Management.CimType]::String, $false)
    $newClass.Properties["Manufacturer"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("Name", [System.Management.CimType]::String, $false)
    $newClass.Properties["Name"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("SerialNumber", [System.Management.CimType]::String, $false)
    $newClass.Properties["SerialNumber"].Qualifiers.Add("read", $true)
    $newClass.Properties.Add("DeviceFound", [System.Management.CimType]::UInt32, $false)
    $newClass.Properties["DeviceFound"].Qualifiers.Add("read", $true)
    $newClass.Put()
}

# Check whether we already created our custom WMI class on this PC, if not, create it
[void](Get-WMIObject MonitorDetails -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror)
if ($wmiclasserror)
{
    try { Create-Wmi-Class }
    catch
    {
        "Could not create WMI class"
        Exit 1
    }
}

# Iterate through the monitors in Device Manager
$monitorInfo = @()
Get-WMIObject Win32_PnPEntity -Filter "Service='monitor'" | % { $k=0 }
{
    $mi = @{}
    $mi.Caption = $_.Caption
    $mi.DeviceID = $_.DeviceID
    # Then look up its data in the registry
    $path = "HKLM:\SYSTEM\CurrentControlSet\Enum\" + $_.DeviceID + "\Device Parameters"
    $edid = (Get-ItemProperty $path EDID -ErrorAction SilentlyContinue).EDID

    # Some monitors, especially those attached to VMs either don't have a Device Parameters key or an EDID value. Skip these
    if ($edid -ne $null)
    {
        # Collect the information from the EDID array in a hashtable
        $mi.Manufacturer += [char](64 + [Int32]($edid[8] / 4))
        $mi.Manufacturer += [char](64 + [Int32]($edid[8] % 4) * 8 + [Int32]($edid[9] / 32))
        $mi.Manufacturer += [char](64 + [Int32]($edid[9] % 32))
        $mi.ManufacturingWeek = $edid[16]
        $mi.ManufacturingYear = $edid[17] + 1990
        $mi.DeviceFound = 1
        $mi.HorizontalSize = $edid[21]
        $mi.VerticalSize = $edid[22]
        $mi.DiagonalSize = [Math]::Round([Math]::Sqrt($mi.HorizontalSize*$mi.HorizontalSize + $mi.VerticalSize*$mi.VerticalSize) / 2.54)

        # Walk through the four descriptor fields
        for ($i = 54; $i -lt 109; $i += 18)
        {
            # Check if one of the descriptor fields is either the serial number or the monitor name
            # If yes, extract the 13 bytes that contain the text and append them into a string
            if ((Get-LittleEndianInt $edid $i) -eq 0xff)
            {
                for ($j = $i+5; $edid[$j] -ne 10 -and $j -lt $i+18; $j++) { $mi.SerialNumber += [char]$edid[$j] }
            }
            if ((Get-LittleEndianInt $edid $i) -eq 0xfc)
            {
                for ($j = $i+5; $edid[$j] -ne 10 -and $j -lt $i+18; $j++) { $mi.Name += [char]$edid[$j] }
            }
        }
       
        # If the horizontal size of this monitor is zero, it's a purely virtual one (i.e. RDP only) and shouldn't be stored
        if ($mi.HorizontalSize -ne 0)
        {
            $monitorInfo += $mi
        }
    }
   
}
$monitorInfo

# Clear WMI
Get-WmiObject MonitorDetails | Remove-WmiObject

# And store the data in WMI
$monitorInfo | % { $i=0 }
{
    [void](Set-WmiInstance -Path \\.\root\cimv2:MonitorDetails -Arguments @{DeviceID=$_.DeviceID; ManufacturingYear=$_.ManufacturingYear; `
    ManufacturingWeek=$_.ManufacturingWeek; DiagonalSize=$_.DiagonalSize; Manufacturer=$_.Manufacturer; Name=$_.Name; SerialNumber=$_.SerialNumber; DeviceFound=$_.DeviceFound})
    $i++
}

So now, running Get-WMIObject -class MonitorDetails should return some information (2 monitors shown as attached to this device)

Now we need to get the newly created class into SCCM. First of all, we'll add it to the inventoried class list.

Updating SCCM Client Base Classes for Hardware Inventory

Run the SCCM Management Console

• Click on Administration Section
• Click on Client Settings
• Right click on Default Client Settings, then properties (Has to be the default one to select the new class). If you have additional client settings you can enable and disable the class for collection afterwards, but it must first be selected via the Default Client Settings
• Click Hardware Inventory
• Click Set Classes and you'll see the following screen

• Click the Add Button, then click Connect

• Specify the machine name where the class was created above then click Connect
• Locate the newly created class MonitorDetails, tick the box, then click OK

When you get returned to the Hardware Inventory Class screen, the new class MonitorDetails will be listed and will be selected. If you have other custom Client Settings, deselect the new class at this point otherwise it will be picked up for all devices that the Default Client Settings are assigned to.

If you have other custom Client Settings, go into each relevant one, and under Hardware Inventory, Set Classes, and then tick the box for MonitorDetails.

Before you click OK to save your settings, its a good idea at this point to load up CMTRACE and load up the log file <SCCMInstallFolder>\Logs\dataldr.log - you will see entries go through for the new class as its added to the SCCM database as a new table (in this case you'll see 2 new tables created in SQL - dbo.MONITORDETAILS_DATA and dbo.MONITORDETAILS_HIST), you will also see SQL views created (dbo.v_GS_MONITORDETAILS)

So now the new class has been selected, and the relevant SQL structures updated - we now need to tell our client devices to use and create the new WMI class and report information back to SCCM.

Client Baseline Settings

Back to the main SCCM Management Console

• Click on Assets and Compliance Section
• Click on Compliance Settings, Configuration Items
• Click on Create Configuration Item ( I won't detail all the screens here - skipping to the "Settings" section - specify name, description, supported platforms etc as you see fit.
• You want to create a new Settings with a Setting Type of Script and a Data type of String. Now we'll specify the Discovery Script and the Remediation Script

Discovery Script (Script Language : Powershell)

$ClassInfo = Get-WMIObject MonitorDetails -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror

if ($wmiclasserror) { $ClassFound = 0 } else { $ClassFound = 1 }

$ClassFound

Remediation Script (Script Language : Powershell)

The Remediation script is the Powershell script at the top of this post - just copy and paste it in...

At this point we now need to specify a Compliance Rule

• Make sure the Rule Type is Value, and the the Value Equals 1
• Tick the box for Run the specified remediation script...and save

• Once the configuration item is created, create a Configuration Baselines and add the new item above.
• And finally, Deploy the Configuration Baseline to a collection - at this point don't forget to tick the box Remediate noncompliant rules when supported, otherwise the new class won't be created on your devices!

Now thats all done, when you look at the hardware inventory for any device, the monitor details will now be listed (Devices, right click, Start, Resource Explorer).

And finally, run a report to retrieve your new custom class!

SELECT * FROM v_GS_MONITORDETAILS

Useful SCCM 2012 SQL Queries

Decided to start recording useful SCCM information that I've stumbled across or worked out whilst working on SCCM 2012 with +liaminnes  - hopefully covering things like useful SQL queries, oddities found during configuration and administration, custom WMI classes and custom registry entries and how to include them in Hardware inventory etc

So, to start things off, SQL Queries. We started work on a Dashboard for our server & workstation estate to give "the management" a summary of the current status of our estate

The dashboard was designed inside MS SQL Server Report Builder and added as a custom report to the SCCM  Management Console.

Top Client Operating Systems

SELECT OPSYS.Caption0 as 'Operating System', COUNT(*) AS 'Client Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
INNER JOIN v_R_System sys on OPSYS.ResourceID = sys.ResourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1
GROUP BY OPSYS.Caption0
ORDER BY Count(*) desc


Workstation Machine Types

WITH mytop15(model0, clientcount) AS
(
SELECT TOP 15 Model0, COUNT(distinct sys.name0) FROM v_GS_Computer_System ass JOIN v_R_System sys ON ass.ResourceID=sys.resourceID
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS ON ass.ResourceID = OPSYS.resourceID
WHERE sys.client0=1 AND sys.obsolete0=0 AND sys.Active0=1
GROUP BY Model0
ORDER BY 2 desc
)
SELECT * FROM mytop15 UNION ALL
SELECT 'Others' as model0, COUNT(distinct sys.name0) as ClientCount
FROM v_GS_Computer_System ass JOIN v_R_System sys ON ass.ResourceID=sys.resourceID
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS on ass.ResourceID = OPSYS.resourceID
WHERE
sys.client0=1 AND sys.obsolete0=0 AND sys.Active0=1
AND Model0 NOT IN (SELECT Model0 FROM mytop15)
ORDER BY 2 desc

Clients By Domain

SELECT DISTINCT sys.Resource_domain_OR_Workgr0 as ClientDomain, 
COUNT(distinct sys.name0) AS ClientCount
FROM v_R_System sys
INNER JOIN v_GS_OPERATING_SYSTEM OPSYS ON sys.ResourceID = OPSYS.ResourceID
WHERE client0=1 AND obsolete0=0 AND Active0=1
GROUP BY Resource_domain_OR_Workgr0
ORDER BY ClientCount DESC


Warranty

Will come back to this report later, as its based on a custom WMI class, compliance items, and a scheduled task to "fill in the blanks", but here's the SQL

SELECT sub.WarrantyInfo0, COUNT(*) AS ID FROM
(
SELECT
CASE
    WHEN war.WarrantyInfo0 IS NULL THEN 'Not Yet Reported'
    ELSE war.WarrantyInfo0
END AS WarrantyInfo0
FROM v_GS_WARRANTYDETAILS war
INNER JOIN v_R_System sys on sys.ResourceID = war.ResourceID
WHERE sys.client0=1 AND sys.obsolete0=0 AND sys.active0=1) sub
GROUP BY sub.WarrantyInfo0

In addition to the dashboard above, I've written some more custom reports that have proven useful.

Inactive Machines


List of machines that haven't "talked" to the SCCM servers for more than 28 days...

SELECT sys.ResourceID, sys.ResourceType, sys.Name0, sys.SMS_Unique_Identifier0,
sys.Resource_Domain_OR_Workgr0, sys.Client0,
casum.LastActiveTime, casum.LastDDR, casum.LastOnline,
csys.Manufacturer0, csys.Model0
FROM v_R_System sys
INNER JOIN v_CH_ClientSummary casum ON casum.ResourceId = sys.ResourceId
INNER JOIN v_GS_COMPUTER_SYSTEM csys ON csys.ResourceID = sys.ResourceID
WHERE casum.ClientActiveStatus = 0
AND casum.LastActiveTime < DATEADD(d,-28,getdate())
ORDER BY casum.LastActiveTime


Duplicate Machines

SELECT csys.name0, sys.Operating_System_Name_and0, sys.Active0, sys.Client0, 
csum.ClientActiveStatus, csum.IsActivePolicyRequest
FROM v_GS_COMPUTER_SYSTEM csys
LEFT OUTER JOIN v_R_System sys ON csys.ResourceID = sys.resourceID
LEFT OUTER JOIN v_CH_ClientSummary csum on csys.ResourceID = csum.ResourceID
WHERE csys.Name0 IN 
(SELECT csys.Name0
FROM v_GS_COMPUTER_SYSTEM csys
GROUP BY Name0
HAVING COUNT(*) > 1)


Report on reported versions of software (selectable)

Based on a parameter @ProgramName - this allows user on SCCM Report Manager to specify a parameter for the report.

SELECT * FROM (
SELECT DISTINCT v_R_System_Valid.Netbios_Name0 AS "Computer Name", v_R_System_Valid.ResourceID,
v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS "Product Name", v_GS_ADD_REMOVE_PROGRAMS_64.Publisher0 AS "Publisher",
v_GS_ADD_REMOVE_PROGRAMS_64.Version0 AS "Version", v_GS_ADD_REMOVE_PROGRAMS_64.InstallDate0
FROM v_GS_ADD_REMOVE_PROGRAMS_64
INNER JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 LIKE '%' + @ProgramName + '%'
UNION ALL
(
    SELECT DISTINCT v_R_System_Valid.Netbios_Name0 AS "Computer Name", v_R_System_Valid.ResourceID,
    v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 AS "Product Name", v_GS_ADD_REMOVE_PROGRAMS.Publisher0 AS "Publisher",
    v_GS_ADD_REMOVE_PROGRAMS.Version0 AS "Version", v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
    FROM v_GS_ADD_REMOVE_PROGRAMS
    INNER JOIN v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
    JOIN v_GS_OPERATING_SYSTEM ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    WHERE v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%' + @ProgramName + '%'
)) AS u
ORDER BY "Computer Name","Product Name", Publisher, Version

More to follow...