ConfigMgr Report: Total Number of Packages Per Distribution Point

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: [sourcecode language=”SQL”] select ServerNALPath, COUNT (PackageID) As PackageCount from v_DistributionPoint group by ServerNALPath order by PackageCount [/sourcecode] This query works on both ConfigMgr 2007 and 2012. ConfigMgr 2007 Report: ConfigMgr 2012 Report:

Continue reading

SCCM Site Systems and Components Summarizer Reports

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

Continue reading

SCCM Report: Site Boundaries

I wrote this simple report yesterday to list and search site boundaries: Report Name: SCCM Site Boundaries SQL Query: [sourcecode language=”SQL”] 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 [/sourcecode]   Prompts: Name: BoundaryName Prompt Text: Boundary Name Prompt SQL Statement: [sourcecode language=”SQL”] begin if (@__filterwildcard = ”) Select DisplayName from v_BoundaryInfo order by DisplayName else Select DisplayName from v_BoundaryInfo where DisplayName LIKE @__filterwildcard

Continue reading

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

This is a report I wrote last week: “System Boot Time for Computers in a Specific Collection”. SQL Statement: [sourcecode language=”SQL”] 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 [/sourcecode]   Prompts: Name: CollectionID Prompt Text: Collection Prompt SQL Statement: [sourcecode language=”SQL”] begin if (@__filterwildcard = ”) select v_Collection.CollectionID, v_Collection.Name from v_Collection order by v_Collection.Name else select v_Collection.CollectionID,

Continue reading
%d bloggers like this: