ビデオヘルプ

SQL Queries

作成: 04 Oct 2012 • 更新: 15 Oct 2012 | コメント数: 8
この問題は解決されました。 ソリューションを参照してください。

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?

コメント コメント数: 8最新のコメントを表示

kavin の写真

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 の写真

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

John Santana の写真

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 の写真

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

Mithun Sanghavi の写真

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
Associate Security Architect

MIM | MCSA | MCTS | STS | SSE | SSE+ | ITIL v3

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

ソリューション
_mtquery の写真

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

John Q. の写真

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 の写真

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.