Friday, May 29, 2015

SCCM report for WSUS server location

This report will provide WSUS server location from all the devices in the environment.
SCCM default reports do not provide this information and provided hardware inventory will not collect the information as well.
Before creating SCCM report for WSUS location, we need to extend the hardware inventory from registry.
The WSUS information will be stored in “HKLM\Software\Policies\Microsoft\Windows\WindowsUpdate" registry key. We need to create a new mof file to add this location to the hardware inventory. For how to create to create a custom mof file, please refer to this article.

Open a notepad and copy below content and save the file as configuration.mof
**************************************************************************
#pragma namespace ("\\\\.\\root\\cimv2")
#pragma deleteclass("WSUSLocation", NOFAIL)
[DYNPROPS]
Class WSUSLocation
{
[key] string KeyName;
String WUServer;
String WUStatusServer;
};

[DYNPROPS]
Instance of WSUSLocation
{
KeyName="RegKeyToMOF_32";
[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUServer"),Dynamic,Provider("RegPropProv")] WUServer;
[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUStatusServer"),Dynamic,Provider("RegPropProv")] WUStatusServer;
};

#pragma namespace ("\\\\.\\root\\cimv2")
#pragma deleteclass("WSUSLocation_64", NOFAIL)
[DYNPROPS]
Class WSUSLocation_64
{
[key] string KeyName;
String WUServer;
String WUStatusServer;
};

[DYNPROPS]
Instance of WSUSLocation_64
{
KeyName="RegKeyToMOF_64";
[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUServer"),Dynamic,Provider("RegPropProv")] WUServer;
[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Policies\\Microsoft\\Windows\\WindowsUpdate|WUStatusServer"),Dynamic,Provider("RegPropProv")] WUStatusServer;
};

***************************************************************************

Open a notepad and copy and paste below content then save the file as import.mof
***************************************************************************
#pragma namespace ("\\\\.\\root\\cimv2\\SMS")
#pragma deleteclass("WSUSLocation", NOFAIL)
[SMS_Report(TRUE),SMS_Group_Name("WSUSLocation"),SMS_Class_ID("WSUSLocation"),
SMS_Context_1("__ProviderArchitecture=32|uint32"),
SMS_Context_2("__RequiredArchitecture=true|boolean")]
Class WSUSLocation: SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String WUServer;
[SMS_Report(TRUE)] String WUStatusServer;
};

#pragma namespace ("\\\\.\\root\\cimv2\\SMS")
#pragma deleteclass("WSUSLocation_64", NOFAIL)
[SMS_Report(TRUE),SMS_Group_Name("WSUSLocation64"),SMS_Class_ID("WSUSLocation64"),
SMS_Context_1("__ProviderArchitecture=64|uint32"),
SMS_Context_2("__RequiredArchitecture=true|boolean")]
Class WSUSLocation_64 : SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String WUServer;
[SMS_Report(TRUE)] String WUStatusServer;
};

****************************************************************************
Now open the configuration.mof file from <SCCM_Install_Directory>\Microsoft Configuration Manager\inboxes\clifiles.src\hinv on SCCM server then add the contents of newly created configuration.mof to it, then save the file.
Please Note: Backup the original configuration.mof file before making any changes.

Next import the import.mof file into Default Client Settings by going \Administration\Overview\Client Settings in SCCM Console.

Once the import is complete, now create an SCCM custom report using below SQL query to get a report on collected inventory.
******************************************************************************
SELECT     v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name], WSUSLocation_DATA.WUServer00 AS [WSUS Server Name]
FROM         WSUSLocation_DATA CROSS JOIN
                      v_GS_COMPUTER_SYSTEM
GROUP BY v_GS_COMPUTER_SYSTEM.Name0, WSUSLocation_DATA.WUServer00, WSUSLocation_DATA.WUStatusServer00

*******************************************************************************
Please note, before running a report you may have to wait for few days to complete hardware inventory collection cycle. Otherwise the report will show empty records.

The collected inventory report will look like as below;
More SCCM custom reports can be found here

3 comments:

  1. Hi Venu,

    From SCCM DB, we can get the information without extending the Hardware Inventory.


    Select * from vSMS_SUPSyncStatus will do.

    Regards,
    Jijo

    ReplyDelete
  2. Hello,
    When I ran the query
    SELECT v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name], WSUSLocation_DATA.WUServer00 AS [WSUS Server Name]
    FROM WSUSLocation_DATA CROSS JOIN
    v_GS_COMPUTER_SYSTEM
    WHERE v_GS_COMPUTER_SYSTEM.Name0 LIKE '%SCCM%'
    GROUP BY v_GS_COMPUTER_SYSTEM.Name0, WSUSLocation_DATA.WUServer00, WSUSLocation_DATA.WUStatusServer00

    I am getting several records for each machine!!!
    Machine Name WSUS Server Name
    AGOSCCMDP01 NULL
    AGOSCCMDP01 http://vopwsus2k12:8530
    AGOSCCMDP01 http://wsus01
    AGOSCCMDP01 https://DGIT-SU-SCCM-P1.ad:8531
    AGOSCCMDP01 https://VRPSCCMSU01.ad:8531

    Any idea?
    Thanks,
    Dom

    ReplyDelete
  3. Hello,
    I removed the CROSS JOIN for a simple Join
    SELECT v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name], WSUSLocation_DATA.WUServer00 AS [WSUS Server Name]
    FROM WSUSLocation_DATA JOIN
    v_GS_COMPUTER_SYSTEM on WSUSLocation_DATA.MachineID = v_GS_COMPUTER_SYSTEM.ResourceID
    WHERE v_GS_COMPUTER_SYSTEM.Name0 like '%SCCM%'
    GROUP BY v_GS_COMPUTER_SYSTEM.Name0, WSUSLocation_DATA.WUServer00, WSUSLocation_DATA.WUStatusServer00
    ORDER BY v_GS_COMPUTER_SYSTEM.Name0
    and it works fine for me now...
    Thanks,
    Dom

    ReplyDelete