4 Replies Latest reply on Jul 8, 2009 2:41 AM by phoffmann

    Direct Database Queries

    Rookie
    tyanni Oct 15, 2008 6:44 AM

      Is anyone here doing direct database queries? We'd like to include some of the inventory information in our wiki (especially patch/vulnerability state), but I am not sure about the best way to accomplish this. I'd love to chat with someone about what they are pulling or get some suggestions about the best way to do this.

       

      Thanks!

      Tim

        • 1. Re: Direct Database Queries
          Employee
          puercomal Oct 15, 2008 8:04 AM (in response to tyanni)

          and how. There's a lot of stats gathered by ldms_core -- dig through its source and you'll see a bunch of direct sql queries, or you can just post its report emails to the wiki.

           

          Another way to get good sql is to use profiler to watch reports that you like being run. It's a good idea to do this on a test core without a lot of activity though.

          1 of 1 people found this helpful
          • 2. Re: Direct Database Queries
            Rookie
            tyanni Oct 15, 2008 8:11 AM (in response to puercomal)

            I hadn't thought of using the profiler - great suggestion. I'll also take a look at the tool you linked to. Anyone have some queries they'd like to post/send me?

            Thanks!

            Tim

            • 3. Re: Direct Database Queries
              Rookie
              agconmet Jul 7, 2009 3:03 PM (in response to tyanni)

              Here is a query against LANDESK 8.8 SQL Database:

               

              SELECT * FROM dbo.ComputerVulnerability WHERE Computer_Idn = ? then in a loop through each record, those where field Detected <> 1 are dropped

               

              We have build a Web Based LANDESK Patch Management web page using PERL and IIS. It may not have all of the functionality that LANDESK offers but it works and it is fast.

              • 4. Re: Direct Database Queries
                phoffmann SupportEmployee
                phoffmann Expert Jul 8, 2009 2:41 AM (in response to tyanni)

                tyanni wrote:

                 

                I hadn't thought of using the profiler - great suggestion. I'll also take a look at the tool you linked to. Anyone have some queries they'd like to post/send me?

                Thanks!

                Tim

                 

                Tim,

                 

                Most advanced use of LANDesk tends to involve some DB-querying at some point, as some reports (for instance) may require such disparate data that we've not provided a report for it, or is impossible to do as a report otherwise through the UI.

                 

                The question really is mainly what you're after here - as there's no "top 10 useful queries" (at least not that I know of), as what you're after is a highly individual task. Certainly it'd be a good idea to use Profiler and/or look at the database.

                 

                I would STRONGLY recommend using a test core server (with a clean DB) with 1-2 clients to get to grips with the basics, as you will stand much more of a chance to come to terms with things when you've got a small set of data to look at, than having a live production DB with 1000's of clients (potentially) and whatever junk they've collected to deal with.

                 

                One thing that I can give you, is a piece of SQL function that will search the entire database for a string. That is documented here:

                - http://community.landesk.com/support/docs/DOC-2901

                 

                We can try to give a few pointers for places to look at, depending on what data in particular you're keen on.

                 

                - Paul Hoffmann

                LANDesk EMEA Technical Lead