CMWT is the rebirth, or afterbirth, of DPMS, which was the son of ADWT, which was begotten by WWA.  Verily, it is so.  And sayeth thee upon they throne (while playing with my phone of course) each generation begot the next.  And whatever.  Enough under-caffeinated mumbo-jumbo.  Time to get my coffee on…

In a nutshell, CMWT or Configuration Manager Web Tools, is a web application which provides a unique customizeable interface to (long inhale…) Microsoft System Center Configuration Manager 2012 R2 SP1.  The “interface” is 99% read-only, viewing of status and information.  1% (if enabled) allows for adding and removing collection members via the browser.  It works with IE8+, Edge, Chrome, Firefox, Safari, Silk, Opera, Dolphin and anything relatively modern and HTML5-capable.  Not that I’m using HTML5, but if the browser supports HTML5 then it’s probably not a piece of shit.

Figure 1 below, shows a small example of one of the many reports is the Operating Systems Summary.  There are many others, including reports that show summary counts by model, chassis type, location, AD site, IP subnet, MS Office version, and so on.


The main focus of this rambling blog gibberish is the SCCM Custom Reports module.  Figure 2, below, shows an example of the custom reports page.  The actual T-SQL code is  provided further below…


Figure 3, below, shows the SQL table structure for “CustomReports”.  You can modify the varchar() types to nvarchar() if you prefer.  That would help with some non-English languages and special characters as well.


Figure 4 below, shows the T-SQL code for each of the reports shown in Figure 2.  You can load this SQL mess into a Query window in SQL Server Management Studio (or load via script, SQLCMD or OSQL or whatever) and populate the “CustomReports” table for convenient surfing of nerd stuff.  You may want to pre-scrub the T-SQL code below to replace “contoso\dstein” with your name, or if you’re in a goofy mood, replace them all with “seymour\hiney” or whatever.

UPDATED 7/9/2015: added column aliases and new columns to some reports to support CMWT’s “auto-link” feature.


