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.
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.
| filter(start_time >= T('2020-03-30 03:08:00.000 +0300') and start_time <= T('2020-03-30 03:09:00.000 +0300')
| select(time:start_time[1 day],count(*))TIP: replace the [1 day ] a shorter or longer time period: ms or millis, sec or s, min or m, hour or h,
| group(time)
| sort(start_time)
| filter(username = ‘foo’)
| select(username, data_received:sum(bytes_in))
| group(username)
| sort(data_received desc)
| select(real_countries:cc(real_ip))
| select(real_countries, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)
| select(autsysname:asname(real_ip))TIP: remove DESC from the sort function to see rare occurrences on top
| select(autsysname, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)
| select(vpn_countries:cc(vpn_ip))
| select(vpn_countries, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(100)
| select(start_time, geo(real_ip))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.
| sort(start_time)
| 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)
| 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)
| select(duration:$timeToSec(duration),*)TIP: to learn the average session duration for unique users, replace SUM with AVG or any other aggregate function.
| select(SUM(duration),*)
| group(username)
| sort(sum_duration DESC)
| 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)
$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)
$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)
@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))