How to query SEPM database using MSSQL Management Studio

Products

• Endpoint Protection

Issue/Introduction

Support engineers often request customers to provide the output of a SEPM (Symantec Endpoint Protection Manager) database query. This valuable information helps diagnose and resolve issues efficiently. In this guide, we'll walk you through the process of querying the SEPM database using Microsoft SQL Server Management Studio (MSSQL Management Studio).

Environment

Symantec Endpoint Protection Manager  14.X with Microsoft SQL Server

Symantec Endpoint Protection Manager  14.3 RU1 and later with default database

(In 14.3 RU1 the default database that is installed with SEPM changed from the embedded database to the Microsoft SQL Server Express 2017 database, therefore you can use this method in that setup)

Resolution

Before you start querying the SEPM database, we highly recommend performing a complete database backup. This precautionary step ensures that your data is protected in case anything unexpected happens during the querying process.

Instructions: 

To query a database using Microsoft SQL Server Management Studio (SSMS) and export the query result to a CSV file, follow these steps:

Step 1: Open Microsoft SQL Server Management Studio

Open Microsoft SQL Server Management Studio (SSMS). You can usually find it in your Windows Start menu or search for it.

Step 2: Connect to the Database

In SSMS, connect to your SQL Server instance where the Symantec Endpoint Protection Manager database is hosted. You'll need to provide the authentication method (Windows Authentication or SQL Server Authentication), and credentials.

Step 3: Ensure queries are exported with headers.

Go to Tools > Options

Navigate to Query Results > SQL Server > Results to Grid, then check “Include column headers when copying or saving the results” option:

Click OK to save changes, close and restart SSMS to apply changes.

Step 4: Open a New Query Window

After connecting to the database, ensure SEPM database is selected (default name: sem5)

open then a new query window. You can do this by clicking on "New Query" in the toolbar or by pressing Ctrl + N on your keyboard.

Step 5: Paste and Execute Your Query

Copy and paste the SQL query you want to execute into the query window.

Step 6: Execute the query

To execute the query, click the "Execute" button in the toolbar (green triangle) or press F5 on your keyboard. This will run the query and display the result in the "Results" tab.

Step 7: Export Query Results to CSV

To export the query result to a CSV file, follow these steps:
a. In the "Results" tab, right-click anywhere in the result grid.
b. Select "Save Results As..." from the context menu.
c. In the "Save As" dialog box, choose a location to save the CSV file and provide a file name with a ".csv" extension.
d. In the "Save as type" dropdown, select "CSV (Comma delimited) (*.csv)."
e. Click the "Save" button to save the query result as a CSV file.