Perfect. Thanks for your help.
From: [email protected] [mailto:[email protected]] On
Behalf Of Daniel Ratliff
Sent: Tuesday, September 09, 2014 10:03 AM
To: [email protected]
Subject: [mssms] RE: Reporting question
Ah, in that case you need and AND.
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%')))
AND
(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_64 ON dbo.v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID =
v_R_System_1.ResourceID
WHERE
(dbo.v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 LIKE '%Symantec Endpoint
Protection%')))
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt
Sent: Tuesday, September 09, 2014 12:41 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Reporting question
We are in the middle of the migration unfortunately, so this is for the 2007
side.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Tuesday, September 09, 2014 9:32 AM
To: [email protected]<mailto:[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.
________________________________
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.
________________________________
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.
