SCCM collection query to get Software inventory failed devices

For an SCCM/MECM device collection that identifies computers where Software Inventory has not successfully reported recently, you can use the following query:

select SMS_R_System.ResourceID, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceID where SMS_G_System_LastSoftwareScan.LastScanDate < DateAdd(dd,-7,GetDate())

This identifies devices whose last reported Software Inventory scan is older than seven days. You can change -7 to another value, such as -3 or -14, depending on how many days you want to allow before considering the Software Inventory scan overdue.

If by "failed" you specifically mean the SCCM client reports the Software Inventory cycle as failed, rather than simply having an old or missing inventory timestamp, the collection needs to use SCCM client status/state-message information instead of LastSoftwareScan.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin