Force Point SQL query SCCM

In a case you need some SQL quires  for Force point, you can use the following necessary queries to build SQL report:

Computer without forcepoint, without servers, only client:

SELECT DISTINCT sys.Netbios_Name0,
                sys.User_Domain0,
                sys.User_Name0,
                os.Caption0
FROM v_R_System sys
       JOIN (select ResourceID,
			 sum(case when DisplayName0 NOT LIKE '%FORCEPOINT%' then 1 else 0 end)  as isfp
			 from v_Add_Remove_Programs
			 group by ResourceID
			 having sum(case when DisplayName0 LIKE '%FORCEPOINT%' then 1 else 0 end) = 0) arp ON sys.ResourceID = arp.ResourceID
       JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
WHERE os.Caption0 NOT LIKE '%Server%'; 

Computers with forcepoint that are less then X version:

select  all SMS_R_System.Name0 from vSMS_R_System AS SMS_R_System INNER JOIN Add_Remove_Programs_64_DATA AS __tem_ADD_REMOVE_PROGRAMS_640 ON __tem_ADD_REMOVE_PROGRAMS_640.MachineID = SMS_R_System.ItemKey  INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey   where (__tem_ADD_REMOVE_PROGRAMS_640.DisplayName00 like N'FORCEPOINT ENDPOINT' AND __tem_ADD_REMOVE_PROGRAMS_640.Version00 < N'8.5.2832')

Computer without forcepoint, without servers, only client from specific collection:

SELECT DISTINCT sys.Netbios_Name0,
sys.User_Domain0,
sys.User_Name0,
os.Caption0
FROM v_R_System sys
JOIN (select ResourceID,

sum(case when DisplayName0 NOT LIKE '%FORCEPOINT%' then 1 else 0 end) as isfp

from v_Add_Remove_Programs
group by ResourceID
having sum(case when DisplayName0 LIKE '%FORCEPOINT%' then 1 else 0 end) = 0) arp ON sys.ResourceID = arp.ResourceID
JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
JOIN v_FullCollectionMembership coll on coll.resourceid = sys.Resourceid
WHERE os.Caption0 NOT LIKE '%Server%' and coll.collectionID like '%XXXXXX%';

 

For any question, lease a comment.