Tweet
Quering the UVM PostgreSQL Database for Netflow Data (NG Firewall)
I was having trouble aggregating all the different Untangle app logs remotely on my Graylog server; there were inconsistencies in which apps would pull the relevant network traffic information from the sessions table. I threw up a feature request, but thats probably a while away (this isn't exactly a high priority problem).
So I decided to whip together some shell scripts that will read from the PostgreSQL database on NG Firewall. They are pretty rough (I'm no dev by any formal training), but they get the job done and it was suprisingly simple and quick (only 2 days on and off).
All these scripts do is query the 'uvm' database using the 'reports' schema and pulls from the 'sessions' table. This table stores everything except web specific information (although still contains the basic traffic info).
I specifically wanted the output format to match, as close as possible, to the regular syslog JSON format generated by UVM itself. This made it really easy to just ship it off to my Graylog and have it parsed out into fields in the same way.
The scripts are run by crontab every 5 minutes or so. When it finds logs, it will ship it (in JSON format) using the logger program to your choice of Syslog server.
They do require the 'jq' command present on the system (I installed via apt).
Again, I'm no actual dev, so if anyone has any improvement suggestions to make, it would be very welcome!
Querying Blocked Firewall App Events:
Querying Blocked Threat Prevention Non-Web Events
So I decided to whip together some shell scripts that will read from the PostgreSQL database on NG Firewall. They are pretty rough (I'm no dev by any formal training), but they get the job done and it was suprisingly simple and quick (only 2 days on and off).
All these scripts do is query the 'uvm' database using the 'reports' schema and pulls from the 'sessions' table. This table stores everything except web specific information (although still contains the basic traffic info).
I specifically wanted the output format to match, as close as possible, to the regular syslog JSON format generated by UVM itself. This made it really easy to just ship it off to my Graylog and have it parsed out into fields in the same way.
The scripts are run by crontab every 5 minutes or so. When it finds logs, it will ship it (in JSON format) using the logger program to your choice of Syslog server.
They do require the 'jq' command present on the system (I installed via apt).
Again, I'm no actual dev, so if anyone has any improvement suggestions to make, it would be very welcome!
Querying Blocked Firewall App Events:
Code:
#!/bin/bash
# Ensure commands are present before running
for packages in psql jq tac logger; do
checkbin=$(which ${packages} &>/dev/null)
[[ ${?} = 1 ]] && echo "Missing program ${packages}, can't continue without it. Exiting." && exit 1
done
umask 137
# Postgres DB Info
DB="uvm"
DBUSER="postgres"
DBHOST="127.0.0.1"
DBPORT="5432"
# Remote Logging Server Info
SYSLOG_SERVER="192.168.1.10"
SYSLOG_PORT="514"
# Filter specific traffic and remove potentially noisy logs (AND, !=)
SELECT_FILTERS="WHERE firewall_blocked is TRUE"
# File location to store the timestamp of the last message sent remotely
LAST_TIMESTAMP_FILE="/var/lib/UVMLogCollectorFwLastTimestamp"
# Read the last timestamp value from the file, set var with value - if file doesnt exist, set time to 1 day ago
if [[ -f "$LAST_TIMESTAMP_FILE" ]]; then
LAST_TIMESTAMP=$(cat "$LAST_TIMESTAMP_FILE")
else
LAST_TIMESTAMP=$(date --date='-1 day' +%Y-%m-%dT%H:%M:%S.000)
echo $LAST_TIMESTAMP > $LAST_TIMESTAMP_FILE
fi
# PSQL Query - Key names (and leading 'uvm' tag) hardcoded to match OEM logging format (to maintain consistency when processing both uvm logs and these logs together)
# some sessions table columns are missing - largely useless ones - add them back if you want
PSQL_SELECT="
SELECT
replace(
replace(
concat('uvm[0]: ',
json_build_object(
'reason','RULE',
'appName','Firewall',
'sessionEvent',
json_build_object(
'sessionId',sessions.session_id,
'timeStamp',sessions.time_stamp,
'bypassed',sessions.bypassed,
'entitled',sessions.entitled,
'protocol',sessions.protocol,
'protocolName', CASE WHEN sessions.protocol = 6 THEN 'TCP' WHEN sessions.protocol = 17 THEN 'UDP' ELSE sessions.protocol::text END,
'hostname',sessions.hostname,
'username',sessions.username,
'policyId',sessions.policy_id,
'policyRuleId',sessions.policy_rule_id,
'localAddr',sessions.local_addr,
'remoteAddr',sessions.remote_addr,
'CClientAddr',sessions.c_client_addr,
'CServerAddr',sessions.c_server_addr,
'CServerPort',sessions.c_server_port,
'CClientPort',sessions.c_client_port,
'SClientAddr',sessions.s_client_addr,
'SServerAddr',sessions.s_server_addr,
'SServerPort',sessions.s_server_port,
'SClientPort',sessions.s_client_port,
'clientIntf',sessions.client_intf,
'serverIntf',sessions.server_intf,
'clientCountry',sessions.client_country,
'clientLatitude',sessions.client_latitude,
'clientLongitude',sessions.client_longitude,
'serverCountry',sessions.server_country,
'serverLatitude',sessions.server_latitude,
'serverLongitude',sessions.server_longitude,
'tagsString',sessions.tags
),
'timeStamp',sessions.time_stamp,
'flagged',sessions.firewall_flagged,
'blocked',sessions.firewall_blocked,
'ruleId',sessions.firewall_rule_index,
'class','class com.untangle.app.firewall.FirewallEvent'
)
)
,' : ', ':')
,', ',',')
FROM reports.sessions $SELECT_FILTERS AND time_stamp > '$LAST_TIMESTAMP'
ORDER BY time_stamp DESC"
# Run query against 'uvm' database using 'reports' schema in the 'sessions' table - additional arguments added for parsable output - tac used to produce newest messages at bottom
RESULT="$(/bin/psql -t -A -h $DBHOST -p $DBPORT -U $DBUSER -d $DB -P pager=off -c "$PSQL_SELECT" | tac)"
# Send query results to syslog server - one line per packet (logger jumbles output when parsing entire multi-line variable at once)
while IFS= read -r line; do
/bin/logger -dn$SYSLOG_SERVER -P$SYSLOG_PORT --rfc3164 $line
done <<< "$RESULT"
# Store the latest timestamp value in the file - ensure that logs aren't repeatedly sent in the future
NEW_LAST_TIMESTAMP=$(echo "$RESULT" | grep -o '{.*}' | awk 'BEGIN { FS="|"; } { print $1; }' | tail -n 1 | /bin/jq -r '.sessionEvent.timeStamp')
# If there are no logs in searched timeframe, just exit (retaining last timestamp value)
if [[ -z "$NEW_LAST_TIMESTAMP" ]]; then
exit 0
fi
echo "$NEW_LAST_TIMESTAMP" > "$LAST_TIMESTAMP_FILE"
exit 0
Querying Blocked Threat Prevention Non-Web Events
Code:
#!/bin/bash
# Ensure commands are present before running
for packages in psql jq tac logger; do
checkbin=$(which ${packages} &>/dev/null)
[[ ${?} = 1 ]] && echo "Missing program ${packages}, can't continue without it. Exiting." && exit 1
done
umask 137
# Postgres DB Info
DB="uvm"
DBUSER="postgres"
DBHOST="127.0.0.1"
DBPORT="5432"
# Remote Logging Server Info
SYSLOG_SERVER="192.168.1.10"
SYSLOG_PORT="514"
# Filter specific traffic and remove potentially noisy logs (AND, !=)
SELECT_FILTERS="WHERE threat_prevention_blocked is TRUE"
# File location to store the timestamp of the last message sent remotely
LAST_TIMESTAMP_FILE="/var/lib/UVMLogCollectorTPNWLastTimestamp"
# Read the last timestamp value from the file, set var with value - if file doesnt exist, set time to 1 day ago
if [[ -f "$LAST_TIMESTAMP_FILE" ]]; then
LAST_TIMESTAMP=$(cat "$LAST_TIMESTAMP_FILE")
else
LAST_TIMESTAMP=$(date --date='-1 day' +%Y-%m-%dT%H:%M:%S.000)
echo $LAST_TIMESTAMP > $LAST_TIMESTAMP_FILE
fi
# PSQL Query - Key names (and leading 'uvm' tag) hardcoded to match OEM logging format (to maintain consistency when processing both uvm logs and these logs together)
# some sessions table columns are missing - largely useless ones - add them back if you want
PSQL_SELECT="
SELECT
replace(
replace(
concat('uvm[0]: ',
json_build_object(
'reason','DEFAULT',
'appName','Threat_Prevention',
'sessionEvent',
json_build_object(
'sessionId',sessions.session_id,
'timeStamp',sessions.time_stamp,
'bypassed',sessions.bypassed,
'entitled',sessions.entitled,
'protocol',sessions.protocol,
'protocolName', CASE WHEN sessions.protocol = 6 THEN 'TCP' WHEN sessions.protocol = 17 THEN 'UDP' ELSE sessions.protocol::text END,
'hostname',sessions.hostname,
'username',sessions.username,
'policyId',sessions.policy_id,
'policyRuleId',sessions.policy_rule_id,
'localAddr',sessions.local_addr,
'remoteAddr',sessions.remote_addr,
'CClientAddr',sessions.c_client_addr,
'CServerAddr',sessions.c_server_addr,
'CServerPort',sessions.c_server_port,
'CClientPort',sessions.c_client_port,
'SClientAddr',sessions.s_client_addr,
'SServerAddr',sessions.s_server_addr,
'SServerPort',sessions.s_server_port,
'SClientPort',sessions.s_client_port,
'clientIntf',sessions.client_intf,
'serverIntf',sessions.server_intf,
'clientCountry',sessions.client_country,
'clientLatitude',sessions.client_latitude,
'clientLongitude',sessions.client_longitude,
'serverCountry',sessions.server_country,
'serverLatitude',sessions.server_latitude,
'serverLongitude',sessions.server_longitude,
'tagsString',sessions.tags
),
'serverReputation',sessions.threat_prevention_server_reputation,
'serverCategories',sessions.threat_prevention_server_categories,
'clientReputation',sessions.threat_prevention_client_reputation,
'clientCategories',sessions.threat_prevention_client_categories,
'timeStamp',sessions.time_stamp,
'flagged',sessions.threat_prevention_flagged,
'blocked',sessions.threat_prevention_blocked,
'ruleId',sessions.threat_prevention_rule_id,
'class','class com.untangle.app.threat_prevention.ThreatPreventionEvent'
)
)
,' : ', ':')
,', ',',')
FROM reports.sessions $SELECT_FILTERS AND time_stamp > '$LAST_TIMESTAMP'
ORDER BY time_stamp DESC"
# Run query against 'uvm' database using 'reports' schema in the 'sessions' table - additional arguments added for parsable output - tac used to produce newest messages at bottom
RESULT="$(/bin/psql -t -A -h $DBHOST -p $DBPORT -U $DBUSER -d $DB -P pager=off -c "$PSQL_SELECT" | tac)"
# Send query results to syslog server - one line per packet (logger jumbles output when parsing entire multi-line variable at once)
while IFS= read -r line; do
/bin/logger -dn$SYSLOG_SERVER -P$SYSLOG_PORT --rfc3164 $line
done <<< "$RESULT"
# Store the latest timestamp value in the file - ensure that logs aren't repeatedly sent in the future
NEW_LAST_TIMESTAMP=$(echo "$RESULT" | grep -o '{.*}' | awk 'BEGIN { FS="|"; } { print $1; }' | tail -n 1 | /bin/jq -r '.sessionEvent.timeStamp')
# If there are no logs in searched timeframe, just exit (retaining last timestamp value)
if [[ -z "$NEW_LAST_TIMESTAMP" ]]; then
exit 0
fi
echo "$NEW_LAST_TIMESTAMP" > "$LAST_TIMESTAMP_FILE"
exit 0