Thanks for the responses, Sherry.
What we’re doing is building a utility for admins to create Software Update Deployment collections based on details provided from a RFC. The computernames listed in the RFC will be displayed in a GUI with some details from SCCM. I wanted to have the last scan date value in one of the columns. Our workaround: Since there was so much more data that the end-user wanted to see, we decided to put much of it in a custom report based on the computernames in the RFC. The lastscandate ended up making it into the list of items to be displayed on the report instead of the GUI. I may investigate a configuration item for this at some point but, for now, the requirement will be met. -Nick- From: [email protected] [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Tuesday, October 6, 2015 5:31 PM To: [email protected] Subject: Re: [mssms] Strategies for automating fixes for WUA issues Random thought...you'd have to test it a lot... as a ConfigItem, if you wanted to look in root\ccm\StateMsg have the configItem return "StateID" and in the where location in the CI, Where TopicType=501 A StateID = 3 means "Success" I think a 5 means failed?? (I didn't go look it up, but I think it does). But basically you'd want "Compliant" to be 3, and any other value is probably not compliant. so you could build a collection query off of the non-compliants then--if that's what you're trying to do. TopicType=501 is the SUM Scanning type. Or perhaps instead of returning StateID and you want it to be a 3, have it return StateDetails, and compliant would be if StateDetails is blank (?) and any value other than blank is (I think?? the error code?? again, I don't have a failed box in front of me, you'd have to poke around). Not sure if you can "make" a direct "Compliant is a blank value", I know you can do a NULL, but it's not a null in there, it's a value of nothing on this box (a success) that I'm looking at. So you might need to posh script something custom to force a write-host of <some value you want to use that means success> or to write-host out the StateDetails value instead, for a non-compliant value; which you can then use to make wql queries against. Anyway, it's a thought. zero testing whatsoever. just a possibility you'd have to look at yourself. On Tuesday, October 6, 2015 3:13 PM, Sherry Kissinger <[email protected]> wrote: >From my way back memory, this used to work in WQL on CM07. probably still >works in CM12... but yeah, it's not actually looking at updatescanstatus--it's >looking at status messages from clients, and limiting to "only within the last >2 days" -- you need to limit by date otherwise a statusmessage of 11423 from >179 days ago could drop the machine into the collection. So not exactly what you are looking for... but might be ok... select SMS_R_SYSTEM.ResourceID from SMS_R_SYSTEM join sms_statusmessage on sms_r_system.netbios_name0=sms_statusmessage.machinename where sms_statusmessage.messageid='11423' and datediff(DD,sms_statusmessage.time, GetDate()) <2) Note you only get a 11423 scan failed for any reason. On Tuesday, October 6, 2015 11:43 AM, Nick <[email protected]> wrote: SCCM 2012 – Old post that I didn’t find any answers to. --- I am also “unaware of any way to access the contents of v_UpdateScanStatus via WQL to create the target collections ” Also “Anyone know the reasons behind not exposing more of these diagnostic views available in SQL so they are available in WQL for use within the console outside of reporting?” -Nick- From: [email protected] [mailto:[email protected]] On Behalf Of Michael Roach Sent: Tuesday, November 4, 2014 4:19 AM To: [email protected] Subject: [mssms] Strategies for automating fixes for WUA issues I’m in the process of migrating our data center to ConfigMgr 2012 R2 CU3 and am tired of having to manually run SQL queries and copy the results into a collection direct membership to push a remediation script for various software update issues. For example as I migrate machines from the ConfigMgr 2007 to ConfigMgr 2012 site some of the systems are failing to update their local policy to reflect the new Update Source resulting in a ‘Group policy conflict’ scan error. This is easy enough to fix with a script but I’m unaware of any way to access the contents of v_UpdateScanStatus via WQL to create the target collections (and manually changing the SQL behind a query rule in the DB is not something I can do here). This is just one of a dozen common issues that I’d like to be able to simply automate away an initial remediation attempt. My question to the group is what strategies are you employing to automate the remediation of software update scan / client issues when Microsoft exposes the data we need via SQL but is not making that available to us in the console via WQL? Is there some way that I can use Compliance Items to check for scan errors and then have the collections build off of those? Thanks! P.S. Anyone know the reasons behind not exposing more of these diagnostic views available in SQL so they are available in WQL for use within the console outside of reporting?
