Patch Management Solution

This report does not work with ITMS 8.0.

Could you upload an updated version?

Thank you.

This is a great report.. however I do not get the details of buletins related to Windows 7 in the report. Do I need to make any changes in the query ?

This report has been extremely helpful. There is just one part I cannot figure out. Where does the @Filter = EB3A1A12-E1C7-4431-B060-F0333E4E488C come from? I appreciate any information you can give me on this. Thank you.

This is a great report. I have a question which I have not been able to figure out. Where does the value @Filter = EB3A1A12-E1C7-4431-B060-F0333E4E488C come from? I appreciate any information. Thank you.

This is really great. My question has to do with @Filter = EB3A1A12-E1C7-4431-B060-F0333E4E488C. Where is this value coming from? I appreciate any information you can provide. Thank you.

I have completed review of the 8.0 database schema changes and put out an updated report for ITMS 8.0 on INFO2905 and included the Note: to modify the GUID as needed to scope the target/filter to decrease rendered results.

Apologies for the delayed response:

Scott,

Please review the SQL Script; at the top of the query is the GUID that pertains to the 'All Computers with Software Update Plug-in Installed' and that can be swapped out for any Filter/Target GUID. You may review the Console > Manage > Filters; right-click > Properties, and pull that GUID out for replacement in this SQL Query. That should help to reduce the overall client count rendered.

Network23,

I am currently reviewing this custom SQL report to work in ITMS 8.0. I will update this article with that additional script as soon as I have that ready.

Hi Joshua Rasmussen,

It seems that the report doesn´t work in ITMS 8.0 because the view: "vPMCore_SoftwareUpdateAdvertismentRebootRequired" is missing is there another view we can use instead?

Network23

if you ask this question then you have not had to hunt for machines not patched. suggest dont use this and go look at the IT Anayltics instead 

This report is not intended for regular use and is a specialized report written for those that want all this data returned for each update/bulletin. If you need more vague data; Console > Reports > Software > Patch Management > Compliance Reports (Update, Computer & Bulletin) are in order.

Hi Scott W. Allen,

Click "edit" for this report and:

1. You can use SELECT TOP 100 or more in the begining of this report SQL Query

2. Also you can use ORDER BY [Computer Name] (or other column instead of PC name).

declare @Filter as uniqueidentifier--Define the variable with the @VariableName
set @Filter =
--'036E2BD3-4E10-4C23-B262-9A6B128A597F'
'EB3A1A12-E1C7-4431-B060-F0333E4E488C'

select TOP 10000
       c.Name as 'Computer Name'
      ,sb.Name + ': ' + su.Name as 'Bulletin: Update'
      ,ISNULL (su.SeverityName, sb.Severity) as 'Severity'
      ,case
            when isu._ResourceGuid is not null then 'Installed'
            when isu._ResourceGuid is null then 'Vulnerable'
            else 'Unknown'
      end as 'IsInstalled', ese.Start as 'Install Start Date', ese.Status --Vulnerable to Revised Update or updated Service Pack etc.
      ,case
            when sb.Enabled = 1 then 'True'
            when sb.Enabled = 0 then 'False'
            else 'Unknown'
      end as 'Downloaded'
      ,ci.Name as 'Vendor'
      ,case
     when reb.RebootRequired = 1 then 'Yes'
     when reb.RebootRequired = 0 then 'No'
     else 'Unknown'
end as 'Reboot Required'
from vRM_Computer_Item c--vcomputer--This is the computer, used for its guid and name
join Inv_Applicable_Windows_Software_Update asu--Get updates applicable to each computer
      on asu._ResourceGuid = c.Guid
left join vPMCore_SoftwareUpdateAdvertismentRebootRequired reb on reb._ResourceGuid = asu._ResourceGuid and reb._SWUGuid = asu.SoftwareUpdateGuid --Reboot Required 1=YES & 0=NO (Note: If 'NULL' returned; could be purged data or problem with missed event - see TECH167291)
left join Inv_Installed_Windows_Software_Update isu
      on isu._ResourceGuid = asu._ResourceGuid
            and isu.SoftwareUpdateGuid = asu.SoftwareUpdateGuid
join ResourceAssociation bul2su --Gets us from the update to the bulletin via its ResourceAssociation
      on bul2su.ChildResourceGuid = asu.SoftwareUpdateGuid
            and bul2su.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293'--Software Bulletin To Software Update
join vPMCore_SoftwareBulletin sb --Used to get the name, severity and Enabled status of the bulletin
      on sb.Guid = bul2su.ParentResourceGuid
join vPMCore_SoftwareUpdate su --Used to get the name and severity of the update
      on su._ResourceGuid = asu.SoftwareUpdateGuid
left join ItemReference ir on ir.ChildItemGuid = su._ResourceGuid --remove 'left' join to display only updates with a created Policy
and ir.Hint = 'swu'
left join Evt_AeX_SWD_Execution ese on c.Guid = ese._ResourceGuid --NULL = data has been purged from maintenance or failed to process etc.
and ese.AdvertisementId = ir.ParentItemGuid
left join ResourceAssociation super
      on super.ParentResourceGuid = asu.SoftwareUpdateGuid
            and super.ResourceAssociationTypeGuid = '644A995E-211A-4D94-AA8A-788413B7BE5D'--Software Component Supersedes Software Component
join CollectionMembership cm--Tables used for the filter parameter
      on cm.ResourceGuid = c.Guid
      and cm.CollectionGuid = @Filter
join ResourceAssociation bul2vend--Used to get the link from the bulletin to vendor
      on bul2vend. ParentResourceGuid = sb.Guid
            and bul2vend.ResourceAssociationTypeGuid = '2FFEB9F0-601E-4746-A830-BDB200076503'--Software Bulletin To Vendor
join vRM_Company_Item ci on ci.Guid = bul2vend.ChildResourceGuid--Used to get the name of the vendor
where super.ChildResourceGuid is null     --Exludes all superseded updates, not targetable by Patch

ORDER BY [Computer Name]
DESC

3. Don't forget about default "Group By" report controls where you can group report data

I'm getting 432140 Rows of results. WAY TO MUCH DATA! Is there a way to limit it so, for instance, it doesn't show updates succesfully installed?

Added revision 5 of this custom SQL Query to return additional column: IP Address