Tag Archive: SCCM 2007

PowerShell – Ping all SCCM servers

Part of daily environment health checks is to verify that all SCCM infrastructure servers are reachable on the network.  Usually you can verify this by viewing the Monitoring tab in the SCCM console, but in cases where you have multiple standalone distribution point devices you may want to simply ping them to check their availability.  This PowerShell script will help you to do this task without having to write a batch file to ping each device.

# Script to ping all SCCM servers


foreach ($Server in $ServerName) {
if (test-Connection -ComputerName $Server -Count 2 -Quiet ) {
write-Host “$Server is online ” -ForegroundColor Green
} else
{ Write-Warning “$Server is offline ”


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.

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

PowerShell – Manage SCCM Inbox counts

A good practice in SCCM is to check your Inbox folder file counts daily for any backlog that may point to a potential component problem.  This task is easily achieved through a simple PowerShell command.  The SMS_Inbox_Monitor information is found in WMI.  If you would like to get an Inbox file count you can run the following PowerShell command.

Get-WmiObject -Class Win32_PerfFormattedData_SMSINBOXMONITOR_SMSInbox -ComputerName SERVERNAME1, SERVERNAME2, SERVERNAME3 | Select-Object -Property PSComputerName, Name, FileCurrentCount

This will work for both SCCM 2007 and SCCM 2012.  I like to save this information to my SQL database so that I can keep track of the weekly counts.  Here is a sample script how to import this information into your database.

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# Import SCCM Inbox Counts
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #


$events = Get-WmiObject -Class Win32_PerfFormattedData_SMSINBOXMONITOR_SMSInbox -ComputerName SERVERNAME1, SERVERNAME2, SERVERNAME3 | Select-Object -Property PSComputerName, Name, FileCurrentCount
$connectionString = “Data Source=SCCM_SQL_SERVER;Integrated Security=true;Initial Catalog=SCCM_Support;”
$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
$bulkCopy.DestinationTableName = “SCCM_Inbox_Count”
$dt = New-Object “System.Data.DataTable”

# build the datatable
$cols = $events | get-member -MemberType NoteProperty | select -Expand Name
foreach ($col in $cols) {$null = $dt.Columns.Add($col)}

foreach ($event in $events)
$row = $dt.NewRow()
foreach ($col in $cols) { $row.Item($col) = $event.$col }

# Write to the database!

SCCM Report / SQL query – WSUS Server sync status

SCCM 2012 has a menu item in the console under the Monitoring tab that will display the Software Update Point Synchronization Status.  Use of the SQL query below to display the same information in a reporting format.

SELECT SiteCode, WSUSServerName, WSUSSourceServer, SyncCatalogVersion, LastSuccessfulSyncTime,
CASE [LastSyncState]
WHEN 6700 THEN ‘WSUS Sync Manager Error’
WHEN 6701 THEN ‘WSUS Synchronization Started’
WHEN 6702 THEN ‘WSUS Synchronization Done’
WHEN 6703 THEN ‘WSUS Synchronization Failed’
WHEN 6704 THEN ‘WSUS Synchronization In Progress Phase Synchronizing WSUS Server’
WHEN 6705 THEN ‘WSUS Synchronization In Progress Phase Synchronizing SMS Database’
WHEN 6706 THEN ‘WSUS Synchronization In Progress Phase Synchronizing Internet facing WSUS Server’
WHEN 6707 THEN ‘Content of WSUS Server is out of sync with upstream server’
WHEN 6709 THEN ‘SMS Legacy Update Synchronization started’
WHEN 6710 THEN ‘SMS Legacy Update Synchronization done’
WHEN 6711 THEN ‘SMS Legacy Update Synchronization failed’
END AS ‘Last Sync State’, LastSyncStateTime

The following query will work for SCCM 2007 systems.

SELECT uss.SiteCode, si.ServerName, si.SiteName,
uss.ContentVersion, uss.SyncTime
FROM update_syncstatus uss, v_Site si
WHERE uss.SiteCode=si.SiteCode
AND uss.contentversion < 999999