Monthly Archives: February 2012

SCCM Site Systems and Components Summarizer Reports

Written by Tao Yang

I received an email today from someone who downloaded my SCCM Health Check Script 3.5. He asked me if I can help to modify the script to only display Site Systems and Components status.

I thought this can be easily achieved by creating few simple reports inside SCCM. If you are running SCCM 2007 R3 and have Reporting Service Point configured, you can publish these reports to SQL Reporting Services and create some schedules to email out daily.

So I quickly wrote 3 reports:

1. Site Status Overview Report – A high level overview of site status

2. Site System Status Report – Provides same information as what shows under Site System Status in SCCM console.

3. Site Component Status Since 12:00AM Report – Provides same information as what shows under Component Status in SCCM console (assuming the Threshold period setting under Component Status Summarizer setting is left as default of ‘Since 12:00:00 AM’)

Below are the SQL queries for each report:

Site Status Overview Report

SiteStatus.SiteCode, SiteInfo.SiteName, SiteStatus.Updated 'Time Stamp',
Case SiteStatus.Status
When 0 Then 'OK'
When 1 Then 'Warning'
When 2 Then 'Critical'
Else ' '
End AS 'Site Status',
Case SiteInfo.Status
When 1 Then 'Active'
When 2 Then 'Pending'
When 3 Then 'Failed'
When 4 Then 'Deleted'
When 5 Then 'Upgrade'
Else ' '
END AS 'Site State'
From V_SummarizerSiteStatus SiteStatus Join v_Site SiteInfo on SiteStatus.SiteCode = SiteInfo.SiteCode
Order By SiteCode

Site System Status Report

SELECT distinct
Case v_SiteSystemSummarizer.Status
When 0 Then 'OK'
When 1 Then 'Warning'
When 2 Then 'Critical'
Else ' '
End As 'Status',
SiteCode 'Site Code',
SUBSTRING(SiteSystem, CHARINDEX('\\', SiteSystem) + 2, CHARINDEX('"]', SiteSystem) - CHARINDEX('\\', SiteSystem) - 3 ) AS 'Site System',
REPLACE(Role, 'SMS', 'ConfigMgr') 'Role',
SUBSTRING(SiteObject, CHARINDEX('Display=', SiteObject) + 8, CHARINDEX('"]', SiteObject) - CHARINDEX('Display=',SiteObject) - 9) AS 'Storage Object',
Case ObjectType
When 0 Then 'Directory'
When 1 Then 'SQL Database'
When 2 Then 'SQL Transaction Log'
Else ' '
END AS 'Object Type',
CAST(BytesTotal/1024 AS VARCHAR(49)) + 'MB' 'Total',
CAST(BytesFree/1024 AS VARCHAR(49)) + 'MB' 'Free',
CASE PercentFree
When -1 Then 'Unknown'
When -2 Then 'Automatically grow'
ELSE CAST(PercentFree AS VARCHAR(49)) + '%'
END AS '%Free'
FROM v_SiteSystemSummarizer
Order By 'Storage Object'

Site Component Status Since 12:00AM Report:

SELECT distinct
Case v_ComponentSummarizer.Status
When 0 Then 'OK'
When 1 Then 'Warning'
When 2 Then 'Critical'
Else ' '
End As 'Status',
SiteCode 'Site Code',
MachineName 'Site System',
ComponentName 'Component',
Case v_componentSummarizer.State
When 0 Then 'Stopped'
When 1 Then 'Started'
When 2 Then 'Paused'
When 3 Then 'Installing'
When 4 Then 'Re-Installing'
When 5 Then 'De-Installing'
Else ' '
END AS 'Thread State',
Errors 'Errors',
Warnings 'Warnings',
Infos 'Information',
Case v_componentSummarizer.Type
When 0 Then 'Autostarting'
When 1 Then 'Scheduled'
When 2 Then 'Manual'
ELSE ' '
END AS 'Startup Type',
CASE AvailabilityState
When 0 Then 'Online'
When 3 Then 'Offline'
ELSE ' '
END AS 'Availability State',
NextScheduledTime 'Next Scheduled',
LastStarted 'Last Started',
LastContacted 'Last Status Message',
LastHeartbeat 'Last Heartbeat',
HeartbeatInterval 'Heartbeat Interval',
ComponentType 'Type'
from v_ComponentSummarizer
Where TallyInterval = '0001128000100008'
Order By ComponentName

