1 2 Previous Next 19 Replies Latest reply on Jan 18, 2018 8:50 AM by systemssupport

    Report/Query to show a group of PC's with outstanding patches.

    systemssupport Apprentice

      Due to PCI we have to report on a group of PC's that may have any outstanding patches that older than the 30 day published date.

       

      I currently have a basic query built but struggling to get further with it. I will show what I currently have and then explain what I need adding.

       

      Currently have:

       

      "Computer"."Detected Patch and Compliance Definitions"."Date Published" < "01/12/2017 00:00"

      AND "Computer"."LANDESK Management"."Agent settings"."Name" = "Production Workstations"

      OR "Computer"."LANDESK Management"."Agent settings"."Name" = "Pilot Workstations"

       

      Production Workstations and Pilot Workstations are the name of the two Distribution and Patch settings we use for all PC's within the business.

       

      So the above gives me ALL PC's that show any patches with a date published prior to 01/12/2017 within "Detected Patch and Compliance Definitions" of a PC's inventory.

       

      However I don't want all PC's I just need to query on a certain group of PC's. Is there anyway I can do this as I am seriously struggling and need to get this completed asap.

        • 1. Re: Report/Query to show a group of PC's with outstanding patches.
          phoffmann SupportEmployee

          Sure.

           

          Use the Multi-insert feature & add an additional clause. For instance, based on device name if you don't have another way of reducing the group.

           

          So here's some pseudo-code for the query (note that I *AM* using ()-s here!):

          {Your previous Query logic...}

          AND (
          COMPUTER.DEVICE NAME LIKE 'DEVICE_1'

          OR COMPUTER.DEVICE NAME LIKE 'DEVICE_2'

          OR COMPUTER.DEVICE NAME LIKE 'DEVICE_3'

          OR COMPUTER.DEVICE NAME LIKE 'DEVICE_4'
          )

           

          For instance. So the basic steps -- I'm assuming you've got a list / know what devices you want to report this on.

           

          So ...

           

          • have that list of device names (ideally EXACTLY, as LIKE is much slower than "=" operations and less accurate !).
          • Edit your query and use the MULTI-INSERT button (see screenshot for reference)
          • ... chuck in the list of devices - make sure to remember the following:
            • Switch the operator over to "OR" (it's "AND" by default).
            • IIRC, there's a limit of 50 objects being added at a time.
            • Don't forget to have a "," or so as a separator at the end of your device names .

           

          • Switch the "top" element over from an OR to an AND...
          • ... and finally group up the list of "DEVICE NAME = blah" stuff with the ()-s.

           

          ... that should do it?

          • 2. Re: Report/Query to show a group of PC's with outstanding patches.
            phoffmann SupportEmployee

            As a separate topic - since you're doing patch reporting ... you may want to have a peek at this as well -- Getting started with Patch Reporting (SQL, Tables & such) .

            • 3. Re: Report/Query to show a group of PC's with outstanding patches.
              systemssupport Apprentice

              Ok so I've narrowed this down further.

               

              Query now looks like:

               

              "Computer"."Detected Patch and Compliance Definitions"."Date Published" < "01/12/2017 00:00"

              AND ("Computer"."LANDESK Management"."Agent settings"."Name" = "Production Workstations"

              OR "Computer"."LANDESK Management"."Agent settings"."Name" = "Pilot Workstations")

              AND "Computer"."Computer Location" ="domainname/Internal/Call Team/CDE Finance/Computers"

              OR "Computer"."Computer Location" ="domainname/Internal/Sales Team/CDE Sales/Computers"

               

              Should of also added that Select Columns/Machine Components selected are:

               

              "Computer"."Device Name" - Device Name

              "Computer"."Type" - Type

              "Computer"."Detected Patch and Compliance Definitions"."Patch Required" - Patch Required

              "Computer"."Primary Owner" - Primary Owner

              "Computer"."Detected Patch and Compliance Definitions"."Date Published" - Date Published

               

              Problem I am having now is that when running the query am seeing BLANK sections for "Patch Required"....

              • 4. Re: Report/Query to show a group of PC's with outstanding patches.
                systemssupport Apprentice

                It's also bringing back results with a patch published date LATER than 01/12/2017!?

                • 5. Re: Report/Query to show a group of PC's with outstanding patches.
                  systemssupport Apprentice

                  If only! Only just got funding for an ivanti Endpoint Manager Bootcamp... after 2 years!

                  • 6. Re: Report/Query to show a group of PC's with outstanding patches.
                    phoffmann SupportEmployee

                    So - first of all ... you want to add a set of ()-s around the last two things (computer location) for "query logic cleanliness". At best, it won't make a difference, at worst you can screw yourself over quite badly by having AND/OR statements not correctly sectioned off .

                     

                    ... and when you run your query, you do get data in the "date published" column (I'd argue - always add the title & reason for the fields as well).

                     

                    OK - so ... couple of questions.

                    • Are you ABLE to run a SQL statement based report? (I.e. - if I throw you some SQL, can you actually get it to run)?
                    • How friendly are you with the DBA (if you have one?)? Bribing people in IT with simple things like food is usually quite helpful .
                    • What data fields do you specifically need? I can try to whip up something on the quick for you so that you have "the data right now", and we can walk you through doing things "the proper way" after your blood pressure went back to normal?
                    • 7. Re: Report/Query to show a group of PC's with outstanding patches.
                      systemssupport Apprentice

                      Thanks for your assistance.

                       

                      So I added Group () around the last FIVE Computer Locations (Yes it's a few more now haha). And now nothing comes back at all when running the query hahaha.

                       

                      Answers ;0)

                       

                      I have no access to SQL at all.

                      Am friendly(ish) with the DBA, no need for bribes

                      Data fields needed are:

                       

                      Oh blood pressure is high when am told "Need this by the end of the week"... nothing like pressure.

                      • 8. Re: Report/Query to show a group of PC's with outstanding patches.
                        phoffmann SupportEmployee

                        Should only take a few minutes / 30 mins or so.

                         

                        As an aside - (as hinted at in the "Getting started with patch reporting") - pester you boss(-es) into getting you basic SQL training signed off. I'm not talking about something like "how to become a SQL admin in 1 week" sort of affair - but even just a 1-2 day introduction into SQL will help you a TONNE ... (and then beg your DBA to get you at least read-access to a Query analyser console) .

                         

                        Leave this with me, and I'll work something up that you can then fiddle with your DBA to fine tune ... .

                        1 of 1 people found this helpful
                        • 9. Re: Report/Query to show a group of PC's with outstanding patches.
                          phoffmann SupportEmployee

                          OK - snuggle up with your DBA  so you can run this (you'll need to edit a few strings though!).

                           

                          How's this as a starting point (I've added plenty of comments so you / your DBA can understand what I'm doing where, hopefully) .

                           

                          select COMP.DeviceName, COMP.Type as 'Device Type', OS.OSType as 'OS Name',
                          CV.Patch as 'Patch Required', VUL.Vul_ID as 'Vulnerability Name',
                          COMP.PrimaryOwner as 'Primary Owner Name', VUL.PublishDate as 'Vulnerability Publish Date'
                          from COMPUTER COMP (nolock)
                          -- JOINS with other tables...
                          LEFT OUTER JOIN BehaviorInfo2v AGNTBEHV (nolock) on AGNTBEHV.Computer_Idn = COMP.Computer_Idn
                          LEFT OUTER JOIN Operating_System OS (nolock) on OS.Computer_Idn = COMP.Computer_Idn
                          LEFT OUTER JOIN CVDetected CV (nolock) on CV.Computer_Idn = COMP.Computer_Idn
                          LEFT OUTER JOIN Vulnerability VUL (nolock) on CV.Vulnerability_Idn = VUL.Vulnerability_Idn
                          -- WHERE clause(-s)...
                          WHERE
                          -- Filtering on Computer Location...
                          (COMP.ComputerLocation = 'Location_String_1'
                          OR COMP.ComputerLocation = 'Location_String_2'
                          --- keep adding as needed.
                          OR COMP.ComputerLocation = 'Location_String_3')
                          -- Filtering on Agent Setting Name
                          AND (AGNTBEHV.Name = 'Production Workstations'
                          OR AGNTBEHV.Name = 'Pilot Workstations'
                          )
                          -- Filtering on Vulnerability publish date being older than 30 days. Feel free to alter the number.
                          -- I'm doing this "dynamically" so I don't have issues with regional date differences and don't need
                          -- to edit the report every time it gets run. So "GetDate()-30" just resolves to "current date minus 30". 
                          AND VUL.PublishDate < (GetDate()-30)
                          -- Filtering for OS/application vulnerabilities.
                          -- Without this, it'll include Security Threats, Custom Vulnerabilities & so on ... a *LOT*.
                          AND VUL.Type = 0
                          -- ORDER BY "for the pretty"
                          ORDER BY COMP.DeviceName, VUL.Vul_ID
                          

                           

                          ... let me know how that goes?

                          • 10. Re: Report/Query to show a group of PC's with outstanding patches.
                            systemssupport Apprentice

                            Thank you. Really appreciate your help!

                            • 11. Re: Report/Query to show a group of PC's with outstanding patches.
                              phoffmann SupportEmployee

                              Eh - you can thank me once we got it all working.

                               

                              No worries - all been there. Hence - let's get you out of your initial hole to begin with, and then we can have a more relaxed approach once the immediate forest fire is dealt with .

                              • 12. Re: Report/Query to show a group of PC's with outstanding patches.
                                systemssupport Apprentice

                                Getting query analyser installed now. Will get back to you asap.

                                • 13. Re: Report/Query to show a group of PC's with outstanding patches.
                                  systemssupport Apprentice

                                  YOU MY FRIEND ARE A LEGEND AND A SAVIOUR!!! THANK YOU!

                                   

                                  This has worked perfectly!! SQL Query came back blank. Then added another computer location for PC's that I knew had vulnerabilities and low and behold.... they listed!!!!

                                   

                                  Now how do I get this SQL Query into a report hahahaa.

                                  • 14. Re: Report/Query to show a group of PC's with outstanding patches.
                                    phoffmann SupportEmployee

                                    We can take that one on in "bits and pieces".

                                     

                                    As a general note, I tend to express that "sooner or later" (usually - sooner), folks WILL need to dig into the database for best reporting tools. The query tool is a "SQL light" sort of thing for "folks that don't do SQL". It does its job fairly well, but it comes with limitations in that it is ... "simple" (or "simpler").

                                     

                                    Full-on usage of SQL does help a lot (and you *CAN* use SQL-statements in a report in the reporting tool!). So you may want to play around with some of the standard reports we have (all our reports can be copied & then edited to be examined / used as a basis of work). For instance, a lot of the SLM reports will likely be using SQL statements since they're poking data that's "there" but not usually displayed in inventory.

                                     

                                    Do take note that "reporting" (and reporting design) is very much a "world of its own" ... and I tend to find using XTraction is a much prettier & less painful experience (plus, I can still throw raw SQL based reports at XTraction & my customers don't know any better).

                                     

                                    But yeah - happy to have gotten you unstuck and helped a tad along .

                                     

                                    <You see now why I am a HUGE evangelist on getting folks to get signed off on attending basic SQL courses ... any kind of advanced operation very much benefits from that skill-set and it doesn't take that much of an investment to get there. You don't need to be a SQL wizard. You just need to know where to look up / how to google for syntax >

                                    1 2 Previous Next