Answered by:
Report - Computers without specific software registered in Add Remove Programs

Question
-
Hi .
I am looking to build a report that will show all systems in a specific collection that do not have a specific program registered under the add/remove progams .
I have tried to build a quer myself but it dosent work pretty well for me since i gives me duplicatets and fulse positives .
Does anyone have a similer report created that is willing to share the sql code ?
ThanksThursday, June 4, 2009 12:52 PM
Answers
-
Display name should not be quaotes
Try this :
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 like '%Symantec EndPoint Protection%')without any additional prompt
//Eswar Koneti @ www.eskonr.com
- Proposed as answer by Ameen01 Monday, August 1, 2011 1:07 PM
- Marked as answer by Robert Marshall - MVPMVP Sunday, November 27, 2011 4:25 PM
Monday, August 1, 2011 12:42 PM
All replies
-
Hi,
This query will return all computers without .Ne Framework 3.5 SP1 installed
select distinct SMS_R_System.Name from SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft .NET Framework 3.5 SP1")
If you want to create a query you can use this step by step guide - http://agerlund.spaces.live.com/blog/cns!3A51A2B50B5C1F51!357.entry
Kent Agerlund | http://agerlund.spaces.live.com/blog/Thursday, June 4, 2009 1:14 PM -
hi,
I am getting error in SCCM 2007?
possible to help?Wednesday, August 26, 2009 12:08 PM -
What is the error?
Standardize. Simplify. Automate.Wednesday, August 26, 2009 12:24 PM -
anyway i was looking to find a quarry to return all station without the configuration manager client agents. and i thought if i replace' Microsoft .NET Framework 3.5 SP1' As shown in my friends query above i would succeed. but then then it brings the below error.
if there is any one with the quarry to return all station without the configuration manager client agents. you can help.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'SMS_R_System'.
JOhn Mukundo IT Sytem Admin TelecomWednesday, June 15, 2011 11:29 AM -
I dont think SCCM client makes its entry in Add/Remove as SMS Does not do that.
I think there is easy way to do it .
Goto collection All System (Containing all the machines) in the collection sort the machines with Client type machines not showing clients does not have client or they may have some other issue (WMI corrupt) or some other issues.
Wednesday, June 15, 2011 11:38 AM -
<form style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px" action="report.asp" enctype="application/x-www-form-urlencoded" method="post">Hi Kent,I am getting the follwing error:An error occurred when the report was run. The details are as follows:Invalid object name 'SMS_R_System'.
Error Number: -2147217865 Source: Microsoft OLE DB Provider for SQL Server Native Error: 208
This is the same query which you have provided replaced with "Symantec Endpoint Protection".
select distinct SMS_R_System.Name from SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Symantec Endpoint Protection")
Can you please tell me what I am missing...?
Many thanks,
Ameen.
Mohammad AmeenuddinMonday, August 1, 2011 10:32 AM -
select distinct SMS_R_System.Name from SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Symantec Endpoint Protection")
Can you please tell me what I am missing...?
Yes. The quoted query is WQL and is used for creating collections.
You have to use SQL in webreports.
Torsten Meringer | http://www.mssccmfaq.deMonday, August 1, 2011 10:34 AM -
Thank you very much Tor,
All I need is pull report from SCCM for computers on which Symantec Endpoint Protection is not installed.
I have created report in SCCM with the above query but no luck.
May I know how can I acheive this...?
Many thanks,
Ameen
Mohammad AmeenuddinMonday, August 1, 2011 10:54 AM -
post ur query ?
take a look at thsi blog post to list computers whcih do not have software installled onto them ?
http://eskonr.com/2010/01/sccm-report-for-applications-installled-on-computers-with-without/ and select the application from list of avilable .
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 Display name :
select DisplayName0 from v_Add_Remove_Programs
//Eswar Koneti @ www.eskonr.comMonday, August 1, 2011 11:12 AM -
Thanks Eswar,
I have removed earlier query and I have tried by pasting your query in report properties.I get an error saying must declare the scalar variable "@display name"
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)
Mohammad AmeenuddinMonday, August 1, 2011 11:45 AM -
Replace "DisplayName0 = @displayname" with "DisplayName0 like '%Symantec Endpoint Protection%'"
Torsten Meringer | http://www.mssccmfaq.deMonday, August 1, 2011 11:49 AM -
I am pasting this whole query but sql doesnt accept.Am I doing the right thing...?
********************************************************
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 like '%Symantec EndPoint Protection%'"
prompt for Display name :
select DisplayName0 from v_Add_Remove_Programs*****************************************
Many thanks,
Ameen
Mohammad AmeenuddinMonday, August 1, 2011 12:24 PM -
Display name should not be quaotes
Try this :
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 like '%Symantec EndPoint Protection%')without any additional prompt
//Eswar Koneti @ www.eskonr.com
- Proposed as answer by Ameen01 Monday, August 1, 2011 1:07 PM
- Marked as answer by Robert Marshall - MVPMVP Sunday, November 27, 2011 4:25 PM
Monday, August 1, 2011 12:42 PM -
Thank you very much Eswar,
This works.
Have a nice week.
Mohammad AmeenuddinMonday, August 1, 2011 1:08 PM -
Hi all,
How would I limit this to a prompted specific collection? Prompt for software Name, instead of useing Symantec Endpoint?
thanks,
Mark
Wednesday, November 14, 2012 7:10 PM -
This one works for SCCM version 1006, finds machines missing 'Microsoft .NET Framework 4.5.2'
Note there is another view, v_GS_ADD_REMOVE_PROGRAMS for non x64 that you might have to search also
**********************************
select
v_R_SYSTEM.ResourceID,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0
from
v_R_SYSTEM inner join v_GS_COMPUTER_SYSTEM
on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId
where
v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_SYSTEM
inner join v_GS_COMPUTER_SYSTEM
on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS_64
on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_SYSTEM.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like 'Microsoft .NET Framework 4.5.2'
)
order by Name0
Friday, September 30, 2016 2:01 PM -
This one works for SCCM version 1006, finds machines missing 'Microsoft .NET Framework 4.5.2'
Note there is another view, v_GS_ADD_REMOVE_PROGRAMS for non x64 that you might have to search also
**********************************
select
v_R_SYSTEM.ResourceID,v_R_SYSTEM.ResourceType,v_R_SYSTEM.Name0,v_R_SYSTEM.SMS_Unique_Identifier0,v_R_SYSTEM.Resource_Domain_OR_Workgr0,v_R_SYSTEM.Client0
from
v_R_SYSTEM inner join v_GS_COMPUTER_SYSTEM
on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId
where
v_GS_COMPUTER_SYSTEM.Name0 not in
(select distinct v_GS_COMPUTER_SYSTEM.Name0
from v_R_SYSTEM
inner join v_GS_COMPUTER_SYSTEM
on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_SYSTEM.ResourceId
inner join v_GS_ADD_REMOVE_PROGRAMS_64
on v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID = v_R_SYSTEM.ResourceId
where v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 like 'Microsoft .NET Framework 4.5.2'
)
order by Name0
Hi Adam, just an fyi, It is best to use resourceid instead of name in your subselect query , additionally you should use v_add_remove_programs SQL view as it will get both x86 and x64 software titles.
Garth Jones
Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx
Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased
Friday, September 30, 2016 2:52 PM