In CM12 dbo.v_Add_Remove_Programs is the combination of both x86 and x64 Add /
Remove programs. Whereas v_GS_INSTALLED_SOFTWARE is using AI data, which does
not always included all ARP data. You can see this with this following query
and result from my lab. Yes v_GS_INSTALLED_SOFTWARE does filter out a lot of
the Software Update entries from ARP but you will also notice that
v_GS_INSTALLED_SOFTWARE filtered out Microsoft Visio Premium too.
Declare @PC as varchar(250)
SEt @PC = 'M8'
select
ARP.DisplayName0
from
v_R_System_Valid RV
join dbo.v_Add_Remove_Programs ARP on RV.ResourceID = ARP.ResourceID
Where
RV.Netbios_Name0 = @PC
and ARP.DisplayName0 Not in
(
select
GSIS.ARPDisplayName0
from
v_R_System_Valid RV
join dbo.v_GS_INSTALLED_SOFTWARE GSIS on
RV.ResourceID = GSIS.ResourceID
Where
RV.Netbios_Name0 = @PC)
Order By
ARP.DisplayName0
[cid:[email protected]]
Garth Jones
Chief Architect
Tel: 613-627-4801 x168
Fax: 613-627-4802
www.Enhansoft.com<http://www.enhansoft.com/>
[Description: Description:
cid:[email protected]]<http://www.enhansoft.com/>
Enhancing Your Business
[Description: Description: Description: Description: Description: Description:
Description:
cid:[email protected]]<http://be.enhansoft.com/>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<https://twitter.com/enhansoft>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Enhansoft's YouTube
Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description:
Description: Description: Description: Description: Description:
cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/>
[cid:[email protected]]<http://mms.mnscug.org/>
Ask Me About Our Free SSRS
Reports<http://www.enhansoft.com/pages/Free-SSRS-Reports.aspx>
From: [email protected] [mailto:[email protected]] On
Behalf Of Daniel Ratliff
Sent: Tuesday, September 9, 2014 12:32 PM
To: [email protected]
Subject: [mssms] RE: Reporting question
Are you on 2012? Don't use v_gs_add_remove, use v_gs_installed_software, it
pulls from both.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt
Sent: Tuesday, September 09, 2014 12:11 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] Reporting question
Hi All,
Still learning reporting (getting better!) but I'm kind of stumped by this one.
Need to have a report that lists computers that don't have a program in
Add/Remove Programs both the x86 and x64 versions.
Here is my query for the x86, but I need to also check for x64 and only list
the systems that don't have either version installed.
SELECT dbo.v_R_System.ResourceID, dbo.v_R_System.ResourceType,
dbo.v_R_System.Name0, dbo.v_R_System.SMS_Unique_Identifier0,
dbo.v_R_System.Resource_Domain_OR_Workgr0,
dbo.v_R_System.Client0
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON
dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_GS_COMPUTER_SYSTEM.Name0 NOT IN
(SELECT DISTINCT v_GS_COMPUTER_SYSTEM_1.Name0
FROM dbo.v_R_System AS v_R_System_1
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM AS v_GS_COMPUTER_SYSTEM_1 ON
v_GS_COMPUTER_SYSTEM_1.ResourceID = v_R_System_1.ResourceID INNER JOIN
dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID =
v_R_System_1.ResourceID
WHERE
(dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Symantec Endpoint
Protection%')))
I tried just adding an "OR" statement, but that didn't seem to work.
SELECT dbo.v_R_System.ResourceID, dbo.v_R_System.ResourceType,
dbo.v_R_System.Name0, dbo.v_R_System.SMS_Unique_Identifier0,
dbo.v_R_System.Resource_Domain_OR_Workgr0,
dbo.v_R_System.Client0
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON
dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_GS_COMPUTER_SYSTEM.Name0 NOT IN
(SELECT DISTINCT v_GS_COMPUTER_SYSTEM_1.Name0
FROM dbo.v_R_System AS v_R_System_1
INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM AS v_GS_COMPUTER_SYSTEM_1 ON
v_GS_COMPUTER_SYSTEM_1.ResourceID = v_R_System_1.ResourceID INNER JOIN
dbo.v_GS_ADD_REMOVE_PROGRAMS ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID =
v_R_System_1.ResourceID
WHERE
(dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Symantec Endpoint
Protection%') OR
(dbo.v_GS_ADD_REMOVE_PROGRAMs_64.DisplayName0 LIKE '%Symantec Endpoint
Protection%')))
Thanks for your help!
Matt Atkinson
Client Systems Engineer
3601 Murray Blvd Ste. 175 Beaverton, OR 97005
W: 971-282-0342 C: 503-851-4620
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
