Video Screencast Help

SQL Queries

Created: 04 Oct 2012 • Updated: 15 Oct 2012 | 8 comments
This issue has been solved. See solution.

Does anyone know where in the SEP SQL database query the actions (clean, deleted, quarantined, etc) taken over a specific time and the virus names/infections that SEP detected? Can I perform one query to obtain this info or two separate queries?

Comments 8 CommentsJump to latest comment

kavin's picture

it depends on how old data you want to pull from the DB, There are different table in DB and if you are using SQL DB then you can login to the DB and see what all different tables are available and using a different combinations of query should give you the expected result.

But the Easier way would be to use SEPM> minitors > logs or SEPM > reporting tab for this

 

_mtquery's picture

I need the raw data and unfortunately, the reports in the SEPM do not provide that.

John Santana's picture

Yes, I saw this somewhere , I'll dig around and try to get back to you again about this.

 

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.

pete_4u2002's picture

is it for forwarding to syslog server or any log collector? if yes, enable the syslog forwarding.

Mithun Sanghavi's picture

Hello,

Check these Articles:

Symantec™ Endpoint Protection Manager Database Schema Reference 12.1

http://www.symantec.com/docs/DOC4324

Exporting log data to a text file http://www.symantec.com/docs/HOWTO55416

Exporting data to a Syslog server http://www.symantec.com/docs/HOWTO55417

Symantec Endpoint Protection 12.1.x event log entries 

http://www.symantec.com/docs/TECH186925

Also, Check this Thread: https://www-secure.symantec.com/connect/forums/sql-querys-database

Hope that helps!!

Mithun Sanghavi
Senior Consultant
MIM | MCSA | MCTS | STS | SSE | SSE+ | ITIL v3

Don't forget to mark your thread as 'SOLVED' with the answer that best helped you.

SOLUTION
_mtquery's picture

Mithun, thank you for the Schema Reference Guide. That was helpful. Thanks.

John Q.'s picture

Data you can find in Risks log in the console should be coming from ALERTS table in the database.

You must join it to other tables if you want to have full set of data:

 - to VIRUS table using VIRUSNAME_IDX field if you want to have threat name in clear text

 - to ACTUALACTION table using ACTUALACTION_IDX if you want to have action taken in clear text

 

Please remember to mark the proper comment as SOLUTION:
 - to identify threads that do not require further assistance
 - to let other visitors know how to fix such issue

John Santana's picture

From: https://www-secure.symantec.com/connect/forums/sql...

DECLARE @Hostname NVARCHAR(512) 

SELECT @Hostname = '%%' 

DECLARE @Groupname NVARCHAR(2000) 

SELECT @Groupname = '%%' 

SELECT Dateadd(hour, +2, Dateadd(second, ASL.event_time / 1000, 
                         '1970/01/01 00:00')) AS 
       "EVENT-TIME", 
       ASL.host_name 
       AS "COMPUTER", 
       S.name 
       AS "SEPM", 
       G.name 
       AS "GROUP", 
       ASL.event_source 
       AS "EVENT-SOURCE", 
       ASL.event_desc 
       AS "DESCRIPTION", 
       ASL.event_data 
       AS "RULENAME", 
       'N/A' 
       AS "LOCAL-IP", 
       'N/A' 
       AS "REMOTE-IP", 
       'N/A' 
       AS "APPLICATION", 
       'N/A' 
       AS "LOCATION", 
       'N/A' 
       AS "INTRUSION URL", 
       NULL 
       AS "LOCAL-PORT", 
       NULL 
       AS "REMOTE-PORT" 
FROM   dbo.v_agent_system_log ASL 
       JOIN dbo.v_groups G 
         ON ASL.group_id = G.id 
       JOIN dbo.v_servers S 
         ON ASL.server_id = S.id 
WHERE  G.name LIKE @Groupname 
       AND ASL.host_name LIKE @Hostname 
UNION ALL 
SELECT Dateadd(hour, +2, Dateadd(second, ASL.event_time / 1000, 
                         '1970/01/01 00:00')) AS 
       "EVENT-TIME", 
       ASL.host_name 
       AS "COMPUTER", 
       S.name 
       AS "SEPM", 
       G.name 
       AS "GROUP", 
       'N/A' 
       AS "EVENT-SOURCE", 
       ASL.event_desc 
       AS "DESCRIPTION", 
       'N/A' 
       AS "RULENAME", 
       ASL.local_host_ip_text 
       AS "LOCAL-IP", 
       ASL.remote_host_ip_text 
       AS "REMOTE-IP", 
       ASL.app_name 
       AS "APPLICATION", 
       ASL.location_name 
       AS "LOCATION", 
       ( ASL.intrusion_url + '  _Payload URL/' 
         + ASL.intrusion_payload_url ) 
       AS "INTRUSION URL", 
       NULL 
       AS "LOCAL-PORT", 
       NULL 
       AS "REMOTE-PORT" 
