-- Find Symantec Endpoint Protection
Declare @UserSIDs as varchar(Max) = 'Disabled',
@ProductName as varchar(50) = 'Symantec Endpoint Protection',
@CollectionID as varchar(10) = 'SMS00001' --All Systems Collection
select Distinct
RV.Netbios_Name0 as 'ComputerName',
rv.User_Name0 as 'UserName',
u.Full_User_Name0 as 'FullName',
u.Mail0 as 'EmailAddress',
u.telephoneNumber as 'PhoneNumber',
p.ProductName0 as 'ProductName',
p.ProductVersion0 as 'ProductVersion',
'FoundStatus' = 'True'
From
fn_rbac_R_System_Valid(@UserSIDs) as RV
join fn_rbac_GS_INSTALLED_SOFTWARE(@UserSIDs) as P on p.ResourceID = RV.ResourceID
join fn_rbac_R_User(@UserSIDs) as u on u.User_Name0 = rv.User_Name0
Where
RV.ResourceID in
(
select
p.ResourceID
from
fn_rbac_GS_INSTALLED_SOFTWARE(@UserSIDs) as P
Where
p.ProductName0 LIKE @ProductName
) and
p.ProductName0 LIKE @ProductName and
RV.ResourceID in
(
select
fcm.ResourceID
from fn_rbac_FullCollectionMembership(@UserSIDs) as fcm
where fcm.CollectionID = @CollectionID
)
UNION
select Distinct
RV.Netbios_Name0 as 'ComputerName',
rv.User_Name0 as 'UserName',
u.Full_User_Name0 as 'FullName',
u.Mail0 as 'EmailAddress',
u.telephoneNumber as 'PhoneNumber',
'ProductName' = 'Not Found',
'ProductVersion' = 'Not Found',
'FoundStatus' = 'False'
From
fn_rbac_R_System_Valid(@UserSIDs) as RV
join fn_rbac_R_User(@UserSIDs) as u on u.User_Name0 = rv.User_Name0
Where
RV.ResourceID not in
(
select
p.ResourceID
from
fn_rbac_GS_INSTALLED_SOFTWARE(@UserSIDs) as P
Where
p.ProductName0 LIKE @ProductName
) and
RV.ResourceID in
(
select
fcm.ResourceID
from fn_rbac_FullCollectionMembership(@UserSIDs) as fcm
where fcm.CollectionID = @CollectionID
)
ORDER BY
FoundStatus
