Skip to content

Instantly share code, notes, and snippets.

@eschen42

eschen42/ulogd2sql.icn

Created October 22, 2024 15:04
Show Gist options
  • Save eschen42/9dd0add02d8585c7da30122d476ee9a2 to your computer and use it in GitHub Desktop.
Save eschen42/9dd0add02d8585c7da30122d476ee9a2 to your computer and use it in GitHub Desktop.
Download ZIP
Parse ulogd syslog lines to SQLite insertion statements
Raw
ulogd2sql.icn
#!/usr/local/bin/icon
############################################################################
#
# File: ulogd2sql.icn
#
# Subject: Parse ulogd syslog lines to SQLite insertion statements
#
# Author: Art Eschenlauer, adapted from rough-in by Microsoft Copilot
#
# Date: October 19, 2024
#
############################################################################
#
# This file is in the public domain.
#
############################################################################
#
# usage: grep ulogd /var/log/<ulogd.log> | ./ulogd2sql.icn
#
# This program parses input lines in the format
#
# input_string := "Oct 19 13:58:30 xmi_firewall ulogd[1709]: [DESTROY] _
# ORIG: SRC=10.6.16.137 DST=216.239.32.223 PROTO=TCP SPT=57886 _
# DPT=443 PKTS=2 BYTES=104 , _
# REPLY: SRC=216.239.32.223 DST=192.168.1.2 _
# PROTO=TCP SPT=443 DPT=57886 PKTS=1 BYTES=40"
#
# The output is SQLite3-compatible insertion statements for the following
# SQLite3 schema:
#
# CREATE TABLE Devices (
# device_id INTEGER PRIMARY KEY AUTOINCREMENT,
# device_name TEXT NOT NULL
# );
#
# CREATE TABLE IPAddresses (
# ip_id INTEGER PRIMARY KEY AUTOINCREMENT,
# ip_address TEXT NOT NULL UNIQUE
# );
#
# CREATE TABLE IPAddresses (
# ip_id INTEGER PRIMARY KEY AUTOINCREMENT,
# ip_address TEXT NOT NULL UNIQUE
# );
#
# CREATE TABLE Protocols (
# proto_id INTEGER PRIMARY KEY AUTOINCREMENT,
# proto_name TEXT NOT NULL UNIQUE
# );
#
# CREATE TABLE Logs (
# log_id INTEGER PRIMARY KEY AUTOINCREMENT,
# log_timestamp TEXT NOT NULL,
# orig_src_ip INTEGER,
# orig_dst_ip INTEGER,
# orig_proto INTEGER,
# orig_spt INTEGER,
# orig_dpt INTEGER,
# orig_pkts INTEGER,
# orig_bytes INTEGER,
# reply_src_ip INTEGER,
# reply_dst_ip INTEGER,
# reply_proto INTEGER,
# reply_spt INTEGER,
# reply_dpt INTEGER,
# reply_pkts INTEGER,
# reply_bytes INTEGER,
# FOREIGN KEY (orig_src_ip) REFERENCES IPAddresses(ip_id),
# FOREIGN KEY (orig_dst_ip) REFERENCES IPAddresses(ip_id),
# FOREIGN KEY (orig_proto) REFERENCES Protocols(proto_id),
# FOREIGN KEY (reply_src_ip) REFERENCES IPAddresses(ip_id),
# FOREIGN KEY (reply_dst_ip) REFERENCES IPAddresses(ip_id),
# FOREIGN KEY (reply_proto) REFERENCES Protocols(proto_id)
# );
#
############################################################################
#
# Links: none
#
############################################################################
# This code was roughed in by the Microsoft Copilot AI, which did a passable
# job of generating Icon-like code, but much correction was required.
procedure main()
local iso_zulu, iso_date, year, tz_offset, timestamp, host
local input_string
local month, day, time
local orig_src, orig_dst, orig_proto, orig_spt, orig_dpt, orig_pkts,
orig_bytes
local reply_src, reply_dst, reply_proto, reply_spt, reply_dpt,
reply_pkts, reply_bytes
local sql
iso_zulu := open("date -Iseconds", "pr") |
stop(&progname, ": failed to open pipe to read date")
# expect a result similar to 2024-10-19T11:53:43-05:00
iso_date := read(iso_zulu) |
stop(&progname, ": failed to read date from pipe")
close(iso_zulu)
#DEBUG write("iso_date is ", iso_date)
iso_date ? year := tab(upto('-'))
\year | stop(&progname, ": failed to extract year")
tz_offset := right(iso_date, 6)
# iso_date ? tz_offset := (tab(many('0123456789-:T')), move(6))
\tz_offset | stop(&progname, ": failed to extract tz_offset")
# # Example input_string:
# input_string := "Oct 19 13:58:30 xmi_firewall ulogd[1709]: [DESTROY] _
# ORIG: SRC=10.6.16.137 DST=216.239.32.223 PROTO=TCP SPT=57886 DPT=443 _
# PKTS=2 BYTES=104 , REPLY: SRC=216.239.32.223 DST=192.168.1.2 PROTO=TCP _
# SPT=443 DPT=57886 PKTS=1 BYTES=40"
# Oct 19 18:59:15 xmi_firewall ulogd[1709]: [DESTROY] _
# ORIG: SRC=10.6.16.137 DST=13.70.79.200 PROTO=TCP SPT=60078 DPT=443 _
# PKTS=17 BYTES=18783 , REPLY: SRC=13.70.79.200 DST=192.168.1.2 PROTO=TCP _
# SPT=443 DPT=60078 PKTS=21 BYTES=5787
# root@syslog:~/fw_syslog/grok # grep 'ulogd.*PROTO=UDP' /var/log/ntwrk.log | tail -n 1
# Oct 19 18:53:06 xmi_firewall ulogd[1709]: [DESTROY] ORIG: SRC=10.6.16.49 DST=10.6.16.1 PROTO=UDP SPT=55510 DPT=53 PKTS=1 BYTES=68 , REPLY: SRC=10.6.16.1 DST=10.6.16.49 PROTO=UDP SPT=53 DPT=55510 PKTS=1 BYTES=107
# root@syslog:~/fw_syslog/grok # grep 'ulogd.*PROTO=TCP' /var/log/ntwrk.log | tail -n 1
# Oct 19 18:53:06 xmi_firewall ulogd[1709]: [DESTROY] ORIG: SRC=10.6.16.49 DST=142.250.191.228 PROTO=TCP SPT=30533 DPT=80 PKTS=14 BYTES=865 , REPLY: SRC=142.250.191.228 DST=192.168.1.2 PROTO=TCP SPT=80 DPT=30533 PKTS=21 BYTES=24969
every input_string := !&input do {
orig_src := orig_dst := orig_proto := orig_spt := orig_dpt :=
orig_pkts := orig_bytes := ""
reply_src := reply_dst := reply_proto := reply_spt := reply_dpt :=
reply_pkts := reply_bytes := ""
input_string ? (
(
if not find("xmi_firewall ulogd")
then next
else &null
),
(
if not find(" PROTO=" || ("TCP" | "UDP") || " ")
then next
else &null
),
#DEBUG write(&errout, "... trace ..."),
(month := tab(upto(' '))),
tab(many(' ')),
(day := tab(upto(' '))),
tab(many(' ')),
(time := tab(upto(' '))),
tab(many(' ')),
#DEBUG write(&errout, month),
#DEBUG write(&errout, day),
#DEBUG write(&errout, time),
(host := tab(upto(' ')),
tab(many(' '))),
tab(find("SRC=")),
tab(match("SRC=")),
(orig_src := tab(find(" "))),
tab(find("DST=")),
tab(match("DST=")),
(orig_dst := tab(find(" "))),
tab(find("PROTO=")),
tab(match("PROTO=")),
(orig_proto := tab(find(" "))),
tab(find("SPT=")),
tab(match("SPT=")),
(orig_spt := tab(find(" "))),
tab(find("DPT=")),
tab(match("DPT=")),
(orig_dpt := tab(find(" "))),
tab(find("PKTS=")),
tab(match("PKTS=")),
(orig_pkts := tab(find(" "))),
tab(find("BYTES=")),
tab(match("BYTES=")),
(orig_bytes := tab(find(" "))),
tab(find("REPLY: ")),
tab(find("SRC=")),
tab(match("SRC=")),
(reply_src := tab(find(" "))),
tab(find("DST=")),
tab(match("DST=")),
(reply_dst := tab(find(" "))),
tab(find("PROTO=")),
tab(match("PROTO=")),
(reply_proto := tab(find(" "))),
tab(find("SPT=")),
tab(match("SPT=")),
(reply_spt := tab(find(" "))),
tab(find("DPT=")),
tab(match("DPT=")),
(reply_dpt := tab(find(" "))),
tab(find("PKTS=")),
tab(match("PKTS=")),
(reply_pkts := tab(find(" "))),
tab(find("BYTES=")),
tab(match("BYTES=")),
(reply_bytes := tab(many(&digits)))
) | (stop(&errout, "no match: ", input_string), next)
month := case month of {
"Jan": "01"
"Feb": "02"
"Mar": "03"
"Apr": "04"
"May": "05"
"Jun": "06"
"Jul": "07"
"Aug": "08"
"Sep": "09"
"Oct": "10"
"Nov": "11"
"Dec": "12"
}
#DEBUG write(&errout, month)
\year | stop("year is null for: ", input_string)
\month | stop("month is null for: ", input_string)
\day | stop("day is null for: ", input_string)
\time | stop("time is null for: ", input_string)
\tz_offset | stop("tz_offset is null for: ", input_string)
#DEBUG write(&errout, year)
#DEBUG write(&errout, month)
#DEBUG write(&errout, day)
#DEBUG write(&errout, time)
#DEBUG write(&errout, tz_offset)
timestamp := year || "-" || month || "-" || day || "T" || time ||
tz_offset
#DEBUG write(&errout, timestamp)
sql := "-- " || input_string || "\n" ||
"INSERT or IGNORE INTO Devices (device_name) VALUES ('" ||
host || "');\n" ||
"INSERT or IGNORE INTO IPAddresses (ip_address) VALUES ('" ||
orig_src || "');\n" ||
"INSERT or IGNORE INTO IPAddresses (ip_address) VALUES ('" ||
orig_dst || "');\n" ||
"INSERT or IGNORE INTO Protocols (proto_name) VALUES ('" ||
orig_proto || "');\n" ||
"INSERT or IGNORE INTO IPAddresses (ip_address) VALUES ('" ||
reply_src || "');\n" ||
"INSERT or IGNORE INTO IPAddresses (ip_address) VALUES ('" ||
reply_dst || "');\n" ||
"INSERT or IGNORE INTO Protocols (proto_name) VALUES ('" ||
reply_proto || "');\n" ||
"INSERT INTO Logs (log_timestamp, device_id, orig_src_ip, orig_dst_ip, " ||
"orig_proto, orig_spt, orig_dpt, orig_pkts, orig_bytes, " ||
"reply_src_ip, reply_dst_ip, reply_proto, reply_spt, reply_dpt, " ||
"reply_pkts, reply_bytes)\nVALUES ('" || timestamp || "', " ||
"(SELECT device_id FROM Devices WHERE device_name = '" ||
host || "'), " ||
"(SELECT ip_id FROM IPAddresses WHERE ip_address = '" ||
orig_src || "'), " ||
"(SELECT ip_id FROM IPAddresses WHERE ip_address = '" ||
orig_dst || "'), " ||
"(SELECT proto_id FROM Protocols WHERE proto_name = '" ||
orig_proto || "'), " ||
orig_spt || ", " || orig_dpt || ", " || orig_pkts || ", " ||
orig_bytes || ", " ||
"(SELECT ip_id FROM IPAddresses WHERE ip_address = '" ||
reply_src || "'), " ||
"(SELECT ip_id FROM IPAddresses WHERE ip_address = '" ||
reply_dst || "'), " ||
"(SELECT proto_id FROM Protocols WHERE proto_name = '" ||
reply_proto || "'), " ||
reply_spt || ", " || reply_dpt || ", " || reply_pkts || ", " ||
reply_bytes || ");\n"
write(sql)
}
end
# vim: ai sw=2 ts=2 et nu :
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment