Tech Community Live: Endpoint Manager edition
Jul 21 2022, 08:00 AM - 12:00 PM (PDT)
SOLVED

Dynamic collection needed

%3CLINGO-SUB%20id%3D%22lingo-sub-3300593%22%20slang%3D%22en-US%22%3EDynamic%20collection%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300593%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20need%20a%20dynamic%20collection%20for%20an%20update%20that%20fails%20with%20error%200x800F080F(-2146498545).%20I%20cannot%20find%20a%20WQL%20attribute%20class%20that%20will%20help%20me%20identify%20computers%20with%20this%20failure.%20No%20problem%20with%20a%20SQL%20query%20but%20I%20cannot%20figure%20out%20how%20to%20match%20the%20SQL%20views%20to%20attribute%20classes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20someone%20could%20point%20out%20some%20attribute%20classes%20to%20look%20at%2C%20that%20would%20be%20appreciated.%20When%20I%20figure%20this%20out%2C%20I%20will%20post%20the%20solution%20that%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3300593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConfigMgr%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EConfiguration%20Manager%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3319965%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20collection%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3319965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F148024%22%20target%3D%22_blank%22%3E%40Rob%20Moorhead%3C%2FA%3E%26nbsp%3BThis%20is%20not%20an%20officially%20supported%20method%20but%20you%20can%20get%20details%20using%20this%20method.%3CBR%20%2F%3ESQL%20query%20to%20WQL%20and%20WQL%20Query%20to%20SQL%20using%20%3CA%20href%3D%22https%3A%2F%2Fwww.anoopcnair.com%2Fconvert-wql-queries-to-sql-queries-using-sccm%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.anoopcnair.com%2Fconvert-wql-queries-to-sql-queries-using-sccm%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20best-recommended%20way%20to%20get%20the%20mapping%20my%20experience%20is%20using%20%3CA%20href%3D%22https%3A%2F%2Fwww.anoopcnair.com%2Fsccm-resource-explorer-helps-to-troubleshoot%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ESCCM%20resource%20explorer%3C%2FA%3E%26nbsp%3Bas%20explained%20in%20one%20of%20the%20dynamic%20collection%20creation%20posts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20Try%20to%20get%20the%20mapping%20tip%20from%20resource%20explorer%20and%20build%20query%20based%20on%20that%20-%20%3CA%20href%3D%22https%3A%2F%2Fwww.anoopcnair.com%2Fsccm-collection-windows-11-upgrade-compatible%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EHow%20to%20Create%20SCCM%20Collection%20for%20Windows%2011%20Upgrade%20Compatible%20Devices%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3320435%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20collection%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3320435%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F148024%22%20target%3D%22_blank%22%3E%40Rob%20Moorhead%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20an%20Configuration%20Baseline%20(Item%20-%20Script)%20and%20from%20Deployment%20-%20create%20New%20Collection%20-%20Non-compliant...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdevblogs.microsoft.com%2Fscripting%2Fuse-powershell-to-easily-find-information-about-hotfixes%2F%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdevblogs.microsoft.com%2Fscripting%2Fuse-powershell-to-easily-find-information-about-hotfixes%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powershell%22%3E%3CCODE%3E%24val%20%3D%20gwmi%20-cl%20win32_reliabilityRecords%20-filter%20%22sourcename%20%3D%20'Microsoft-Windows-WindowsUpdateClient'%22%20%7C%20where%20%7B%20%24_.message%20-match%20'0x800F080F'%20%7D%20%0A%0A%20%20%20%20if%20(%24val%20-eq%200)%20%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%24Compliant%20%3D%20%24true%0A%20%20%20%20%7D%0A%20%20%20%20if%20(%24val%20-ne%200)%0A%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%24Compliant%20%3D%20%24false%0A%20%20%20%20%7D%0A%0A%24Compliant%0A%0A%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20G.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3322049%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20collection%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3322049%22%20slang%3D%22en-US%22%3ESolution%20proposed%20by%20Justin3315%20on%20%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2Fen-US%2Fcb676fe7-365b-49a6-80df-88d19ac9a1ca%2Fcreate-collection-based-on-software-update-scan-status-last-error-code%3Fforum%3DConfigMgrCompliance%23cb676fe7-365b-49a6-80df-88d19ac9a1ca%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.technet.microsoft.com%2FForums%2Fen-US%2Fcb676fe7-365b-49a6-80df-88d19ac9a1ca%2Fcreate-collection-based-on-software-update-scan-status-last-error-code%3Fforum%3DConfigMgrCompliance%23cb676fe7-365b-49a6-80df-88d19ac9a1ca%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%C2%AB%26nbsp%3BIt%20should%20work%20using%20the%20scan%20error%20you%20specified%20above%20but%20you%20may%20have%20to%20change%20the%20LastEnforcementMessageID%20and%2For%20the%20Status%20values.%26nbsp%3B%C2%BB%3CBR%20%2F%3E%3CBR%20%2F%3Eselect%20SMS_R_SYSTEM.ResourceID%2CSMS_R_SYSTEM.ResourceType%2CSMS_R_SYSTEM.Name%2CSMS_R_SYSTEM.SMSUniqueIdentifier%2CSMS_R_SYSTEM.ResourceDomainORWorkgroup%2CSMS_R_SYSTEM.Client%20from%20sms_r_system%20inner%20join%20SMS_UpdateComplianceStatus%20on%20SMS_UpdateComplianceStatus.machineid%3Dsms_r_system.resourceid%20where%20SMS_UpdateComplianceStatus.LastEnforcementMessageID%20%3D%2011%20and%20SMS_UpdateComplianceStatus.LastErrorCode%20%3D%20-2147023293%20and%20SMS_UpdateComplianceStatus.Status%20%3D%202%3CBR%20%2F%3E%3CBR%20%2F%3ERenald%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3325421%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20collection%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3325421%22%20slang%3D%22en-US%22%3EThank%20you%20to%20everyone%20that%20responded!%20I%20went%20with%20Renald's%20suggestion%20and%20it's%20working%20as%20expected.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I need a dynamic collection for an update that fails with error 0x800F080F(-2146498545). I cannot find a WQL attribute class that will help me identify computers with this failure. No problem with a SQL query but I cannot figure out how to match the SQL views to attribute classes.

 

