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))
What to Search from your Office 365 Audit Logs?
Here’s a guide on quickly accessing and running advanced queries on your Office365 audit logs with your desktop computer and the free edition of SpectX. Whether you need to know more about logins, accessing, editing, deleting files - O365 logs are the data to go to. The query examples we've prepared will give you a broad overview of user activities and zoom into the suspicious. If you're in the incident response phase, you can use the queries to learn the details.
Analyze Cloudfront Logs in S3
SpectX gets you instant insights into your web resources served by Cloudfront. Download the free desktop edition to your computer to parse and query the S3 bucket storing your log files. Follow the steps below and run the query examples to get a general overview or specific questions answered in minutes. The example queries are also included in the SpectX Github query pack.
Analyzing 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.