Tutorial: Query the Access Server Log Database with the logdba Tool
Helpful commands to query the log database for Access Server with the logdba tool.
Overview
The Access Server maintains a log database of all VPN and web server usage. You can query this database using the ./logdba tool. This tutorial gives you a list of commands for doing that.
You can also query your log from the Admin Web UI's Log Reports page.
Tip
Access Server's command-line tools, including the logdba tool, are in the scripts directory: /usr/local/openvpn_as/scripts/.
An installed Access Server.
Console access and the ability to get root access.
Connect to the console and get root privileges.
Change to the scripts directory:
cd /usr/local/openvpn_as/scripts/
You're ready to run logdba commands to query the log database.
Use the following list of commands to query your Access Server log database:
Show all database records formatted as comma-separated values (CSV):
./logdba --csv
Show all database records formatted as javascript object notation (JSON):
./logdba --json
Show all active records (currently connected VPN sessions):
./logdba --csv --active=1
Show active or closed connections/sessions initiated in the last four hours:
./logdba --csv --start_time_ge="-4h"
Show VPN and web service authentication failures from the last 24 hours:
./logdba --csv --start_time_ge="-1d" --auth_bfilt=0
Show VPN log records (omit web records):
./logdba --csv --service_filt=VPN
Show web records (omit VPN records). Both forms below are equivalent:
./logdba --csv --service_filt="WEB_ADMIN,WEB_CLIENT" ./logdba --csv --service_filt="WEB%"
Show only XML_API log records (omit web and VPN records) with the API method called. These include sacli commands that access the Access Server XML API but omit all XML API accesses from the web server.
Tip
Adding log_db_xml_api_verbose=true to the as.conf file will cause web server accesses to the XML API to also be logged. Refer to the debug flag tutorial for more details.
./logdba --csv --service_filt=XML_API --columns="+api_method"
Show VPN sessions that used more than 2.5MB of bandwidth during the past 24 hours, and format bandwidth stats using KB, MB, etc. notation.
./logdba --csv --start_time_ge="-1d" --bytes_total_ge="2.5mb" --fmt_bw_all
Show all records formatted as JSON, and use unix time instead of formatted time:
./logdba --json --start_time_outfmt=unix
Show the ten VPN sessions over the last month that used the most bandwidth, with the highest bandwidth session shown at the top of the list.
./logdba --csv --service_filt=VPN --start_time_ge="-1M" --order_by=bytes_total --limit 10 --desc
Show VPN sessions and restrict the columns shown to a subset:
./logdba --csv --service_filt=VPN --columns="username,start_time,duration,bytes_total,error"
Show users "alice" and "bob" usage during 2009:
./logdba --csv --username_filt="alice,bob" --start_time_ge="2009.01.01" --start_time_lt="2010.01.01"
Show the eight most recent records for user bob, ordered from more recent to less recent:
./logdba --csv --limit=8 --desc --username_filt=bob
Show all records, but format the start time according to the RFC 2822 internet email standard:
./logdba --csv --start_time_outfmt="%a, %d %b %Y %H:%M:%S +0000" --start_time_outfmt_gmt
Show VPN records for the previous one day + 12 hours (including still-active sessions), show session duration in HH:MM format, and sort by session duration by increasing order:
./logdba --csv --service_filt=VPN --start_time_ge="-1d 12h" --order_by=duration --duration_fmt
Display additional logdba info:
./logdba --help