Thursday, February 13, 2020

Receive notifications on pfsense ipsec or openvpn connection/disconnection

A couple of scripts to receive notifications for ipsec or openvpn (dis)connections on your pfsense firewall. This was tested with a Netgate SG-5100 firewall with pfsense version 2.4.4-RELEASE-p3 (amd64).


  • SSH to your pfsense firewall. Upload files to /root/
  • chmod 755
 4 -rwxr-xr-x   1 root  wheel   1999 Jul 17  2019 ipsec.php
 4 -rwxr-xr-x   1 root  wheel    198 Jul 17  2019 notify.php
 4 -rwxr-xr-x   1 root  wheel    308 Jul 17  2019
 4 -rwxr-xr-x   1 root  wheel    498 Jul 17  2019

Open VPN

For open VPN:
  • Login to GUI
  • VPN
  • Edit your OpenVPN Server
  • Under Advanced Configuration, Custom options, add the following line:
client-connect /root/;client-disconnect /root/;


Create cron to run every minute or so. This will not give exact times for connect/disconnect but within a 60 second window.
  • Login to GUI
  • Services
  • Cron
* * * * * root /usr/local/bin/php /root/ipsec.php > /dev/null 2>&1


Setup your SMTP server. The above files utilize pfsense's php functions to send via the send_smtp_message function
  • Login to GUI
  • System
  • Advanced
  • Notifications Tab

Wednesday, February 12, 2020

Tuesday, February 11, 2020

AWS Kibana Open Distro Alerting to MS Teams

MS Teams is a collaboration tool similar to Slack and it comes free with office365 business subscriptions. Below is a query to update the Kibana alerting trigger message (that comes with Open Distro) to use the MS Teams message card.

Note, this will only update the first trigger. If anyone scripts this to update all triggers, please comment below.

Run the query from Kibana dev tools.

Friday, February 7, 2020

Ubuntu 18.04 Installing ClamAV with clamonacc

A quick guide to installing ClamAV with clamonacc on Ubuntu 18.04.

Note: This was installed on a virtualmin server with clamav. Existing clamav was removed/purged via apt-get and reinstalled. clamd.conf reflects settings as part of virtualmin and clamonacc.

Install clamav and mailx

sudo apt-get install -y bsd-mailx clamav clamav-base clamav-daemon clamav-docs clamav-freshclam

Create Quarantine Folder

mkdir /root/quarantine

Create the following files (see gist files below at end of blog or here)
  • /etc/systemd/system/clamonacc.service
  • /etc/clamav/clamd.conf
  • /etc/clamav/
Modify them as needed (paths, email addresses, settings, etc)

Enable services

systemctl enable clamav-daemon.service
systemctl enable clamonacc.service
systemctl enable clamav-freshclam.service

Add root to clamav group

Clam, by default, will run under the clamav user and group. You can choose to keep/configure this or run as root. If you scan /root or folders owned by root you can encounter lstat() errors while running under the clamav user.

usermod -a -G clamav root


00 01,13 * * *  /usr/bin/freshclam --quiet
0 19 * * 1-5 nice -n 16  systemd-cat --identifier="clamav-scan" clamdscan --quiet --fdpass /home /tmp  >/dev/null 2>&1
0 6 * * 6 nice -n 18  systemd-cat --identifier="clamav-scan" clamdscan --quiet --fdpass /  >/dev/null 2>&1

Final Step
  • Reboot
Gist Files

Wednesday, February 5, 2020

Virtualmin - Moving MySQL 5.7 to 8 - SUPER privilege(s) and DEFINER errors

While attempting to restore Virtualmin domains from Ububtu 16.04 with AWS MySQL RDS 5.7 to Ubuntu 18.04 with a remote AWS RDS MySQL 8.0 I ran into an issue where MySQL had SUPER privilege(s) and DEFINER errors when running the restore:

Example Error

Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

Example Restore

virtualmin restore-domain --source /root/backups --all-domains --all-features

How to resolve

Enable log_bin_trust_function_creators = 1; in your custom parameters for AWS RDS MySQL and reboot the instance.

Get a export of your MySQL databases (I ended up using using webmin to grab a backup of all databases and SCPed those to the new server). Then proceed with the following replace commands to clean up the SQL dumps (after extracting the gzips)

sed -i.bak 's#MyISAM#InnoDB#g' *
sed -i.bak 's#SET @@SESSION\.SQL_LOG_BIN= 0;##g' *
sed -i.bak "s#SET @@GLOBAL\.GTID_PURGED='.*';##g" *
sed -i.bak 's#NO_AUTO_CREATE_USER##g' *

Note: The first replacement above is to convert your tables from MyISAM to InnoDB, ignore if you want to keep MyISAM. If you do not want a lot of .bak.bak.bak files, remove the '.bak' from the commands above.


When running the restore Virtualmin will fail on importing the mysql database. However, the database and user information will be created. After it has been created import your SQL above. This can be done through Webmin or command line.

If you are importing multiple domains, you can run the following command to continue importing your other domains. Repeat the steps above if you get another MySQL import error.

virtualmin restore-domain --source /root/backups --all-domains --all-features --only-missing

MySQL 8 Gotchas

With MySQL 8 you may run into an authentication issue after doing the above. Usually this shows up as a 500 error on your site or a 2054 from mysql. Run the following command to fix the authentication type for your user.