Web Report

Custom Client Health Monitoring, Part 3

Posted on December 19, 2011 Updated on February 25, 2013

This is part 3 of 3 on my custom client health monitoring reports, which I created way back in SMS 2003 and I still use today. Part 1 outlines a custom report that I call “Computers that belong to X number of monitored collections“, which shows how many collections in which a computer resides. Part 2, a drill down of the first report, will then show exactly which collections that computer is a member of, called “Collection memberships for a specific computer“. Drilling down into that report then shows a report that will show all computer records that are in the collection. This report can be particularly helpful when trying to identify a set of computers (such as all at a particular location) that are being impacted.

The code:

select Count(FCM.Name) as 'Total number of computers in collection', 
   COL.Name, COL.Comment, COL.CollectionID
 from v_FullCollectionMembership FCM, v_Collection COL
 where  ((COL.CollectionID='CollectionID1' and FCM.CollectionID='CollectionID1') or
   (COL.CollectionID='CollectionID2' and FCM.CollectionID='CollectionID2') or
   (COL.CollectionID='CollectionID3' and FCM.CollectionID='CollectionID3') or
   (COL.CollectionID='CollectionIDetc' and FCM.CollectionID='CollectionIDetc') )
 group by COL.Name, COL.CollectionID, COL.Comment
 order by 'Total number of computers in collection' DESC

Now, link this report for further drill down into the default SCCM report for computer information by

Edit the report and select the “Links” tab
Change the “Link type” to “Link to another report”
Select report “Members of a selected collection”
Create a new Target Report Prompt where Name=variable, Prompt text=Collection ID, and Column=4
Click OK and enjoy drill down ;-)

This now completes a trio of custom client health monitoring/reporting collections!

This entry was posted in ConfigMgr 07, Scripting and tagged SQL, Web Report.

Custom Client Health Monitoring, Part 2

Posted on December 12, 2011 Updated on February 25, 2013

This is part 2 of 3 on my custom client health monitoring reports, which I created way back in SMS 2003 and I still use today. Part 1 outlines a custom report that I call “Computers that belong to X number of monitored collections“, which shows how many collections in which a computer resides. Drilling down into that report will then show exactly which collections that computer is a member of. This report I call “Collection memberships for a specific computer“. The code for this report is as follows:

select distinct SYS.Name0 as 'Computer',
  SYS.User_Name0 as 'User Name',
  col.Name, col.CollectionID
from v_R_System SYS
LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
LEFT join v_Collection col on col.CollectionID=fcm.CollectionID
where SYS.Name0=@var1 AND (
  FCM.CollectionID='CollectionID1' or
  FCM.CollectionID='CollectionID2' or
  FCM.CollectionID='CollectionID3' or
  FCM.CollectionID='CollectionIDetc' )
GROUP BY SYS.Name0, SYS.User_Name0, col.name, col.collectionID
ORDER BY SYS.Name0

Now, link this report for further drill down into the default SCCM report for computer information by

Edit the report and select the “Links” tab
Change the “Link type” to “Link to another report”
Select report “Hardware – General – Computer information for a specific computer”
Create a new Target Report Prompt where Name=variable, Prompt text=Computer Name, and Column=1
Click OK and enjoy drill down ;-)

Next up: Part 3 of 3 – Monitored Collections Summary

This entry was posted in ConfigMgr 07 and tagged SQL, Web Report.

Custom Client Health Monitoring, Part 1

Posted on December 9, 2011 Updated on February 25, 2013

Something that I created way back with SMS 2003 is created a set of reports as my own homegrown monitoring for client health (note: I still use these reports today with SCCM 2007) . This is part 1 of 3.

BACKGROUND: In a typical environment, a computer can reside in many collections with many different advertisements assigned to it. Often these collections are designed with queries that will return a subselect set of systems that “do not have something installed”. For example, the query may be designed to list a set of systems that do not have Adobe Reader, so as the software installs and the PCs submit the updated inventory, those computers will then get removed from the collection upon the next scheduled update cycle.

The following report, Computers that belong to X number of monitored collections, will list PCs in the most collections. However, not all collections are subselect collections that contain computers missing software. For example, I have recurring scripts assigned to various PCs to perform various tasks that I do not want as part of the results. This code only reports on the collections that I care about. To use this, simply change the “CollectionID” with the collection that you want to report upon.

