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 was created for the request of an 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