Reboot pending WQL query

[dharris21]

Hello all,

 

i have been trying to implement this query found here on SCCM 2012 SP1 CU3 with no success http://social.technet.microsoft.com/Forums/en-US/844afc93-0611-4470-a109-f51b37babd3e/config-manager-2012-collection-to-query-rebootsfor-patch-management?forum=configmanagersecurity

 

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 AS sms_r_system inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid where c.LastEnforcementMessageID = 9

 

normally if i found a query online, i could paste it in and would work, or i could look at what values they were using and implement it from scratch, but this query doesnt seem to work for me.

 

when i paste this query into query design, it give me back a message " this query either has a syntax error or is using features of the language not supported in design view.

 

I scoured through the query wizard looking for the values it seems to be calling, but cannot find them.

 

i can not find:

 

SMS_UpdateComplianceStatus

LastEnforcementMessageID

 

What am i missing?

 

Any help would be appreciated.

[GarthMJ]

I just tested the query there is nothing wrong with it, it work perfectly for CM12.

 

The SMS_UpdateComplianceStatus is no exposed via the wizard but is available to be used within your queries.

[dharris21]

were you able to just paste it in, or did you have issue getting the syntax right from the paste. I figured it could be the copy/paste from the website I found it on as linked, but I didn't mess around with reorganizing the query in wizard. ill have to look at it when I get to work tomorrow. please let me know if copy/paste worked for you. I am surprised that the values aren't available in the wizard to be selected as options.

[GarthMJ]

I just cut and pasted it.

[dharris21]

is there anything I could have done wrong when pasting the query after I click show query language, that would cause the error I stated above when I click OK after pasting it?

[dharris21]

I think I might have figured it out, but wont be able to verify till I get to work. I started the virtual lab from TechNet, and was able to get query to work. i think i was getting hung up on a normal procedure i do when working with queries. If I paste one in, instead of creating it from scratch with designer, i always click "Show Query Design" so i can see what selections it was making. it never dawned on me that just clicking OK would work, but it also never dawned on me that there was a query out there that didn't have a design per say for viewing.

[dverbern]

Query worked when pasted in for me too, although I definitely needed to add a "DISTINCT" to the initial SELECT statement as I had many, many entries for each system found by the query.