4 Replies Latest reply on Sep 4, 2013 10:52 AM by dblansky

    Scheduled report from a scheduled task.

    wmorse Apprentice

      I have a task that runs every night; it is just an inventory scan task that I run on all my laptops. The purpose it to see which laptop users actually leave their laptops overnight and which ones take them home. So that when it comes time to replace them we can deny laptops and issue desktops to those users that only take their laptops home once every 6 months or so. All I want in the report is a list of the ones that failed and which ones completed. After the task runs I can right click on devices in the task and chose view as report. I want to be able to schedule this report after the task completes so that I don't have to manually run the report each day. Does anyone know how to create such a report or a better way to figure out what laptops are connected over night?

        • 1. Re: Scheduled report from a scheduled task.
          synsa Specialist

          Hey Wmorse,

           

          What a great idea!  I'm going to investigate if there is any better ways to do this but to keep on the idea you've come up with... I would schedule the inventory scan task to occur daily at say 11pm and then schedule a report at say 1am.  Not sure if you've used the reporting tool and I'm not overly keen to get into it since I'm by no means good at it myself but creating a report uses SQL.  I've thrown together some basic SQL query that can be put into a report to give you what you want (I think).

           

          SELECT TMH.LD_Task_idn, T.Task_Name,  TMH.Computer_idn, C.DeviceName, TMH.MAC_STATUS, TMH.Status_Time, TMH.RetCode_Message, C.PrimaryOwner, C.ComputerLocation

          FROM LD_Task_Machine_History TMH

          INNER JOIN Computer C ON TMH.Computer_idn = C.Computer_idn

          JOIN LD_TASK T ON TMH.LD_Task_idn = T.LD_Task_idn

          WHERE TMH.LD_Task_idn = N'13006' AND TMH.MAC_STATUS = N'2'

          ORDER BY C.DeviceName

           

          There "where" has the task ID available by right-click > info on the scheduled task.

          I gathered by looking at the tasks I've completed that MAC_STATUS = 2 means Done (I think MAC_Retcode could also be used instead of MAC_STATUS for a more accurate result.  By the looks of it for this one the done & successful return is 229965824).

           

          I didn't test this on a re-occuring task but there may be a need to add in something to take the latest "status_time" for each devicename.

           

          I'm not exactly sure how you would collate the history of this report for a 6 monthly review yet though.  Haven't come up with anything other than lots of excel.

           

          If you get stuck on the creating the report part let me know and I'll attempt to explain but usually I just copy and edit an existing one and edit to my requirements.

          • 2. Re: Scheduled report from a scheduled task.
            wmorse Apprentice

            I have never used the reporting tool, Any quick easy 123.... steps you could suggest to get this report built?

            • 3. Re: Scheduled report from a scheduled task.
              synsa Specialist

              Reporting tool is a big beast.  I'll post some steps when I can but will be on holidays for 2 weeks.  In the meantime I found an existing report that almost does what you want but will be very long.  "Software distribution delivery status by task".

               

              Also if you have access to the SQL DB (assuming it is SQL) then you can run the query I posted as read-only.

              1 of 1 people found this helpful
              • 4. Re: Scheduled report from a scheduled task.
                Specialist

                I'm using this query to find failed devices - I also have it filtered in the Table Filters where "Task Status != Done" (!= is not equal to) to hide the successful items.

                 

                 

                 

                Select A0.Displayname as "Computer Name",

                'Task Status' = Case

                WHEN A2.MAC_STATUS = '0' THEN 'Waiting'

                WHEN A2.MAC_STATUS = '2' THEN 'Done'

                WHEN A2.MAC_STATUS = '3' THEN 'Failed'

                WHEN A2.MAC_STATUS = '7' THEN 'Off'

                WHEN A2.MAC_STATUS = '8' THEN 'Canceled'

                WHEN A2.MAC_STATUS = '10' THEN 'Delayed'

                WHEN A2.MAC_STATUS = '11' THEN 'Unknown Status Code'

                END

                     from Computer A0

                LEFT OUTER JOIN LD_TASK_MACHINE A2 ON A0.Computer_Idn = A2.COMPUTER_IDN

                LEFT OUTER JOIN LD_TASK A1 ON A1.LD_TASK_IDN = A2.LD_TASK_IDN

                LEFT OUTER JOIN TCP A3 ON A0.Computer_Idn = A3.Computer_Idn

                WHERE A1.TASK_NAME = 'Your_Task_Name_Here'

                order by [Task Status]