select distinct
   SYS.Name0 as 'Computer',
   SYS.User_Name0 as 'User Name',
   Count (*) as 'Number of missing installs',
   HWSCAN.LastHWScan,
   SWSCAN.LastScanDate
 from v_R_System SYS
 LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
 LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
 LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
 LEFT JOIN v_UpdateScanStatus UPSCAN on SYS.ResourceID=UPSCAN.ResourceID
 where SYS.Name0=FCM.Name AND (
   FCM.CollectionID='CollectionID1' or
   FCM.CollectionID='CollectionID2' or
   FCM.CollectionID='CollectionID3' or  
   FCM.CollectionID='CollectionIDetc' )
 GROUP BY SYS.Name0,
   SYS.User_Name0,
   HWSCAN.LastHWScan,
   SWSCAN.LastScanDate
 ORDER BY 'Number of missing installs' desc, SYS.Name0

Results can look like this:

Next up: drilling down into this report to reveal Collection memberships for a specific computer

This entry was posted in ConfigMgr 07 and tagged SQL, Web Report.

Report For “relevant” Software

Posted on January 4, 2011 Updated on March 14, 2015

This is a web report that I created to display only “relevant” software within the organization. It eliminates junk that we personally do not care about reporting (such as common software, drivers, certain manufacturers, etc.). When a computer is being imaged (or a new computer is being given to a user) to identify which software needs to be reinstalled.

Hopefully this web report SQL will be useful to anyone else!

Optionally, you can download the RDL file (from my OneDrive) for use in SQL Reporting Serivces.