INSERT INTO dbo.CustomReports
('50 Most Recently-Discovered Computers','Top 50 most-recently discovered computers in the SCCM site','SELECT TOP 50 dbo.v_R_System.ResourceID, dbo.v_R_System.Name0 AS ComputerName, dbo.v_R_System.AD_Site_Name0 AS ADSiteName, dbo.v_R_System.Client_Version0 AS ClientVer, dbo.v_R_System.User_Name0 AS UserName, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS Windows, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model FROM dbo.v_R_System LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID ','computers,resources,discovery','6/26/2015 1:34:00 PM','7/9/2015 12:41:00 PM','contoso\dstein','contoso\dstein')
,('Microsoft Office Version Count Summary','Microsoft Office Version Count Summary','SELECT DISTINCT DisplayName0 AS ProductName, COUNT(*) AS QTY FROM (SELECT DISTINCT dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID, dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, dbo.v_GS_ADD_REMOVE_PROGRAMS.Publisher0, dbo.v_GS_ADD_REMOVE_PROGRAMS.InstallDate0 FROM dbo.v_R_System INNER JOIN dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_R_System.ResourceID = dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID WHERE (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft Office Prof%') OR (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft Office 9%') OR (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE 'Microsoft Office 365%')) AS T1 GROUP BY DisplayName0','office,software,microsoft,applications','6/26/2015 1:46:00 PM','','contoso\dstein','')
,('Software Products by Vendor','Software Products by Vendor','SELECT DISTINCT Publisher0, COUNT(DISTINCT DisplayName0) AS Products FROM dbo.v_GS_ADD_REMOVE_PROGRAMS WHERE Publisher0 IS NOT NULL AND LTRIM(Publisher0)<>'' GROUP BY Publisher0 ','software,applications,vendors','6/24/2015 1:46:00 PM','','contoso\dstein','')
,('Top 50 Most-Installed Applications','The top 50 most common installed applications','SELECT TOP 50 T1.DisplayName0 AS ProductName, Installs FROM (SELECT DISTINCT DisplayName0,Publisher0,COUNT(DISTINCT ResourceID) AS Installs FROM dbo.v_GS_ADD_REMOVE_PROGRAMS GROUP BY DisplayName0, Publisher0) AS T1 WHERE DisplayName0 IS NOT NULL','software,applications,installations,installs','6/24/2015 1:50:00 PM','','contoso\dstein','')
,('Java Runtime Versions','Versions of Java Runtime and installation counts','SELECT DISTINCT DisplayName0, COUNT(DISTINCT ResourceID) AS QTY FROM dbo.v_GS_ADD_REMOVE_PROGRAMS WHERE DisplayName0 LIKE 'Java%' GROUP BY DisplayName0','','6/26/2015 3:46:00 PM','','contoso\dstein','')
,('Client Health Problems','Clients reporting errors and health issues','SELECT NetBiosName,AssignedSiteCode,HealthType,HealthState,HealthStateName,ErrorCode,ExtendedErrorCode,LastHealthReportDate FROM dbo.v_ClientHealthState WHERE ErrorCode <> 0','','6/29/2015 2:40:00 PM','','contoso\dstein','')
,('DHCP Server Addresses','Distinct list of DHCP server addresses pulled from managed clients','SELECT DISTINCT DHCPServer0 AS DHCP_Server, COUNT(DISTINCT ResourceID) AS Clients FROM dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE DHCPServer0 IS NOT NULL AND LTRIM(DHCPServer0)<>'' GROUP BY DHCPServer0','network,dhcp,servers,address,subnet','6/30/2015 4:38:00 PM','','contoso\dstein','')
,('Mismatched Names and Serial Numbers','Computers with differing Names and BIOS Serial Numbers','SELECT DISTINCT dbo.v_R_System.Name0 AS ComputerName, dbo.v_GS_PC_BIOS.SerialNumber0 AS SerialNum, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.v_R_System.AD_Site_Name0 AS ADSiteName, dbo.v_GS_COMPUTER_SYSTEM.UserName0 AS UserName FROM dbo.v_GS_PC_BIOS INNER JOIN dbo.v_R_System ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID AND dbo.v_GS_PC_BIOS.SerialNumber0 <> dbo.v_R_System.Name0 INNER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID','computers,devices,names,bios,serial','7/6/2015 4:14:00 PM','7/9/2015 12:47:00 PM','contoso\dstein','contoso\dstein')
,('Site Accounts','Accounts assigned to site system functions','SELECT UsageName,UserName,SiteNumber,Availability FROM dbo.vSMS_SC_AccountUsage','site,server,accounts,security,system,users','7/7/2015 2:14:00 PM','','contoso\dstein','')
,('DP Distribution Link Configurations','MP to DP site link configurations','SELECT AddressType,ItemName,UnlimitedRateForAll,DestinationType FROM dbo.vSMS_SC_Address_SDK','dp,distribution,servers,site,links,network','7/7/2015 2:18:00 PM','','contoso\dstein','')
,('ADR Summary','Automatic Deployment Rules summary report','SELECT TOP 1000 AutoDeploymentID,Name,Description,AutoDeploymentEnabled,LastRunTime FROM dbo.vSMS_AutoDeployments','updates,patching,adr,deployment,rules','7/7/2015 2:20:00 PM','','contoso\dstein','')
,('Site Maintenance Tasks - SQL Tasks','SQL maintenance tasks summary report','SELECT TaskName,TaskType,IsEnabled,NumRefreshDays,DaysOfWeek,BeginTime,LatestBeginTime,BackupLocation,DeleteOlderThan FROM dbo.vSMS_SC_SQL_Task','site,server,maintenance,tasks,sql,database','7/7/2015 2:22:00 PM','','contoso\dstein','')
,('Package Distribution Status Summary','Status of Package Distributions to DP servers','SELECT dbo.v_ContDistStatSummary.PkgID, dbo.v_Package.Name AS PackageName, dbo.v_ContDistStatSummary.LastStatusTime, dbo.v_ContDistStatSummary.TargeteddDPCount, dbo.v_ContDistStatSummary.NumberInstalled, dbo.v_ContDistStatSummary.NumberInProgress, dbo.v_ContDistStatSummary.NumberErrors FROM dbo.v_ContDistStatSummary INNER JOIN dbo.v_Package ON dbo.v_ContDistStatSummary.PkgID = dbo.v_Package.PackageID','packages,distribution,dp,content','7/9/2015 10:00:00 AM','','contoso\dstein','')

So, there you have it.  If you need more views just let me know.

When CMWT is ready, I will be posting it for free download for anyone who cares or who has suffered enough brain trauma to think it’s actually cool.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s