What to look for in your OpenVPN Access Server logs?

By Raido Karro and Liisa Tallinn 
7 April 2020

This is a tutorial on analyzing and enriching  OpenVPN Access Server logs with SpectX. What’s the traffic load? What’s the user activity like? Where do they log on from? How much data are they sending and receiving? How many different IPs per user, users per IP? Any suspicious IPs in the mix?  SpectX is a log analyzer that runs locally, skips ingestion and creates quick virtual views and queries directly on top of raw (flat) log files or databases. The Desktop edition is free.

If your organisation has switched to working from home during the pandemic, the number of users simultaneously VPNing to your networks has probably gone through the roof. Many are looking for ways to scale their VPN. Some have even opted for a VPN rations' policy towards critical services.  In this situation, it makes a lot of sense to take a good look at the logs and see what’s really going on. What was anomalous before, isn’t necessarily unusual today - defining new baselines and zooming into the outliers is a smart move before establishing new rules and additional spending on your VPN infrastructure.

OpenVPN logs - where are they located?

To view the logs, go to the OpenVPN Access Server Admin Web UI and click on the Log Reports on the left menu. To read these logs with SpectX, export and save the logs to your local machine or another storage you can access with SpectX, e.g. a file server or cloud storage. The export button in the OpenVPN admin UI advertises a .csv file, however, the downloaded file is, in fact, a .txt file.  This doesn’t matter, SpectX can read both. Alternatively, SpectX can also read the database file directly from the Access Server. 

Viewing and Parsing the Logs with SpectX

1. Download and open SpectX, click on ‘Input Data’ at the top menu 
2. Navigate to the exported .txt file and click Prepare Query. 


3. Replace the default CSV pattern (schema) and initial query you see in the query window with this OpenVPN Access Server log pattern and initial query. The pattern matches both the previous and current versions of OpenVPN (2.6.1 and 2.7.5).
4. Replace the path to file is the one generated when navigating to the file in step
5. Press Run


The auto-detected .csv pattern will parse the data just fine; however, the trouble is, there are no column headers included in the OpenVPN export file, and CSV fields are just strings. These are not very useful to filter specific time periods including milliseconds, calculate totals and averages from bytes sent and received, enrich IPs with geolocation data and third party reputation databases. In short - SpectX’ pattern produces typified data fields.

Having pressed 'Run', the structured results should look something like this.

Configure SpectX to access Maxmind 

One last thing before jumping to the query-related fun. To enrich IP-addresses with geolocations, create a Maxmind account and configure SpectX’ to access the Geolite database. Here are the instructions. 

Queries

Paste a query to the end of the initial query and feel free to additionally play with the results. The queries are designed to give you detailed insights into traffic, data volumes and user activities.

1. Records from a specific time period

