How to query Patch DB using SQL

Version 1
    Share This:

    Some times Patch Management repository filter don't work as you need and there's a limitation about logic operators.  You just can use AND operator for filter conditions.

     

    A more flexible way is create dynamic patch group based on Report Center queries.

    You have to create the query in /Queries/Patchmanagement to make it available in Patch Management.

     

    The following query is an example of how to query Patch DB for all Windows critical patches:

     

    --====== PATCHES FOR SEVERITY ***Critical*** =======

    SELECT

        DISTINCT pdb_node_id.repository_id, p1.value as Severity,  p2.value as Vendor, p3.value as BulletinID, p4.value as VendorID

    FROM

        pdb_node_properties p0, --PatchSource

        pdb_node_properties p1, --Severity

        pdb_node_properties p2, --PatchVendor

        pdb_node_properties p3, --BulletinID

        pdb_node_properties p4, --VendorID

        pdb_node_id

    WHERE

        pdb_node_id.type = 'Patch' AND

        p0.id = pdb_node_id.id AND p0.property = 'PatchSource' AND p0.value = 'WindowsPatchSource' AND

        p1.id = pdb_node_id.id AND p1.property = 'Severity#MSSeverity' AND UPPER(p1.value) = UPPER( 'Critical') AND

        p2.id = pdb_node_id.id AND p2.property = 'PatchVendorType#pdb.PatchVendorType' AND

        p3.id = pdb_node_id.id AND p3.property = 'ID#pdb.bulletinId' AND

        p4.id = pdb_node_id.id AND p4.property = 'ID#pdb.vendorId'

    --=============================================


    Note:  based on BBCA documentation, you can't use aliases in Report Center queries if you will use it to generate dynamic patch groups so you need delete aliases from query.


    In a recent incident I need to use this query to exclude some patches that are in Shavlik repository but are no more available from Microsoft to download.  Those patches were avoiding dynamic patch group to publish so the solution is to exclude those patches from dynamic group.

    You can use the patch repository filter to exclude those patches but in my case when I add more than 5 patch ID's, patch management never show the result.


    I have to use the following query to exclude the 14 problematic patches from dynamic group:

     

    SELECT

        DISTINCT pdb_node_id.repository_id, p1.value , p2.value , p3.value , p4.value

    FROM

        pdb_node_properties p0, --PatchSource

        pdb_node_properties p1, --Severity

        pdb_node_properties p2, --PatchVendor

        pdb_node_properties p3, --BulletinID

        pdb_node_properties p4, --VendorID

        pdb_node_id

    WHERE

        pdb_node_id.type = 'Patch' AND

        p0.id = pdb_node_id.id AND

        p0.property = 'PatchSource' AND p0.value = 'WindowsPatchSource' AND

        p1.id = pdb_node_id.id AND p1.property = 'Severity#MSSeverity' AND UPPER(p1.value) = UPPER( 'Critical') AND

        p2.id = pdb_node_id.id AND p2.property = 'PatchVendorType#pdb.PatchVendorType' AND

        p3.id = pdb_node_id.id AND p3.property = 'ID#pdb.bulletinId' AND

        p4.id = pdb_node_id.id AND p4.property = 'ID#pdb.vendorId' AND

        pdb_node_id.repository_id not in

        (

        'MS02-042.Q326886.Q326886_W2K_SP4_X86_EN.exe.641301052',

        'MS03-037.Q822150.VBA64-KB822150-X86-ENU.EXE._1902299727',

        'MS02-033.Q322273.Q322273_CS2002_EN.EXE._1599886903',

        'MS02-009.Q318089.vbs56nen.exe.1367499795',

        'MS02-009.Q318089.vbs55nen.exe._210990252',

        'MS02-032.Q320920.wm320920_71.exe._1239475359',

        'MS02-011.Q289258.Q289258engi386.EXE.1603292951',

        'MS04-032.Q840987.WindowsServer2003-KB840987-x86-enu.EXE.1216605805',

        'MS02-061.Q316333.8.00.0679_enua.exe.2072740888',

        'MS02-065.Q329414.q329414_mdac_all_x86.exe._218822457',

        'MS02-033.Q322273.Q322273_EN.EXE._283398336',

        'MS02-065.Q329414.Q329414_mdac_all_x86.exe.1925393639',

        'MS02-009.Q318089.vbs55nen.exe._375310540',

        'MS02-047.Q323759.Q323759a.exe.75211661',

        'FF05-106.QFF7259.FireFox307259-106.EXE.1406287395'

        )