Tag Archives: MimboloveSCCM Reports

ConfigMgr Report: Total Number of Packages Per Distribution Point

Written by Tao Yang

Today I had to create a report in ConfigMgr to list total number of packages that have been assigned to each Distribution Point. The SQL query is rather simple, a one-liner. Here’s the query:

select ServerNALPath, COUNT (PackageID) As PackageCount from v_DistributionPoint group by ServerNALPath order by PackageCount

This query works on both ConfigMgr 2007 and 2012.

ConfigMgr 2007 Report:

image

ConfigMgr 2012 Report:

image

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

Select
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

image

Site System Status Report

image

Site Components Status Since 12:00AM Report:

image

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

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

 

Prompts:

Name: BoundaryName

Prompt Text: Boundary Name

Prompt SQL Statement:

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

SCCM Report: “System Boot Time for Computers in a Specific Collection

Written by Tao Yang

This is a report I wrote last week:

“System Boot Time for Computers in a Specific Collection”.

SQL Statement:


select  distinct
v_R_System_Valid.ResourceID,
v_R_System_Valid.Netbios_Name0 AS [Computer Name],
V_GS_OPERATING_SYSTEM.LastBootUpTime0 AS [Last Boot Time],
v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site.SiteCode as [SMS Site Code]
from v_R_System_Valid
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)
left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
Where v_FullCollectionMembership.CollectionID = @CollectionID
Order by v_R_System_Valid.Netbios_Name0

 

Prompts:

Name: CollectionID

Prompt Text: Collection

Prompt SQL Statement:

begin
if (@__filterwildcard = '')
select v_Collection.CollectionID, v_Collection.Name from v_Collection order by v_Collection.Name
else
select v_Collection.CollectionID, v_Collection.Name from v_Collection
WHERE v_Collection.CollectionID like @__filterwildcard
order by v_Collection.Name
end

Please note the system boot time is collected from LastBootUpTime in Win32_OperatingSystem via SCCM client hardware inventory. Therefore the information is as current as SCCM client’s last hardware inventory.

Example:

image