Threat Prevention Extracting events from TP log

Currently reading
Threat Prevention Extracting events from TP log


NAS Support
DS1520+, DS218+, DS215j
  1. RT2600ac
  2. MR2200ac
  3. RT6600ax
  4. WRX560
Operating system
  1. macOS
Mobile operating system
  1. iOS
Last edited:
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, ''::inet + iphdr.ip_src, ''::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 |   | z.y.x.w         | ET INFO Netlink GPON Login Attempt (GET)
 1651104598 | 2022-04-28 00:09:58.284 |    | z.y.x.w         | ET CINS Active Threat Intelligence Poor Reputation IP group 82
 1651104598 | 2022-04-28 00:09:58.284 |    | z.y.x.w         | ET SCAN NMAP -sS window 1024
Top job m8!

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


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.

Create an account or login to comment

You must be a member in order to leave a comment

Create account

Create an account on our community. It's easy!

Log in

Already have an account? Log in here.

Similar threads

The activity log that is managed by Safe Access is held in a SQLite3 database. To access the database you...

Welcome to! is an unofficial Synology forum for NAS owners and enthusiasts.

Registration is free, easy and fast!

Trending threads