Add the v_GS_System SQL view.
Declare @CollID char(8)
Set @CollID = 'SMS00001'
SELECT
S.SystemRole0,
arp.DisplayName0,
Count(*) AS 'Count',
arp.Publisher0,
arp.Version0,
@CollID as CollectionID
FROM
dbo.v_R_System_Valid RV
join dbo.v_Add_Remove_Programs arp on RV.ResourceID = ARP.ResourceID
JOIN dbo.v_FullCollectionMembership fcm on RV.ResourceID = fcm.ResourceID
join dbo.v_GS_SYSTEM S on RV.ResourceID = S.ResourceID
WHERE
fcm.CollectionID = @CollID
GROUP BY
S.SystemRole0,
arp.DisplayName0,
arp.Publisher0,
arp.Version0
ORDER BY
S.SystemRole0,
arp.Publisher0,
arp.Version0
Garth Jones
Chief Architect
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://www.enhansoft.com/blog>[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/>
Subscribe to Enhansoft's Newsletter!<http://www.enhansoft.com/register>
From: [email protected] [mailto:[email protected]] On
Behalf Of Brian McDonald
Sent: Thursday, June 9, 2016 2:22 PM
To: [email protected]
Subject: [mssms] All SW Query Help
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
