Example of custom ITMS report query showing computers without SEP agent installed

Products

• IT Management Suite
• Server Management Suite
• Client Management Suite

Issue/Introduction

KB created for request of example query to find all computers without Symantec Endpoint Protection installed

Environment

ITMS 8.x

Resolution

Custom report query examples are provided as a courtesy and used/modified at your own risk. Technical Support does not assist with customizations.

SELECT
    vc.Name as 'Computer',
    vc.Domain,
    vc.[IP Address],
    vc.[System Type],
    vc.[OS Name]
FROM vComputer vc
WHERE 
    Guid NOT IN 
        (SELECT inst._ResourceGuid
            FROM vRM_Software_Component_Item sci
            Left JOIN Inv_InstalledSoftware inst
            ON inst._SoftwareComponentGuid = sci.Guid
            AND inst.InstallFlag = 1              
            WHERE LOWER(sci.Name) LIKE '%symantec endpoint protection%') 
    and vc.IsManaged = 1
order by vc.Name

--Additional query example:

select * from vcomputer where
not exists
( select 1 from Inv_AddRemoveProgram where DisplayName = 'Symantec Endpoint Protection' and vcomputer.Guid = Inv_AddRemoveProgram._ResourceGuid)
and vcomputer.IsManaged = 1