SCCM report applications installed on computers without Updates

By Eswar KonetiJanuary 27, 5:40 pm3 Mins Read SCCM 2007 11,816 Views

Below listed the basic reports to display computers with particular add and remove application installed on computers.

Report to list all the computers which have particular software/application installed ?

SELECT
c.Name0,
a.DisplayName0

FROM
v_GS_ADD_REMOVE_PROGRAMS a,
v_R_System c

WHERE a.ResourceID = c.ResourceID
AND a.DisplayName0 like '%Adobe acrobat%'

GROUP BY
c.Name0,
a.DisplayName0

ORDER BY c.name0

You can also add the prompt value to ask for application name as well.

Report to List all add and remove programs on Particular computer excludes software updates ?

SELECT A.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0

FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B
WHERE A.ResourceID = B.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

AND Name0 = @computer

GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
ORDER BY A.DisplayName0

Prompt for creation @ computer:

select Name0 from v_GS_COMPUTER_SYSTEM

You can also apply the same report on to collection by simply adding prompt value

select b.name0 [computer Name],COUNT(DisplayName0)[ARP Count] from v_GS_ADD_REMOVE_PROGRAMS a,v_GS_COMPUTER_SYSTEM B,v_FullCollectionMembership C
where B.ResourceID=a.ResourceID and
a.ResourceID=C.ResourceID
and A.DisplayName0 NOT LIKE 'Hotfix for %'
AND A.DisplayName0 NOT LIKE 'Security Update for %'
AND A.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND A.DisplayName0 NOT LIKE 'Update for Office %'
AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
AND A.DisplayName0 NOT LIKE 'Update for Windows %'
AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'

AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND c.CollectionID = @Collection
group by B.Name0

SELECT b.Name0,a.DisplayName0,A.InstallDate0, A.Version0,A.Publisher0
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
WHERE A.ResourceID = B.ResourceID
AND A.ResourceID = C.ResourceID

AND A.DisplayName0 NOT LIKE 'Hotfix for %'
AND A.DisplayName0 NOT LIKE 'Security Update for %'
AND A.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND A.DisplayName0 NOT LIKE 'Update for Office %'
AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
AND A.DisplayName0 NOT LIKE 'Update for Windows %'
AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'

AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND CollectionID =@Collection
GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0,B.Name0
ORDER BY b.name0

Prompt for collection:

SELECT CollectionID, Name FROM v_Collection

SCCM report to list all the computers which doesnt have particular application/software installed onto it ?

Query Report which display Computers without/doesn't have specific software in Add Remove Programs for a given collection:

Select Distinct
sys.Netbios_Name0,
sys.User_Domain0,
sys.User_Name0
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
sys.ResourceID not in (select sys.ResourceID
from
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where
DisplayName0 = @displayname)

Prompt for Displayname:

select DisplayName0 from v_Add_Remove_Programs

25 Comments

Pingback: SCCM ConfigMgr Compliance status of client for multiple software update groups | Eswar Koneti Blog
Bashir on March 25, 2015 1:17 PM

How can i create my own report to query all installed software and application on a specific collection, if you can explain it step by step it will be good, because i am new in Configuration Manager and i wanna know more about it.

thanks.

Reply
- Eswar Koneti on March 31, 2015 7:26 AM
  
  you can try something like this SQL Query:
  
  SELECT A.DisplayName0, A.Version0, Count(*) AS 'Count'
  FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
  WHERE A.ResourceID = B.ResourceID
  AND A.ResourceID = C.ResourceID
  AND A.DisplayName0 NOT LIKE 'Hotfix for %'
  AND A.DisplayName0 NOT LIKE 'Security Update for %'
  AND A.DisplayName0 NOT LIKE 'Update %'
  AND CollectionID = 'PS100012'
  GROUP BY A.DisplayName0, A.Version0
  ORDER BY A.DisplayName0
  
  Replace the collectionID
  
  Reply
levi on August 7, 2012 2:44 PM

Hello Eswar, I have a scenario where this query would be very useful. But i receive the error below

