Skip to content

Compliance report by machine

Featured Replies

I am trying to create a report that lists patch compliance by machine. It would be similar in scope to the standard compliance 3 report Update list (per update). I have no background in SQL and my attempts so far have not been successful.

 

I would like it to take in a collection and update list. I would like it to output something similar to this. Where missing = patches that are required in the update list that are not installed. Where Total is the total number of patches in the list. Compliant just needs to be a percent of the first 2. I would like this to happen for each computer in the collection.

 

Computer Name Missing Total % Compliant Collection ID

 

WKS01 (4) (174) 97.8% ABC000014

 

WKS02 (8) (174) 95.6% ABC000014

 

Any help would be appreciated.

I am trying to create a report that lists patch compliance by machine. It would be similar in scope to the standard compliance 3 report Update list (per update). I have no background in SQL and my attempts so far have not been successful.

 

I would like it to take in a collection and update list. I would like it to output something similar to this. Where missing = patches that are required in the update list that are not installed. Where Total is the total number of patches in the list. Compliant just needs to be a percent of the first 2. I would like this to happen for each computer in the collection.

 

Computer Name Missing Total % Compliant Collection ID

 

WKS01 (4) (174) 97.8% ABC000014

 

WKS02 (8) (174) 95.6% ABC000014

 

Any help would be appreciated.

 

 

 

Doing that will require alot of different views.

The hard part will be to narrow it down to a single update list. If you wanted a compliance report for all downloaded patches it would be much much easier.

 

 

  • Author

is there a guide to creating reports anywhere? If I can get the groundwork I can take the time to build what I want.

is there a guide to creating reports anywhere? If I can get the groundwork I can take the time to build what I want.

 

Look at this:

Reference - Configuration Manager 2007 SQL Views Schema.zip

 

 

As you can see there is allot of work in specifying a single update list (found in this view: "v_AuthListInfo").

 

You can start off with this code:

 

SELECT
a.Name0,
COUNT(distinct b.Title) AS 'Total Patches targeted', 
COUNT(distinct case when b.LastState not in (107,105,101) then b.Title else NULL end) AS 'Distribution Incomplete'

FROM
v_R_System a
JOIN v_GS_PatchStatusEx b on b.ResourceID=a.ResourceID
JOIN v_FullCollectionMembership c on c.ResourceID=a.ResourceID
JOIN v_UpdateInfo d on d.CI_UniqueID=b.UniqueUpdateID


GROUP BY a.Name0

 

 

It will show total patches targeted and total number of incomplete distributions for all patches on all computers at the moment.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.