If someone could point out some attribute classes to look at, that would be appreciated. When I figure this out, I will post the solution that works.

 

Thank you!

Rob

4 Replies

@Rob Moorhead This is not an officially supported method but you can get details using this method.
SQL query to WQL and WQL Query to SQL using https://www.anoopcnair.com/convert-wql-queries-to-sql-queries-using-sccm/

 

The best-recommended way to get the mapping my experience is using SCCM resource explorer as explained in one of the dynamic collection creation posts.

 

Example Try to get the mapping tip from resource explorer and build query based on that - How to Create SCCM Collection for Windows 11 Upgrade Compatible Devices.

@Rob Moorhead 

Maybe with an Configuration Baseline (Item - Script) and from Deployment - create New Collection - Non-compliant...

 

https://devblogs.microsoft.com/scripting/use-powershell-to-easily-find-information-about-hotfixes/ 

 

$val = gwmi -cl win32_reliabilityRecords -filter "sourcename = 'Microsoft-Windows-WindowsUpdateClient'" | where { $_.message -match '0x800F080F' } 

    if ($val -eq 0) 
    {
        $Compliant = $true
    }
    if ($val -ne 0)
    {
        $Compliant = $false
    }

$Compliant

 

Regards, G.

 

best response confirmed by Rob Moorhead (Occasional Contributor)
Solution
Solution proposed by Justin3315 on https://social.technet.microsoft.com/Forums/en-US/cb676fe7-365b-49a6-80df-88d19ac9a1ca/create-collec...

« It should work using the scan error you specified above but you may have to change the LastEnforcementMessageID and/or the Status values. »

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_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid where SMS_UpdateComplianceStatus.LastEnforcementMessageID = 11 and SMS_UpdateComplianceStatus.LastErrorCode = -2147023293 and SMS_UpdateComplianceStatus.Status = 2

Renald
Thank you to everyone that responded! I went with Renald's suggestion and it's working as expected.