Report Sample Screenshots:

Site Status Overview Report


Site System Status Report


Site Components Status Since 12:00AM Report:


I’ve exported these reports into a .mof file, which can be downloaded HERE.

PowerShell Script: Remove All Packages From A SCCM Distribution Point

Written by Tao Yang

Often, SCCM administrators found packages still assigned to distribution points that no longer exist. There are scripts available to remove these “orphaned” package distributions via SMS Provider. i.e. This one called DPClean.vbs from TechNet Blog: Removing a retired DP from all your packages. It was written for SMS 2003.

I’m not sure if SMS 2003 works differently when deleting package distribution via SMS Provider as I don’t have a SMS 2003 environment around that I can test. But, this script may not work in a multi-tiered SCCM environment (multiple primary sites below a central site). This script only tries to remove package distributions from the site where the user entered.

Use my test environment at home as an example to explain the issue with this script in SCCM 2007:

I have a central site (Site Code: CEN, Site Server: ConfigMgr00), a primary site (Site Code: TAO, Site Server: ConfigMgr01) and a secondary site (Site Code: S01, Site Server; ConfigMgr02) reporting to the primary site TAO.

I created a package called “Configure Windows Firewall Service” on my central site CEN. The Package ID is CEN00013:


This package has been assigned to 2 distribution points:




Notice that there is a pad lock symbol next to ConfigMgr01. If you right click \\MGMT02\Packages$, there is a “Delete” option:


When right click CONFIGMGR01, “Delete” option is not available:


This is because even though the package was created on the central site CEN, but this package was assigned to the DP CONFIGMGR01 on the child primary site TAO.

If I get to the package on Child Primary site TAO, there is no pad lock on CONFIGMGR01 and the “Delete” option is available:


If I use the same way as DPClean.vbs (only in PowerShell this time):


I Firstly locate the package distribution from the central site CEN’s SMSProvider, then use delete() method to remove it, I get a “Generic failure” error.

Notice that the properties of the package distribution object, the SourceSite value is “TAO”. it means the package was assigned to the specific DP from site “TAO”.

Now, if I repeat above PowerShell commands on site “TAO”:


No errors returned as it was successfully deleted.

Now, on the SCCM console:





The DP was deleted on both sites.

In conclusion, no matter which method is used (either via GUI or via SMS provider), the package can only be removed from a DP on the site where it was distributed.

I am also facing the issues around how the SCCM environment is operated at work. we have around over 1000 branch DPs across multiple primary sites. These branch DPs often get decommissioned or rebuilt (not by people who manage SCCM). the people who decommission these Branch DPs do not have knowledge on how SCCM environment is setup. I would not expect them to correctly enter the SCCM site server name when running the script.

Therefore I’ve re-written the script in PowerShell. The only parameter this script requires is the name of the distribution point (can be a normal DP, a DP that’s a Server Share or a Branch DP).


  • The SCCM site information are published in AD
  • Remote registry service is enabled on both management point and the site server.
  • The account that runs the script needs to have admin access to the management point and site server.
  • The account that runs the script has access to SMS provider’s WMI namespace root\sms\site_<site code>.

