4 Replies Latest reply on Jul 27, 2009 4:16 PM by DarkmatterSFO

    Query/Report for software installed in last x days

    DarkmatterSFO Apprentice

      We want to generate a report for all software installed in the past 7 days.

       

      We have used the following query to generate a report:  Computer"."Software"."Application Suites"."Application Suite"."Install Date" > "DATEADD(day, -7, getdate())"

       

      We do get data back, but the date installed is outside of the paraments and do not seem to follow a logical pattern.

       

      Any insite would be greatly appreciated

        • 1. Re: Query/Report for software installed in last x days
          zman Master

          I believe that the format for the install date is varchar and you can't use the getdate on this type of field. There was a post on this a while back but I can't seem to do a Vulcan Mind Meld with Clearspace right now.  THis query will give you scheduled taks installin a date range but will not list software installed out side of LANDesk

           

          DECLARE @start datetime,@finish datetime
          SET @start = '2008-9-01 12:29:00'
          SET @finish = '2008-9-30 12:29:00'
          
          
          SELECT LD_TASK.TASK_NAME, LD_TASK_MACHINE.STATUS_TIME, LD_TASK_MACHINE.MAC_RETCODE
          FROM LD_TASK LD_TASK, LD_TASK_MACHINE LD_TASK_MACHINE
          WHERE LD_TASK_MACHINE.LD_TASK_IDN = LD_TASK.LD_TASK_IDN AND LD_TASK_MACHINE.MAC_RETCODE <> 1110 AND LD_TASK_MACHINE.MAC_RETCODE <> 1101 AND LD_TASK_MACHINE.MAC_RETCODE <> 1001 AND((LD_TASK_MACHINE.STATUS_TIME Is Not Null And LD_TASK_MACHINE.STATUS_TIME>= @start And LD_TASK_MACHINE.STATUS_TIME<= @finish))
          ORDER BY LD_TASK.TASK_NAME
          

           

          So here is the post. http://community.landesk.com/support/message/16302#16302 Needless to say I ran the query Jack had attached and it does not work.  Date ranges where all over the place. Also note that many vendors don't use Install Date.

          1 of 1 people found this helpful
          • 2. Re: Query/Report for software installed in last x days
            Employee

            Install Date is a string, not a Datetime value, so you can't relaibly do date-related arithmetic functions on it... that Dateadd trick is from MS SQL Server and you might be having trouble because of database engine, code page, or who knows what.

             

            This is a problem with the source data rather than LANDesk's handling of it. The vendors are responsbile for updating that field and there is no standard for how it will be formatted.

            • 3. Re: Query/Report for software installed in last x days
              DarkmatterSFO Apprentice

              Your comments, along with Jack Coates are very helpfull and give me the info I needed. Thank you so much.

              • 4. Re: Query/Report for software installed in last x days
                DarkmatterSFO Apprentice

                Thank you Jack, this info is very helpful and has answered my question. Mark