BTW, Shameless self-promotion. This and a lot of other topics are covered in my new book. http://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/
From: [email protected] [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Friday, January 22, 2016 7:50 PM To: [email protected] Subject: RE: [mssms] RE: SQL Query Help Ah, that's right. Thanks Garth! Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Friday, January 22, 2016 7:23 PM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] RE: SQL Query Help That part of HW. If the view starts with v_gs_ it is from HW Inv. Sent from my iPhone On Jan 22, 2016, at 7:10 PM, Daniel Ratliff <[email protected]<mailto:[email protected]>> wrote: Is v_GS_System part of HINV or the DDR? Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Garth Jones Sent: Friday, January 22, 2016 6:02 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: SQL Query Help Keep in mind that all of these query will only show you x86 software titles. Use v_ADD_REMOVE_PROGRAMS to get both x86 and x64 SW titles. On top of that want to join in v_R_System_Valid so that you see only SW for computers that are active. Then to ensure that you only get servers only, use the SystemRole column from v_GS_SYSTEM. I removed all the "Not like" as they really increase the query time. select distinct arp.DisplayName0 as 'Application Name', OS.Caption0 AS 'Operating System', COUNT(*) AS COUNT from dbo.v_R_System_Valid RV inner join dbo.v_ADD_REMOVE_PROGRAMS as arp on RV.resourceID = arp.ResourceID INNER JOIN dbo.v_GS_OPERATING_SYSTEM as OS on RV.resourceID = OS.ResourceID Inner Join dbo.v_GS_SYSTEM S on RV.resourceID = S.ResourceID Where S.SystemRole0 = 'Server' GROUP BY arp.DisplayName0, OS.Caption0 ORDER BY arp.DisplayName0, OS.Caption0 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Lindsay, Charles Sent: Friday, January 22, 2016 5:13 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: SQL Query Help You would have to join another view into your query. In keeping with the existing "format" of the query that you sent as a starting point, it would look like this: select distinct arp.DisplayName0 as 'Application Name' ,COUNT(1) AS COUNT ,OS.Caption0 AS 'Operating System' from v_GS_ADD_REMOVE_PROGRAMS as arp INNER JOIN v_GS_OPERATING_SYSTEM as OS on OS.resourceID = arp.ResourceID WHERE arp.DisplayName0 not like 'Security Update%' AND arp.DisplayName0 not like 'Definition Update%' AND arp.DisplayName0 not like 'Hotfix%' AND arp.DisplayName0 not like 'Update for%' GROUP BY arp.DisplayName0,OS.Caption0 ORDER BY [Application Name], [Operating System] Using joins in the query statement will allow you to link relevant data from other views in the database. In this case, the "ResouceID" column is the unique key in the view that represent each unique SCCM client that has sent ARP data to the SCCM server. Joining another view that contains information about the operating system that's focused on the "ResouceID" as the unique identifier between the two views allows you pull in additional information about each SCCM client... in this case, the operating system information. If you want information for "just" servers, then you can add to the "WHERE" statement by including "AND OS.Caption0 LIKE 'Microsoft Windows Server%'": select distinct arp.DisplayName0 as 'Application Name' ,OS.Caption0 AS 'Operating System' ,COUNT(1) AS COUNT from v_GS_ADD_REMOVE_PROGRAMS arp INNER JOIN v_GS_OPERATING_SYSTEM as OS on OS.resourceID = arp.ResourceID WHERE arp.DisplayName0 not like 'Security Update%' AND arp.DisplayName0 not like 'Definition Update%' AND arp.DisplayName0 not like 'Hotfix%' AND arp.DisplayName0 not like 'Update for%' AND OS.Caption0 LIKE 'Microsoft Windows Server%' GROUP BY arp.DisplayName0,OS.Caption0 ORDER BY [Application Name], [Operating System] Of course, the addition is assuming that all of your "servers" are Microsoft Windows Servers and that your hardware inventory for SCCM is collecting that information. I'm confident that it's a "default" information item collected by the SCCM client for hardware inventory purposes, but can't say that with absolutely certainty as it's been a while since I've built an SCCM environment completely from scratch. Thanks, Charlie From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Beardsley, James Sent: Friday, January 22, 2016 4:38 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] SQL Query Help I have the following query to pull a list of all applications from all clients and the count of each installation. Management wants to know if it's possible to tell whether or not any of the applications are installed on a server OS. That's a more advanced query than I have the ability to write. What would be the best way to do that? select distinct arp.DisplayName0 as 'Application Name' ,COUNT(1) AS COUNT from v_GS_ADD_REMOVE_PROGRAMS arp WHERE arp.DisplayName0 not like 'Security Update%' AND arp.DisplayName0 not like 'Definition Update%' AND arp.DisplayName0 not like 'Hotfix%' AND arp.DisplayName0 not like 'Update for%' GROUP BY arp.DisplayName0 Any guidance would be appreciated. Thanks, James ________________________________ Confidentiality Notice: This e-mail is intended only for the addressee named above. It contains information that is privileged, confidential or otherwise protected from use and disclosure. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, or dissemination of this transmission, or taking of any action in reliance on its contents, or other use is strictly prohibited. If you have received this transmission in error, please reply to the sender listed above immediately and permanently delete this message from your inbox. Thank you for your cooperation. 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.