How the script works:

  1. Search AD for active/accessible SCCM sites
  2. Connect to the management point and site server of each site published in AD and get details of each SCCM site.
  3. Connect to the SMS provider of each discovered primary site and search for the distribution point.
  4. If the distribution point is found, connect the SMS provider of the primary site where the DP belongs to and get a list of all packages that are assigned to this DP. The list of packages assigned to the DP is displayed on the PowerShell console. If nothing is found, the script ends.
  5. For each package distribution that belongs to DP’s home primary site, delete it using the delete() method.
  6. For each package distribution that belongs to other primary sites, search for the site info of that particular site from the list that obtained from step 2, and get the SMS provider server name. Then connect to the SMS provider of the source site and delete the package distribution using delete() method.
  7. details of any successful and failed deletions are displayed on the PowerShell console.
  8. Wait for 15 seconds, repeat step 3 to double check, see if there are still any packages been assigned to the DP.
  9. If there are still packages assigned to the DP, display a message on the PowerShell console with instruction and a SQL query to run against the SCCM site database to remove them from the database (*Note: deleting straight off the database is not supported by Microsoft.)

An Issue with the script:

While I was testing the script, I did find an issue (not sure if the issue is with the logics of the script or, with SCCM itself).

I ran the script to delete all packages off a DP located on my secondary site S01. at that time, there were 3 “Install_Pending” packages against this DP. there were assigned to this DP from the central site CEN. The script ran successfully, deleted all packages on this DP from each package distribution’s source site, including these 3 “Install_Pending” packages (from CEN). However, when double check again, these 3 packages still exist in S01’s primary site TAO’s database. So, the deletions have not been replicated from central site CEN to child primary TAO.

This is why I configured the script to display instructions on how to remove them from site database (unsupported way).

The script can be downloaded here: Clean-DP.PS1

*Note: This script DOES NOT remove the actual packages from the hard disks of distribution points. The script does not actually connect to the DP at all. it can run AFTER the DP is decommissioned.


Please do not hesitate to contact me if you have any issues or questions about this script.

PowerShell Script: Get SCCM Management Point server name from AD

Written by Tao Yang

I wrote this function as a part of a script that I’m working on. it searches AD for the management point server name for a particular SCCM site:

Function Get-MPFromAD ($SiteCode)
	$domains = Get-AllDomains
	Foreach ($domain in $domains)
		Try {
			$ADSysMgmtContainer = [ADSI]("LDAP://CN=System Management,CN=System," + "$($Domain.Properties.ncname[0])")
			$AdSearcher = [adsisearcher]"(&(Name=SMS-MP-$SiteCode-*)(objectClass=mSSMSManagementPoint))"
			$AdSearcher.SearchRoot = $ADSysMgmtContainer
			$ADManagementPoint = $AdSearcher.FindONE()
			$MP = $ADManagementPoint.Properties.mssmsmpname[0]
		} Catch {}

	Return $MP

Note: This function uses another function called Get-AllDomains, which I’ve blogged before here: So make sure you include BOTH functions in your script.

PowerShell Script: Calculate First and Last IP of a Subnet

Written by Tao Yang

I just wrote this script to calculate the first and last IP of a subnet based on any given IP (within the subnet) and it’s subnet mask:

Syntax: .\Get-NetworkStartEndAddress.ps1 “IP address” “Subnet Mask”

Download here: Get-NetworkStartEndAddress.ps1

SCCM Report: Site Boundaries

Written by Tao Yang

I wrote this simple report yesterday to list and search site boundaries:

Report Name: SCCM Site Boundaries

SQL Query:

SELECT distinct
v_BoundaryInfo.DisplayName AS [Boundary Name],
Case v_BoundaryInfo.BoundaryType
When 0 then 'IP Subnet'
When 1 then 'AD Site'
When 2 then 'IPV6 Prefix'
When 3 then 'IP Range'
End As 'Type',
v_BoundaryInfo.Value AS [Value],
v_BoundaryInfo.SiteCode AS [Site Code]
From v_BoundaryInfo WHERE DisplayName LIKE @BoundaryName



Name: BoundaryName

Prompt Text: Boundary Name

Prompt SQL Statement:

if (@__filterwildcard = '')
Select DisplayName from v_BoundaryInfo order by DisplayName
Select DisplayName from v_BoundaryInfo where DisplayName LIKE @__filterwildcard order by DisplayName