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))
Investigate Exchange Server Logs to Detect the HAFNIUM Exploit
In case you are working directly with raw log files to analyze the Exchange Server 0-day vulnerability, we have prepared these SpectX queries to help you detect if, how, and when your Exchange server was hit.
Introducing SpectX Business
SpectX log parser and query engine for instant log investigation and query automation is now available for smaller teams and organizations. Starting at 79€/month, the Business license introduces several opt-in features for analyzing raw log files stored in file servers, S3, Azure, Google Storage, Elastic clusters and databases.
Collecting and Analyzing Google Workspace Audit Logs
What's the most accessed publicly shared document in your Drive? How many documents per user were downloaded during the past month? Where are all those calendar (spam) invites coming from? If your business relies on Gmail, Docs, Drive, Calendar, Meet and other products from Google, the answers to these questions lie in Google Workspace (formerly G Suite) audit logs.
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.