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:
- Identify Machines with SEP Installed: It checks for computers that have Symantec Endpoint Protection installed.
- Report Non-Installation: It provides a report for computers that do not have the software installed.
- 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
ProductNamematches "Symantec Endpoint Protection". - Additionally, it limits results to those within the specified collection.
- Filters to find resources where the
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 insubquery ensures only systems without the product are selected. - It also checks for systems within the specified collection.
- The
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.
