2 Replies Latest reply on Jun 24, 2016 11:04 AM by tpratt

    Create new query using SQL


      I am new to LDMS/ALM and want to know if it is possible for me to create a new query within LDMS using raw SQL as opposed to using the query builder within the tool. Can somebody please help me find a way? This would prove far more useful for me than the built in query builder.



        • 1. Re: Create new query using SQL
          phoffmann SupportEmployee

          Hello there & welcome to the LANDesk family .


          The quick answer to your question is "Yes / No" depending on the detail.


          So - you *CAN* use "full on SQL statements" - but you need to go through a report for it (as the report tool allows you to do that as data sources). If you want/need to do really complicated stuff, then a custom report would be the way to do so.


          The Console stuff otherwise won't allow you to do SQL-y things directly (which I'm principally a big proponent of from a security point of view). What is it you're trying to do ultimately (maybe we can help) ?


          The LD Query tool is (intentionally) a "SQL light" type approach, so that people can query for stuff with little / minimal understanding of the world of SQL.




          Couple of tips to help you out / speed you along:

          - How to find and query Custom Data via SQL

          - HOW TO: Hacking LANDesk Queries


          The latter article is mainly useful as it shows a neat little trick - the QUERY table (where we store your LD queries) has a useful column called "QUERYSQL" where we give you SQL for "the LD query you've written". That is a *GREAT* way to locate any data you're keen on.


          Just do a simple query around the inventory attribute you care about (1 line for the easiest) & save the query & check out the QUERYSQL for that query ... you see table names & data columns access. That can make life a LOT easier.


          Hope that helps a bit .


          Other stuff that may be useful to jump-start your stuff:

          - Getting Started with the MBSDK (Example Scripts Included)

          - How to mass manage Blocked Inventory Items / Blocked Unknown Items


          ... Let us know if you need more!

          1 of 1 people found this helpful
          • 2. Re: Create new query using SQL
            tpratt Apprentice

            Similar to the above reply - while not 100% what you are asking for I have a number of cases where we need the ability to construct the meat of a complex query directly in the database; and then want to be able to have this used by the simplistic Query Builder. What I've done:


            * Write whatever SQL is required in the database - and then save as a view in the database.

            * Then simply create a New Data Source (the data source is the LANDesk database itself), with a new Connection Type to the Data Source (the connection type will be the View you have created).

            * When you create this new connection type - you will also get a new Business Object of the same name.


            See screen shots below - of the Data Source and then the Business object view. These are now available in Query designer - for basic queries or filters (ex - a filter do that it can be linked to a specific Incident for example as a windows collection).  Very handy at least in our case. The only real drawback I find is that the Custom Object can't be linked to the related OOTB object so that you can "click the item in the query and launch the OOTB object.


            Not sure if this applies based on what you are trying - but it does make it possible to query any data where the base object is not available (ex - OOTH Box there are "linked Workstations" and "all Workstations" object to query - but when I wanted to do a classic "outer join" to see all workstations and the linked user if there was one I could not do this with an available object.