7 Replies Latest reply on Jul 20, 2016 2:45 AM by phoffmann

    Query for Reporting

    talk2neeraj2k7 Apprentice

      Hi Everyone,

       

      I am trying to build post patching report using queries that should include at least below mentioned columns, I have already tried built-in report in Reports >> Security > Vulnerabilities "remediated vulnerabilities" and also Patching History by device that I got from the LANDesk community.

      The issue with these reports is selecting 50 devices out of 1000 to run report against, I mean keep scrolling and selecting few devices out of many is not flexible, So I looked at "Patching History by device", there we have the option to select device groups instead of checking indivisual devices, now that's really good. However I want the report in Excel format, so I export it and after exporting it, it requires lot of excel related formatting like deleting empty rows, etc. and it does not have below mentioned 7 columns that I require at a minimum in my report.

      1) Device Name

      2) VUL ID

      3) Patch Name

      4) Patch Action - Success or Failed

      5) Message (Repair done / failed etc.)

      6) Severity (Critical, High, Medium etc.)

      7) Action Date (What date patch installed or failed)

       

      So I started exploring LANDesk queries, and built this query,

      New Picture (1).bmp

      It is working fine and giving me correct number of records, however it does not have "Severity" and "VUL ID" that I want to include.

       

      So I modified query this way:

      New Picture (2).bmp

      I added Severity and ID from "Detected Patch and Compliance Definitions", this query is running, however giving me wrong number of patches installed, and many rows are getting duplicated with patch name.

      it looks like: Windows6.1something.exe   Critical

                         Windows6.1something.exe   N/A

                         Again_some_patch.exe        Critical

                         Again_some_patch.exe        N/A


      Is it because "Patch History" and "Detected Patch and Compliance Definitions" may be separate tables in SQL DB and they can not build any connection (joins) between each other, so records are getting duplicated. I am not sure.


      Is it possible to fix it without using any SQL query, if not, can anyone write an example SQL query to guide me (as I am new to SQL code that is the reason I am preferring queries).


      Thank you,

        • 1. Re: Query for Reporting
          phoffmann SupportEmployee

          Hello there!

           

          First of all - (in case you can get your boss to read this) - yes, you'd absolutely benefit from a foundation in SQL. If you can get some time set aside / get sent on SQL training, that'll make both the admin-work around LANDesk easier as well as the reporting. I tend to say that "sooner or later - usually sooner - you're going to need to dive into the database".

           

          It doesn't need to be a full "SQL Server Administration" course - just a foundation in the SQL language itself - and the rest is practice & knowing where to look things (/syntax) up, as you should have an idea with what's possible .

           

          As a hopefully useful reference, here's an awesome (free) site that I've been using for years to look up SQL syntax myself (3rd party site obviously)  -- SQL Tutorial -- !

           

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

           

          So - with that out of the way - on to your real question. (I'll point you at a bunch of relevant tables).

           

          List of tables referenced:

          - COMPUTER (resolve COMPUTER_IDN's - includes device ID's and Device Names)

          - CVDETECTED (list of detected vulnerabilities & what patch name(s) they need - resolve by COMPUTER_IDN / VULNERABILITY_IDN )

          - VULNERABILITY (list of all vulnerabilities - resolve by VULNERABILITY_IDN / VUL_ID if you want the "human friendly" ID )

          - PATCHHISTORY (history of patches - resolve by COMPUTER_IDN and PATCH (patch file name))

          - PATCH (list of actual patches - resolve against PATCH_IDN and UNIQUEFILENAME)

           

          • Patch History is linked to Computer entries (COMPUTER_IDN's) - and not Computer Vulnerabilities as such. Essentially it's "just strings". You can see for yourself in the PATCHHISTORY table based on COMPUTER_IDN .

           

          There are complicating factors - the ACTION is a numerical value in the database (PATCHHISTORY.ACTIONCODE) which the console translates ("Reboot Needed" / "Success" etc.). Now it IS possible to substitute those via SQL in-line (via UNION statements for instance), but it's a bit of a pain, as you don't have a simple readable "text" value to just throw back.

           

          So I've gone and picked up the PATCHHISTORY.MESSAGE instead (which is a string). See how that works for you (I've not had any failed patch installs to test against).

           

          • Severity (VULNERABILITY.SEVERITY) is also a numerical value in the database. Furthermore, there's the choice of "Vendor" severity and an optional overriden severity (OrigSeverity will be NOT NULL if an override has taken place).

           

          This SQL should get you started ... I *tried* to make it relatively simple (hence quite a lot of lines), in as far as I could. How does this work for you?

          select Comp.DeviceName, Vul.Vul_ID, CVD.Patch, PH.ActionCode, PH.Message, Vul.Severity, PH.ActionDate

          from Computer Comp, Vulnerability Vul, CVDetected CVD, PatchHistory PH

          where Vul.Vulnerability_Idn = CVD.Vulnerability_Idn

          AND CVD.Patch = PH. Patch

          AND Comp.Computer_Idn = PH.Computer_Idn

          AND CVD.Computer_Idn = Comp.Computer_Idn

          AND PH.ActionDate > 2016-06-06

          order by Comp.DeviceName, Vul.Vul_ID

           

          The WHERE clauses are essentially to marry the data up correctly. Could equally be done via something like a "LEFT OUTER JOIN ON..." type of approach, but the above should hopefully be less confusing?

           

          Hope this helps.

          • 2. Re: Query for Reporting
            talk2neeraj2k7 Apprentice

            Hi Paul,

             

            First of all sorry for long delayed response, got busy with other stuff around, did not have access to SQL DB, probably that is the reason it took me so long.

            As per your suggestion I started learning SQL little bit, Thanks for the great reference w3schools.com\sql

             

            Today when I was about to reply to this post, while searching I saw another very helpful new master piece from you, Yes I am talking about this awesome content.

            https://community.landesk.com/thread/32154?start=0&tstart=0

            https://community.landesk.com/docs/DOC-41354

             

            Your efforts are really appreciated by everyone in this community !

             

            Now back to the SQL query that you wrote for me to have below mentioned columns in post patch report, we really need Severity and VUL_ID in our post patching report.

            1) Device Name

            2) VUL ID

            3) Patch Name

            4) Patch Action - Success or Failed

            5) Message (Repair done / failed etc.)

            6) Severity (Critical, High, Medium etc.)

            7) Action Date (What date patch installed or failed)

             

            Sorry this is going to contain lot of snapshots, it helps to visualize things better

            1.PNG

            I run above query as mentioned by you with additional ActionCode = 2 (just to see successful patches) and with ActionDate less than or equal to today's date (2016-07-06)

             

            11.PNG

            it shows 0 results.

             

            2.PNG

            Then I run above query, where ActionDate > 2016-04-30 (as we started patching using LANDesk after 30 April)

             

            22.PNG

            It does show results, however very few no. of records, we patched a lot.

             

            222.PNG

            It returned only 493 rows.

             

            3.PNG

            Here I skip ActionCode, to see everything success, failed etc

             

            33.PNG

            It does show results, however very few no. of records, again we patched a lot. (Failed + success + other things, I expected more records to show)


            333.PNG

            It returned only 6256 rows.


            4.PNG

            In this query, I did ActionDate = 2016-06-10 as marked blue, the reason I checked this date because in previous upper snapshot marked blue "ActionDate" it shows that patching did happen on 2016-06-10 (whether failed or success)

             

            44.PNG

            it shows 0 results. See all the blue marked, from upper snapshot we know that patching happened on 2016-06-10, so it should have returned something instead of 0 results.


            5.PNG

            I run this query, only for successful patching so using ActionCode = 2 and ActionDate greater than 30 April as we started patching after 30 April using LANDesk.


            55.PNG


            It returned correct results.


            555.PNG

            So it returned 18815 rows, Now these records are correct, our successful patching so far 18815 patches on all the devices. (I run this query because I am hoping to see the same no. of records with Device Name, Severity and VUL_ID in my post patch report, however I am getting very few records as you can see in topmost snapshots when I am pulling them with Device Name, Severity and VUL_ID)


            6.PNG

            Here I am using LANDesk Query instead of direct SQL query.


            66.PNG

            So this query returns almost correct no. of records (notice almost)


            666.PNG

            The reason why I am saying almost because SQL query above returned 18815 records and with the same logic LANDesk query supposed to show same no. of records (instead it is showing 18507)


            Now 18815 - 18507 = 307 difference, I can easily compromise on this slight difference No worries

             

            However when I run the SQL query to fetch post patching report with Severity and VUL_ID columns, it shows very less no. of records for Action Code = 2 (success) it shows around 493 records (My goal is to see there 18000+ returned rows / records)

             

            I hope, I am clearly describing what issue I am facing through lot of snapshots , don't want to confuse anyone !

             

            Does this issue has anything to do with Gather Patch Historical Information Task, I hope yes, so that's the reason I run that task daily.

             

            I am not sure but does below mentioned red highlighted point has anything related with this issue, If Yes, please let me know.

             

            kkkkk.png

             

            It was 90 days, I thought may be it is related so I increased this no. to 180 days, but still same results.

             

            Thank you for your attention on this !

             

            Once again Thumbs Up for your new master piece - Queries for Patch reporting

             

            Regards,

            Neeraj

            • 3. Re: Query for Reporting
              phoffmann SupportEmployee

              Hi Neeraj - glad to hear that the article I've written up has been useful to you.

               

              I've had a look at your post & should be able to help you along a little bit I hope. No problems on the screenshot side of things - that served to explain what you're trying to do & as such is fine .

               

              (Friendly) Warning - this will be a bit longer, as I'm trying to explain the stream of consciousness, so that the "WHY" of various decisions & changes makes sense. So apologies for the wall of text - it's meant to be helpful & if it's "a bit much" - just go through it a little bit at a time.

               

              So ... first up - a few pointers.

              1. You're querying the CVDETECTED table - that ONLY holds data about DETECTED vulnerabilities (so - by definition - once you patched a vulnerability & re-scanned the device, you're not going to find any "stuff you're NOT vulnerable to" type data in that.
              2. That's what you want to query the COMPUTERVULNERABILITY table for (that stores data on what your devices *ARE* and *ARE NOT* vulnerable to - important distinction).
              3. IMPORTANT NOTE - The COMPUTERVULNERABILITY table only gets updated when you run "Gather Historical" (with the settings that you set there).
              4. The "Gather Historical" settings don't seem to affect the contents of the PATCHHISTORY table (but there's a bunch of maintenance stuff that doesn't tend to run all too often on my VM's as they're not on 24x7).
              5. When using dates in SQL, still use '-s.... having run a simple query on PatchHistory, I got 0 results back as well, until I encapsulated the date value with '-s ... so I suspect that there's some "sometimes I feel like it / sometimes I don't" style shenanigans going on from the SQL side of things. So ... better to use '-s and "make sure" (I've not had any problems with queries there).
              6. A complicating factor that was overlooked was "multi-patch" / pre-requisite type steps - so I'll attempt to crowbar your SQL around somewhat

               

              Couple of caveats as well:

              • I'm at a slight disadvantage here, since I'm not able to look at your data & DB - and my lab DB's generally tend to get reset a lot, so I don't exactly have a lot of old data to be trawling through.
              • ... BUT - that has its advantages too - as I've spotted something you hadn't, on account of dealing with a smaller quantity of data.

               

              Now - a few exploratory things... and how your query evolved!

               

              • First up - using '-s around dates. As per the below, you see the difference that that one thing makes .

              No Quotes around the date:

              SQL_Dates_NoQuotes.jpg

              And now the exact same thing with quotes:

              SQL_Dates_WithQuotes.jpg

              ... so - use quotes ... that should reduce some of the SQL-side falsification of results.

               

              • Use COMPUTERVULNERABILITY instead of CVDETECTED

              As explained above, CVDETECTED will only list vulnerabilities that *ARE* detected - so if you don't detect an instance of a vulnerability at all anymore, you're going to be robbing yourself of some data lines.

               

              I've amended your SQL to be like so at the moment:

              select COMP.DeviceName, Vul.Vul_ID, CV.Patch, PH.ActionCode, PH.Message, Vul.Severity, PH.ActionDate
              from Computer Comp, Vulnerability Vul, ComputerVulnerability CV, PatchHistory PH
              where Vul.Vul_ID = CV.Vul_ID
              and CV.Patch = PH.Patch
              and Comp.Computer_Idn = PH.Computer_Idn
              and Comp.Computer_Idn = CV.Computer_Idn
              and PH.ActionDate <= '2016- 07-07'
              and PH.ActionCode = 2
              order by Comp.DeviceName, Vul.Vul_ID
              
              

               

              ... but noticed the following interesting thing (4 returned lines versus 6 expected) when I compared against my 'complete' PatchHistory contents:

              Patch_SQL_1.jpg

               

              I was a bit curious as to why I was missing 2 lines ... and after a quick suspicion-based check was proven right:

              Patch_SQL_2.jpg

              ==> I suspect that a good chunk if your "missing data rows" comes from the fact that you're installing patches that are a pre-requisites or some such (in my case, for instance, that blockertoolkit) ... in some cases, a single vulnerability will also require multiple patches to be installed. So - something to watch out for.

               

              So - rather than linking the PATCH name back to the CVDETECTED / CUSTOMVULNERABILITY table, let's switch things around & link it back to the PATCH table instead. So now our SQL will look like so (WILL NOT WORK):

              select COMP.DeviceName, Vul.Vul_ID, CV.Patch, PH.ActionCode, PH.Message, Vul.Severity, PH.ActionDate
              from Computer Comp, Vulnerability Vul, ComputerVulnerability CV, PatchHistory PH, Patch P
              where Vul.Vul_ID = CV.Vul_ID
              -- changes start here
              and P.UniqueFilename = PH.Patch
              and Patch.Vulnerability_Idn = Vul.Vulnerability_Idn
              -- changes stop here
              and Comp.Computer_Idn = PH.Computer_Idn
              and Comp.Computer_Idn = CV.Computer_Idn
              and PH.ActionDate <= '2016- 07-07'
              and PH.ActionCode = 2
              order by Comp.DeviceName, Vul.Vul_ID
              
              

               

              BUT ... this SQL statement will not work. It'll fail with an error - "The multi-part identifier "Patch.Vulnerability_Idn" could not be bound." - that's because Vulnerability <=> Patch information is a 1:* relationship, not a 1:1 relationship. This is the sort of reason why I recommend people report against VULNERABILITIES and not patches, as the latter is a right pain in the neck and not as helpful to be honest.

               

              So ... we need to shuffle things around a bit...

               

              <Gah - and VMWare just decided to die as I ran out of disk space ... OK - let me fix up a few things & then re-run this - at the moment, I'm thinking of using UNION here to sort this out - that strikes me as the better way to do this ... or as an alternative, using two calls to the same table ... shall save this for now & edit it later when I get some time ... >

               

              [EDIT]

              ==========

               

              And we're back in action. After mulling over the problem for a while, I figured that rather than solving the entire problem in one go, let's take it on a little step at a time.

               

              So ... I've decided to start from scratch, and use the "JOIN" operator to link tables, rather than doing the whole "AND..." clauses, as it tends to be less messy with more complicated queries. The net-effect is the same, it's just more readable I find.

              -- Step 1 => Back to basics. "Just" link the PatchHistory & Computer Table(s)
              select COMP.DeviceName, PH.Patch, PH.ActionCode, PH.Message, PH.ActionDate
              from PatchHistory PH
              -- Link up COMPUTER_IDN's between PATCHHISTORY and the COMPUTER table first
              LEFT OUTER JOIN Computer Comp (nolock) on Comp.Computer_Idn = PH.Computer_Idn 
              where PH.ActionCode=2
              

               

              which brings me back something like this:

              Patch_SQL_3.jpg

               

              OK - now, on to the next little breadcrumb - link/adding to the PATCH table:

              -- Step 2 => Linking the PatchHistory patchname to a the corresponding vulnerability entry...
              select COMP.DeviceName, PH.Patch, PH.ActionCode, PH.Message, PH.ActionDate, P.UniqueFilename, P.Vulnerability_Idn
              from PatchHistory PH
              LEFT OUTER JOIN Computer Comp (nolock) on Comp.Computer_Idn = PH.Computer_Idn 
              -- Adding the join to the 'new' PATCH table via the unique name of patches deployed
              LEFT OUTER JOIN Patch P (nolock) on P.UniqueFilename = PH.Patch
              where PH.ActionCode=2
              -- Also - adding an ÖRDER BY" clause to make the data more easily readable
              order by Comp.DeviceName, PH.Patch
              

               

              Now we get something like this back:

              Patch_SQL_4.jpg

              IMPORTANT NOTE:

              I'm not 100% certain why SQL has decided to show the "Install Flash Player" vulnerability being remediated 2x (esp. since the Action Date is quite unique). It will require some additional clean-up work that I'm not sure I'll have time for this or next week, but as long as you're aware of this, it's something you can keep an eye out for & refine.

               

              Now then - ignoring the fact that SQL decided to throw a 2x entry our way for some reason, let's move on.  Let's add the VUL_ID and SEVERITY from the VULNERABILITY table!

              -- Step 3 => Linking in the Vulnerability table to get the VUL_ID and VUL_Severity
              select COMP.DeviceName, PH.Patch, PH.ActionCode, PH.Message, PH.ActionDate, P1.UniqueFilename, P1.Vulnerability_Idn, Vul.Vul_ID, Vul.Severity
              from PatchHistory PH
              LEFT OUTER JOIN Computer Comp (nolock) on Comp.Computer_Idn = PH.Computer_Idn 
              LEFT OUTER JOIN Patch P1 (nolock) on P1.UniqueFilename = PH.Patch
              LEFT OUTER JOIN Vulnerability Vul (nolock) on Vul.Vulnerability_Idn = P1.Vulnerability_Idn
              where PH.ActionCode=2
              order by Comp.DeviceName, PH.Patch
              

               

              This nets us something like this (had to smush a few columns to fit it onto a single screen):

              Patch_SQL_5.jpg

               

              All right - nearly there ... next step is mainly clean up - deleting columns that we don't really need & re-organising the remaining columns in the way you wanted them to be:

              -- step 4 => Shuffling the column fields around in the first line & deleting 'duplicates'
              select COMP.DeviceName, Vul.Vul_ID, PH.Patch, PH.ActionCode, PH.Message, Vul.Severity, PH.ActionDate
              from PatchHistory PH
              LEFT OUTER JOIN Computer Comp (nolock) on Comp.Computer_Idn = PH.Computer_Idn 
              LEFT OUTER JOIN Patch P1 (nolock) on P1.UniqueFilename = PH.Patch
              LEFT OUTER JOIN Vulnerability Vul (nolock) on Vul.Vulnerability_Idn = P1.Vulnerability_Idn
              where PH.ActionCode=2
              order by Comp.DeviceName, PH.Patch
              

               

              That will then look like so:

              Patch_SQL_6.jpg

               

              ... which actually gets us all of the desired data, without diving into either CVDETECTED or COMPUTERVULNERABILITY tables ...

               

              The only "weird" thing left is that 2x display line which I'm not sure where that's coming from (especially since there's only 1 Flash patch with that file name) ... either way, I think this is enough to get you 95% of the way to where you want to be & you can bang out the rest with a friendly DBA (if he points out where I'm going wrong, please post his correction here - always happy to learn).

              1 of 1 people found this helpful
              • 4. Re: Query for Reporting
                talk2neeraj2k7 Apprentice

                Thank you Paul ! appreciate your prompt solution.

                 

                 

                I tried each step as demonstrated by you, like in your case blockertoolkit patch was missing, in my case I observed that patches like microsoftfixit.msi was missing from the output, as you told patches like pre-requisites etc it won't show.

                 

                To follow the steps with less amount of records is really great, it makes it easy to learn, that's why with every query I started adding one more line for (and where DeviceName = 'XYZ'), so that for one device I can manually count the records from security and patch information

                and verify whether or not my query is giving me correct no. of records.

                 

                Good info on date issues in SQL, I was trying something like this, 20160707 as well as 2016-07-07 both did not work, great tip to use '' quotes always with date and time.

                 

                In Gather patch historical information, Build report data for definitions published less than [180] days ago, by default it is 90 or 60 days I think. Can I interpret this days setting as, lets say MS published a patch MS001 in July 2014, and I applied this patch using LANDesk in July 2016, so after patching when I am going to fetch the report, it will not show me that patch in my post patch report. since I applied a patch that was published more than 180 days before. (2 year difference) and if MS published a patch MS002 in May 2016, and I apply it in July 2016, in that case in my post patch report it will show that patch (2 months difference), I am not sure, am I interpreting this days setting correctly?

                 

                Using ComputerVulnerability table made the results looks more better and accurate compare to previous CVD table that we were using. CVD table was giving me 493 records, and by using ComputerVulnerability table I got almost 6000 records, (want to see 18000+ records) (understand many factors involve there like you said pre-req patches etc) (So if not 18000 then lets say 15000) That's really weird right

                 

                Your examples and queries are really helping me to learn SQL gradually, Thanks for that.

                 

                Curious to see your query with UNION statements, I am sure that will resolve the multi-patch identifier issue and hopefully give correct no. of records.

                 

                Once again appreciate your efforts !

                 

                Thank you,

                • 5. Re: Query for Reporting
                  phoffmann SupportEmployee

                  OK - so I've edited my previous post, adding on to it the various SQL re-think steps that I've done. It (mostly) seems to do the desired job, with only one "WTF" oddity in it that doesn't make much sense to me (may need a less tired look at it with fresh eyes). It's going to be some SQL internal logic nonsense - but at least it gets most of the stuff in, in a sensible way.

                   

                  And didn't even need UNIONS or whatnot - just a simple fresh look at it & taking things on 1 little step at a time.

                   

                  That should get you most of the way to where you want to be I hope (and - worst comes to the worst, can always spit the results out into a CSV or what not, & run a script to delete such weird duplicate entries), if you can't find a friendly DBA to correct whatever SQL is doing weird based on my statement.

                   

                  Enjoy (I hope)!

                  • 6. Re: Query for Reporting
                    talk2neeraj2k7 Apprentice

                    Fantastic Paul, your modified SQL query really did what I wanted to see in my post patching report. Having following your long walk through SQL  lessons, now these SQL queries started making sense to me.

                    This last modified query is really quite simple and easy to understand compare to previous ones.

                     

                    So here is my testing of query / added few lines, to get less duplicates and to make results more relevant for me:-

                     

                    1) So first I just ran LANDesk Query to get the correct no. of records, as I have noticed that it always gives exact no. of records. (Of course it  can not show me Severity and VUL_ID column and that's where you started helping me / teaching me SQL)

                    3.PNG

                    So it shows 20674 records (Now we expect our SQL query to show same no. of records / or almost same no. of records)

                    33.PNG

                    2) Now I execute SQL query, excluded various language definitions in VUL_ID as it was unnecessary generating lots of records with same patch file. (it reduced lots of unnecessary rows in my case.)

                    1.bmp

                    So it shows 22652 records.

                    11.bmp

                    22652 - (subtract 20674) above LANDesk Query generated records in step no 1. So, the difference is 1978 records we are seeing extra. (lets try to reduce it a bit further)

                     

                    3) Here I run exact same query with just single change and that is use of (DISTINCT), you were right when you said that "may need a less tired look at it with fresh eyes" This simple addition removed many duplicates.

                    2.bmp

                    So it shows 21140 records.

                    22.bmp

                    Lets do unnecessary maths again 21140 - (subtract 20674) above LANDesk Query generated records in step no 1. So, the difference is only 466 records we are seeing extra. (It means DISTINCT removed almost 1500 duplicate records in my case)

                     

                    Now how to remove these 466 extra records, I think it's better not to do any further Phd on this minor no. of duplicate records. (it's just a minor difference, as you said I got almost what I wanted to get since starting of this thread)

                     

                    I must appreciate your time and efforts while working on this Query / Post (I know it took your significant early morning hours) Thanks a ton Paul !

                     

                    The best part while you were working on all this, you walked through each and every step, so that it can make sense for me / others.

                     

                    Hats off from community members !

                     

                    Thank you,

                    Neeraj Kumar

                    1 of 1 people found this helpful
                    • 7. Re: Query for Reporting
                      phoffmann SupportEmployee

                      Happy to help along a bit.