Change built in report "Computers with specific software registered in add remove programs" to show computers without the software

Question

I want to edit a canned report that gives me every computer in a collection with a specific software to give me every computer in a collection WITHOUT a specific software.

How do I do this?

This is the report I want to edit:

This query will give me EVERY computer in configuration manager without this software, but I don't know how to make it do it in a specific collection which is what I need so was hoping I could get some help

Select Distinct
sys.Netbios_Name0,
sys.User_Domain0,
sys.User_Name0
FROM
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
sys.ResourceID not in (select sys.ResourceID
from
v_R_System sys
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
where
DisplayName0 like '%Lightspeed Filter Agent%')

Below is the query of the canned report which lets me enter in the collection as well as a software and version. Was hoping to combine the above query into this one and make it work, but couldn't figure it out.

Select DISTINCT sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0, arp.Version0 
FROM fn_rbac_R_System(@UserSIDs)  sys 
JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID  
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID 
WHERE dbo.fn_RemoveNonPrintableChars(arp.DisplayName0) like dbo.fn_RemoveNonPrintableChars(@filterwildcard) and fcm.CollectionID=@CollID and arp.Version0 like '%' + @Version + '%'

Any idea on how to do this?

Answer 1

This article will help you. https://www.recastsoftware.com/resources/how-to-create-a-collection-prompt-query/

Answer 2

Hi, @chris

Thank you for posting in Microsoft Q&A forum.

Here is the query that you can filter by the collection ID. Replace the CollectionID with your collection ID.

Select Distinct

sys.Netbios_Name0,

sys.User_Domain0,

sys.User_Name0

FROM

v_R_System sys

JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID

JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE

sys.ResourceID not in (select sys.ResourceID

from

v_R_System sys

JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID

where

DisplayName0 like '%Lightspeed Filter Agent%')

and CollectionID = 'xxxxxxxx'

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.