Compliance report by machine

[TTech]

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.

[wmmayms]

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.

 

 

[TTech]

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

[wmmayms]

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.