| filter(start_time >= T('2020-03-30 03:08:00.000 +0300') and start_time <= T('2020-03-30 03:09:00.000 +0300')

2. Number of requests in time

| select(time:start_time[1 day],count(*))
| group(time)
| sort(start_time)

TIP: replace the [1 day ] a shorter or longer time period: ms or millis, sec or s, min or m, hour or h,
week or w

3. Records from a specific user

| filter(username = ‘foo’)

4. Top users by data received from the user

| select(username, data_received:sum(bytes_in))
| group(username)
| sort(data_received desc)

5. Top 100 countries of user IPs

| select(real_countries:cc(real_ip))
| select(real_countries, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)

6. Top 100 AS names of user IPs

| select(autsysname:asname(real_ip))
| select(autsysname, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)

TIP: remove DESC from the sort function to see rare occurrences on top

7. Top 100 countries of the IPs assigned by the server

| select(vpn_countries:cc(vpn_ip))
| select(vpn_countries, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)

8. Users on a map in time

| select(start_time, geo(real_ip))
| sort(start_time)

Run the query and click on the Map button on the top menu. Slide the grey ribbon behind the ‘play’ button to shorten it and enjoy a ‘pew-pew’ map by clicking Play.

9. Users coming from the same real_ip

| select(username:REPLACE(username, '\n', ''))
| select(users:ARRAY_AGG(username))
| group(real_ip)
| select(users:ARRAY_UNIQ(users),real_ip)
| sort(ARRAY_LEN(users) DESC)

10. Different real_ips from unique users

| select(username:REPLACE(username, '\n', ''))
| select(IPs:ARRAY_AGG(real_ip))
| group(lower(username))
| select(username,IPs:ARRAY_UNIQ(IPs))
| sort(ARRAY_LEN(IPs) DESC)

11. Total amount of time the user was connected to the Access Server

| select(duration:$timeToSec(duration),*)
| select(SUM(duration),*)
| group(username)
| sort(sum_duration DESC)

TIP: to learn the average session duration for unique users, replace SUM with AVG or any other aggregate function.

12. Total amount of data sent to the user

| select(username:REPLACE(username, '\n', ''), in:round(bytes_in*unit_in/$MB,2),out:round(bytes_out*unit_out/$MB,2),*)
| select(sum_MBytes_out:round(sum(bytes_out),2),*)
| group(username)
| sort(sum_MBytes_out DESC)

13. Users connecting to the VPN from the TOR network

$torpattern = <<<END_OF_PATTERN
(
'ExitNode ' LD:exitNode EOL
'Published ' TIMESTAMP('yyyy-MM-dd HH:mm:ss'):published EOL
'LastStatus ' TIMESTAMP('yyyy-MM-dd HH:mm:ss'):lastStatus EOL
)?
'ExitAddress ' IPV4:exitAddress ' ' TIMESTAMP('yyyy-MM-dd HH:mm:ss'):addrTime EOL
END_OF_PATTERN;

@tor= PARSE(src:'https://check.torproject.org/exit-addresses', pattern:$torpattern);
LIST('file://path-to-file(s)')
| parse(pattern:$pattern)
| select(username:REPLACE(username, '\n', ''),*)
  | join(@tor on real_ip= exitAddress)

14. Check IPs against the Alienvault IP reputation database

$alienpattern = "IPADDR:ip '#' INT:nr1 '#' INT:nr2 '#' LD:Message '#' WORD*:cc '#' LD*:city '#' float:lat ',' Float:long '#' INT:nr3 EOL";
@alienvault = LIST('http://reputation.alienvault.com/reputation.data')
| parse(pattern:$alienpattern);
LIST('file://path-to-file(s)')
| parse(pattern:$pattern)
| select(username:REPLACE(username, '\n', ''),*)
| join(@alienvault on real_ip = ip)

Reading logs directly from the DB file

Last but not least. The logs are in fact written to a database file in the OpenVPN server and SpectX could also read this file. To access the DB file with SpectX, either mount the folder to the machine running SpectX or export the DB file to storage (cloud, on-prem file server, local machine) that SpectX can access and use this query to read the file (replace the path):

@table=JDBC_TABLE(uri:'jdbc:sqlite:file://Download/log.db', table:'log');
@table

//to get typified fields for timestamps and IPs, add this select-statement to the query
| select(timestamp:TIMESTAMP(timestamp), start_time:TIMESTAMP(start_time), real_ip:IPADDR(real_ip), vpn_ip:IPADDR(vpn_ip),*)

//to see the fields in the same order as in the database, use this select-statement:
| select(session_id,
    node,
    username,
    common_name,
    start_time:TIMESTAMP(start_time),
    duration,
    service,
    api_method,
    active,
    real_ip:IPADDR(real_ip),
    vpn_ip:IPADDR(vpn_ip),
    proto,
    port,
    bytes_in,
    bytes_out,
    bytes_total,
    error,
    auth,
    extra,
    timestamp:TIMESTAMP(timestamp))

Back to articles