[SQL] Windows Update Scan State

SELECT 

a.LastErrorCode as "Error",
b.Name0,
b.Last_Logon_Timestamp0,
c.Caption0,
d.WUServer0


  FROM v_UpdateScanStatus a
  inner join v_R_System b on a.ResourceID = b.ResourceID
  inner join v_GS_OPERATING_SYSTEM c on a.ResourceID = c.ResourceID
  inner join v_GS_WSUSSettings_WSUSServer0 d on a.ResourceID = d.ResourceID
  where a.LastErrorCode!= '0'
  order by Error ASC
  

WSUS Server Report

COMPLIANT
SELECT a.ResourceID
      ,b.Name0 as "Name"
      ,a.TimeStamp as "Time Stamp"
      ,a.WUServer0 as "WSUS Server"
 ,b.Full_Domain_Name0 as Domain
 ,b.Distinguished_Name0 as FQDN
 ,b.Operating_System_Name_and0 as OS
 ,b.Build01 as "OS Build"
  FROM v_GS_WSUSSettings_WSUSServer0 a
  join v_R_System b on a.ResourceID=b.ResourceID
  WHERE a.WUServer0 = 'http://WSUSSERVER:8530'

NON-COMPLIANT
SELECT a.ResourceID
      ,b.Name0 as "Name"
      ,a.TimeStamp as "Time Stamp"
      ,a.WUServer0 as "WSUS Server"
 ,b.Full_Domain_Name0 as Domain
 ,b.Distinguished_Name0 as FQDN
 ,b.Operating_System_Name_and0 as OS
 ,b.Build01 as "OS Build"
  FROM v_GS_WSUSSettings_WSUSServer0 a
  join v_R_System b on a.ResourceID=b.ResourceID
  WHERE (a.WUServer0 <> 'http://WSUSSERVER:8530' or a.WUServer0 IS NULL)

****

IMPORT.MOF:

// RegKeyToMOF by Mark Cochrane (tribute to Skissinger & Steverac)

// this section tells the the inventory agent what to report to the server

#pragma namespace ("\\\\.\\root\\cimv2\\SMS")

#pragma deleteclass("WSUSServer", NOFAIL)

[SMS_Report(TRUE),SMS_Group_Name("WSUSServer"),SMS_Class_ID("WSUSSettings|WSUSServer|1.0")]

Class WSUSServer: SMS_Class_Template

{

[SMS_Report(TRUE),key] string KeyName;

[SMS_Report(TRUE)] String WUServer;

};

CONFIGURATION.MOF

// RegKeyToMOF by Mark Cochrane (tribute to Skissinger & Steverac)

// this section tells the inventory agent what to collect

#pragma namespace ("\\\\.\\root\\cimv2")

#pragma deleteclass("WSUSServer", NOFAIL)

[DYNPROPS]

Class WSUSServer

{

[key] string KeyName;

String WUServer;

String WUStatusServer;

Uint32 AcceptTrustedPublisherCerts;

};

[DYNPROPS]

Instance of WSUSServer

{

keyname="RegKeyToMOF[MarkCochrane]";

[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;

};

SQL - Add Remove Programs joined to Logged on User

select 
hs.ResourceID,hs.ARPDisplayName0,hs.ProductVersion0,
b.User_Domain0, b.User_Name0,b.Name0
from v_HS_INSTALLED_SOFTWARE hs 
join v_R_System b on hs.ResourceID=b.ResourceID
where ProductName0 like '%app name%'