Report For “relevant” Software

Posted on Updated on

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', '{&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&T', 'AT&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
Advertisements

2 thoughts on “Report For “relevant” Software

    Henry Bosch said:
    March 21, 2011 at 12:00 pm

    Hi Nick

    If only the built in had a “Exclude Junk” text box eh?

    Thanks for yours, saves me some typing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s