Skip to main content

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

Abstract

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 with root privileges.

  1. Connect to the console with 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.

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