I needed to cleanup an issue with an OpenVPN user. I cleaned was using a multi-factor solution with OpenVPN. The multi-factor solution was case sensitive, while the VPN was not. To clean up the mess, I needed to remove the certificates from the certs SQLite database.

Database location:

# cd /usr/local/openvpn_as/etc/db

Connect to the database:

# sqlite3 certs.db

List the tables in the database:

sqlite> .tables

List column names in the table:
sqlite> pragma table_info(certificates);

List all the common_name records in the database:
sqlite> select common_name from certificates ;

Delete records with the common name somename:
sqlite> delete from certificates where common_name=’somename‘;

This worked well to clean up the database, and get the user working with a correct ovpn file.

Note: You could also edit the ovpn with data from the database as well.

Here is how you display a table (log in this instance) layout:

sqlite> .schema log

I used the following to query the login activity. You have to convert the timestamp in the start_time column. Also, the duration is stored in seconds, so I converted it to minutes.

sqlite> select username,strftime(‘%m-%d-%Y’, datetime(start_time, ‘unixepoch’)),duration/60 from log;

To run it from a script, save your query in a file (zreport) and run the following:

sqlite3 log.db < zreport