SCCM 2012 - Collection WQL Query

[fr43nk]

Hello.

I want to buil a collection, that contains all clients with a certain MS Office 2010 Patch installed.

I think that a membership rule with a wql query is the way i have to go.

 

Thats what I got so far:

 

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYS TEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_PatchStatusEx on SMS_G_System_PatchStatusEx.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PatchStatusEx.ID = "MS12-060" and SMS_G_System_PatchStatusEx.LastStateName = "Installed"

 

Is SMS_G_System_PatchStatusEx the place I have to look ?

 

Can somebody help me with that ?

 

Sincerely,

frank

[GarthMJ]

No, That WQL view does not exist within CM12.

 

Try Configuration Item Compliance State instead.

[fr43nk]

thank your for the hint.

but its still not working

 

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_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceID WHERE SMS_G_System_CI_ComplianceState.CI_UniqueID = "49696650-4242-42d5-931d-fb097c1f786e" AND SMS_G_System_CI_ComplianceState.IsDetected = 1

 

at least i dont have syntax errors

[GarthMJ]

Why do you say the query is not working? It looks fine to me..

[fr43nk]

cause no clients are listed, and i know for sure that 8 clients have that patch already installed

 

when i run this powershell script on those clients, they get in the right collection - that i cant use that script is a different story

 

# Sammlung, Computername und Patch definieren

$SiteName="P01"

$SCCMServer="server.domain.xxx"

$SCCMNameSpace="root\sms\site_$SiteName"

$SCCMCollectionID = "P010004C"

$ComputerToAdd = get-content env:computername

$Patch = "KB2597986"

# fügt den Computer zur Sammlung per Directer Regel

FUNCTION AddToCollection{

$computer = gwmi -computer $SCCMServer -namespace $SCCMNameSpace -Query "select * from SMS_R_System where NetBiosName='$ComputerToAdd'"

$SCCMCollection=[WMI]"\\$($SCCMServer)\$($SCCMNameSpace):SMS_Collection.CollectionID='$SCCMCollectionID'"

$ruleClass = [wmiclass]"\\$($SCCMServer)\$($SCCMNameSpace):SMS_CollectionRuleDirect"

$newRule = $ruleClass.CreateInstance()

$newRule.RuleName = $ComputerToAdd

$newRule.ResourceClassName = "SMS_R_System"

$newRule.ResourceID = $computer.ResourceID

$null = $SCCMCollection.AddMembershipRule($newRule)

}

 

#überprüfen ob Update MS12-060 (KB2720573) installiert ist und ggf. Funktion ausführen

$PatchSession = New-Object -ComObject Microsoft.Update.Session

$PatchSearcher = $PatchSession.CreateUpdateSearcher()

$PatchSearcher.QueryHistory(0, $PatchSearcher.GetTotalHistoryCount()) | Where-Object {$_.Title -match $Patch} |

ForEach-Object {

AddToCollection

}

[GarthMJ]

There is your first problem. You are run PoSH script. :-)

 

The answer is: you are comparing apples and oranges. This PoSH script is query the CI for SU, the WQL query is comparing the CI for DCM.

[fr43nk]

Thank you very much for your hints, you helped me to solve the problem and even to do more

than i expected.

I wasnt aware of the meaning of DCM, but now that i did some research, the whole stuff makes sense.

I created Configuration Items and Baselines, and now i am able to do wql queries like i planed.

 

Best regards,

fr43nk