LogRhythm Remote Windows Log Collection Integration with Symantec SEPM MS SQL DB

This post describes how to configure LogRhythm Agnet to collect the Symantec SEPM logs through MS SQL DB.

Symantec Endpoint Protection is an endpoint security solution created through a layered approach to defense. With unique, layered technology, it detects and removes more malware than any other product in its class. Derived from Symantec’s global intelligence network, Symantec Endpoint Protection's unique Insight and SONAR technologies enable faster scan, more accurate detection, and higher performance while utilizing fewer resources. With single management console, Symantec Endpoint Protection provides advance protection across multiple platforms both physical and virtual.

Symantec Endpoint Protection can be configured to store its event logs in a Microsoft SQL database. Collection from a Microsoft SQL database requires:
• Universal Database Log Adapter (UDLA) Log Source
• A LogRhythm Agent to collect the logs
• Access to the Microsoft SQL database that Symantec Endpoint Protection uses for storing event logs

Method 1 - Syslog Forwarding

1  This is traditional way to forward logs from SEPM to Syslog servers, such as ArcSight, Splunk, Qradar, LogRhythm, etc. 

Note: SEPM does not support multiple syslog servers. Only one host can be configured and supported.

Procedure

• Log in to your Symantec Endpoint Protection Manager system.
• In the left pane, click the Admin icon.
• In the bottom of the View Servers pane, click Servers.
• In the View Servers pane, click Local Site.
• In the Tasks pane, click Configure External Logging.
• From the Generals tab, select the Enable Transmission of Logs to a Syslog Server check box.
• In the Syslog Server field, type the IP address of your Syslog Server that you want to parse the logs.
• In the UDP Destination Port field, type 514.
• In the Log Facility field, type 6.
• In the Log Filter tab, under Management Server Logs, select the Audit Logs check box.
• In the Client Log pane, select the Security Logs check box.
• In the Client Log pane, select the Risks check box.
• Click OK.

Method 2 - ODBC Connection

1 Configure Symantec Endpoint Protection Account