Select distinct arp.DisplayName0, arp.Publisher0, Count(*) as 'Count'
FROM v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID 
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID=@CollID
and (arp.publisher0 not in (' Hewlett-Packard', '{&amp;Tahoma8}Hewlett-Packard', 'HEWLET~1|Hewlett-Packard', 'Hewlett Packard', 'Hewlett Packard Development Company L.P.', 'Hewlett Packard Development Company, L.P.', 'Hewlett Packard Development Group, L.P.', 'Hewlett Packard Development Group, LLP', 'Hewlett-Packard', 'Hewlett-Packard Co.', 'Hewlett-Packard Company', 'Hewlett-Packard Development Company, L.P.', 'HP', 'HPQ', '1E', 'Conexant', 'Conexant Systems', 'NVIDIA Corporation', 'McAfee', 'McAfee, Inc.', 'Trend Micro', 'Trend Micro Inc.', 'Trend Micro Inc. (tm)', 'Trend Micro Incorporated', 'TrendMicro','Citrix Online, a division of Citrix Systems, Inc.', 'Citrix Systems', 'Citrix Systems, Inc.', 'Broadcom', 'Broadcom Corporation','Dell', 'Dell Inc', 'Dell Inc.', 'Roxio', 'Roxio Inc.,', 'Easlman Kodak Company', 'Eastman Kodak Company', 'Eastman Kodak', 'RealNetworks', 'RealNetworks, Inc', 'RealNetworks, Inc.', 'Veetle, Inc', 'IBM', 'IBM Corporation', 'Lexmark International, Inc.',  'Lenovo', 'Lenovo Group Limited', 'Lenovo Group Limited.', 'Linksys', 'Linksys By Cisco Systems',  'Logitech', 'Logitech Inc.', 'Logitech, Inc.', 'Name of your company', 'Your Company Name',  'Kyocera Mita Corporation', 'KyoceraMita', 'Nikon',  'Canon', 'Canon Inc', 'CANON INC.',  'LeapFrog',  'Comcast',  'Comcast Cable Communications Management LLC', 'SEIKO EPSON', 'SEIKO EPSON Corp.',  'SEIKO EPSON CORPORATION',  'ATI',  'ATI Technologies',  'ATI Technologies Inc.',  'ATI Technologies, Inc.', 'Texas Instruments Inc.',  'TomTom',  'amrtomp3converter.com', 'TomTom International B.V.',  'Nero AG', 'AT&amp;T', 'AT&amp;T Inc.', 'Nortel', 'Nortel Networks', 'NOS Microsystems Ltd.', 'InstallShield Software Corp.', 'Novell', 'Novell, Inc.', 'Novell Inc', 'Novell, Inc', 'VMware, Inc.', 'Advanced Micro Devices, Inc.', 'Advanced Micro Devices Inc.', 'Apache Software Foundation', 'Garmin Ltd or its subsidiaries', 'AVG Technologies') or arp.publisher0 is null)
and arp.DisplayName0 not like '%add-in%'
and arp.DisplayName0 not like '%CPSID%'
and arp.DisplayName0 not like '%Driver%' 
and arp.DisplayName0 not like '%Downloader%' 
and arp.DisplayName0 not like '%live meeting%'
and arp.DisplayName0 not like '%malware%'
and arp.DisplayName0 not like '%Modem%' 
and arp.DisplayName0 not like '%outlook connector%'
and arp.DisplayName0 not like '%plug-in%'
and arp.DisplayName0 not like '%Shockwave%'
and arp.DisplayName0 not like '%SQL Server Native Client%'
and arp.DisplayName0 not like '%SQL%Setup%'
and arp.DisplayName0 not like '%toolbar%'
and arp.DisplayName0 not like '%Uninstall%'
and arp.DisplayName0 not like '%Update for%'
and arp.DisplayName0 not like '%Visio Viewer%'
and arp.DisplayName0 not like '%Visual C++%' 
and arp.DisplayName0 not like '%Visual Studio%Runtime' 
and arp.DisplayName0 not like '.print%' 
and arp.DisplayName0 not like '2007 Microsoft%' 
and arp.DisplayName0 not like 'AccelerometerP11%'
and arp.DisplayName0 not like 'Acrobat.com%' 
and arp.DisplayName0 not like 'Ad-Aware%'
and arp.DisplayName0 not like 'Adobe %Client%'
and arp.DisplayName0 not like 'Adobe %Language%'
and arp.DisplayName0 not like 'Adobe %Library%'
and arp.DisplayName0 not like 'Adobe %Support%'
and arp.DisplayName0 not like 'Adobe Air%' 
and arp.DisplayName0 not like 'Adobe Acrobat 8.%' 
and arp.DisplayName0 not like 'Adobe Flash%'
and arp.DisplayName0 not like 'Adobe Reader%'
and arp.DisplayName0 not like 'Adobe Setup%'
and arp.DisplayName0 not like 'Adobe WAS%'
and arp.DisplayName0 not like 'Adobe XMP%'
and arp.DisplayName0 not like 'Adobe%Color%'
and arp.DisplayName0 not like 'Adobe%common%'
and arp.DisplayName0 not like 'Adobe%Exten%'
and arp.DisplayName0 not like 'Adobe%Help%'
and arp.DisplayName0 not like 'Adobe%library%'
and arp.DisplayName0 not like 'Adobe%Photoshop%Starter%'
and arp.DisplayName0 not like 'Adobe%Security%'
and arp.DisplayName0 not like 'Adobe%support%'
and arp.DisplayName0 not like 'Adobe%Update%'
and arp.DisplayName0 not like 'Amazon%'
and arp.DisplayName0 not like 'Apple App%'
and arp.DisplayName0 not like 'Apple Software Update%' 
and arp.DisplayName0 not like 'Apple%Support%' 
and arp.DisplayName0 not like 'ATI%' 
and arp.DisplayName0 not like 'AuthenTec%' 
and arp.DisplayName0 not like 'AutoUpdate%' 
and arp.DisplayName0 not like 'Audible Download%'
and arp.DisplayName0 not like 'avast%'
and arp.DisplayName0 not like 'Bing bar%'
and arp.DisplayName0 not like 'BlackBerry Device%'
and arp.DisplayName0 not like 'BlackBery%media%'
and arp.DisplayName0 not like 'bluetooth%'
and arp.DisplayName0 not like 'Boingo%'
and arp.DisplayName0 not like 'Bonjour%' 
and arp.DisplayName0 not like 'Broadcom%' 
and arp.DisplayName0 not like 'C30e%'
and arp.DisplayName0 not like 'Calendar Printing Assist%'
and arp.DisplayName0 not like 'Canon%'
and arp.DisplayName0 not like 'Catalyst Control%' 
and arp.DisplayName0 not like 'CC%' 
and arp.DisplayName0 not like 'CheckScanner'
and arp.DisplayName0 not like 'Choice Guard%' 
and arp.DisplayName0 not like 'Cisco Clean Access%'
and arp.DisplayName0 not like 'Cisco WebEx%'
and arp.DisplayName0 not like 'Cisco%VPN%' 
and arp.DisplayName0 not like 'Citrix online%' 
and arp.DisplayName0 not like 'Citrix%Web%'
and arp.DisplayName0 not like 'Citrix%XenApp%'
and arp.DisplayName0 not like 'Compatibility Pack%'
and arp.DisplayName0 not like 'Configuration Manager%' 
and arp.DisplayName0 not like 'Connect'
and arp.DisplayName0 not like 'Coupon Printer%'
and arp.DisplayName0 not like 'D3DX10%'
and arp.DisplayName0 not like 'Desktop Authority%' 
and arp.DisplayName0 not like 'Dell %'
and arp.DisplayName0 not like 'Ding%'
and arp.DisplayName0 not like 'DirectX%'
and arp.DisplayName0 not like 'E7520EEC%'
and arp.DisplayName0 not like 'EMC SourceOne%'
and arp.DisplayName0 not like 'Epson %'
and arp.DisplayName0 not like 'Fingerprint sensor%' 
and arp.DisplayName0 not like 'getPlus%Adobe' 
and arp.DisplayName0 not like 'Google Talk%' 
and arp.DisplayName0 not like 'Google Tool%' 
and arp.DisplayName0 not like 'Google%Update%'  
and arp.DisplayName0 not like 'HighMAT%' 
and arp.DisplayName0 not like 'Hotfix%'
and arp.DisplayName0 not like 'HP %'
and arp.DisplayName0 not like 'HP Quick%' 
and arp.DisplayName0 not like 'Intel%' 
and arp.DisplayName0 not like 'Internet Explorer' 
and arp.DisplayName0 not like 'Intervideo%' 
and arp.DisplayName0 not like 'Intuit%CheckScanner' 
and arp.DisplayName0 not like 'iPass%' 
and arp.DisplayName0 not like 'J2SE%' 
and arp.DisplayName0 not like 'Java%' 
and arp.DisplayName0 not like 'Junk mail%'
and arp.DisplayName0 not like 'kuler'
and arp.DisplayName0 not like 'Macromedia Flash%' 
and arp.DisplayName0 not like 'Microsoft %APIs%' 
and arp.DisplayName0 not like 'Microsoft .NET%'
and arp.DisplayName0 not like 'Microsoft ACT%' 
and arp.DisplayName0 not like 'Microsoft Application%'
and arp.DisplayName0 not like 'Microsoft ASP.NET%'
and arp.DisplayName0 not like 'Microsoft Base Smart Card%' 
and arp.DisplayName0 not like 'Microsoft Choice%' 
and arp.DisplayName0 not like 'Microsoft Compression%' 
and arp.DisplayName0 not like 'Microsoft Default Manager%'
and arp.DisplayName0 not like 'Microsoft Easy Assist%' 
and arp.DisplayName0 not like 'Microsoft Exchange%' 
and arp.DisplayName0 not like 'Microsoft Forefront%'
and arp.DisplayName0 not like 'Microsoft Intel%'
and arp.DisplayName0 not like 'Microsoft Lync%'
and arp.DisplayName0 not like 'Microsoft Office 20%' 
and arp.DisplayName0 not like 'Microsoft Office Access%' 
and arp.DisplayName0 not like 'Microsoft Office Professional%' 
and arp.DisplayName0 not like 'Microsoft Office%2007' 
and arp.DisplayName0 not like 'Microsoft Office%2010' 
and arp.DisplayName0 not like 'Microsoft Report Viewer Redistributable%'
and arp.DisplayName0 not like 'Microsoft Save as PDF%' 
and arp.DisplayName0 not like 'Microsoft search%'
and arp.DisplayName0 not like 'Microsoft Security %'
and arp.DisplayName0 not like 'Microsoft %Service Pack%'
and arp.DisplayName0 not like 'Microsoft Silverlight%' 
and arp.DisplayName0 not like 'Microsoft Sync%' 
and arp.DisplayName0 not like 'Microsoft System Center%'
and arp.DisplayName0 not like 'Microsoft UI Engine%'
and arp.DisplayName0 not like 'Microsoft User-Mode Driver%' 
and arp.DisplayName0 not like 'Microsoft WSE%' 
and arp.DisplayName0 not like 'Microsoft Windows SDK%'
and arp.DisplayName0 not like 'Microsoft XML%' 
and arp.DisplayName0 not like 'Microsoft_vc%'
and arp.DisplayName0 not like 'Mozilla Maintenance Service' 
and arp.DisplayName0 not like 'MSN Messenger%'
and arp.DisplayName0 not like 'MSVCRT%' 
and arp.DisplayName0 not like 'MSXML%'
and arp.DisplayName0 not like 'My Web%'
and arp.DisplayName0 not like 'MyDSC%'
and arp.DisplayName0 not like 'MyFonts%'
and arp.DisplayName0 not like 'MyHeritage%'
and arp.DisplayName0 not like 'MyPublisher%'
and arp.DisplayName0 not like 'Norton%'
and arp.DisplayName0 not like 'Novell %Client%'
and arp.DisplayName0 not like 'OpenOffice.org%'
and arp.DisplayName0 not like 'Opera%'
and arp.DisplayName0 not like 'PDF Settings%'
and arp.DisplayName0 not like 'PDF settings%'
and arp.DisplayName0 not like 'Photo%RAW'
and arp.DisplayName0 not like 'Picasa%'
and arp.DisplayName0 not like 'Poker%'
and arp.DisplayName0 not like 'QuickTime%' 
and arp.DisplayName0 not like 'RDC%' 
and arp.DisplayName0 not like 'Safari'
and arp.DisplayName0 not like 'SCR3xxx Smart Card Read%'
and arp.DisplayName0 not like 'ScriptLogic%' 
and arp.DisplayName0 not like 'Segoe%' 
and arp.DisplayName0 not like 'Skype%'
and arp.DisplayName0 not like 'Sonic%' 
and arp.DisplayName0 not like 'SoundMax%' 
and arp.DisplayName0 not like 'Spelling dictionaries%'
and arp.DisplayName0 not like 'Stamps.com%'
and arp.DisplayName0 not like 'Suite Shared%'
and arp.DisplayName0 not like 'Synaptics%'
and arp.DisplayName0 not like 'Time%Zone%Update%' 
and arp.DisplayName0 not like 'tipci' 
and arp.DisplayName0 not like 'Topaz%'  
and arp.DisplayName0 not like 'Trend%' 
and arp.DisplayName0 not like 'VLC media%'
and arp.DisplayName0 not like 'Verizon%Firmware%'
and arp.DisplayName0 not like 'VPN%' 
and arp.DisplayName0 not like 'VZAccess%'
and arp.DisplayName0 not like 'Watch Football%'
and arp.DisplayName0 not like 'WebEx%' 
and arp.DisplayName0 not like 'WebFldrs XP%' 
and arp.DisplayName0 not like 'WIMGAPI%' 
and arp.DisplayName0 not like 'Windows%'
and arp.DisplayName0 not like 'Winzip%'
and arp.DisplayName0 not like 'Wireshark%'
and arp.DisplayName0 not like 'WOL Magic Packet%'
and arp.DisplayName0 not like 'WPF Toolkit%'
and arp.DisplayName0 not like 'XML Paper%'
and arp.DisplayName0 not like 'Yahoo%'
and arp.DisplayName0 not like 'Zune%'
group by arp.DisplayName0, arp.Publisher0
order by arp.DisplayName0

