How to Use SQL in ConfigMgr to Identify Services Linked to Specific EXE Files

Microsoft Configuration Manager (ConfigMgr), also known as SCCM, is a powerful tool for managing devices, software, and services in an enterprise environment. Often, IT administrators need to track down specific executables (.exe files) to determine which services rely on them, especially when troubleshooting or auditing systems. Using SQL queries, you can efficiently locate this information in the ConfigMgr database.

In this guide, we’ll explore a step-by-step process to craft and execute a SQL query to identify services associated with a specific .exe file in your ConfigMgr environment.

SQL Query to Find Services by Executable Path in ConfigMgr

The ConfigMgr database includes several views, such as v_GS_SERVICE and v_GS_OPERATING_SYSTEM, which hold detailed information about services and their configurations. By querying these views, you can pinpoint services tied to a specific .exe file.

SQL Query Example

Here’s the SQL query to identify services by their executable path:

SELECT
    s.NetName AS 'Device Name',
    s.UserName AS 'Service Account',
    s.DisplayName AS 'Service Display Name',
    s.ServiceName AS 'Service Name',
    s.PathName AS 'Executable Path',
    os.Caption0 AS 'OS Name',
    os.Version0 AS 'OS Version'
FROM
    v_GS_SERVICE s
INNER JOIN
    v_GS_OPERATING_SYSTEM os ON s.ResourceID = os.ResourceID
WHERE
    s.PathName LIKE '%<YourExecutableNameHere>%'
ORDER BY
    s.NetName, s.DisplayName;

Parameters

• <YourExecutableNameHere>: Replace this placeholder with the name of the .exe file (e.g., example.exe).
• v_GS_SERVICE: This view contains details about all services on devices managed by ConfigMgr.
• v_GS_OPERATING_SYSTEM: This view provides operating system details for managed devices.
• s.PathName: A column in v_GS_SERVICE that stores the path to the service’s executable file.

Step-by-Step Execution

Follow these steps to execute the query and analyze the results:

1. Access the ConfigMgr Database

• Launch SQL Server Management Studio (SSMS).
• Connect to the database server hosting your ConfigMgr database.

2. Paste the SQL Query

Open a new query window, paste the provided SQL query, and replace <YourExecutableNameHere> with the name of the executable.

3. Run the Query

Click the "Execute" button or press F5. The query will retrieve:

• Device Name: The machine where the service is running.
• Service Account: The account under which the service operates.
• Service Display Name: The friendly name of the service.
• Service Name: The technical service name.
• Executable Path: The full path to the executable.
• OS Name and Version: Details about the operating system.

4. Analyze the Results

The results will show all services linked to the specified executable across devices in your ConfigMgr environment.

Benefits of Using SQL for Service Analysis in ConfigMgr

1. Efficiency: Query multiple systems at once to locate a specific executable.
2. Accuracy: Leverage ConfigMgr’s robust database to ensure comprehensive data collection.
3. Flexibility: Customize the query to filter results by operating system, device name, or service account.

Real-World Use Cases

• Identify Misconfigured Services: Find services using outdated or incorrect executable paths.
• Audit Critical Executables: Locate services that depend on key business applications.
• Investigate Potential Threats: Detect unknown or unauthorized executables running as services.

Conclusion

Leveraging SQL queries in ConfigMgr simplifies the task of identifying services linked to specific .exe files. This process not only saves time but also enhances visibility into your IT infrastructure, aiding in compliance, troubleshooting, and efficient system management.

By using the query shared in this guide, you can stay in control of your services and executables, ensuring a secure and well-managed environment.