The Windows System Assessment Scan is a prerequisite for the Windows computer Patching process to be successful.

Without installed and applicable update data in the CMDB the client computers cannot received any Software Update policies to install missing updates.

How can we find computers that are not successfully running the Windows System Assessment Scan?

Patch Management Solution 8.x

The following SQL query can be used in a FILTER to list the computers, where the Windows System Assessment Scan failed in the last 7 days.

select _ResourceGuid, COUNT(*) Failures
  from Evt_AeX_SWD_Execution
 where packageid = '6D417916-467C-46A7-A870-6D86D9345B61'
   and Start > GETDATE() - 7
   and ReturnCode != 0
   and _ResourceGuid not in (
        -- List of all computers that have succesfully run the WSAS during the period
        select _ResourceGuid
          from Evt_AeX_SWD_Execution
         where packageid = '6D417916-467C-46A7-A870-6D86D9345B61'
           and Start > GETDATE() - 7
           and ReturnCode = 0
         group by _ResourceGuid
        )
  group by _ResourceGuid

To run a custom SQL REPORT instead use the following query:

select 
    vComputer.Name, 
    CASE 
        WHEN COUNT(Evt_AeX_SWD_Execution._id) = 0 THEN 'No Data' 
        WHEN COUNT(Evt_AeX_SWD_Execution._id) > 0 THEN CAST (COUNT(Evt_AeX_SWD_Execution._id) as nvarchar (50)) + ' Failures'
    END Failures
  from vComputer
left join Evt_AeX_SWD_Execution on vComputer.Guid = Evt_AeX_SWD_Execution._ResourceGuid
    and packageid = '6D417916-467C-46A7-A870-6D86D9345B61'
where ((Start > GETDATE() - 7 and ReturnCode != 0) or Evt_AeX_SWD_Execution._ResourceGuid is null)
    and IsManaged = 1
   and _ResourceGuid not in (
        -- List of all computers that have succesfully run the WSAS during the period
        select _ResourceGuid
          from Evt_AeX_SWD_Execution
         where packageid = '6D417916-467C-46A7-A870-6D86D9345B61'
           and Start > GETDATE() - 7
           and ReturnCode = 0
         group by _ResourceGuid
        )
  group by vComputer.Name

Note! The EvT_AeX_SWD_Excution table is an event table limited (by default) to 1 million rows. This depending on the size of your environment and how busy it is this may cover anything from a few month worth of data to just a few weeks.