Tutorial: Query the Access Server Log Database with the logdba Tool⁠

• An installed Access Server.

• Console access and the ability to get root access.

1. Connect to the console and get root privileges.

2. 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.

./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