FROM   dbo.v_agent_security_log ASL 
       JOIN dbo.v_groups G 
         ON ASL.group_id = G.id 
       JOIN dbo.v_servers S 
         ON ASL.server_id = S.id 
WHERE  G.name LIKE @Groupname 
       AND ASL.host_name LIKE @Hostname 
UNION ALL 
SELECT Dateadd(hour, +2, Dateadd(second, ABL.event_time / 1000, 
                         '1970/01/01 00:00')) AS 
       "EVENT-TIME", 
       ABL.host_name 
       AS "COMPUTER", 
       S.name 
       AS "SEPM", 
       G.name 
       AS "GROUP", 
       CASE 
         WHEN ABL.vapi_name IS NULL THEN 'Tamper Protection' 
         ELSE ABL.vapi_name 
       END 
       AS "EVENT-SOURCE", 
       ABL.description 
       AS "DESCRIPTION", 
       ABL.rule_name 
       AS "RULENAME", 
       'N/A' 
       AS "LOCAL-IP", 
       'N/A' 
       AS "REMOTE-IP", 
       ABL.caller_process_name 
       AS "APPLICATION", 
       'N/A' 
       AS "LOCATION", 
       'N/A' 
       AS "INTRUSION URL", 
       NULL 
       AS "LOCAL-PORT", 
       NULL 
       AS "REMOTE-PORT" 
FROM   dbo.v_agent_behavior_log ABL 
       JOIN dbo.v_groups G 
         ON ABL.group_id = G.id 
       JOIN dbo.v_servers S 
         ON ABL.server_id = S.id 
WHERE  G.name LIKE @Groupname 
       AND ABL.host_name LIKE @Hostname 
UNION ALL 
SELECT Dateadd(hour, +2, Dateadd(second, ATL.event_time / 1000, 
                         '1970/01/01 00:00')) AS 
       "EVENT-TIME", 
       ATL.host_name 
       AS "COMPUTER", 
       S.name 
       AS "SEPM", 
       G.name 
       AS "GROUP", 
       'N/A' 
       AS "EVENT-SOURCE", 
       ( 'Action: ' + CASE WHEN ATL.blocked = 1 THEN 'BLOCKED' WHEN ATL.blocked 
         = 0 
         THEN 
         'ALLOWED' END + ' - Protcol: ' + CASE WHEN ATL.network_protocol = 1 
         THEN 
         'IP, Ethernet' WHEN ATL.network_protocol = 2 THEN 'TCP' WHEN 
         ATL.network_protocol = 3 THEN 'UDP' WHEN ATL.network_protocol = 4 THEN 
         'ICMP' 
         END 
         + ' - Direction: ' + CASE WHEN ATL.traffic_direction = 1 THEN 'IN' WHEN 
         ATL.traffic_direction = 2 THEN 'OUT' WHEN ATL.traffic_direction = 0 
         THEN 
         'Unknown' END ) 
       AS "DESCRIPTION", 
       ATL.rule_name 
       AS "RULENAME", 
       ATL.local_host_ip_text 
       AS "LOCAL-IP", 
       ATL.remote_host_ip_text 
       AS "REMOTE-IP", 
       ATL.app_name 
       AS "APPLICATION", 
       ATL.location_name 
       AS "LOCATION", 
       'N/A' 
       AS "INTRUSION URL", 
       ATL.local_port 
       AS "LOCAL-PORT", 
       ATL.remote_port 
       AS "REMOTE-PORT" 
FROM   dbo.v_agent_traffic_log ATL 
       JOIN dbo.v_groups G 
         ON ATL.group_id = G.id 
       JOIN dbo.v_servers S 
         ON ATL.server_id = S.id 
WHERE  G.name LIKE @Groupname 
       AND ATL.host_name LIKE @Hostname 
ORDER  BY "event-time" DESC, 
          "computer";

Kind regards,

John Santana
IT Professional

--------------------------------------------------

Please be nice to me as I'm newbie in this forum.