Sql query for jobs and tasks with the last run date for each

[greg williams]

I have been looking on the internet  forums and places for a sql query that can give me the list of all the jobs and tasks in my cmdb database and show the last time is has been run against a workstations.    I am looking to clean up all the old tasks that are not used without having to click each and every folder and task and job in my Console.   

If anyone has made a query like this one would you mind sharing. 

Thanks  Greg

[Alex Hedley]

Report of all jobs and tasks and the date last deployed https://www.symantec.com/connect/forums/report-all-jobs-and-tasks-and-date-last-deployed

[Igor Perevozchikov]

You can use default "Job/Task Status Detail" report to get results and see all required info about each Job/Task that ran.

Open SMP Console -> Reports -> All Reports -> expand "Task Server" folder -> expand "Status" folder -> find there "Job/Task Status Detail" report. In this opened report you can sort "Date Executed" column and use "Group By:" option as well. On mouse double click you can drill-down there data to see what client computers and when executed appropriate Job/Task.

Here is a resolved SQL Query from this default "Job/Task Status Detail" report

--DECLARE @Status               int
DECLARE @lastNDays            int
DECLARE @guidFilterCollection uniqueidentifier
DECLARE @guidTaskInstance     uniqueidentifier
DECLARE @strTaskName          nvarchar(1024)
DECLARE @strTaskInstanceName  nvarchar(1024)

--SET @Status = CAST('-1' AS int)
SET @lastNDays = CAST('-1' AS INT)
SET @guidFilterCollection = CASE 'eb3a1a12-e1c7-4431-b060-f0333e4e488c'
                                WHEN '%' THEN NULL
                                         ELSE CAST('eb3a1a12-e1c7-4431-b060-f0333e4e488c' AS uniqueidentifier) END
SET @guidTaskInstance     = CASE '%'
                                WHEN '%' THEN  NULL
                                         ELSE CAST('%' AS uniqueidentifier) END
SET @strTaskName = CASE '%'
                        WHEN '%' THEN NULL
                        WHEN ''  THEN NULL
                                 ELSE '%' END
SET @strTaskInstanceName = CASE '%'
                                WHEN '%' THEN NULL
                                WHEN ''  THEN NULL
                                         ELSE '%' END

EXEC[dbo].[Reports_tmTaskStatusDetail]
    @FilterCollectionGuid = @guidFilterCollection,
    @TaskInstanceGuid     = @guidTaskInstance,
    @TaskInstanceName     = @strTaskInstanceName,
    @TaskName             = @strTaskName,
    @Status               = -1,
    @LastNDays            = @lastNDays,
    @Trustees             = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{F8689B6C-3CAF-4F32-9CA8-26B43DA3793B}',
    @Culture              = 'en-US'

 

Regards,

IP.

[greg williams]

This works to a point.   maybe I am doing something wrong but when I run the sql code in the linked comment above or look at the Executed Jobs/Tasks by Filter   in Reports.   i only get tasks and jobs from two months back.   

I can look at jobs in the console and there are last run statuses from 2017.   I was hoping to see them also.  

 

[Igor Perevozchikov]

Mentioned above resolved SQL query from default "Job/Task Status Detail" report already has set parameter to show all available executed jobs or tasks per whole date/time period, even should include 2017 year.

• You mean if you open "Executed Jobs/Tasks by Filter" report in Console and will set for example value 780 in 'Last N Days' field, then it returns you info about executed tasks in 2017 year?

Probably task instance(s) execution events were purged already from database. You can check "Clean Update Task Data" task instances which were completed, double mouse click on each completed task isntance to see how much rows were processed (purged)

Open Symantec Management Console -> Settings -> All Settings -> expand "Notification Server" folder -> expand "Task Settings" folder -> click on "Cleanup Task Data" -> mouse double click on required completed task instance -> check processed rows amount.

You can check which is latest older task instance execution event is available in your SQL table "Evt_Task_Instances"

SELECT * FROM Evt_Task_Instances
ORDER BY _eventTime
ASC

 

 

[greg williams]

I looked back into our system and we are purging task data from two month and older.   The suggestions above do  work.