Patch Management report for pending/Not Installed patches for a collection/filter

[Pavankumar]

Hi All,

I am in need of a SQL report which will fetch below fields. 

Computer Name, OS Name, Bulletin Name, Update Name, Release Date, Reboot Status, Installed Status. 

The intention of this report is to determine all pending/not installed patches on a group of machines or a collection/filter. I have found one similar report by going through Compliance By Computer --> Right Click on any machine and choose View Applicable/Not Installed Updates. 

 

Unfortunately this report is not be editable and shows report for only one machine at a time, and does not show Reboot status. 

Please help me out in creating a report which will help greatly to improvise patch compliance issue in our environment. 

Thanks.

[Alex Hedley]

If you right click on the Report you can "View as XML" and grab the Query you can see how it's built.

Or right click "Clone" then you can edit it.

[Pavankumar]

Hi Alex,

Thank you for your response, I have already cloned the report "Software Compliance by Computer" that report will provide me patch compliance for a filter/collection in % and patch as applicable, installed or not installed, as mentioned in below screenshot. 

 

If I have to get the details of patches for any specific machine, I have to Right click on that computer and choose View Applicable, Installed or Not Installed Updates. 

I am in need of a report of all Not-Installed patches on all computers of a Filter/Collection. Please let me know is there any way I can generate one such.

Thanks.

 

 

[Alex Hedley]

Is there a WHERE clause in the "Not Installed" Report that has just a single Guid?

 

Possible helpful Articles, giving ideas of how to extend a current report to allow multiple instead of one.

Computers With Software Installed Report
https://www.symantec.com/connect/downloads/computers-software-installed-report

Computers With Software Installed Report (All Components)
https://www.symantec.com/connect/articles/computers-software-installed-report-all-components

[Pablo Llorente]

Hello,

I was looking exatly for the same report/SQL query. Maybe in the last 4 years someone developed something.. it would be very useful!

Thanks.

[Igor Perevozchikov]

Hi Pablo Llorente!

Please download and import this attached "Patch Management - (Not Installed Updates) report.xml" 
Specify appropriate Organizational group for computers and required filter to see report output

Best regards,
IP.

Attachment(s)

Patch Management - (Not Installed Updates) report.xml   38 KB 1 version

[Pablo Llorente]

Thanks a lot Igor.

I have imported it is not working as expected.

If I use the "Windows COmpliance by Bulletin" report:

[image: image.png]

Right click on Not Installed Computers report:

[image: image.png]

[image: image.png]

Now, using your report:

I dont get any record for the same KB:

[image: image.png]

An example, I have taken only computers from one particular OU to reduce
the scope:

[image: image.png]

It seems that the OS patches are not shown. I have edited the report and it
what Im seeing (maybe any parameter by default needs to be changed?)


















*EXEC [dbo].[spPMWindows_ApplicableComputers] @OperatingSystem = '%',
@DistributionStatus = 'active', @ScopeCollectionGuid =
'8ae9b068-cde6-454e-b672-52a63e22887a', @StartDate = '2023-02-05
00:00:00.000', @EndDate = '2024-02-05 00:00:00.000', @SWBGuid
= '00000000-0000-0000-0000-000000000000', @SWUGuid =
'00000000-0000-0000-0000-000000000000', @CompGuid =
'00000000-0000-0000-0000-000000000000', @pCulture = 'en',
@FilterCollection = 'eb3a1a12-e1c7-4431-b060-f0333e4e488c',
@TrusteeScope =
'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{6C4D09E2-C8A9-4138-8139-DC3BC1E6DA5A},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F}',
@VendorGuid = '00000000-0000-0000-0000-000000000000',
@CategoryGuid = '00000000-0000-0000-0000-000000000000', @ViewMode
= 'applicable', @SupersedenceStatus = 'non-superseded', @Severity
= 'non-superseded', @CustomSeverity = 'non-superseded'*

[image: image.png]

Running the query in my DB I'm getting 0 values, even changing some
parameters like TRustedScope etc..

Any suggestions?

Thanks!

Best Regards / Saludos
___________________________

PABLO LLORENTE ABAD

[Igor Perevozchikov]

Well, according to your SQL Query, parameters aren't correct
Here is example from my environment for this custom report

How looks SQL query that returns data in my environment:

EXEC [dbo].[spPMWindows_ApplicableComputers]
    @OperatingSystem     = '%',
    @DistributionStatus  = '%',
    @ScopeCollectionGuid = '91c68fcb-1822-e793-b59c-2684e99a64cd',
    @StartDate           = '2023-02-05 00:00:00.000',
    @EndDate             = '2024-02-05 00:00:00.000',
    @SWBGuid             = '00000000-0000-0000-0000-000000000000',
    @SWUGuid             = '00000000-0000-0000-0000-000000000000',
    @CompGuid            = '00000000-0000-0000-0000-000000000000',
    @pCulture            = 'en-US',
    @FilterCollection    = '01024956-1000-4cdb-b452-7db0cff541b6',
    @TrusteeScope        = '{0AB15774-DB0E-46F9-AC41-1D1405B61A1E},{1154345B-B906-4711-BF15-726CB015A85A},{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{5D557F48-4EAF-49A3-BE25-BF9F8DCEF6AC},{6C0465D3-BDB7-451C-AA59-6F37113DFE89},{7643FB8A-3F01-498C-9F9D-63E7A7C4A194},{9C2B769C-FD8E-456C-9028-FDD5A20BF90A},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{BD37BC65-F35B-4287-97D1-30AAB81B93BF},{EC3E67F1-5CD0-47E2-A0C1-813995A68E0E}',
    @VendorGuid          = '00000000-0000-0000-0000-000000000000', 
    @CategoryGuid        = '00000000-0000-0000-0000-000000000000',
    @ViewMode            = 'vulnerable',
    @SupersedenceStatus  = 'non-superseded',
    @Severity = '--any--',
    @CustomSeverity = '--any--'

Best regards,
IP.

[Pablo Llorente]

Thanks Igor,

Sorry, I replied yesterday but for any reasson the message was not sent. Additionally It seems that whenever I reply from my email, the imeages attached are corrupted.

I have paste your query in my DB and the query is returning results, so for any reason, the query that I use, the one taken from "Resolved Query" editing the report, is not correct... but anyway, thanks to send me yours!

I dont know how to filter the results by KB number in your query, when we are executing a stored procedure, but I have paste the results in excel and as you can see in the screenshot below, Im not getting results that contains patches for OS specifically, only for Office and third party applications. Do you know why it could happen?

Is the same  when I run the report.

Any idea Igor?