3 Replies Latest reply on Mar 23, 2011 8:58 AM by vanyans

    How to create missing software report

    Rookie

      (landesk ms 9 with sp2)

      We have 12 programs that need to be installed on every machine. Some machines are already installed, some partly.

      I was tasked to create a report that will show which software is missing on which machine.

      For example:

                      Adobe Reader:

                                      Machine 1

                                      Machine 2

      Flash Player

                                      Machine4

                                      Machine6

      And so on…

       

      I know the query should go something like this for report that will give me only machines that do not have Adobe Reader:

      SELECT DISTINCT

      A0.DISPLAYNAME AS "Device Name",

      A0.TYPE AS "Type",

      A1.OSTYPE AS "OS Name"

      FROM

      Computer A0 (nolock)

      LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn

      right JOIN AppSoftwareSuites A2 ON A0.Computer_Idn = A2.Computer_Idn

      WHERE

      (A0.Computer_Idn NOT IN (SELECT Computer_Idn FROM AppSoftwareSuites WHERE SUITENAME=Adobe Reader’))

      ORDER BY  A0.DISPLAYNAME

       

      Any suggestions on how to make a query that will list all the software in one report as in example above.

       

      Thank you very much.

        • 1. Re: How to create missing software report
          Rookie

          I think I figured it out. I'll try to use separate DataSets for each software I need to check and then show everything in one report. I'll create separate table for each DataSet in the report.

          If anyone has a better idea please share it

           

          Thanks

          • 2. Re: How to create missing software report
            Simon Ma Employee

            Hello,

             

            I think my report should help you. My custom report use 10 applications (but you can add as many other as you want).
            For each device and each of this 10 applications, I look if the application is installed or not.

             

            For my report I use :
                       - A Temporary table with all the application name (SuiteName) that I'm looking for
                       - 2 CURSOR

             

            The result is something like :

                  DeviceName 1
                        Application A : Installed
                        Application B : Installed
                        Application C : Not Installed
                        Application D : Not Installed
                        Application E : Installed
                        Application F : Not Installed
                        ...

             

                 DeviceName 2
                        Application A : Not Installed
                        Application B : Not Installed
                        Application C : Installed
                        Application D : Not Installed
                        Application E : Installed
                        Application F : Installed
                        ...

             

            But you can change the conception and regroup the data by application (for instance)

             

            So here is the SQL query I use for this report :

             

            --TEMPORY TABLE OF THE APPLICATION I LOOK FOR

             

                 DECLARE @AppStore table (App varchar(50))

                 insert into @AppStore (App) values ('Adobe Reader 9')
                 insert into @AppStore (App) values ('Microsoft Office Professional Plus 2007')
                 insert into @AppStore (App) values ('WinZip')
                 insert into @AppStore (App) values ('Windows Media Player 11')
                 insert into @AppStore (App) values ('Picasa')
                 insert into @AppStore (App) values ('Adobe Flash Player 10 ActiveX')
                 insert into @AppStore (App) values ('WebEx')
                 insert into @AppStore (App) values ('Google Earth')
                 insert into @AppStore (App) values ('Microsoft Office Live Meeting 2007')
                 insert into @AppStore (App) values ('Java(TM) 6 Update 11')


                 DECLARE @C varchar(50)
                 DECLARE @A varchar(50)

                 DECLARE @Result table (Computer varchar(50), Appli varchar(50), Status varchar(50))

             

            -- FIRST CURSOR FOR ALL THE COMPUTER IN THE DATABASE

                

                 DECLARE CURS_C CURSOR FOR
                      SELECT Computer_Idn FROM Computer
                 OPEN CURS_C;

                 FETCH NEXT FROM CURS_C INTO @C ;

                 WHILE @@FETCH_STATUS = 0
                 BEGIN

             

            -- SECOND CURSOR FOR ALL PREVIOUSLY DEFINED APPLICATION
                 

                 DECLARE CURS_A CURSOR FOR
                      SELECT App FROM @AppStore
                 OPEN CURS_A

                 FETCH NEXT FROM CURS_A INTO @A ;
                 WHILE @@FETCH_STATUS = 0
                 BEGIN

            -- THE RESULT

             

                 INSERT INTO @Result (Computer, Appli, Status)

                 SELECT @C,@A,
                    CASE WHEN (Select SuiteName FROM AppSoftwareSuites
                              Where SuiteName LIKE @A AND Computer_Idn= @C ) IS NULL
                              THEN 'Not installed'
                              ELSE 'Installed'
                    END
             

                 FETCH NEXT FROM CURS_A INTO @A ;
                 END

                 CLOSE CURS_A;
                 DEALLOCATE CURS_A;

                 FETCH NEXT FROM CURS_C INTO @C ;

                 END
                

                 CLOSE CURS_C;
                 DEALLOCATE CURS_C;

             

                 select C.Devicename, R.Appli, R.Status
                 from @Result R
                 inner join Computer C on C.Computer_Idn = R.Computer

            • 3. Re: How to create missing software report
              Rookie

              Thanks a lot! This is exactly what I was looking for!