7 Replies Latest reply on Jul 6, 2016 4:06 AM by phoffmann

    Queries for Patch reporting

    pauldavies1971 Apprentice

      Hi everyone

       

      So i am pushing out Windows patches and I was trying to see if there is a query that allows you to see what Patches have been pushed to any particular machine.

       

      Also is there a Query that allows me to search for a Particular patch to see if its installed too?

       

      Finally is there a query that allows me to confirm my patches have been deployed?

       

      Thanks

       

      Paul

        • 1. Re: Queries for Patch reporting
          phoffmann SupportEmployee

          So short answer - "yes" / "can be done".

           

          Longer answer - "How good/comfortable are you with SQL"?

           

          A few things we show in the GUI (such as a devices' repair history & current vulnerabilities) and have default reports for, but it sounds like you're trying to do a bunch of custom things. I can explain a couple of points around DB tables for you to look at / through, but would first need to know what level of comfort you would have with that (you're not going to CHANGE anything, you're just going to be doing selects / read-operations).

           

          You could then yank those into a custom report.

           

          There's other variations to get to a similar goal too (push come to shove - a "custom column view" & exporting that as a CSV for instance).

          • 2. Re: Queries for Patch reporting
            pauldavies1971 Apprentice

            Thanks

             

            simply put i'm not good with SQL. yes i think your right i'm looking to create a custom report. A standard report wont do it.

             

            I can create Queries but its more know what part of the data your looking at that would give you the list of Windows patches installed onto a machine?

             

            Paul

            • 3. Re: Queries for Patch reporting
              phoffmann SupportEmployee

              That's fine - as long as you're comfortable using SQL, that's the key part.

               

              I'll have a look at what I think would be useful data to yank out & build a few queries & explain what I'm doing when I get a few moments to myself, to help get you started.

               

              That way you don't have to (re-)invent the wheel, but only jiggle it around a bit / make it suit your liking .

              • 4. Re: Queries for Patch reporting
                pauldavies1971 Apprentice

                Sounds great, I look forward to hearing from you

                 

                Paul

                • 5. Re: Queries for Patch reporting
                  phoffmann SupportEmployee

                  So this will be a bit of a longer post ...

                   

                  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:

                  - http://w3schools.com/sql/

                   

                  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.

                   

                   

                  BASIC 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.

                   

                   

                   

                  KEY ATTRIBIUTES:

                  • 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 your queries!

                   

                  ==============

                   

                  • "Query that allows you to see what Patches have been pushed to any particular machine."

                  Not sure that makes an awful lot of sense. You CAN pull that easily enough via the patch history (see your query 3 below), but 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

                   

                  • 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
                  and CV.COMPUTER_IDN = COMP.COMPUTER_IDN
                  

                   

                  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

                   

                  NOTE -- 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
                  

                   

                   

                  • 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)?

                  GatherHistorical.jpg

                   

                  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):

                  CleanRepairHistory.jpg

                   

                  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')
                  

                   

                  ... think that should about cover you / give you a decent leg up with your needs?

                  • 6. Re: Queries for Patch reporting
                    phoffmann SupportEmployee

                    OK - edited my previous reply to include all of the information in one spot.

                     

                    Should be plenty in there to get you started / going.

                    • 7. Re: Queries for Patch reporting
                      phoffmann SupportEmployee

                      Once the article will be approved (hopefully later today), it should be visible under this link here - Getting started with Patch Reporting (SQL, Tables & such) - !