HOW TO: Hacking LANDesk Queries

Version 1

     

     

    I - Introduction

    Whilst LANDesk Management Suite 9.5 introduced a "multi-insert" option when creating LANDesk Queries, there was a way to achieve this previously as well.

     

    This particular .process was developed back for LANDesk Management Suite 8.8 but is still valid even with LANDesk Management Suite 9.6 as - at heart - the way that we store queries hasn't really changed significantly,

     

    This will require you to have direct SQL access to the database for obvious reasons.

     

    I.A - Preparation

    As a pre-caution, I would recommend that you stop/disable the "LANDesk Scheduler Service" while editing any queries with SQL so that you don't enter a query-resolution cycle with a temporarily wrong / dysfunctional query which would then go on to serve as target for software distribution tasks / policies and so on.

     

    I.B - The Mandatory Disclaimers

    Point 1 - This is a hack. Therefore this IS NOT and WILL NOT be supported by LANDesk Support (officially or at all).

    Point 2 - This is very much an advanced use case. Making use of direct SQL access to the database, this is very much not something that should be given access to without careful due & consideration.

    Point 3 - If the way we store / process queries should change significantly in the future, this is likely to break in its current form.

    Point 4 - Test your intended stuff on a dedicated test system first. "Just in case". If you break your live server somehow because testing wasn't a consideration, you'll have no one to blame but yourself.

     

    With all of that said however, I've used this technique successfully for years (back in 8.7 versions, if memory serves), and shy of a very significant change to how LANDesk stores queries, it should keep working for a good time. It's meant as a help for those awkward moments when you need to alter a query with 500+ objects and "all you want to do" is just change one operator to another type, rather than re-creating the query in its entirety.

     

    II - Query Basics and the database

    Here we will cover the relationship between how a LANDesk-query starts out in the 32-bit console, and how it gets stored in the database / what we can do with it.

     

    II.1 - Create your LANDesk query

    Let us begin with a simple LANDesk query which consists of the following text (we're checking for any device called 'PIKESPEAK' or 'Dirge'):

     

    "Computer"."Device Name" = "PIKESPEAK"
    OR "Computer"."Device Name" = "Dirge"
    
    
    
    
    

     

    Beginning_Query_Modded.JPG

     

     

    II.2 - Locating your LD-query in the DB

    Next - we'll have a look in the database to see what our LD-query looks like there. For that either of the two follow SQL statements will do the trick:

     

    select * from QUERY
    
    
    
    
    

     

    - OR (if you have a lot of queries) consider alternatively  -

     

    select * from Query where Name='{NAME_OF_YOUR_QUERY}'
    
    
    
    
    

     

    So in our example, the SQL would look like so:

    select * from Query where Name='Mistreated_Query'
    
    
    
    
    

     

     

    Obviously, amend the correct name for the query as appropriate.

     

    If you just display the entire QUERY table, you obviously need to find your relevant query.

     

    The result will then give us something like the following:

    Query_SQL_Original_Modded.JPG

     

    There are three key items of potential interest here:

     

    II.2.A - The QUERY_IDN

    This is the unique identifier by which the LD-query is referenced inside the database. You will want to make a note of the QUERY_IDN of the query you'll be manipulating.

     

    II.2.B - The NAME

    This is a simpler, human-readable reference to the query.

     

    II.2.C - The FILTER

    This is where we will be operating - this is the "simple-text" that we can directly modify in the DB-backend and then use the LDMS Console to (re-)interpret our new string and apply the relevant changes.

     

    II.2.D - The "just in case" technicality...

    Remember - if in doubt, cross-check the CONSOLEUSER_IDN (resolving it against the CONSOLEUSER-table) to make sure you're modifying the right query for the right user!

     

    II.3 - What does the FILTER look like?

    As you will be able to see, the FILTER-value for my example query is a straight BNF translation from all of the text in the LANDesk Console:

    "Computer"."Device Name" = "PIKESPEAK" OR "Computer"."Device Name" = "Dirge"

     

    III - What actually needs doing to make the query look like I want it to?

    Not as much as one might think.

     

    III.1 - Modify the FILTER

    In this case, it's a simple SQL update statement that needs to be done - plain text - that's all. It's probably easier to begin with this template:

     

    update QUERY
    set FILTER='{VALUE}'
    where QUERY_IDN={QUERY_IDENTIFIER}
    
    
    
    
    

     

    and writing out the actual FILTER-value seperately before injecting it into the template, along with the QUERY_IDN value. In our case, the QUERY_IDN value is "4" and the FILTER should look like this (for a given scenario where we want to look for devices called either "Avocado" or "Hydro"):

    "Computer"."Device Name" = "Avocado" OR "Computer"."Device Name" = "Hydro"

    This then would look as follows in SQL Enterprise Manager

    HaveRun_SQL_Against_Filter.jpg

     

    To verify - here's what the table looks like after the above update-statement, a simple "SELECT * FROM QUERY" (or a more specific based on QUERY_IDN) will help::

    NewFilterValue_Modded.jpg

     

    III.2 - Bringing it into the UI

    With the DB-side work done, we can now open up the query to examine it and make sure it gets written out fully as it should.

     

    Once opened, edit + undo an action in the 32-bit console on the query (such as adding & removing a "Space" in the query name), so that the changes are saved & written out properly (and most importantly - re-evaluating fields such as QUERYSQL for instance). In this screenshot, for instance, we can see that our DB-side alteration of the query has had its desired results.

     

    Final_Query_Modded.JPG

     

    III.3 - You're done

    Congratulations!

     

    IV - Additional possibilities & tables

    Whilst the UI offers plenty of possibility to configure these additional bits, I'm merely pointing out that this *CAN* be done via the DB-backend as well. "Just in case" it might become necessary for the future.

     

    IV.1 - The QUERYFIELDS table

    This table holds the columns that a query will display. Generally speaking, the UI is of good use here, but "it can be done" via SQL injection.

     

    The general format of the table is as follows:

    A - QUERYFIELDS_IDN - Unique Identifier for each row / primary key

    B - QUERY_IDN - Identifier for which Query the relevant row applies to.

    C - DispName - Display Name of the relevant name (In theory you could call "data value X" by any name you want, if it makes your life easier)

    D - BNFFIELDS - The actual data column in BNF format (same as the FILTER-field for QUERY).

    E - QUALIFIER - This is usually empty. In case you've got a "multi-value" data-set (such as "application name") you can add a qualifier (such as "7Zip") if you just want ONE of those to be displayed.

     

    IV.2 - The QUERYSORT table

    Small and simple - this table keeps track of what data column (in BNF format) the query should be sorted by. By and large, I would discourage playing with this table through SQL, as this is easy enough to do through the UI.

     

    V - Other advanced use-cases

    These steps are great if you have to "mass migrate" operators for queries. What does this mean?

     

    Well - one example covers the following situation for instance:

    I have a query with 100+ "OR"-ed individual string values. This query is currently searching for values using "LIKE" - which is very slow. Using the above steps, one could easily replace the "LIKE" with an "=" operator across the board, and remove the '%' from the search strings.


    This sort of "mass update" is easy to do as all you'd need to do is a simple SQL "replace" function. Using the same "replace" logic/need, you could even replace individual strings automatically (rather than having to find them in a massive query by hand).


    VI - FAQ and other considerations

    A catch-all category to address various items & points.

     

    VI.1 - What NOT to do

    First and foremost, please *ONLY* use functional operators that the LANDesk Query editor provides you in the GUI.

     

    Whilst SQL itself can cope just fine with operators such as "IN" or functions such as "COUNT", and IF...THEN... clauses, the Console will (at best) crash or worse if it runs into unexpected operators that are not implemented.

     

    Remember, the LANDesk Queries are essentially "SQL made easy" ... if you need to perform a highly complicated SQL operation, please use SQL Enterprise Manager for it.

     

    Please respect the GUI-given functions as information and limitation for what you can do / will be allowed.

     

     

    VI.2 - Do I need to worry about the Inventory Service running / stop services

    Not really.

     

    The Inventory Service doesn't touch queries itself, so in that respect they're not going to clash.

     

    The only thing that might potentially be affected is the Scheduler service if it happens to be resolving Queries at the time (see the PREPARATION section above).

     

    VI.3 - Why don't we just use the export/import function for queries?

    Could it be done - absolutely!

     

    However, you can't control (as easily) where you update the strings. You can't just "replace ALL strings" with your intended values, because not only will the query export hold the BNF-format of the query (our "search text"), but also will it store the query SQL itself. And if you "simply replace" strings, there's a good chance you'll be breaking SQL along with it.

     

    So it's not that it CAN'T be done - it's more a case that the above process is more precise and less prone to cause problems.