By Raido Karro and Liisa Tallinn
7 April 2020
The questions to ask from VPN logs are pretty straightforward. 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? Here’s a tutorial on analyzing and enriching OpenVPN Access Server logs with SpectX, a free tool that runs locally, skips ingestion and creates quick virtual views and queries directly on top of raw (flat) log files or databases.
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))
Exploring PowerShell Transcripts
Even if you’re not using PowerShell, your adversaries and pen-testers are. Why use custom hax0r tools when every Windows machine comes with a built-in scripting environment? Powershell lets the attacker do pretty much anything while leaving no traces on disks. It’s a mistake not to love PowerShell as an attacker and a mistake not to love PowerShell logs as a defender.
What to look for in your OpenVPN Access Server logs?
What’s the traffic load? What’s the user activity like? Where do they log on from? How much data is being sent and received? How many different IPs per user, users per IP? Any bad IPs in the mix? Here’s a tutorial on analysing and enriching OpenVPN Access Server logs with SpectX, a free tool that runs locally, skips ingestion and creates quick virtual views and queries directly on top of raw log files or databases.
Meet Free SpectX
To help security analysts analyse raw logs and any ad-hoc data in time-critical investigations, SpectX is glad to launch the free edition of its rapid log analyzer. The parser and query engine help investigators quickly parse and query unlimited volumes of raw and compressed log files without ingestion or indexing directly from their storages like on-prem servers, AWS, Azure, Hadoop and/or SQL databases.
Analyzing IIS logs: Microsoft Exchange, OWA and ActiveSync Activities
Need to audit a specific user connecting to their mailbox? Troubleshoot why users are not able to sync their mobiles via ActiveSync? Investigate errors in time intervals? The answer to these and many other questions lie in the IIS logs of the Microsoft Exchange server.