Finding Symantec Endpoint Protection with SCCM SQL Query

In IT environments, particularly in enterprise settings, maintaining visibility over installed software is crucial for security and compliance. One commonly used tool for endpoint security is Symantec Endpoint Protection (SEP). To efficiently track installations of SEP across systems managed by SCCM, a structured SQL query can be employed. Below is a detailed breakdown of the SQL query designed to identify whether Symantec Endpoint Protection is installed on machines within a specified collection.

Purpose of the Query

This query is structured to perform the following tasks:

1. Identify Machines with SEP Installed: It checks for computers that have Symantec Endpoint Protection installed.
2. Report Non-Installation: It provides a report for computers that do not have the software installed.
3. Focus on a Specific Collection: The query restricts its search to a designated collection (e.g., "All Systems Collection").

Code Explanation

The SQL query is built using several components to ensure comprehensive results. Let's break down each part:

-- Declare parameters for user SIDs, product name, and collection ID
Declare @UserSIDs as varchar(Max) = 'Disabled',
        @ProductName as varchar(50) = 'Symantec Endpoint Protection',
        @CollectionID as varchar(10) = 'SMS00001' -- All Systems Collection

• Parameters:
  • @UserSIDs: This variable determines the visibility of the data based on the user's permissions.
  • @ProductName: This specifies the software we are searching for, in this case, Symantec Endpoint Protection.
  • @CollectionID: This identifies the specific collection of systems being queried (here, it targets all systems).

Main Query to Find Installed SEP

The first part of the query retrieves systems with Symantec Endpoint Protection installed:

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
    )

• Select Clause: This retrieves a distinct list of fields related to the systems, including the computer name, user details, product name, and version.

• From Clause:

  • fn_rbac_R_System_Valid(@UserSIDs): This function returns valid resources based on user permissions.
  • fn_rbac_GS_INSTALLED_SOFTWARE(@UserSIDs): This function returns the installed software for those valid resources.
  • fn_rbac_R_User(@UserSIDs): This function links the user details to the machines.

• Where Clause:

  • Filters to find resources where the ProductName matches "Symantec Endpoint Protection".
  • Additionally, it limits results to those within the specified collection.

Main Query to Find Non-Installed SEP

The second part of the query handles the identification of systems without SEP installed:

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
    )

• UNION Clause: This combines results from both queries, ensuring that systems without SEP are included in the final report.
• Select Clause: Similar to the previous section, but returns a status of "Not Found" for systems without the product installed.
• Where Clause:
  • The not in subquery ensures only systems without the product are selected.
  • It also checks for systems within the specified collection.

Final Output and Ordering

ORDER BY 
    FoundStatus

The final output is ordered by FoundStatus, allowing systems with SEP installed to appear first in the results.

Conclusion

This SQL query serves as an effective tool for IT administrators managing endpoint security through SCCM. By clearly identifying which systems have Symantec Endpoint Protection installed and which do not, organizations can ensure compliance and security standards are met. Moreover, the ability to run such queries empowers administrators to maintain oversight of their software environment, facilitating proactive management of potential vulnerabilities.

Using structured SQL queries like this one not only enhances visibility into the software landscape but also supports strategic decision-making regarding security posture and software management across an organization.