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