5 Replies Latest reply on Jan 14, 2011 4:06 PM by jmichno

    Software by Machine SQL Query

    Apprentice

       

      This is a tough question, but I know there are many smart people out there reading these boards:

       

       

      Trying to create a Crystal Report similar to the Add/Remove Programs by Computer report.  I need to make that report more attractive, filter out Hotfixes,etc.  This would also enable me to show specific groups of computers (ie. departments) and the cumulation of software installed.  Anyone developed the SQL query that produces this report?

       

       

        • 1. Re: Software by Machine SQL Query
          Expert

          You are right not an easy one here.... You may want to look at the SLM Group reporting for this... Just a thought.

          • 2. Re: Software by Machine SQL Query
            Apprentice

            Novar,

             

            Did you ever get this acomplished? I am looking for the same thing.

            • 3. Re: Software by Machine SQL Query
              zman Master

              I would start a new post on this subject.

              • 4. Re: Software by Machine SQL Query
                ChrisHagy Apprentice

                Select c.FieldNames, app.Fieldnames

                from Computer c

                join AppSoftwareSuites app

                on c.Computer_idn = app.Computer_idn

                 

                 

                For Fields, Look at the Computer Table and AppSoftwareSuites Table.. Most of them will have the same names, without spaces as in the Inventory Tree.

                 

                in Later Version of LDMS the Updates have moved to another Table so that shouldn't be as large of a problem, but you may still want to find things like (KB or Security Update, or Hotfix and filter them out with a Where Clause..

                 

                a good Field to use to give you an Idea of how old the Inventory Record Data is, is the SWLastScanDate which is the Column Name for "Last Software Scan Date" in the Inventory.

                 

                This will give you a Result Set like

                 

                DeviceA, ProductA, VersionA

                DeviceA, ProductB, VersionB

                DeviceA, ProductC, VersionC

                DeviceB, ProductA, VersionA

                DeviceB, ProductB, VersionB

                DeviceB, ProductC, VersionC

                DeviceC, ProductD, VersionD

                 

                you will just need to Group by DeviceName for ex. in your report so you get

                 

                DeviceA

                - ProductA, VersionA

                - ProductB, VersionB

                DeviceB

                - ProductA,VersionA

                 

                this query with a different grouping will also give you Software on Computers, so you can find out what Systems have Adobe Reader for example..

                • 5. Re: Software by Machine SQL Query
                  Apprentice

                  Hey Chris,

                   

                  I think I can select only the grouping of machines I want by doing it this way...

                   

                  Select C.DeviceName, App.Suitename, App.Version From Computer C

                  join AppSoftwareSuites app

                  on C.Computer_idn = App.Computer_idn

                  Where C.DeviceName Like 'XXXX%'

                   

                  But how can I make it show me the applications sum of the applications per my Like statement without repeating the DeviceName.

                   

                  I want to know what applications are installed for a department, for this query, I do not care about reporting DeviceName.  I simply use it to filter.