Make available an account that the LogRhythm Agent will use to access the Microsoft SQL database. We recommend using or creating an account that has read-only access into the tables required for collection. You`ll need to set the account that will be able to interactively logging on the DB Console/manager and be able of running the Query Statement until you can pull the Symantec Logs.

1. Create a new login

2. Enter username and password

3. Server Roles

4. Database Role Mapping

5. Permission

2 Configure the ODBC Driver

LogRhythm accesses Symantec Endpoint Protection logs via an ODBC driver. The recommended driver should already be installed on the Agent server by default, if not please install it. If the driver doesn’t work during connection test you may need (later) installing the latest drivers matching you SQL DB App Name. SQL Server
• Company Name. Microsoft Corporation
• Version. 2000.85.1132.00
• Date. 4/13/2008
• Download Location. Pre-installed
You can also check https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sqlserver?view=sql-server-ver15 for more info.

1.

2.

3.

4.

5.

6.

7.

8.

9.

3 Test the log pull query in the MS SQL Server Management Studio

With the created service account that allow reading into the Symantec DB, run & update the below 2 queries. Once the query is working and pulling the records for at least 1000 records, transmit the SQL query statement to Vendor. Vendor after will embed this tested query into the UDLA (ODBC) log source collecting SQL statement portion to execute from System Monitor Agent and will manage updating the parsing matrix statement accordingly after.

Please Note: Update or remove the DB Prefix SEPM.dbo. as you see fit to have BOTH below queries properly working. In SQL Mng Studio and after also within the ODBC test driver tool (next section).

select TOP 1000 DATEADD(ss,SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME/1000, '1-1-1970')
Last_Update, DATEADD(ss,SEPM.dbo.V_SEM_COMPUTER.TIME_STAMP/1000, '1-1-1970') timestamp,
SEPM.dbo.SEM_AGENT.AGENT_ID, SEPM.dbo.PATTERN.VERSION VIRUSDEF,
SEPM.dbo.PATTERN.SEQUENCE, LOWER(SEPM.dbo.Identity_map.NAME) DOMAIN_ID,
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_DOMAIN_NAME,
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_NAME,
SEPM.dbo.V_SEM_COMPUTER.CURRENT_LOGIN_USER,
SEPM.dbo.V_SEM_COMPUTER.CURRENT_LOGIN_DOMAIN,
SEPM.dbo.V_SEM_COMPUTER.DNS_SERVER1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.DHCP_SERVER_TEXT,SEPM.dbo.V_SEM_COMPUTER.IP_ADDR1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.IP_ADDR2_TEXT, SEPM.dbo.V_SEM_COMPUTER.IP_ADDR3_TEXT,
SEPM.dbo.V_SEM_COMPUTER.IP_ADDR4_TEXT, SEPM.dbo.V_SEM_COMPUTER.GATEWAY1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.MAC_ADDR1, SEPM.dbo.V_SEM_COMPUTER.COMPUTER_DESCRIPTION,
SEPM.dbo.V_SEM_COMPUTER.OPERATION_SYSTEM, SEPM.dbo.V_SEM_COMPUTER.SERVICE_PACK,
SEPM.dbo.V_SEM_COMPUTER.PROCESSOR_TYPE, SEPM.dbo.SEM_AGENT.FULL_NAME,
SEPM.dbo.SEM_AGENT.AGENT_VERSION, SEPM.dbo.SEM_AGENT.AGENT_TYPE,
SEPM.dbo.SEM_AGENT.CURRENT_CLIENT_ID, SEPM.dbo.SEM_AGENT.PROFILE_VERSION,
SEPM.dbo.SEM_AGENT.PROFILE_SERIAL_NO, SEPM.dbo.SEM_AGENT.STATUS From
SEPM.dbo.SEM_AGENT join SEPM.dbo.SEM_CLIENT on SEPM.dbo.SEM_AGENT.CURRENT_CLIENT_ID =
SEPM.dbo.SEM_CLIENT.CLIENT_ID join SEPM.dbo.V_SEM_COMPUTER on
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_ID = SEPM.dbo.SEM_AGENT.COMPUTER_ID left join
SEPM.dbo.PATTERN on SEPM.dbo.PATTERN.PATTERN_IDX = SEPM.dbo.SEM_AGENT.PATTERN_IDX JOIN
SEPM.dbo.IDENTITY_MAP ON SEPM.dbo.Identity_map.ID = SEPM.dbo.SEM_AGENT.DOMAIN_ID join
(select SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY,
MAX(SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME) as MAX_TIME from SEPM.dbo.V_SEM_COMPUTER join SEPM.dbo.SEM_AGENT on
SEPM.dbo.SEM_AGENT.COMPUTER_ID=SEPM.dbo.V_SEM_COMPUTER.COMPUTER_ID GROUP BY
SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY) maxresult on SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME = maxresult.MAX_TIME and
SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY=maxresult.HARDWARE_KEY order by
DATEADD(ss,SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME/1000, '1-1-1970')

Please update and test the below override query also required after you tested the first one:

Select TOP 1000 DATEADD(ss,SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME/1000, '1-1-1970')
Last_Update, DATEADD(ss,SEPM.dbo.V_SEM_COMPUTER.TIME_STAMP/1000, '1-1-1970') timestamp,
SEPM.dbo.SEM_AGENT.AGENT_ID, SEPM.dbo.PATTERN.VERSION VIRUSDEF, SEPM.dbo.PATTERN.SEQUENCE, LOWER(SEPM.dbo.Identity_map.NAME) DOMAIN_ID,
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_DOMAIN_NAME,
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_NAME,
SEPM.dbo.V_SEM_COMPUTER.CURRENT_LOGIN_USER,
SEPM.dbo.V_SEM_COMPUTER.CURRENT_LOGIN_DOMAIN,
SEPM.dbo.V_SEM_COMPUTER.DNS_SERVER1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.DHCP_SERVER_TEXT,SEPM.dbo.V_SEM_COMPUTER.IP_ADDR1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.IP_ADDR2_TEXT, SEPM.dbo.V_SEM_COMPUTER.IP_ADDR3_TEXT,
SEPM.dbo.V_SEM_COMPUTER.IP_ADDR4_TEXT, SEPM.dbo.V_SEM_COMPUTER.GATEWAY1_TEXT,
SEPM.dbo.V_SEM_COMPUTER.MAC_ADDR1, SEPM.dbo.V_SEM_COMPUTER.COMPUTER_DESCRIPTION,
SEPM.dbo.V_SEM_COMPUTER.OPERATION_SYSTEM, SEPM.dbo.V_SEM_COMPUTER.SERVICE_PACK,
SEPM.dbo.V_SEM_COMPUTER.PROCESSOR_TYPE, SEPM.dbo.SEM_AGENT.FULL_NAME,
SEPM.dbo.SEM_AGENT.AGENT_VERSION, SEPM.dbo.SEM_AGENT.AGENT_TYPE,
SEPM.dbo.SEM_AGENT.CURRENT_CLIENT_ID, SEPM.dbo.SEM_AGENT.PROFILE_VERSION,
SEPM.dbo.SEM_AGENT.PROFILE_SERIAL_NO, SEPM.dbo.SEM_AGENT.STATUS From
SEPM.dbo.SEM_AGENT join SEPM.dbo.SEM_CLIENT on SEPM.dbo.SEM_AGENT.CURRENT_CLIENT_ID =
SEPM.dbo.SEM_CLIENT.CLIENT_ID join SEPM.dbo.V_SEM_COMPUTER on
SEPM.dbo.V_SEM_COMPUTER.COMPUTER_ID = SEPM.dbo.SEM_AGENT.COMPUTER_ID left join
SEPM.dbo.PATTERN on SEPM.dbo.PATTERN.PATTERN_IDX = SEPM.dbo.SEM_AGENT.PATTERN_IDX JOIN
SEPM.dbo.IDENTITY_MAP ON SEPM.dbo.Identity_map.ID = SEPM.dbo.SEM_AGENT.DOMAIN_ID join
(select SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY,
MAX(SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME) as MAX_TIME from SEPM.dbo.V_SEM_COMPUTER join SEPM.dbo.SEM_AGENT on
SEPM.dbo.SEM_AGENT.COMPUTER_ID=SEPM.dbo.V_SEM_COMPUTER.COMPUTER_ID GROUP BY
SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY) maxresult on
SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME = maxresult.MAX_TIME and
SEPM.dbo.V_SEM_COMPUTER.HARDWARE_KEY=maxresult.HARDWARE_KEY where DATEADD(ss,SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME/1000, '1-1-1970') > '<yesterday date>' order
by DATEADD(ss,SEPM.dbo.SEM_AGENT.LAST_UPDATE_TIME/1000, '1-1-1970')

4 Configure and test the ODBC connector (DSN setup)

This step will allow getting proper tested DSN connection string that will be use after by the System Monitor to connect properly to the Database using the ODBC drivers. Below an example of DSN that is usually set within LR and Microsoft Links.
DSN string sample:
Driver={SQL Server}; DSN={LR Symantec <hostname>}; Server=<hostname.domain.com> \<DB instance ID>;
Note: Server=<hostname.domain.com> OR Server=<IP>

MS instructions: https://support.microsoft.com/en-ca/help/965049/how-to-set-up-a-microsoft-sqlserver-odbc-data-source
To test you can refer to https://docs.microsoft.com/en-us/sql/odbc/odbc-test?view=sql-server-ver15

Configuration LogRhythm

1 Create the Symantec Endpoint Protection Log Source in LogRhyThm Console

You must use a LogRhythm System Monitor to collect the logs. The System Monitor does not need to reside on the Symantec Endpoint Protection server, but it must be able to establish a network ODBC (Open Database Connectivity) connection.
Note: Before you begin, download the Symantec Endpoint Protection XML Configuration File. You will import this file later to populate the UDLA configuration fields for the Log Source.
To create the Symantec Endpoint Protection Log Source, do the following:
1. Start the LogRhythm Client Console and log in as a Global Administrator.
2. Click Deployment Manager on the main toolbar.
3. Click the System Monitors tab.
4. Double-click the System Monitor that will be collecting Symantec Endpoint Protection logs.
5. In the System Monitor Agent Properties dialog box, click the Agent Settings tab.
6. Right-click anywhere in the Log Sources List, and then click New.

The Log Message Source Properties dialog box is displayed.
7. Click the Basic Configuration tab.

8. Set the Log Message Source Type to System: UDLA - Symantec SEP, and set the Log Message Processing Engine (MPE) Policy to LogRhythm Default.

9. Click the UDLA Settings tab.
10. Click Import at the bottom of the UDLA Settings tab, then browse to and open the XML file that you downloaded from LogRhythm.
Important: In the Connection String box, you will need to replace {DSN} with the Symantec Endpoint Protection data source name.

11. Click Test to verify the settings.
12. If the test fails, ensure that the values are correct and test the connection settings again.
13. When the test passes, close the test dialog box.
14. Click OK to save the configuration and close the Log Message Source Properties dialog box.
15. Click OK to close the System Monitor Agent Properties dialog box.

References

1 https://knowledge.broadcom.com/external/article/156485/configure-multiple-syslog-servers-for-en.html


2  https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/symantec-security-software/endpoint-security-and-management/endpoint-protection/all/Monitoring-Reporting-and-Enforcing-Compliance/viewing-logs-v7522439-d37e464/exporting-data-to-a-syslog-server-v8442743-d15e1107.html