I want to see a view in the Software Library that breaks down by client what is shown in the "All Software Updates" node.

Basically, with SCCM 2012 you can see all the software updates and there compliance summary which is nice, however I can't find a way to see a detailed version of this that breaks this all down by client.  

For example if I have a Software Update that is Required on 5, Not Required on 3, Installed on 4, and Unknown on 2 clients I want to know which specific clients make up those numbers. I want to know which clients are required, which are not required, which are already installed and which are unknown.

There doesn't seem to be a view like this in SCCM 2012 and its very odd that it doesn't provide this look into the data.

Am I missing something, can someone point me to the way to see this data quickly.

Check Monitoring -> Reporting node for built-in reports.

If they don't suit you, you can build your own report, here's an example: http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

• Proposed as answer by narcoticoo 7 hours 36 minutes ago

Look to the report Software Updates - A Compliance Compliance 1 - Overall compliance

You will need to organize the updates you want to report on into a software update group

Once you have the update group, you can run this report against a a collection of machines and it will show you which machines are compliant, meaning they have the updates in the software update group installed if they need them, or non compliant.  It will start out showing you a machine count for each compliance level, which you can then click into to see which machines in in each group.

This is a useful report to run if someone asks, which machines in our environment have x update installed.  Create a software update group with that update, create a collection of machines to report on and run the report.

• Proposed as answer by Hackmuss 1 hour 53 minutes ago

I was wondering exactly the same thing this morning...

Perfect Ralph, thanks. That seems to be the best way to do the job.

It's strange that the statistics graphs provided in the Software Update Groups section on the console don't allow you to click and see the details - in exactly the same way that you can under the Deployments section in the Monitoring node.

Typical Microsoft - provide the correct functionality in one place, but forget about it in another...  :-(

Check Monitoring -> Reporting node for built-in reports.

If they don't suit you, you can build your own report, here's an example: http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

• Proposed as answer by narcoticoo Thursday, January 30, 2014 4:43 AM
• Unproposed as answer by Rodney S. Foley 19 hours 58 minutes ago

Look to the report Software Updates - A Compliance Compliance 1 - Overall compliance

You will need to organize the updates you want to report on into a software update group

Once you have the update group, you can run this report against a a collection of machines and it will show you which machines are compliant, meaning they have the updates in the software update group installed if they need them, or non compliant.  It will start out showing you a machine count for each compliance level, which you can then click into to see which machines in in each group.

This is a useful report to run if someone asks, which machines in our environment have x update installed.  Create a software update group with that update, create a collection of machines to report on and run the report.

• Proposed as answer by Hackmuss Thursday, January 30, 2014 10:26 AM
• Unproposed as answer by Rodney S. Foley 19 hours 58 minutes ago

Yeah reporting is the wrong way to deal with this, I need to know this information constantly, this should be available under the Software Updates node. Reports are a work around not a solution.  I have used other patching products from other vendors and they all provide this information in real time in the UI without running a report.

As I mentioned to narcoticoo this is a workaround not a solution. It seems to be a missing feature that their competing patching product all provide.

To make it worse you are saying its not just a report but I have to place the updates in to a update group, so basically I have to place ALL my updates into a single group since I want this against ALL updates.  However I need this constantly as I mentioned to narcoticoo and reporting is not the solution. I will be running this report every 3-4 minutes as I am working through the updates.

So I disagree on the report is useful as it is a pain in the rear to do compared to how it should be. 

I wrote a SQL query that gets me this data faster but it is missing some of the updates because I can't find all the tables I need I guess. Maybe I can look at the reporting to see how they get it to create a SQL query that is just want I need. 

However a SQL query is just another workaround like reports.

This is a huge missing feature from Microsoft.

Please check this out. You may like it:

http://myitforum.com/myitforumwp/2014/01/06/sccm-2012-r2-required-by/

• Marked as answer by Rodney S. Foley 19 hours 7 minutes ago

Yeah this is better than a report, and that was my next step was to see if I could do this in WMI instead of SQL and looks like you can. I may use this as a base to extend to also get Not Required, Installed, and Unknown states as well if possible.

Thanks

There you go, it shouldn't be hard to do at this point.

I'm interested in exactly what you are doing that you need up to date info every 3-4 minutes in the console itself and why simply refreshing a report doesn't meet that need?

Just a note that it appears WMI has the same limits as SQL since it appears the WMI tables all map directly to a SQL counterpart. Basically you can only get Required or Not Required from the Update_ComplianceStatus.Status no update seems to be anything but a 2 (Required) or 3 (Not Required). So my existing SQL query is pulling the same data as that WMI query was.

I found that in SCCM 2007 they had views that provided Unknown and Installed status breakdowns but they didn't make it to 2012. They have to be store Unknown and Installed in the DB some place but I haven't been able to find it yet.

FYI ... I ran the report you point out and it tells me Installed and Required states but it does not tell me if its Not Required and Unknown.

All 4 are important, and it looks like Not Required and Unknown is only stored in summary form without any details as to which clients and update are not required for or unknown. 

This is bad because Installed and Required are not as important as this is used for troubleshooting issues. So When you have several Non Required and Unknown these are the clients I need to know the most as they may actually be required and the unknown state is sometimes sticks around forever and I can never find out which client is in the Unknown state which means I have to go to every endpoint and try to trouble shoot each one.

The best I can do now is rule out the Installed and Required ones. Which can be good if most of the clients are in Required or Installed state however if you have a lot of Not Required finding that Unknown will be a pain.

They have to be store Unknown and Installed in the DB some place but I haven't been able to find it yet.

It is dbo.v_StateNames view.

StateName: Compliance state unknown

I am aware of that view however that is just mapping the state number to a name for several different "TopicTypes". And the one you are point to is actually not for Software Updates Scan Status. The TopicType for a Clients Update Scans is 500, which has 0-3 only. 0 is supposed to be Unknown, 1 not-required, 2 required, and 3 installed.

They set the status for an endpoint/update for 2 and 3 but I have several Unknown, and Not-Required and they don't write those rows to the database if they are in that state. 

It seems when they are gathering data from the client the summarize everything, and one keep the specific rows for clients if they are required or installed, and they don't create a row for the other states. From some digging it looks like they stored this data in 2007, but dropped in in 2012.  

Maybe it is to conserve space or something I don't know but its stupid. It would be nice if there was a hidden setting that would enable it to store these rows as the database is set to collect them, they just don't.

FYI...

For WMI Queries you can use SMS_StateInformation which maps to the v_StateNames.

• Edited by Rodney S. Foley 13 hours 44 minutes ago