this is what I like Select
Netbios_Name0, Caption0, ARPDisplayName0, S.InstallDate0, InstallSource0, OSComponent0, ProductVersion0, Publisher0, SoftwareCode0, Full_User_Name0, Title0, Department0 >From V_R_System VRS Left Join V_GS_INSTALLED_SOFTWARE S on S.ResourceID = VRS.ResourceID Left Join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP MG on MG.ResourceID = VRS. ResourceID Left Join V_GS_OPERATING_SYSTEM OS on Os.ResourceID = VRS.ResourceID Left Join V_R_User U on U.Unique_User_Name0 = MG.TopConsoleUser0 Where ARPDisplayName0 Like '%something%' On Thu, Jun 9, 2016 at 1:21 PM, Brian McDonald <[email protected]> wrote: > Hello all, > > > I'm trying to gather a report of all Software in Add or Remove Programs > from SCCM. I need to identify which servers or workstations have the > software installed. I understand this is going to generate numerous rows > per computer, but this is what has been asked by management. Note, we are > closer to purchasing 3rd party software to assist with the normalization of > this data. But, for now, I have to work with what I have. > > > Here is the baseline query I am using: > > > Declare @CollID char(8) > Set @CollID = 'SMS00001' > SELECT DisplayName0, Count(*) AS 'Count', Publisher0, Version0, @CollID as > CollectionID > FROM v_Add_Remove_Programs arp > JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID > WHERE fcm.CollectionID = @CollID > GROUP BY DisplayName0, Publisher0, Version0 > ORDER BY Publisher0, Version0 > > > Can someone please tell me how I can modify the above query to include the > servers or workstations that have the software installed? > > > Thanks, > > Brian > >
