Tag Archive: maintenance task

SCCM Collection Query – Duplicate host names

SCCM sometimes creates duplicate host name records.  When this happens you will see 2 or more records in your SCCM console showing the same host name or computer name, but with different GUID’s and different resource id’s.  In most cases one record will show as being an active client while the other shows it is not a client.  It is usually a safe bet to remove the non-client record, but be sure to check both records for the creation date as that may help to decide which one is the correct one to keep.

This collection query will show you the duplicate host name records.

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r   full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId   full join SMS_R_System as s2 on s2.Name = s1.Name   where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId

I use this collection as a maintenance collection so that I can check for duplicate host names daily.  If you don’t remove the duplicate SCCM does not know which record should receive policies.  Once a single instance remains you will see that SCCM downloads policies to it.

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.

SELECT
TaskName,
LastStartTime,
LastCompletionTime,
CASE WHEN CompletionStatus = ‘1’ THEN ‘Task failed’ ELSE ‘Task successful’ END AS ‘Status’
FROM
dbo.SQLTaskStatus
WHERE
(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