BTW the dbo.v_Add_Remove_Programs SQL view existing within CM07 too.
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: Wednesday, September 10, 2014 8:02 AM To: [email protected] Subject: [mssms] RE: Reporting question Hmm, that's interesting Garth. Thanks for pointing that out. I didn't realize we were using AI data for most of our reports. I will do some comparisons. Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Tuesday, September 09, 2014 9:59 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Reporting question 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]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Tuesday, September 9, 2014 12:32 PM 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. 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.
