4 Replies Latest reply on Apr 13, 2017 5:29 AM by phoffmann

    MSSQL Query to get Asset Information

    TimR Rookie

      Hi,

       

      I am trying to figure out a way to write a SQL query to the Landesk database to provide all the information found in the dbo.Computer table along with the OS.

      Does anyone have a snippit of SQL that I can run to get this information?  The idea is for me to create a web front-end for other departments in IT to query asset inventory information from a custom web application.  I just don't know where the OS info is stored.  I would imagine there will be some joins to other tables.

       

      Any help would be appreciated.

        • 1. Re: MSSQL Query to get Asset Information
          TimR Rookie

          I think that I may have found this out.

           

          SELECT DISTINCT A0.Computer_Idn,A0.Type,A0.DeviceName,A0.Description,A0.LoginName,A0.HWLastScanDate,A0.SWLastScanDate,A0.VALastScanDate,A0.MainBoardOEM_Name,A0.SystemOEM_Name,A0.DISPLAYNAME, A0.TYPE, A1.OSTYPE  FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn ORDER BY A0.DISPLAYNAME

          • 2. Re: MSSQL Query to get Asset Information
            phoffmann SupportEmployee

            The easiest way to locate the location of data that I've come up with over the years?

             

            ==> Write a query and then use the INSPECTOR.

             

            So for instance -- you're after OS information so -- let's write a query to check "where OS NAME exists" (note that specifically OS info can vary between Windows & NIX & MAC potentially!)

            1 - OS Query.jpg

             

            Only query for the attribute you care about. Leave the default column sets (saves you clutter in a moment).

             

            2 - Then use the INSPECTOR on the query you just generated (you'll see why).

            2 - OS Query Inspect.jpg

             

            3 - Now select the SQL statement that's generated from your query (displayed by default) and throw it into the SQL Query analyser for easier readability:

            3 - OS Query Inspected.jpg

             

            4 - And here we are in the SQL Query analyser. I've cleaned up the SQL for easier readability with a few sensibly placed carriage returns and 2 comments, that's about it .

            4 - OS table located.jpg

             

            (You essentially work your way from the bottom up!)

             

             

            and - to make life easier - in a copy/paste-able form:

            SELECT DISTINCT A0.DISPLAYNAME, A0.TYPE, A1.OSTYPE  
            FROM Computer A0 (nolock) 
            -- The "A1" shorthand resolves to the OPERATING_SYSTEM table in this join
            LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn  
            -- This 'WHERE'clause is actually what you care about. So you need to resolve the "A1" shorthand
            WHERE (A1.OSTYPE IS NOT NULL)   
            ORDER BY  A0.DISPLAYNAME 
            

             

            That's all there is to it

            • 3. Re: MSSQL Query to get Asset Information
              TimR Rookie

              Phoffman,

               

              Many thanks for your post and explanation. It was very helpful and much appreciated.

              • 4. Re: MSSQL Query to get Asset Information
                phoffmann SupportEmployee

                Happy to be of some help.

                 

                Since you're dealing with 3rd party apps - you may find this useful too -- Getting Started with the MBSDK (Example Scripts Included) .

                 

                You can use either dynamically created column sets or "set" column sets and get data from the LDMS DB in the "LANDesk Query" format in XML format, which you may find useful for your purposes perhaps too .

                 

                Whilst there are A LOT of tables ... don't get intimidated by them. The actual architecture of the DB is quite clean & simple ... it's just big .