SCCM Report / SQL Query – SCCM Maintenance Tasks

SCCM has a number of predefined maintenance tasks that runs either daily or weekly depending on how you configured them.  This is a SQL query that will display the status of these tasks.  Note that this query will show the status for all tasks for the past 7 days which will allow you to see the weekly tasks as well.

CASE WHEN CompletionStatus = ‘1’ THEN ‘Task failed’ ELSE ‘Task successful’ END AS ‘Status’
(NOT (LastStartTime LIKE CONVERT(DATETIME, ‘1980-01-01 00:00:00’, 102)))
AND LastCompletionTime < (getdate() -7)

If you want to show how long each task took to complete you can run the following basic query.

SELECT TaskName, TaskType, LastStartTime, LastCompletionTime, CompletionStatus, RunNow, FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 3600) AS Hours,
FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 60) – FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 3600) * 60 AS Minutes,
FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime)) – FLOOR(DATEDIFF(ss, LastStartTime, LastCompletionTime) / 60) * 60 AS TotalSeconds
FROM dbo.SQLTaskStatus

As part of a daily health check I created an SCCM report from the query that will give me a view of the success of the tasks to keep my SCCM environment healthy.  These queries will work on both SCCM 2007 and SCCM 2012

Leave a Comment

Your email address will not be published. Required fields are marked *