[42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘‘’. — [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘.’.

Please let me knowwhat I am doing wrong.

Thanks

SELECT Count(*) AS Count
SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0

FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

WHERE A.ResourceID = B.ResourceID
AND A.ResourceID = C.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for%’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft%’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

AND CollectionID = @collection

GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
ORDER BY A.DisplayName0
Report SQL Statement
—————————

Reply
- Eswar Koneti on August 7, 2012 3:27 PM
  
  Hi,
  the problem is with quotes.can you please replace them by removing and adding it once again as they are converted to fancy quotes by blog.
  
  Reply
heath on July 18, 2012 10:28 PM

The one you provided on July 7.

Reply
- Eswar Koneti on July 19, 2012 7:53 AM
  
  hi,
  After you create report with the given query,you would need to create Prompt for COLLID which i have not given in comments posted on jul 7th.
  please create prompt for COLLID using
  SELECT CollectionID, Name FROM v_Collection
  
  Reply
Dana Bessey on July 16, 2012 8:23 PM

Hello Eswar
RE: Let me say what i understood from ur question…do u want get rid of applications which are not approved but installed…. ?

Yes true

you can create package for uninstall and make it onto collection for the apps who are not approved….
If you have lot of applications which are not approved,you can go with task sequence applied onto one collection…

I do have a lots of apps so initially this would be a cleanup routine, in the future via packages it will be easy to maintain. I used the following SQL to create the report...

SELECT DISTINCT
RSYS.Name0 AS 'Computer',
RSYS.User_Name0 As 'Last User ID',
SF.FileName As 'File Name',
SF.FileDescription As 'File Description',
SF.FilePath As 'File Path',
SF.FileSize As 'File Size',
SF.FileVersion As 'File Version'
FROM
V_R_SYSTEM RSYS
INNER JOIN V_GS_SoftwareFile SF ON RSYS.ResourceID = SF.ResourceID
AND SF.FileDescription like '%Ad-Aware SE Professional%'
AND SF.FileDescription like '%Agatha Christie - Peril at End House%'
AND SF.FileDescription like '%ALOT Appbar%'
AND SF.FileDescription like '%Anti-phishing Domain Advisor%'
AND SF.FileDescription like '%Ask Toolbar%'
AND SF.FileDescription like '%Bejeweled 2 Deluxe%'
AND SF.FileDescription like '%Bing Bar%'
AND SF.FileDescription like '%Blasterball 3%'
AND SF.FileDescription like '%Blekko search bar%'
AND SF.FileDescription like '%Bonjour%'
AND SF.FileDescription like '%Bounce Symphony%'
AND SF.FileDescription like '%Build-a-Lot - The Elizabethan Era%'
AND SF.FileDescription like '%Cake Mania%'
AND SF.FileDescription like '%Chuzzle Deluxe%'
AND SF.FileDescription like '%Cisco Connect%'
AND SF.FileDescription like '%Cisco Systems VPN Client 5.0.01.0600%'
AND SF.FileDescription like '%Coupon Printer for Windows%'
AND SF.FileDescription like '%Dell Dock%'
AND SF.FileDescription like '%Diner Dash 2 Restaurant Rescue%'
AND SF.FileDescription like '%DivX Setup%'
AND SF.FileDescription like '%Excel Password Recovery%'
AND SF.FileDescription like '%Facebook Messenger 2.1.4554.0%'
AND SF.FileDescription like '%Farm Frenzy%'
AND SF.FileDescription like '%FATE%'
AND SF.FileDescription like '%FreeOnlineRadioPlayerRecorder Toolbar%'
AND SF.FileDescription like '%GIMP 2.6.11%'
AND SF.FileDescription like '%ICVERIFY Enterprise Edition Client%'
AND SF.FileDescription like '%Insaniquarium Deluxe%'
AND SF.FileDescription like '%InstantShare%'
AND SF.FileDescription like '%InstantShareAlert%'
AND SF.FileDescription like '%InstantShareDevices%'
AND SF.FileDescription like '%InstantShareDevicesMFC%'
AND SF.FileDescription like '%Jewel Quest II%'
AND SF.FileDescription like '%Jewel Quest Solitaire%'
AND SF.FileDescription like '%John Deere Drive Green%'
AND SF.FileDescription like '%Lernout & Hauspie TruVoice American English TTS Engine%'
AND SF.FileDescription like '%McAfee Security Scan Plus%'
AND SF.FileDescription like '%Memories Disc Creator 2.0%'
AND SF.FileDescription like '%Messenger Companion%'
AND SF.FileDescription like '%Mobile PhoneTools%'
AND SF.FileDescription like '%MSN Music Assistant%'
AND SF.FileDescription like '%Music Editor Free%'
AND SF.FileDescription like '%Musicmatch® Jukebox%'
AND SF.FileDescription like '%Monarch 7.00%'
AND SF.FileDescription like '%Monarch Report Explorer 5.0%'
AND SF.FileDescription like '%Netviewer Support%'
AND SF.FileDescription like '%NetZero Internet%'
AND SF.FileDescription like '%NOOK for PC%'
AND SF.FileDescription like '%pdfforge Toolbar v1.1.2%'
AND SF.FileDescription like '%Penguins!%'
AND SF.FileDescription like '%Plants vs. Zombies%'
AND SF.FileDescription like '%Polar Bowler%'
AND SF.FileDescription like '%Python 2.5.2%'
AND SF.FileDescription like '%RandMap%'
AND SF.FileDescription like '%Safari%'
AND SF.FileDescription like '%Search Toolbar%'
AND SF.FileDescription like '%SearchAssist%'
AND SF.FileDescription like '%ShopAtHome.com Toolbar%'
AND SF.FileDescription like '%Shutterfly Express Uploader%'
AND SF.FileDescription like '%Skype Click to Call%'
AND SF.FileDescription like '%Skype Toolbars%'
AND SF.FileDescription like '%Skype™%'
AND SF.FileDescription like '%Slingo Deluxe%'
AND SF.FileDescription like '%Spybot - Search & Destroy%'
AND SF.FileDescription like '%Spyware Doctor 7.0%'
AND SF.FileDescription like '%Switch Sound File Converter%'
AND SF.FileDescription like '%TomTom HOME 2.8.4.2596%'
AND SF.FileDescription like '%TomTom HOME Visual Studio Merge Modules%'
AND SF.FileDescription like '%Tweakui Powertoy for Windows XP%'
AND SF.FileDescription like '%URL Assistant%'
AND SF.FileDescription like '%Verizon Wireless Mobile Broadband Self Activation%'
AND SF.FileDescription like '%VIP Access SDK (1.0.1.5)%'
AND SF.FileDescription like '%Virtual Villagers - The Secret City%'
AND SF.FileDescription like '%VNC Free Edition 4.1.3%'
AND SF.FileDescription like '%VS10Runtime%'
AND SF.FileDescription like '%VS10RuntimeWin32%'
AND SF.FileDescription like '%VS10Runtimex64%'
AND SF.FileDescription like '%WavePad Sound Editor%'
AND SF.FileDescription like '%Wedding Dash%'
AND SF.FileDescription like '%Windows Live Call%'
AND SF.FileDescription like '%Windows Live Communications Platform%'
AND SF.FileDescription like '%Windows Live Essentials%'
AND SF.FileDescription like '%Windows Live Family Safety%'
AND SF.FileDescription like '%Windows Live Installer%'
AND SF.FileDescription like '%Windows Live Mail%'
AND SF.FileDescription like '%Windows Live Messenger%'
AND SF.FileDescription like '%Windows Live Messenger Companion Core%'
AND SF.FileDescription like '%Windows Live MIME IFilter%'
AND SF.FileDescription like '%Windows Live Photo Common%'
AND SF.FileDescription like '%Windows Live Photo Gallery%'
AND SF.FileDescription like '%Windows Live PIMT Platform%'
AND SF.FileDescription like '%Windows Live SOXE%'
AND SF.FileDescription like '%Windows Live SOXE Definitions%'
AND SF.FileDescription like '%Windows Live Sync%'
AND SF.FileDescription like '%Windows Live Toolbar%'
AND SF.FileDescription like '%Windows Live Upload Tool%'
AND SF.FileDescription like '%Windows Live UX Platform%'
AND SF.FileDescription like '%Windows Live UX Platform Language Pack%'
AND SF.FileDescription like '%Windows Live Writer%'
AND SF.FileDescription like '%Windows Live Writer Resources%'
AND SF.FileDescription like '%WinRAR 4.00 (32-bit)%'
AND SF.FileDescription like '%Xobni Core%'
AND SF.FileDescription like '%Yahoo! Detect%'
AND SF.FileDescription like '%Yahoo! Software Update%'
AND SF.FileDescription like '%Yahoo! Toolbar%'
AND SF.FileDescription like '%Zuma Deluxe%'

ORDER BY RSYS.Name0

The question is, is what i am trying to do feasible and how much difficulty is involved in creating the package, if i have to hand build uninstall routines for every packages, I will attempt another method.

Regards
Dana

Reply
- Eswar Koneti on July 17, 2012 6:59 AM
  
  Did you try this ? http://eskonr.com/2011/09/sccm-ts-vb-script-to-uninstall-applications/
  
  Reply
heath on July 14, 2012 5:23 PM

Thanks, but it still does not work. When I run the report I get the error message The page cannot be displayed because an internal server error has occurred.. I've also tuned off show friendly error messages.

Reply
- Eswar Koneti on July 15, 2012 7:31 AM
  
  which query are you trying for ?
  
  Reply
Dana Bessey on July 13, 2012 7:55 PM

Hello Eswar, I would like to ask a question about your post linked below pertaining to malware reporting...
http://social.technet.microsoft.com/Forums/da/configmgrgeneral/thread/884f70e9-8359-4b36-a67f-e3f8c63727fa

Let me start off by saying I am just getting my feet wet with SCCM. I developed the report as is replacing the malware with apps to my choosing seemingly I would have to apply it to a collection to get results, what I was hoping to is to be able to create an uninstall package or similar to rid computers of a list of apps that are not approved without having to manually create a package for every app. It would also be nice perhaps if each app or each machine was stepped through rather than prcessing every app uninstall and every PC at once. Suggestions?

Thanks in Advance.
Dana

Reply
- Eswar Koneti on July 14, 2012 9:48 AM
  
  Hi,
  Let me say what i understood from ur question...do u want get rid of applications which are not approved but installed.... ? you can create package for uninstall and make it onto collection for the apps who are not approved....
  If you have lot of applications which are not approved,you can go with task sequence applied onto one collection...
  
  Reply
Heath on July 6, 2012 10:04 PM

there is no such thing as A.NAME.

Reply
- Eswar Koneti on July 7, 2012 3:53 PM
  
  see below :
  
  SELECT Count(*) AS Count
  SELECT B.Name0,A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0
  
  FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
  
  WHERE A.ResourceID = B.ResourceID
  AND A.ResourceID = C.ResourceID
  AND A.DisplayName0 NOT LIKE '%Hotfix for%'
  AND A.DisplayName0 NOT LIKE '%Security Update for%'
  AND A.DisplayName0 NOT LIKE '%Update for%'
  AND A.DisplayName0 NOT LIKE '%Update for Microsoft%'
  AND A.DisplayName0 NOT LIKE '%Update for Office%'
  AND A.DisplayName0 NOT LIKE '%Update for Outlook%'
  AND A.DisplayName0 NOT LIKE '%Update for Windows%'
  AND A.DisplayName0 NOT LIKE '%Windows 2000 Hotfix%'
  AND A.DisplayName0 NOT LIKE '%Windows Server 2003 Hotfix%'
  AND A.DisplayName0 NOT LIKE '%Windows XP Hotfix%'
  AND A.DisplayName0 NOT LIKE '%Driver%'
  
  AND CollectionID = @collection
  
  GROUP BY B.Name0,A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
  ORDER BY A.DisplayName0
  
  Reply
Heath on July 6, 2012 8:09 PM

Thank you for your example. I have your query working. I would like to add the Computer Name that the software is installed on. I thought it would be as simple as adding B.NAME0, but I get the following error message. Any ideas?

"Column 'v_GS_COMPUTER_SYSTEM.Name0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

SELECT Count(*) AS Count SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0,B.Name0
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C WHERE A.ResourceID = B.ResourceID AND A.ResourceID = C.ResourceID AND A.DisplayName0 NOT LIKE '%Hotfix for%' AND A.DisplayName0 NOT LIKE '%Security Update for%' AND A.DisplayName0 NOT LIKE '%Update for%' AND A.DisplayName0 NOT LIKE '%Update for Microsoft%' AND A.DisplayName0 NOT LIKE '%Update for Office%' AND A.DisplayName0 NOT LIKE '%Update for Outlook%' AND A.DisplayName0 NOT LIKE '%Update for Windows%' AND A.DisplayName0 NOT LIKE '%Windows 2000 Hotfix%' AND A.DisplayName0 NOT LIKE '%Windows Server 2003 Hotfix%' AND A.DisplayName0 NOT LIKE '%Windows XP Hotfix%' AND A.DisplayName0 NOT LIKE '%Driver%' AND CollectionID = @collection
GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0 ORDER BY A.DisplayName0

Reply
- Eswar Koneti on July 6, 2012 9:24 PM
  
  you will have to use group by class for a.Name0.try adding a.Name0 to group by field.
  
  Reply
Hichem on July 4, 2011 2:23 PM

Hi Eswar,

Can you let me know where I get wrong?

I want to have all the machines that are Running 2 softwares.

set @DirLoc = (case when IsNULL(@DirLoc,'')='' then '%' else @DirLoc end)
set @Computer = (case when IsNULL(@Computer,'')='' then '%' else @Computer end)
set @DisplayName = (case when IsNULL(@DisplayName,'')='' then '%' else @DisplayName end)
set @DisplayName2 = (case when IsNULL(@DisplayName2,'')='' then '%' else @DisplayName2 end)

Select Distinct
sys.Name0 As Computer,
sys.Directory_Location0 As DirLoc,
sys.User_Name0 As LastLoggedOnUser,
arp.Publisher0 As Publisher,
arp.DisplayName0 As DisplayName,
arp.DisplayName0 As DisplayName2,
arp.Version0 As Version,
InstallDate0 As InstallDate

FROM v_R_System sys Left JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
arp.DisplayName0 Like @DisplayName
AND
arp.DisplayName2 Like @DisplayName2
AND
sys.Directory_Location0 Like @DirLoc
AND
Sys.name0 Like @Computer

Order By sys.name0

Thank you

Reply
- Eswar Koneti on August 8, 2011 1:42 PM
  
  did you create prompts for displayname,displaname2,dirloc and computer etc ?
  what error are you getting when creating the query ?
  
  Reply
Gokul on June 6, 2011 2:51 PM

Still Now I am getting Syntex error. Can you help me.

Reply
- Eswar Koneti on June 7, 2011 12:04 PM
  
  What error are you getting ? did u tried replaing the qutation ' from the copy report.
  can u send me the query which u have pasted into reports to eskonr@gmail.com please?
  
  Reply
Joey on April 6, 2011 6:44 AM

Hey Eswar, Still no go. Here is the error i get when i debug in sql on the server with the database is. Im sorry but i am not a sql person.

Here is what it looks like in notepad when i copy it from your site. Is it space sensitive?

*******************************************************************************************

SELECT Count(*) AS ‘#’, SELECT A.DisplayName0,A.InstallDate0 A.Version0,A.Publisher0

FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

WHERE A.ResourceID = B.ResourceID
AND A.ResourceID = C.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows 2000 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows Server 2003 Hotfix%’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’

AND CollectionID = @collection

GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
ORDER BY A.DisplayName0

*****************************************************************************8

Then when i use sql studio manager to debug it throws me the following error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '‘'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Any help would be greatly appreciated

Reply
- Eswar Koneti on April 6, 2011 11:26 AM
  
  Hi,
  I would say ,this must be a syntax Error with quotes.If you copy and paste the query from web,you will see this issue.
  Also there should be a comma after A.InstallDate0 in line 2 which is added now.
  can you try with this now ?
  
  SELECT Count(*) AS Count
  SELECT A.DisplayName0,A.InstallDate0,A.Version0,A.Publisher0
  
  FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C
  
  WHERE A.ResourceID = B.ResourceID
  AND A.ResourceID = C.ResourceID
  AND A.DisplayName0 NOT LIKE 'Hotfix for %'
  AND A.DisplayName0 NOT LIKE 'Security Update for%'
  AND A.DisplayName0 NOT LIKE 'Update for Microsoft%'
  AND A.DisplayName0 NOT LIKE 'Update for Office %'
  AND A.DisplayName0 NOT LIKE 'Update for Outlook %'
  AND A.DisplayName0 NOT LIKE 'Update for Windows %'
  AND A.DisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
  AND A.DisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'
  AND A.DisplayName0 NOT LIKE 'Windows XP Hotfix%'
  
  AND CollectionID = @collection
  
  GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
  ORDER BY A.DisplayName0
  
  Reply
Joey on March 22, 2011 4:32 AM

Hey Eswar,

This doesnt seem to be working for me? I am using SCCM 2007 R3 on Sql 2008. The error i get is below. Any help would be appreciated.

---------------------------
Report SQL Statement
---------------------------
[42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '‘'. -- [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '.'.
---------------------------
OK
---------------------------

Reply
- Eswar Koneti on March 22, 2011 1:05 PM
  
  there could be some typos with quotoes(’) in the query.could you please check it up once ? This works fine for me.
  
  Reply

SCCM report applications installed on computers without Updates

Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

SCCM report list collections with no deployments

How to migrate standalone MBAM to SCCM for bitlocker

25 Comments

Leave a ReplyCancel reply

SCCM report applications installed on computers without Updates

Report to list all the computers which have particular software/application installed ?

Report to List all add and remove programs on Particular computer excludes software updates ?

Prompt for creation @ computer:

Prompt for collection:

Prompt for Displayname:

Related Posts

Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

SCCM report list collections with no deployments

How to migrate standalone MBAM to SCCM for bitlocker

25 Comments

Leave a ReplyCancel reply