6 Replies Latest reply on Apr 29, 2008 3:27 PM by JDell

    Inventory Query issues



      So here is the problem



      I am building a query that will show me all systems that have vulnerabilities but I want to exclude microsoft definitions. When I build my query I am using the following criteria for vulnerabilities in general:



      Security and Patch Definitions.Detected = 1



      That returns all detected vulnerabilities per system and it works fine



      I then add the following criteria:



      *AND Computer.Security and Patch Definitions.Patch Required Not Like kb*



      With that addition I get "Query has no results "



      If I change that to read the following:



      *AND Computer.Security and Patch Definitions.Patch Required Like kb*



      I get all systems that have any vulnerabilities that have the letters kb in the executable name.



      So why does the LIKE but not the NOT LIKE work?









        • 1. Re: Inventory Query issues

          I should add that this is a LDMS for TVT 8.7 w/ SP5 build

          • 2. Re: Inventory Query issues
            phoffmann SupportEmployee

            The crux of this is that when you're trying to make something complex into something simple, things are bound to go awry at times. In this particular case, we're trying to make a highly complex language (SQL) appear rather simple (in the query tool).


            Problem is that this can backfire - not only based on the SQL itself, but based on your database itself (i.e. - Oracle-based DB's behave differently to SQL-based DB's depending on conditions and SQL).


            Now, the questions I'd ask are:

            - What DBMS do you use? (and what patch level)

            - How good is your SQL? / how COMFORTABLE are you with SQL?


            The reason why I'm asking the latter question is rather important. If SQL to you is like hieroglyphics, it wouldn't make sense for me to pursue a particular avenue. If - on the other hand - you are (or have access to) someone who "lives and breathes SQL" (so to speak), then there's a few options I can offer you to make things work for you. Though some of this might be regarded as hacks.


            Let's start with this.


            Paul Hoffmann

            LANDesk EMEA Technical Lead.

            • 3. Re: Inventory Query issues


              To Answer you questions:






              The DBMS is SQL 2005 - this is the customers setup



              The DBMS is the SQL that comes with LDMS 8.7 for TVT - This is the environment I am testing in






              No level of complexity or (hacking) scares me - I am looking for answers and have a nack for reverse engineer even the most complicated structures (I cut my teeth on unmanaged C code and Assembly Language years ago).  Because if the apparent limited functionality (as you said - easy of use interface) in the querying and reporting I am looking to for a way to get under the covers so that I can develop the complex but visually seemless solutions our customers need.   Thanks in Advance.



              • 4. Re: Inventory Query issues

                There's a handful of nifty tricks at http://www.droppedpackets.org/reports that might be of assistance -- nothing to the level of API assistance that you asked for in your other post, but maybe you'll gather some hints.

                • 5. Re: Inventory Query issues
                  phoffmann SupportEmployee

                  The quick rundown of stuff you'll want to look at is this.


                  1 - you'll need a SQL interface - ideally Enterprise Manager, makes life easier.

                  2 - The table you want to query is the QUERY table.

                  3 - Identify the query you're looking for is going to be done by looking for the NAME - grab the QUERY_IDN (primary identifier) and.SQL for it


                  So example SQL would be:


                  >select Query_Idn, Name, QuerySQL from QUERY where Name='My_Query_Name'


                  The QuerySQL field is the one that holds (in plain text) the SQL which is associated to this query.


                  Now - that alone is often enough to understand what's going on (reading the SQL I mean).


                  If in doubt, you can just copy this SQL and run it through Query Analyzer and play with it there to see how things need to change for you to get the results you need.


                  NOTE - you - ideally - want to understand the problem from seeing the SQL and fix it in the Query-tool if possible.


                  SUGGESTION - Keep a query "that works" as a reference, to make sure you have a positive comparison as well. That can help keeping you on the path :).


                  Now - depending on what your problem is exactly (no exact saying what it is), you may or may not be able to "fix" the logic of it through use of the query interface. If you're NOT, you still CAN edit/update the QuerySQL in the database directly.


                  ... but (and this can be a big but) if you hack the SQL in the DB-back end, and someone edits your query (and saves it), it will be overwritten again.


                  This is why it's preferable to have this sort of thing fixed through the interface if possible.


                  Hope this helps :).


                  Paul Hoffmann

                  LANDesk EMEA Technical Lead.

                  1 of 1 people found this helpful
                  • 6. Re: Inventory Query issues

                    I will give this a try thanks.