This entry was posted in ConfigMgr 07 and tagged SQL, Web Report.

Systems with Inventories < 14 Days

Posted on June 22, 2009 Updated on February 25, 2013

This is a little SQL web report I created that identifies systems which have not reported hardware or software inventories within the last 14 days.

SELECT DISTINCT
    SYS.Netbios_Name0,
    HWSCAN.LastHWScan,
    SWSCAN.LastScanDate
FROM v_R_System SYS
LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
WHERE fcm.CollectionID='YourCollectionID' and (
    DATEDIFF(DAY, HWSCAN.LastHWScan, GETDATE()) > 14 or
    DATEDIFF(DAY, SWSCAN.LastScanDate, GETDATE()) > 14 or
    HWSCAN.LastHWScan is null or
    SWSCAN.LastScanDate is null )
ORDER BY SYS.Netbios_Name0

Note: you’ll want to change the collection ID to be the one you need. Or modify the code to prompt for a collection would probably be even better still!

This entry was posted in ConfigMgr 07 and tagged SQL, Web Report.

Web Report for Software Updates Compliance

Posted on May 13, 2009 Updated on February 25, 2013

SCCM doesn’t have a precreated report (that I can find) that quickly shows the patch compliance for a group of systems. So I created one for myself, that I thought I’d share with every one. To implement this, create a web report as you normally would; use the following SQL statement and remember to create a prompt with the information below that!

SQL Statement

select distinct sys.Name0, ui.BulletinID as BulletinID, ui.ArticleID as ArticleID, ui.Title as Title
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_R_System sys on css.ResourceID=sys.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID
where css.Status=2 and ccm.CollectionID=@CollID
order by sys.Name0, ui.ArticleID

Prompts

Name: CollID
Prompt text: Collection ID
Provide a SQL statement:

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

This entry was posted in ConfigMgr 07 and tagged Software Updates, SQL, Web Report.

	IMRAN on Reporting Services Site Role S…
	N. Moseley on ConfigMgr migration script…
	Michael Wolf on ConfigMgr migration script…
	Archive: Easily add… on Script to Prompt for System Na…
	ConfigMgr Content So… on ConfigMgr migration script…

t3chn1ck

Nick Moseley's Day-to-Day Technical Journey

Web Report

Custom Client Health Monitoring, Part 3

Custom Client Health Monitoring, Part 2

Custom Client Health Monitoring, Part 1

Report For “relevant” Software

Systems with Inventories < 14 Days

Web Report for Software Updates Compliance