I use pfSense as my home router and firewall with the
pfBlockerNG
package to eliminate ads and trackers online. I love everything about it,
except the reporting interface. It’s slow and clunky. I wanted to get the data
into ClickHouse so I can create dashboards with
Grafana. Unfortunately, pfBlockerNG only logs data to
the local filesystem.
This post is for folks who want to export data in log files from pfSense to a
central log server. We’ll cover how to do this for the pfBlockerNG DNSBL log,
but it will work for any other service logs that don’t use syslog, like
pfSense zeek.
ClickHouse is an efficient and highly performant
columnar database with a lot of impressive features. The use of MATERIALIZED VIEWS, which traditional RDBMS folks would call INSERT TRIGGERS allow you
to chain inserts and aggregate data into other tables. Using this workflow,
you can create entire data processing pipelines inside of ClickHouse. The
native
AggregatingMergeTree
table is often used to aggregate data, but it’s not always to best solution.
Using a materialized view with a destination AggregatingMergeTree allows
you to transform an event stream into a pre-rendered timeseries table.
AggregatingMergeTree tables use special aggregating columns to store
aggregation states. These aggregation states are not finalized until they are
merged by an aggregate merge function. Aggregation states can be simple, like
min() or max(), or complex, like uniq(). With simple states, only one or
two values need to be stored. The min()/max() states need only store the
current value. avg() can store the value and the number of data points so a
new insert can calculate the new average and increment the number data points.
Complex aggregation states require more space to maintain. Depending on
cardinality, uniqState() columns can be large; in one instance, the
uniqState() required almost 50% of the storage space to store the full
fidelity data in the source table. Performance using uniqMerge() against the
AggregatingMergeTree table proved 3 to 5 times SLOWER than performing
the uniq() query directly against the raw data.
In this article, we’ll explore a few different ways to work-around this issue
and do something cool!
Monitorama is my favorite conference.
Jason delivers an event that creates
community and belonging with speakers who educate the audience on a wide array
of topics. Every year, I hear attendees praising the event, the content, the
venue, the location, and the sense of community. I think what most people
don’t realize is this all intentional. Jason has gone to great lengths to
create an event that ticks all of these boxes. I’ve had the privilege of
helping him out with the event in Portland in since 2016, and the Berlin,
Amsterdam, and Baltimore events. He’s shared a lot of his secrets to the event
over the years, and I think it’s worth sharing some of the wisdom he’s shared
with me and the other event staff over the years.