Threat Prevention Extracting events from TP log

[fredbert]

I've been looking at how to extract event data from the Threat Prevention log and had drawn a blank... until now. To do this you have to enable SSH on the router and login as root using the user admin's password.

I don't advocate anyone messing with this unless they are very confident in what they are doing and don't mind doing a factory rebuild of the router.

The TP data is held in a PostgreSQL database, and after finding which username to use to access databases and which databases are present, called synotps. Here are the tables within the database.

 $ psql --username=postgres --dbname=synotps --command="\dt"
               List of relations
 Schema |        Name        | Type  |  Owner 
--------+--------------------+-------+----------
 public | data               | table | postgres
 public | device             | table | postgres
 public | event              | table | postgres
 public | icmphdr            | table | postgres
 public | iphdr              | table | postgres
 public | loading            | table | postgres
 public | modified_signature | table | postgres
 public | policy_class       | table | postgres
 public | policy_filter      | table | postgres
 public | policy_signature   | table | postgres
 public | schema             | table | postgres
 public | sig_class          | table | postgres
 public | signature          | table | postgres
 public | tcphdr             | table | postgres
 public | udphdr             | table | postgres
(15 rows)

What I was wanting was to extract information on events (when, from IP, to IP, event info) and so far I have worked out that the two tables event and iphdr can be used to generate this output. I've not yet determined where the event's Severity is held, but I'm not that concerned. With this extract I can now work on processing it further and create a summary of signatures and IP addresses. This is the command line code to read out the events.

psql --username=postgres --dbname=synotps --command="select event.ts_epoch, event.timestamp, '0.0.0.0'::inet + iphdr.ip_src, '0.0.0.0'::inet + iphdr.ip_dst, event.sig_name from event, iphdr where event.sid=iphdr.sid and event.cid=iphdr.cid;"

Gives an output like this. The two ?column? are ip_src and ip_dst, resp.

  ts_epoch  |        timestamp        |    ?column?     |    ?column?     |                                                      sig_name                                                     
------------+-------------------------+-----------------+-----------------+-------------------------------------------------------------------------------
 1651100204 | 2022-04-27 22:56:44.758 | 75.185.202.94   | z.y.x.w         | ET INFO Netlink GPON Login Attempt (GET)
 1651104598 | 2022-04-28 00:09:58.284 | 80.82.64.105    | z.y.x.w         | ET CINS Active Threat Intelligence Poor Reputation IP group 82
 1651104598 | 2022-04-28 00:09:58.284 | 80.82.64.105    | z.y.x.w         | ET SCAN NMAP -sS window 1024

 

[Rusty]

Top job m8!

 

[fredbert]

Top job m8!

Thanks.

Adding extra options to the psql command may help too. Using --field-separator, --no-align, and --tuples-only will provide a more CSV like output, without header and footer rows... but don't use a comma separator as some of the sig_name values have commas.

You can also direct the output to a file using --output="path to file". This could make the output accessible from File Station. And I guess using a cron job would be the next step, maybe filtering the events for a set period.

Here are all the options I used and have tested.

$ psql --help

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")

Input and output options:
  -o, --output=FILENAME    send query results to file (or |pipe)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           set field separator (default: "|")
  -t, --tuples-only        print rows only

Connection options:
  -U, --username=USERNAME  database user name (default: "root")

It's certainly easy enough to use Excel and a pivot table to quickly sort out and review the events.

 

[Rusty]

Would be interesting to pull this into Grafana as well.