9 Replies Latest reply on Aug 16, 2016 8:01 AM by TonyFlaherty

    Aged Patch Report?

    Rookie

      Hi,

      I've recently been given access to the LANDesk management suite web front end in order to create queries and reports that can monitor patching KPIs across our estate.  I don't have access to any SQL reporting or querying tools.  I've been told that although we have Data Analytics it is not accessible from the web front end.

       

      I need to create a query that I can then put into a Excel in order to determine the distribution of outstanding patches by age and criticality, i.e. a PivotChart with count on the Y axis and age in weeks on the X axis, this being calculated at TODAY - patch_publication_date

      I can then cut this in different ways to analyse by criticality, installed(Y.N) software vendor, etc.

       

      I think the basic column headings I need are;

      Device NameVulnerability IdPatch Date PublishedPatch Severity Age Wks SeverityVendorDate patch installedSuccessfully Installed

       

      I've been looking at the tables(views?) "Patch and Compliance Definitions" and "Detected Patch and Compliance Definitions" but don't seem to be able to get exactly what I want, for starters the query seems to do an outer join when adding tables, I've also got a report generated by my IT department using Data Analytics that contains at least one vulnerability ID that does not exist in either of these tables.  So I'm a little confused

       

      Anyhow, is there any kind soul out there who could point me in the right direction for producing what I'm looking for, or maybe give me a Data Analytics query that I can supply to IT to run for me?

       

      Also, does anyone know if is it possible for me to have access to Data Analytics without requiring access to the LANDesk server, either through a thick client or a web front end?

       

      Thanks in advance,

       

      Tony

       

      Installed LANDesk software

      Version: 9.60.0.244

      Installed patches:

      LANDESK Software 9.60 - Service Pack 1 : 9.60.1.119B

      LANDesk Software 9.60.1 - Software License Monitoring : 9.60.1

      LANDESK Software - Data Analytics : 9.61.0521

      LANDESK Software - Data Analytics Content : 10.2.1259

      LANDESK Software 9.60.1 - BASE : 2015-0114D

      LANDESK Software 9.60.1 - EPS : 2015-0114D

      LANDESK Software 9.60.1 - RC : 2015-0114D

        • 1. Re: Aged Patch Report?
          phoffmann SupportEmployee

          So to get you started on the basics / foundations of "what is where" - the following should help (apologies up-front for lots of reading material):

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

          - Re: Queries for Patch reporting

           

          Patch reporting can be quite a chunk to bite off. I'm a bit confused that they expect you to do this stuff without giving you access to the Windows Console proper ... bit of a head-scratcher that one (as it'd probably make your life a fair bit easier).

           

          <Also - as an aside / FYI - SP1 is 'somewhat older' ... about January 2015? While SP2 is out there, SP3 is being currently worked on. Just mentioning, in case software maintenance windows should appear on your side >

           

          The Age of a patch is not something we track of as such. We tend to track when a vulnerability was published (which can be different to having a patch), when a vulnerability was detected on each client ... what's the purpose of the "age" thing here? Or are you looking to essentially do a bit of 'your own maths' in that field of essentially (CurrentDate minus ReleaseDate = MyInterestedValue) ?

          1 of 1 people found this helpful
          • 2. Re: Aged Patch Report?
            Rookie

            Thanks for the reply. 

             

            I'm the InfoSec Manager here and am trying to get a handle on how responsive IT are to patching vulnerabilities and therefore what our risk exposure is like, i.e. the period between when a patch was released and so theoretically could have been applied and when it actually was applied.  I've agreed a patching cycle with IT and so have an agreed lag period for patches based on criticality, but at the moment have no way of seeing how IT are performing against this. 

             

            I'm trying to get to something like this PivotChart

            patchpivot.JPG

            Note that patch operations is the number of patches that need to be applied across the estate, so if two patches are needed by 50 machines this will be 100

             

            IT don't want to give me full console access in case I break something, which I totally understand.  From a least privilege point of view I'm happy with only having reporting access, although I've not explored the obviously non-reporting options on the menu in the web portal .  So to be fair it's not IT expecting me to do this stuff as much as I want to audit IT and have had some difficulty getting the reports I want so far, thus I've resorted to a "give me the tools and I'll do it myself" approach.  I come from an Informix SQL background, so whilst my SQL is rusty, I am used to working with data and other reporting tools.

             

            We are aware of the later SPs for the software and I believe there are cunning plans afoot.....

             

            Interesting that the time the vulnerability was identified on the client is tracked, this sounds like it would actually make more sense to monitor this as an indication of exposure time, although I can see shortcomings in that approach as well.  I think I was originally using this date but this seemed to be inconsistent, is this the first time the patch was identified?  if the machine is scanned multiple times, or if the patch is included in more than one bundle, would this date change?

             

            Thanks again for you help here, it is very much appreciated.

            • 3. Re: Aged Patch Report?
              phoffmann SupportEmployee

              So a couple of things then.

               

              1 - You can get access to the console without actually being able to break anything - that's why we've done RBA (Role-based administration) years ago. Pretty quick to set up a bunch of "view only" roles around inventory & patches / vulnerability.

               

              The WebConsole is primarily for your level 1 support folks ... and specifically a lot of the patching / vulnerability type stuff is NOT included there, as that's seen as a "serious admin" type work and is thus properly housed in the Windows Console.

               

              2 - Assuming that your IT folks (not sure how empowered they are around LD itself - if they don't know what you're talking about, you can point them at this thread ) are happy to set that thing up for you, and collect historical data in regular intervals, you get a nice benefit of a vulnerability-specific histogram overview as well (that is to say - a historical trend per vulnerability).

               

              <digs around for a vaguely representational screenshot ...>

               

              <... think I can dig something up from a customer DB ... >

               

              Ah here we go:

              Patch_TrendingData.jpg

               

              That gives you a nice bit of data from a historical perspective. "Normally" you'd expect it to go up (as the vulnerability gets detected) and then down (as it gets patched).

               

              3 - As an aside, check out if you have a SUM/TUM license for LANDesk. If you do, you can get a few free XTraction licenses that you could potentially use for doing this stuff "on the fly" .

              ... the idea being that you could ultimately build a dashboard for your needs (or have it built for you) and then you would always have "all the data - all the time".

               

              It's a data visualisation tool (and looks pretty neat!).

               

              XTraction space on Community:

              - Xtraction

               

              4 - If you end up upgrading to LD 2016, there's a new thing called "Rollout projects" (think "Process management light"). To give you an idea what it does is here (one video, one doc):

              - [Tech Brief On-Demand Webinar 2016] How to utilize "Rollout Projects" in Management Suite 2016

              - How To: Get Started Using the New Rollout Projects Tool for Software Distribution

               

              ... you can use it for software dist stuff (which you don't care about) as well as patching. It could do stuff like e-mail you (as a stake holder) to let you know "hey - pilot stage is done" / "hey - big rollout is done") type of thing. Just planting seeds.

               

              5 - Here's a screenshot of patch information on a device (this is "per vulnerability") to give you an idea of what's around:

              Inventory_PatchData.jpg

               

              ... how's that as a starter for 10?

              1 of 1 people found this helpful
              • 4. Re: Aged Patch Report?
                Rookie

                Thanks again for your help, I'll get together with my IT colleagues and see what we can do around this.  Good to know about the RBA!

                 

                Cheers,

                 

                T.

                • 5. Re: Aged Patch Report?
                  phoffmann SupportEmployee

                  Happy to help. If you / your colleagues get stuck, we can try to help out.

                   

                  By and large (after thinking your requirements over a bit), I think you should be able to use most of the information in the thread I linked earlier - Re: Queries for Patch reporting - as a foundation what you're after. If you're just after "patches that have been deployed" the PATCHHISTORY table is an awesome place to start for you.

                   

                  In addition (found the lil bugger) there's also this thread here which goes over a lot of detail & explains a lot of the structure of how we store things - Query for Reporting - that should help you along.

                   

                  Between that, you should hopefully have enough information to do what you need to. Whether the SQL then ends up in a custom reporting form (the reporting engine does support SQL statements) or XTraction or whatnot is down to you .

                  1 of 1 people found this helpful
                  • 6. Re: Aged Patch Report?
                    phoffmann SupportEmployee

                    ... also a belated point - the exact sort of thing you're after (Pivot and all) should be quite doable with XTraction.

                     

                    Depending on your license (IIRC you require a SUM / TUM license), you may be entitled to a small number of free licenses (2 concurrent connections to LANDesk Service Desk / Management Suite) ... worth checking with your sales guy in over!

                    1 of 1 people found this helpful
                    • 7. Re: Aged Patch Report?
                      Rookie

                      Thanks for the advice on this, for various reasons I haven't been able to progress this at my end   but I will provide feedback as much as I can

                      • 8. Re: Aged Patch Report?
                        jrrippel Apprentice

                        Tony,

                         

                        We just went through something similar - trying to get a list of missing patches per machine per criticality.

                         

                        We ended up putting all of our patches that we care about into a custom group and using that IDN. You can see the IDN there as 3582.

                         

                        Here's the SQL we used for it. The query is a bit of a bear - I'm not 100% great at SQL, but it does run. You should get the PC name, OU, last VulScan and then a list of the patches missing per device.

                         

                        If Object_ID('TempDB..#AllDevices') is not null
                        Begin
                          Drop Table #AllDevices
                        END
                        
                        
                        Select Computer.DeviceName, Computer.ComputerLocation, Computer.VALastScanDate into #AllDevices from Computer
                        
                        
                        
                        
                        ALTER TABLE #AllDevices ADD MissingPatches int,  MissingMSPatches int, Missing3rdPartyPatches int, MissingSPs int, MissingCrit int, MissingHigh int, MissingMedium int, MissingLow int, MissingNA int
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingPatches = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582))
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingMSPatches = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Vendor = 'Microsoft'
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.Missing3rdPartyPatches = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Vendor != 'Microsoft'
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingSPs = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 0
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingCrit = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 1
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingHigh = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 2
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingMedium = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 3
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingLow = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 4
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        UPDATE #ALLDevices
                        SET
                        #AllDevices.MissingNA = A.MissingPatches from (select Computer.DeviceName, count(distinct Vulnerability.Vul_ID) as MissingPatches
                        
                        
                        FROM            CVDetectedV INNER JOIN
                                                 Computer ON CVDetectedV.Computer_Idn = Computer.Computer_Idn INNER JOIN
                                                 Vulnerability ON CVDetectedV.Vulnerability_Idn = Vulnerability.Vulnerability_Idn INNER JOIN
                                                 VulSeverity ON Vulnerability.Severity = VulSeverity.Severity_ID INNER JOIN
                                                 VulType ON Vulnerability.Type = VulType.Type_ID LEFT OUTER JOIN
                                                 CustomGroupVuln ON CVDetectedV.Vulnerability_Idn = CustomGroupVuln.Member_Idn
                        WHERE        (CustomGroupVuln.CustomGroup_Idn IN (3582)) and Vulnerability.Severity = 5
                        group by Computer.DeviceName) A
                        where #AllDevices.DeviceName = A.DeviceName
                        
                        
                        select * from #AllDevices
                        Order by MissingPatches desc
                        
                        1 of 1 people found this helpful
                        • 9. Re: Aged Patch Report?
                          Rookie

                          Thanks for that, I'll see if I can get this tested.