Getting started with Patch Reporting (SQL, Tables & such)

Version 3

    Verified Product Versions

    Endpoint Manager 9.5Endpoint Manager 9.6Endpoint Manager 2016.xEndpoint Manager 2017.x


    I - Introduction

    The content of this article stems from a thread (Re: Queries for Patch reporting) & it seemed like a good idea of putting the information up as an overall article, to make it more easily searchable & editable.


    II - Getting Started


    II.A - Helpful tips (from personal experience)

    • Start small(1). Use 1-2 devices to get a feel for your report (easy to do - include an "AND COMPUTER_IDN IN (1,2)" clause at the end.
    • Start small(2) ... work against a test Core/environment. Start with a handful of patches first ... THEN go up.


    • Get your boss to sign off SQL training for you. I normally explain that "sooner or later - usually sooner" most LANDesk admins will need to do complicated stuff that *CAN* be done, just needs to be done via SQL. It's a great job aid (and a lot of complicated reporting usually requires some SQL understanding).


    You don't need to be a SQL admin - you just need to know what you can do. With that note, here's an awesome (free) SQL reference site that I tend to use regularly to remind me of syntax & examples:



    II.B - Key concepts & details

    • While there *IS* a direct data link between "patch x for Windows 2012 / patch z for windows 10" and "Vulnerability Y" (link 'VULNERABILITY_IDN' with 'PATCH_IDN' in the PATCH table. Link PATCH_IDN with the relevent OS platform in the PATCHTOPLATFORM table), it's not something that can be done well/easily in SQL (I tried) and requires actual programming code.
    • ... so it's easier to check for vulnerabilities by their NAME (i.e. - "MS12-345") as opposed to the specific file(s) you need (as it gets REALLY complicated if you need to potentially install several patches for 1 vulnerability).


    • ... so I'll be referring mainly to VULNERABILITIES (i.e. "MS12-345"), as opposed to patches ("MyFix.exe") (as especially multi-patch vulnerabilities very quicky lead to VERY messy reports, trust me).
    • Be mindful that you've also got patch superceedence to potentially deal with (which gets especially 'fun' when not ALL operating systems' patches get superceeded)..



    II.C - A basic database table overview

    • COMPUTER => Core table that is back-bone of 'everything' device related. You resolve the COMPUTER_IDN here (against 'DeviceName' for instance)
    • VULNERABILITY => Table that holds the 'central' information about vulnerabilites (i.e "MS12-345").
    • CVDETECTED => The current "main" vulnerability table. Only keeps track of what devices *ARE* vulnerable against (which is a much smaller dataset).


    • COMPUTERVULNERABILITY => This only gets populated/refreshed as/when you run "Gather Historical". It contains a listing of what your devices *ARE* and *ARE NOT* vulnerable against based on your parameters. Used to be the 'old' main vulnerability overview table but ballooned out massively in enterprise estates.


    • PATCHHISTORY => This table essentially feeds the "Clean/Repair History" section in the 'Security And Patch Information' view for a device. Nice, simple way to track what actual patch has been installed / when / what the result was.




    II.D - Key attributes/columns

    • COMPUTER_IDN -- use this to associate to devices (DB-internal unique reference). Resolve against a 'friendly' name in the COMPUTER table.
    • VULNERABILITY_IDN -- the equivalent of COMPUTER_IDN (DB-internal unique reference) but for vulnerabilities. Resolve in the VULNERABILITY table for a name (VUL_ID)
    • VUL_ID  -- The "friendly" name of a vulnerability - i.e. "MS12-345".


    ... on to the actual requested queries.




    III - Example Queries



    III.A - Request 1 - a "Query that allows you to see what Patches have been pushed to any particular machine."

    Not sure that makes an awful lot of sense "as written".


    While it is possible to pull that easily enough via the patch history (see query 3 below), surely it's more important to see if you're (still?) vulnerable to a specific vulnerability than making sure that "MyBinary.exe" has been pushed down?


    The reason I say this is because the problem is usually that there's different patches for different OS'es (and sometimes - different numbers of patches for 1 vulnerability based on the target OS). So it's usually easier & more sensible to query against a vulnerability, rather than a specific patch name?


    If you WANT this information, you can have it from the PATCHHISTORY table, which I cover in a bit of detail further below (assuming you're not interested in querying TASKS, which is a separate thing entirely).


    At this point, I also want to make you aware of the DIAGNOSTICS feature (available under 9.6 as "Scheduled Tasks and Diagnostics"). That's part of the "right-click" menu.

    Diagnostics - Starting.jpg


    Here you can see all tasks (and do a bunch of useful things) related to a specific device. That may help you with your question/need here?

    Diagnostics - InTool.jpg


    III.B - Request 2 - Also is there a Query that allows me to search for a Particular patch to see if its installed too?

    You can query this through regular inventory to see if you're vulnerable to a specific vulnerability (and why). That'd be probably the fastest way to do it. I expect that would be usually more helpful than seeing if "patch MyBinary.exe" has been pushed down/installed.


    If you *NEED* the latter, you can probably best use the PATCHHISTORY table (see below around your 3rd query response) ...  but I'd usually go against vulnerability data.


    Regular "am I vulnerable to X?" type questions you can query through a regular LANDesk Query as it's normal inventory information.

    Inventory - Detected_Vuls.jpg


    If you want/need to query this from the database side of things, the following should help (this one is going off the name of the vulnerability - so "2482122_WIN2008_WIN7_Manual" in this case:


    select CV.COMPUTER_IDN, Comp.DeviceName, Vul.Vul_ID, CV.PATCH, CV.REASON from CVDetected CV, Vulnerability Vul, Computer Comp
    -- Clause to resolve the VUL_ID based on the human-friendly name,
    where Vul.Vul_ID = '2482122_WIN2008_WIN7_Manual'
    -- ""Long-winded" but easier to understand way of joining the data from the tables
    -- First - matching up the Vulnerability name
    and VUL.Vulnerability_Idn = CV.Vulnerability_Idn
    -- Now matching up to the COMPUTER table, so we have nice device names to display


    The output of which will look something like this (I only got 1 result back, as I'm running against a small DB):

    SQL - Specific Vuls.jpg



    I'm consciously using the "long winded" way of joining data up in the example above. It's a bit more typing, but tends to be easier to follow for folks who aren't heavily into SQL. You can equally use something like a LEFT OUTER JOIN operation to achieve the same effect & fewer lines.


    The "exact" same SQL with JOIN-based operations would look like so (with an additional ORDER BY ... clause thrown in for good measure):

    select CV.COMPUTER_IDN, Comp.DeviceName, Vul.Vul_ID, CV.PATCH, CV.REASON
    from CVDetected CV (nolock)
    LEFT OUTER JOIN Vulnerability Vul (nolock) on Vul.Vulnerability_Idn = CV.Vulnerability_Idn
    LEFT OUTER JOIN Computer Comp (nolock) on Comp.Computer_Idn = CV.Computer_Idn
    where Vul.Vul_ID = '2482122_WIN2008_WIN7_Manual'
    order by COMP.DEVICENAME



    III.C - Request 3 - Finally is there a query that allows me to confirm my patches have been deployed?

    Given that (ultimately) there's a lot of different ways to patch a box (inside LD and out), it might be easiest to just run "Gather Historical" and have queryable information on devices NOT being vulnerable to vulnerability 'X' (and a reason)?



    You could use inventory query for that (Gather Historical will create/populate the "Patch And Compliance Definitions" section in devices' inventory - which is the "show me stuff I am NOT vulnerable to as well as the stuff I *AM* vulnerable to). By default (for space reasons) we only keep the (separate) "Detected Patch and Compliance definitions" part in inventory (as usually the focus is on what you ARE vulnerable to first and foremost).

    Inventory - Detected_and_GatherHistorical.jpg


    As for "any single device" - you can check it's patch history (with filter) through the GUI. It's a sub-section in a devices' Security and Patch Information screen as per here (notice me using the filter for Vulnerabilities):



    From a SQL point of view, this is pretty much a 1:1 match from the PATCHHISTORY table. If you want to pull this data out of the database, have a look at that table. (Ignore the error on my screenshot - I forgot I'd intentionally borked my DNS records for something unrelated ).


    If you "just" want the information in regarding what patches have been pushed (with LANDesk) on to a device, here's some SQL to help you along (Type=0 is for OS patches).

    select * from PatchHistory where type=0
    -- possible qualifier for a device (replace '123' with a valid number/entry from the COMPUTER table)
    and COMPUTER_IDN=123


    Here's a more posh example based on device name, rather than a specific COMPUTER_IDN (careful - COULD bring back multiple devices' records if you have more than 1 match on the device name!).

    select * from PatchHistory where type=0
    -- possible qualifier for a device based on name
    and COMPUTER_IDN in
    -- resolve COMPUTER_IDN from device name specified below as "MyDevice"
    (select COMPUTER_IDN from COMPUTER where DEVICENAME='MyDevice')


    IV - Further requests / queries...

    ... I'll try to keep an eye on this article & its comments - if people want a specific bit of data or whatnot, please add a comment & I'll see what I can do as & when I (or someone else) can get around to it .