(A shameless plug, buy my wife’s book at https://www.ameliafranz.com/)

Mid Year 2026

As we approach the middle of the year in the first year past the decade’s midpoint, change continues to accelerate. One graduation last week. Another this week. One child returns, another leaves in the Fall for college. The oldest re-evaluates their life and career choices. With our youngest out of high school and her book published, my wife wife re-enters the workforce in her 50’s.

In a month, I will take a road trip with my parents to help them move to Texas. My brother has already moved. Oh, how the roles reverse — having lived in Texas from 1989 to 2006.

In my last post I discussed writing poetry again. That did not last. I started a new job in mid-October and the frenetic pace of AI adoption in personal and professional projects has not slowed.

But I also have not stopped reading.

Yes, the “books in progress” metric is too high.

Some other firsts.

Baseball. I’ve been doing Raven’s games for the last few years, but my youngest and I went to an O’s game. It has probably been since the 1980’s since I went to a baseball game. Maybe it was even Memorial Stadium.

I actually enjoyed it. After 15 years, starting to feel like a native.

O’s vs. Red Socks: a real home run derby

Beer brats. While my wife was away on her book too in Mississippi, we made some dad food! And, yeah, I probably used too much beer.

The beer brats were a hit with the “boys”

But here is the list.

History and Politics

• King of Kings: The Iranian Revolution: A Story of Hubris, Delusion and Catastrophic Miscalculation by Scott Anderson (2025)
• General and the Jaguar: Pershing’s Hunt for Pancho Villa: A True Story of Revolution and Revenge by Eileen Welsome (2009)
• The Hour of the Predator: Encounters with the Autocrats and Tech Billionaires Taking Over the World by Giuliano da Empoli (2025)
• Challenger: A True Story of Heroism and Disaster on the Edge of Space by Adam Higginbotham (2024)
• Tunisgrad by William Collins (2026)
• The Age of Acrimony: How Americans Fought to Fix Their Democracy, 1865–1915 by Jon Grinspan (2021)
• Road of Bones: The Siege of Kohima 1944 — The Epic Story of the Last Great Stand of Empire by Fergal Keane (2013)
• Bernardo de Gálvez: Spanish Hero of the American Revolution by Gonzalo M. Quintero Saravia (2018)
• Washington’s General: Nathanael Greene and the Triumph of the American Revolution by Terry Golway (2006)
• The Fate of the Day: The War for America, Fort Ticonderoga to Charleston, 1777–1780 by Rick Atkinson (2025)
• Race and Reunion: The Civil War in American Memory by David W. Blight (2002)

Tech

• Vibe Coding: Building Production-Grade Software With GenAI, Chat, Agents, and Beyond by Gene Kim and Steve Yegge (2025)

Fiction

• The Longest Man-Made Beach In The World: Biloxi Stories by Amelia Franz (2025)
• I Am a Cat: A new translation of the original Japanese cat classic by Natsume Soseki (Author), Nick Bradley (Translator) in 2005.

2025 marked the first full year where I was IC again!

For most of the year I was able to spend heads-down and hands-on without the distractions of meetings. If 2025 was the year of Kubernetes and Elasticsearch, let’s see where 2025 took me! Some of that did persist into the new year, especially Elastic, but the topics below.

(NOTE: I used gemini-cli to review my commits from cheetzsheetz but this is all humanly-written) with the top tools in each category being:

• Astral UV
• Pydantic AI
• DuckDB
• Suricata
• Trivy
• CrowdStrike FalconPy
• CryptPad

Python

Even with AI, Python is one of those things you have to do the time to keep fluency. In the beginning of 2025 I had a terrible round 2 at MongoDB for an SRE Security Lead where I totally froze on a basic Python coding problem. Fortunately, I got better but it was about spending hours each day on dedicated coding tasks.

• Marimo became a first-class tool in my notebook for exploring APIs and data.
• Pandas became my go-to tool above for slicing and dicing DataFrames in both standalone scripts and in Marimo notebooks.
• UV is now the first tool I install for managing Python and packages on Linux or Mac.
• Python Private Packages on GitLab.
• Simple Schedulers such as schedule or APSchedular when you don’t want to use something more complex like Celery.
• Asyncio is unavoidable these days due to Agent Frameworks and event loops. Get good at it.

AI

Obviously, give the pace of change in frameworks, models, and capabilities of self-hosted and SaaS models, this is tough to keep up with. I did take a bit of a break mid-year because I was burned out of working with LLMs but by the Fall I was back in the game, refreshed.

• Agent Frameworks: Agno, Pydantic, Strands, and Google ADK.
• MCP Servers where I focused on DuckDB and ClickHouse with a bit of Kubernetes at the end of the year.
• Command-Line Tools: Python-LLM and AIChat
• Agentic Editing (Zed and Gemini and Codex CLIs)

Data and Observability

As the saying say, “security is a data problem.” I spent a lot of time with SQL, mostly with ClickHouse or DuckDB but as I tried to find tools that Mac Unified Logs it led me back to some old like NXLog and Syslog-NG which provide varying levels of support. I dusted off my Vector/VRL skills.

• ClickHouse Local Server and CHDB.
• DuckDB
• BindPlane
• Openobserve
• Syslog-NG
• NXLog
• Vector

Networking and Security

I got my start in network security and the first 15 years of my career (before I got into Cloud) was heavily focused in this space. I forgot some things and this year I had to re-learn when I had to manage Palo Alto firewalls. Routing protocols and propagation IKE Main Mode and Agreessive Mode, all these things I hadn’t touched since the Mid 2000s. I ended last year setting up a tap on my home network and right before Christmas I discovered that I had a year’s worth of Suricata data to dig into.

• IPSEC and IKE
• BGP
• Suricata
• Zeek
• Atomic Red Team

DevOps and DevSecOps

Given I took a new Cloud Security role (or at least kind of cloud security-ish) role in Apri, I had to get back some of my Terraform and Ansible skills but spent a lot of time in with container and IAC static code scanning with Trivy, which I preferred to any of the hot mess from CrowdStrike.

• European Cloud Providers like Scaleway and Exoscale
• Terraform and Bicep
• Trivy
• Ansible
• AWS CodeCommit, CodeBuild and CodeDeploy
• Gitlab CI

Security Products & Platforms

This year I used 3 different SIEM/SOAR platforms professionally (Elastic, CrowdStrike LogScale, and Google Chronicle/SecOps) but I did dip my toes into Sentinel to prepare for a job that I had multiple rounds but eventually got ghosted in January after inauguration day, when the job market seized up.

• Azure Sentinel
• CrowdStrike Vulnerability Management and Cloud Security Posture Management
• FalconPy
• Palo Alto Cloud Firewalls
• Sentinel One
• Google SecOps

Miscellaneous

As part of AI you can’t avoid the problem of web search, so I dug into the seedy (and expensive) world of web Scraping. And for my pro-Democracy tech work I set up a web site on Ghost (self-hosting) as well used (and tested local deployment) of CryptPad.

• Web Scraping
• CryptPad
• Ghost

2025: The Year of AI Applicants

I first encountered AI-generated resumes (and candidates using AI live during a screen interview) about a year ago, but this year I’ve seen this practice sky-rocket.

The pattern I’ve seen is a fully polished resume that has nearly 100% coverage of all the skills in the job description. The crudest form looks like this.

So now when I’m manually reviewing resumes I start with LinkedIn because candidates are less likely to fake their public profile (unless they are a totally fake profile, but that is a different problem.)

So I was curious how difficult that would be to implement.

The basic problem was to detect differences between the submitted resume and a LinkedIn profile.

Quick Design Session with Gemini

When I posed to problem to Gemini, it first wanted me to extract real data from the sources and do data validation — either with Pydantic or Python Dataclasses, I can’t remember.

I ignored this advise after sleeping on it. But it was useful to talk through the problem and debate whether LLMs and RAG were the most useful.

The key insight was that I wanted to focus on skill differences between the LinkedIn profile (which I considered accurate) and the resume, which was likely fake.

Enter Agno and Qdrant

Since I’ve already been using Agents with Knowledge (yes, this is a simple script, not a full-blown agent) and ingesting PDF, Markdown, and Website into Qdrant and other vector stores, I thought I’d try that first.

Which components did I use?

• SQLite for Memory and ContentDB — memory is for saving session information (I’ll show below) and I’ve found ContentDB helps Knowledge do a better job.
• Qdrant for my Vector store which I stood up on Podman.

    timestamp_str = datetime.datetime.now().strftime("%y%m%d%M%S")
    my_embedder = GeminiEmbedder()
    memory_db = SqliteDb(db_file="agno-memory.db")
    contents_db = SqliteDb(db_file=f"agno-knowledge-contents-{timestamp_str}.db")

    vector_db = Qdrant(
        name="Qdrant Job Description",
        collection=f"vectors-{timestamp_str}",
        url="http://localhost:6333",
        embedder=my_embedder,
    )

Notice the timestamp_str because I right now I’m just running the comparison for each candidate to limit search space. We will end up having timestamped collections like this.

This isn’t super interesting given so little data has been embeded.

Next load the LinkedIn PDF and Resume with PDFReader

    knowledge = Knowledge(vector_db=vector_db, contents_db=contents_db)

    knowledge.add_content(
        name="Applicant LinkedIn Profile",
        path=f"{kb_path}/linkedin.pdf",
        reader=PDFReader(),
    )

    knowledge.add_content(
        name="Applicant Resume",
        path=f"{kb_path}/resume.pdf",
        reader=PDFReader(),
    )

This will be stored in the ContentDB

% sqlite3 agno-knowledge-contents-2512215726.db
SQLite version 3.51.0 2025-06-12 13:14:41
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE agno_knowledge (
        id VARCHAR NOT NULL, 
        name VARCHAR NOT NULL, 
        description VARCHAR NOT NULL, 
        metadata JSON, 
        type VARCHAR, 
        size BIGINT, 
        linked_to VARCHAR, 
        access_count BIGINT, 
        status VARCHAR, 
        status_message VARCHAR, 
        created_at BIGINT, 
        updated_at BIGINT, 
        external_id VARCHAR, 
        PRIMARY KEY (id)
);
CREATE TABLE agno_schema_versions (
        table_name VARCHAR NOT NULL, 
        version VARCHAR NOT NULL, 
        created_at VARCHAR NOT NULL, 
        updated_at VARCHAR, 
        PRIMARY KEY (table_name)
);
CREATE INDEX idx_agno_schema_versions_created_at ON agno_schema_versions (created_at);
sqlite> select * from agno_knowledge;
eb154723-69eb-5931-b8a1-86d2731b0363|Applicant LinkedIn Profile||null||||0|completed||1766321846|1766321847|
921c90cf-ac37-501e-bbb4-227b53f4084a|Applicant Resume||null||||0|completed||1766321847|1766321847|

Giving the Agent Knowledge

Now all you have to do is pass the objects to the Agent.

agent = Agent(
        name=f"Agno Chat with Applicant Documents",
        model=my_model,
        instructions=[
            "Include sources in your response.",
            "Search your knowledge before answering the question.",
            "Only include the output in your response. No other text.",
            "The sources contain information from a single person who you can refer to as candidate.",
            "Do NOT make up names for candidates like John Doe, only use real names",
        ],
        knowledge=knowledge,
        tools=[ReasoningTools(add_instructions=True)],
        add_datetime_to_context=False,
        markdown=True,
        debug_mode=True,
        db=memory_db,
        add_history_to_context=False,
        num_history_runs=3,
    )

Then run

agent.print_response(
        """Extract the candidates name and location from all sources. Capture all skills and role descriptions listed in both sources
            to identify conflicting information between the Resume and the LinkedIn Profile, examining whether the same content is in each source.
            The LinkedIn profile is likely to be more accurate and the candidate may have added fake skills they do not possess to the resume.
            Provide a confidence score of the likelihood that the resume misrepresents their actual skills where
            100 is completely fake and 1 would be the skills match one to one.
        """,
        stream=True,
        show_full_reasoning=True,
        stream_events=True,
    )

And if you’ll see something like

And the tool calls

The verdict

The candidate is an iOS/Full-Stack developer by trade (per LinkedIn), but the resume presents them as a veteran Security Engineer.

Here is a different profile with the full analysis, where you can see how nicely Agno compared the skills in the resume.

I found good results with Gemini 2.5 Pro and Gemini 3 Flash Preview (Gemini 3 was spotty for some reason) and I thought I’d test https://essential.ai/research/rnj-1 on Ollama and it broke it’s brain!

Also published on my Substack

It has been over five years since I first used Vector, but only in the last several months have I started to take advantage of ClickHouse with S3 as I mentioned on LinkedIn last month.

I also recently stood up a website with Ghost CMS for a non-profit and wanted something cheap and easy (off box) to analyze Nginx and Linux logs.

Since I know that Vector automatically parsed nginx and journald and could easily log to S3 so I gave it a shot.

Create your S3 Bucket & IAM Keypair

Use my CloudFormation template to create the S3 Bucket and IAM credentials that you’ll need to pass to Vector to be able to write JSON events to the bucket.

NOTE: Since CloudFormation outputs are unencrypted, I’ve gotten into a habit of writing the Key to a Parameter so you can delete it later.

S3UserAccessKey:
    Type: AWS::IAM::AccessKey
    Properties:
      UserName: !Ref S3User

  BasicParameter:
    Type: AWS::SSM::Parameter
    Properties:
      Name: !Sub ${S3Bucket}-${S3UserAccessKey}
      Value: !GetAtt S3UserAccessKey.SecretAccessKey
      Type: String

Install Vector Set your Environment Variables

Install the latest vector using your method of choice. I use the APT install script and this as worked across ARM and Intel systems just fine.

You’ll put these in /etc/default/vector so you can avoid putting in your

AWS_ACCESS_KEY_ID=AKIAXXX AWS_SECRET_ACCESS_KEY=YYYYY VECTOR_S3_BUCKET=my-log-bucket

/etc/default/vector

Define your Bucket “Path Schema”

We’ll start at the end focusing on what is most critical on how you will search logs with Clickhouse Local where the path matters.

We’ll get into that later. Trust me. It matters.

sinks:
    s3_bucket:
        type: aws_s3
        inputs:
            - common
        bucket: "${VECTOR_S3_BUCKET}"
        key_prefix: "{{hostname}}/{{event_type}}/%D/"
        region: "us-east-1"
        compression: gzip
        encoding:
           codec: json

This will create a directory structure like this once we have all the event_type's defined. These are essentially your "tables."

The %D will help populated the date structure so you can choose what to search overs, such as:

• asus-pn50/auditd/03/12/25/1741812841-d797e698–3302–4e63-a863–6f4292d04d16.log.gz
• opti3070/journald/03/04/25/1741798470–5f7b4bd9-abb4–42a6–9816-b82c4e7bbe96.log.gz
• ubuntu-s-1vcpu-2gb-nyc3–01/nginx-access/03/13/25/1741824471-f39b039f-be2f-4723-b74f-f5b7e3a495ec.log.gz

(This is where it is helpful to having hostname conventions!)

When you form your SELECT or DESCRIBE from clickhouse you'll be use wildcards to only retrieve a subset of objects.

Define your Data Sources

For basic file monitoring I use the file source and journald to avoid parsing syslog-style messages.

sources:
    journald:
        type: journald

    nginx_access:
        type: file
        include:
            - /var/log/nginx/access.log*
    nginx_error:
        type: file
        include:
            - /var/log/nginx/error.log*
    auditd:
        type: file
        include:
            - /var/log/audit/audit.log
    auth_log:
        type: file
        include:
            - /var/log/auth.log

This will create state files in /var/lib/vector like the following

root@ubuntu-s-1vcpu-2gb-nyc3-01:/var/lib/vector# ls
auditd  auth_log  journald  nginx_access  nginx_error  var_log
root@ubuntu-s-1vcpu-2gb-nyc3-01:/var/lib/vector# ls -al
total 32
drwxr-xr-x  8 vector vector 4096 Mar 13 01:35 .
drwxr-xr-x 46 root   root   4096 Mar 12 13:09 ..
drwxr-xr-x  2 vector vector 4096 Mar 13 01:35 auditd
drwxr-xr-x  2 vector vector 4096 Mar 13 01:35 auth_log
drwxr-xr-x  2 vector vector 4096 Mar 12 16:23 journald
drwxr-xr-x  2 vector vector 4096 Mar 13 01:37 nginx_access
drwxr-xr-x  2 vector vector 4096 Mar 13 01:35 nginx_error

As I was getting the parsers (and the path structure) just right, I would stop vector, delete from the bucket, then delete whichever file and they would be re-ingested. S3 writes are not immediate and sometimes it does take up to 5–10 minutes during your initial ingestion.

Parse and Transform

See the end of the blog for the links to the full code, but transforms do the heavy lifting in VRL and I only used some very simple remaps to parse key value pairs in auditd as well as add the event_type field to each message. In the case of auditd_parse I did delete the "msg" field because it was not ingesting cleaning in ClickHouse, but I'll need to address that later because that field is the only part of the event that contains a timestamp.

transforms:
    auditd_parse:
        type: remap
        inputs:
            - auditd
        source: |
            . = parse_key_value!(.message)
            .event_type = "auditd"
            del(.msg)
            .hostname = get_hostname!()

    auth_parse:
        type: remap
        inputs:
            - auth_log
        source: |
            . = parse_linux_authorization!(.message)
            .event_type = "auth"

    journald_parse:
        type: remap
        inputs:
            - journald
        source: |
            .event_type = "journald"

    common:
        type: remap
        inputs:
            - auth_parse
            - auditd_parse
            - journald_parse
        source: |
            .hostname = get_hostname!()

The common transform at the end allows me to add the common hostname field onto each message.

Parser Development & Testing

Although you can test your VRL on pipelines the best way to do this is either by running vector vrl with an input JSON stream and the code to show the output like this. See my vrl-devtest report scripts and sample data to test this out. This allows you to run across a larger dataset to ensure you are properly handling errors.

This is just the VRL code used by the remap.

.event_type = "auditd"
audit_msg = .msg[0]
audit_regex, err = parse_regex(audit_msg, r'^audit\((?P<time>\d+).*\):')
if err == null {
  .event_timestamp = audit_regex.time
}
del(.msg)
.hostname = get_hostname!()

You then execute with the following command.

vector vrl -i auditd.json -p auditd.vrl -o --print-warnings

When developing the initial parser you can also use the VRL Playground to test your code but it will not include all the functions such as get_hostname()

LLMs were not very helpful for VRL code examples, but there are lots of GitHub projects so searching for parse_regex vector language:yaml in GitHub code search is the best way to find examples.

Before you wire up S3, it does make sense to test locally with just file sources and sinks. The run.sh is just a simple wrapper for vector -vv -c vector.yaml and clean.sh removes the watcher files that are created so you can rapidly retest.

Assessing the Data in S3

Assuming you’ve set your AWS Environment variables, ClickHouse will honor those.

SELECT DISTINCT event_type
FROM s3('https://bucket_name.s3.us-east-1.amazonaws.com/*/*/*/*/*/*.log.gz')

Query id: cd07ac4e-9640-414b-be84-459fc84b8c3d

   ┌─event_type─┐
1. │ auditd     │
   └────────────┘
   ┌─event_type─┐
2. │ auth       │
   └────────────┘
   ┌─event_type─┐
3. │ journald   │
   └────────────┘

3 rows in set. Elapsed: 57.546 sec. Processed 6.20 million rows, 162.91 MB (107.76 thousand rows/s., 2.83 MB/s.)
Peak memory usage: 1.33 GiB.

Remember the path structure allows to you search only subset of the data, for example this counts the various event types in March 2025

SELECT
    count(*) AS cnt,
    event_type
FROM s3('https://bucket_name.s3.us-east-1.amazonaws.com/*/*/03/*/25/*.log.gz')
GROUP BY event_type
ORDER BY cnt ASC

Query id: d1de464f-b6f0-406e-9fbd-20a7dcf0f80c

   ┌─────cnt─┬─event_type─┐
1. │    7717 │ auth       │
2. │   17697 │ auditd     │
3. │ 2181709 │ journald   │
   └─────────┴────────────┘

3 rows in set. Elapsed: 28.226 sec. Processed 2.21 million rows, 58.29 MB (78.19 thousand rows/s., 2.07 MB/s.)
Peak memory usage: 1.33 GiB.

Getting at the Schema

Simply use DESCRIBE to have ClickHouse infer the schema

DESCRIBE TABLE s3('https://bucket_name.s3.us-east-1.amazonaws.com/*/journald/*/*/*/*.log.gz')

And for auditd

And auth which is pretty sparse in comparison.

Digging into the Data

Let’s start with auth… Remember DISTINCT is your friend for starting to characterize.

SELECT DISTINCT (appname, hostname)
FROM s3('https://bucket_name.s3.us-east-1.amazonaws.com/*/auth/*/*/*/*.log.gz')
ORDER BY appname ASC

Query id: 0ad7a767-034d-4728-bfb7-731a0db313a0

    ┌─(appname, hostname)────────────────────────────┐
 1. │ ('CRON','asus-pn50')                           │
 2. │ ('CRON','franz-OptiPlex-7050')                 │
 3. │ ('CRON','opti3070')                            │
 4. │ ('chfn','opti3070')                            │
 5. │ ('dbus-daemon','franz-OptiPlex-7050')          │
 6. │ ('gdm-password]','franz-OptiPlex-7050')        │
 7. │ ('gnome-keyring-daemon','franz-OptiPlex-7050') │
 8. │ ('gpasswd','opti3070')                         │
 9. │ ('groupadd','opti3070')                        │
10. │ ('passwd','opti3070')                          │
11. │ ('pkexec','franz-OptiPlex-7050')               │
12. │ ('polkitd','opti3070')                         │
13. │ ('polkitd','asus-pn50')                        │
14. │ ('sshd','asus-pn50')                           │
15. │ ('sshd','opti3070')                            │
16. │ ('sshd','franz-OptiPlex-7050')                 │
17. │ ('sudo','opti3070')                            │
18. │ ('sudo','asus-pn50')                           │
19. │ ('sudo','franz-OptiPlex-7050')                 │
20. │ ('systemd-logind','asus-pn50')                 │
21. │ ('systemd-logind','opti3070')                  │
22. │ ('systemd-logind','franz-OptiPlex-7050')       │
23. │ ('systemd-machined','asus-pn50')               │
24. │ ('useradd','opti3070')                         │
25. │ ('userdel','opti3070')                         │
26. │ ('usermod','opti3070')                         │
    └────────────────────────────────────────────────┘

What we can already tell from just this table that

• franz-OptiPlex-7050 is the only system running Ubuntu desktop
• Users were added and deleted on opti3070

So pkexec was only run on one system let's take peek

SELECT (timestamp, message)
FROM s3('https://bucket.s3.us-east-1.amazonaws.com/*/auth/*/*/*/*.log.gz')
WHERE appname = 'pkexec'

There is obviously a lot more you can do with the data depending on your SQL skills, but this shows you how to get started!

Putting it all together

data_dir: "/var/lib/vector"
api:
    enabled: true
sources:
    journald:
        type: journald

    auditd:
        type: file
        include:
            - /var/log/audit/audit.log

    auth_log:
        type: file
        include:
            - /var/log/auth.log

transforms:
    auditd_parse:
        type: remap
        inputs:
            - auditd
        source: |
            . = parse_key_value!(.message)
            .event_type = "auditd"
            audit_msg = .msg[0]
            audit_regex, err = parse_regex(audit_msg, r'^audit\((?P<time>\d+).*\):')
            if err == null {
              .event_timestamp = audit_regex.time
            }
            del(.msg)
            .hostname = get_hostname!()

    auth_parse:
        type: remap
        inputs:
            - auth_log
        source: |
            . = parse_linux_authorization!(.message)
            .event_type = "auth"

    journald_parse:
        type: remap
        inputs:
            - journald
        source: |
            .event_type = "journald"

    common:
        type: remap
        inputs:
            - auth_parse
            - auditd_parse
            - journald_parse
        source: |
            .hostname = get_hostname!()

sinks:
    s3_bucket:
        type: aws_s3
        inputs:
            - common
        bucket: "${VECTOR_S3_BUCKET}"
        key_prefix: "{{hostname}}/{{event_type}}/%D/"
        region: "us-east-1"
        compression: gzip
        encoding:
           codec: json

This morning when I finished up my workout at the local YMCA, the snow was softly falling around the American flag in front.

I tried to take a photo on my phone, but it was inadequate for the moment.

For once, the flag was not at half mast as it is too far too often — usually for dead children or others slain in mass shootings.

As someone who was raised in a military family (and who served myself for over a decade) seeing our flag sometimes makes me sad. Whether it is on the back of pickups or Jeeps with blue flags or on the houses of NIMBY neighbors proudly upholding the tradition of racial segregation in Baltimore County.

Less than 30 minutes from where we live now, there is a parade field on Edgewood Area of APG. We lived across the street in the Major’s quarters.

It was next to the post chapel. These were houses built in the early 1900s. They had steam heat that hissed and clanged in winter. And probably a lot of lead paint. There was a large parade field where we flew kites and made snow angels, watching the flakes fall down onto our faces in the darkness.

These are fond flag memories.

I remember crackling speakers on parade fields on the Army bases where I grew up, or marched myself during Basic Training or AIT.

That Lee Greenwood song that made me feel something in my Class-A’s back then, but now it just makes me sick.

(He also sells Bibles, I hear.)

There is real patriotism and there is the fake chest thumping sort that could give a damn about the veterans and their service, where the military is a prop for weak men who want to appear strong. Where service is packaged and sold.

(Yes, I’m thinking about the Mission BBQ “Deuce and Half” truck with flags flying that I saw today on York Road. And the fake pre-packaged patriotism when minimum wage workers slop your mac and cheese and brisket on your plate and ask what kind of sauces you want. You see patches on the wall for units you actually served in. No one there has a clue what the 49th “Lone Star” Armored Division or the 90th Army Reserve Command and the “TO” patch.)

On February 5th, I took off work early and attended my first protest outside of the Treasury Department. There was a buzz in the crowd when Senator Warren came up to the podium. I left with the group from Indivisible Baltimore when Jamie Raskin quoted Thomas Paine. I’m still thinking about that.

On a day when I’ve seen members of CISA resign — or be fired.

On a day that I’m hearing about measles outbreaks in Texas, a state I called home. (There is a TB outbreak in the state where I was born.)

On a day when “Phase 1” of cutting the CDC is making the news.

On a day when news we hear is that the sanctions between the U.S. and Russia put in place following Russia’s invasion into Ukraine, may be lifted.

On a day when a sitting American president says he is above the law.

On a day like this, seeing our flag does not make me feel proud.

Over the summer, I went down the workflow rabbit hole (starting with what I knew like Apache Airflow and Dagster) but ended up deciding on Windmill.dev as the tool of choice.

It was a compromise between pure code options vs. no code for orchestrating random scripts. What were the factors?

• Open Source and good enough feature set in the free version.
• Support for Bash, Python, Typescript, Golang, and more (Ansible has subsequently been added)
• Solid RBAC
• Written in Rust for high performance
• Async task execution triggered by webhooks
• Built in Secrets Management, often through variables and resources.
• AI-codegen (you enter your OpenAI API key when you deploy the instance) and Vim editor support (just kidding!)
• A CLI that allows you do local code development and execution.
• Easy Python dependency management, even though your jobs execute within containers, just import the libraries and they are automatically installed

Self-Hosting Windmill on K3s

Windmill is super easy to deploy with a reliable helm chart that I’ve used on both EKS and K3s (pictured below)

I expose the web UI with Tailscale, but like most Helm resources you can use it to deploy a load-balancer and then terminate with an ACM cert.

apiVersion: v1
kind: Service
metadata:
  annotations:
    meta.helm.sh/release-name: rogwindmill
    meta.helm.sh/release-namespace: windmill
    tailscale.com/expose: "true"
  creationTimestamp: "2024-11-13T23:38:20Z"
  finalizers:
  - tailscale.com/finalizer
  labels:
    app.kubernetes.io/managed-by: Helm
  name: windmill-app
  namespace: windmill
  resourceVersion: "8843"
  uid: 0a5116b1-d48f-42d8-bc40-fd65d842350b

Creating Aurora Instance

This was the first time I’ve used RDS Serverless Aurora. (Turn’s out it was the last because I got a huge bill)

I used Gemini to create a sample dataset

Which I entered through the RDS Query editor

Next, you add the resource, entering the credentials retrieved from AWS Secrets manager

Builders Build!

Postgres is a “script type” just like Python, Bash, or other languages and it allows you to view the data as a script.

A simple listing of the Data

When you run it you’ll see the output in JSON or Table format

Next build the app and use the use the Database Studio widget

When you run the “App” you’ll get the CRUD editor

That allows you to insert (and edit) data.

References

Database studio | Windmill

https://github.com/mdfranz/cheetsheetz/tree/main/windmill

There is only one more month left until 2025. I’m not sure what to think about that. But at least the election is over. America decided, even if I’m not thrilled by the result.

We celebrated two birthdays in the family over the Thanksgiving break. None of us are getting any younger. Our oldest is no longer on our insurance. I turn 55 next year. Speaking of birthdays, we bought tickets for my son and two of his friends to attend the Ravens kickoff back in September. We all got terribly sunburned but it was worth it, even if we lost. First time at a Pro football game since the 1980s.

I visited Manhattan for the first time since 2022 and ate the most expensive steakhouse (and a Kosher one, at that) I’ve ever eaten, then wandered through Wall Street around midnight and past the 9/11 memorial before catching our Uber back to mid-town.

There are still seasons. We even had snow flurries this month and this morning was the coldest day so far. We finally committed to join a new church.

(October and November are pledge time in the Episcopal Church.)

I avoided spending more than $150 on Black Friday specials, but did buy another Raspberry Pi I didn’t really need. You can never have too many home K3s clusters, right?

The weekend before the election, my youngest and I camped in the Michaux forest west of Gettysburg again. (The first time was in June 2023, before the shit hit the fan at Ping).

The calm before the storm.

I’ve stopped listening to The Bulwark for the most part. I’ve tuned out of Amerian politics because it is too depressing, but I have been trying to get involved in a local Indivisible group. Canadians at least understand. I would be ashamed to travel to Europe at the moment.

It has been a few weeks and months of sickness. Food poisoning. Chest colds. Concussions. Too many school absent notices. (If you didn’t know, wisdom teeth removal costs around $3K without dental insurance.) Yet we are very fortunate.

This is the first November since 2022 to be part of layoffs, but others have not been so lucky. I have one more trip to Toronto before the end of the year? Is it five or six? I’ve stopped counting. This type I’ll take the train to Newark like I always did to India. The stock market is up (for now) and my 401K has never been higher and I’ve started looking at my social security payout given I’m less than a decade from being able to starting to use it.

But onto the books.

Society & Politics

I’ve not yet finished Strongmen and Power and Progress, but both were obviously a reaction to socio-technical events: the reality of a authoritarian regime in the U.S. and the never-ending drumb-beat of techno-optimism. I still hope to finish Out of the Darkness, a post 1942 history of Germany — especially relevant given the resurgence of anti-democratic forces in Europe.

• Strongmen: Mussolini to the Present by Ruth Ben-Ghiat (2020)
• Power and Progress: Our Thousand-Year Struggle Over Technology and Prosperity by Daron Acemoglu, Simon Johnson (2023)
• Out of the Darkness: The Germans, 1942–2022 by Frank Trentmann (2024)

History

Having fond memories of my trip to Europe in September and October 2023 (well, minus the part of getting COVID and hearing about my boss getting let go) I revisited the war in France, completing To Lose a Battle having read this text much faster than A Savage War for Peace. This led me to The Road to Dunkirk and The Mastermind of Dunkirk and D-Day and Brothers in Arms. Nimitz at War has been a treat, once again drawing clear lines between the Patton-MacArthur strain of (generally toxic) military leadership and the Eisenhower/Marshall/Nimitz school.

• The Blitzkrieg Myth: How Hitler and the Allies Misread the Strategic Realities of World War II by John Mosier (2011)
• Nimitz at War: Command Leadership from Pearl Harbor to Tokyo Bay by Craig L. Symonds (2022)
• Boyd: The Fighter Pilot Who Changed the Art of War by Robert Coram (2002)
• Brothers in Arms: One Legendary Tank Regiment’s Bloody War from D-Day to V-E Day by James Holland (2021)
• The Battle of Arnhem: The Deadliest Airborne Operation of World War II by Antony Beevor (2018)
• The Road to Dunkirk: The British Expeditionary Force and the Battle of the Ypres-Comines Canal, 1940 by Charles More (2013)
• Mastermind of Dunkirk and D-Day: The Vision of Admiral Sir Bertram Ramsay by Brian Izzard (2020)
• To Lose a Battle: France 1940 by Alistair Horne (2007)
• Military Misfortunes: The Anatomy of Failure in War by Eliot A. Cohen (2006)

Tech & Business

Sometime it is good to review the basics on product development and security. Since I helped review and write an O’Reilly book a few years ago, I still have a subscription and it allows to me to read more technical books. Threat Modeling (Tarandach and Coles) was a nice update on Adam Shostok’s book from 2014. Even before the reality that Elon would be influencing the next administration, I started reading Character Limit and is as depressing as you’d think it would be, but I’ll continue plodding through it.

• Threat Modeling: A Practical Guide for Development Teams
  by Izar Tarandach and Matthew J. Coles (2020)
• Character Limit: How Elon Musk Destroyed Twitter by Kate Conger, Ryan Mac (2024)
• Technical Program Manager’s Handbook by Joshua Alan Teter (2024)
• Product Management in Practice by Matt LeMay (2022)
• Co-Intelligence: Living and Working with AI by Ethan Mollick (2024)
• The Friction Project: How Smart Leaders Make the Right Things Easier and the Wrong Things Harder by Robert I. Sutton, Huggy Rao

Film & TV

I binged latest The Diplomat season like I did the first season on one of my trips to Canada, but all the others we watched with my wife.

• The Bay (Britbox)
• The Diplomat (Netflix, Season 2)
• The Mire: Millennium (Amazon Prime)
• Killing Eve (Netflix)
• The Long Shadow (Acorn)
• Anatomy of a Fall (2023)
• Girl you know it is True (2023) — a German film about Milli-Vanilli!

(This was initially published on my blog on Ghost)

I’ve been using the “free” version of Gemini included with my ChromeBook plus for a few months but recently decided to upgrade to Advanced and testing with documents in my Google Drive and within Google Docs. The advanced version also gives you better image generation capabilities. In the past I’d ingested NIST 800–53 into PrivateGPT on my Mac M3 with mixed results and though I’d try again on my ASUS Nvidia 3060 I added to my son’s old gaming PC this past week, where I had Ollama.

Local Hardware Setup

The stats above were generated by LLM Benchmark

Private RAG Configuration

Ollama 0.3.12

With the exception of having Ollama listening on 0.0.0.0 I used a default install of the latest Ollama with the CUDA 12 drivers that get installed with the shell script

root@rogstrix-b450-ryzen5:/etc/systemd/system/ollama.service.d# cat override.conf 
[Service]
Environment="OLLAMA_HOST=0.0.0.0"

I used the following model for llama3.2

$ ollama show llama3.2:latest
  Model
    architecture        llama     
    parameters          3.2B      
    context length      131072    
    embedding length    3072      
    quantization        Q4_K_M

PrivateGPT 0.6.2 (Llama 3.2 + Qdrant + Nomic)

Python is a blessing and curse for Development, but PrivateGPT is the easiest of the local RAG tools that I’ve found to install. It is on my list to get H2oGPT working with Ollama but it is a little trickier, so I put it off.

Since I’m running Ubuntu 24.04 (which uses Python 3.12) I had to install Python 3.11 with uv in a virtual environment I created. I tried to make PrivateGPT run with uv, but ended up sticking with Poetry and once I installed the right Python version it worked with no issue. By default

ubuntu@privatgpt:~/private-gpt-0.6.2/.venv$ cat pyvenv.cfg 
home = /home/ubuntu/.local/share/uv/python/cpython-3.11.6-linux-x86_64-gnu/bin
implementation = CPython
uv = 0.4.18
version_info = 3.11.6
include-system-site-packages = false
prompt = private-gpt-0.6.2

server:
  env_name: ${APP_ENV:ollama}

llm:
  mode: ollama
  max_new_tokens: 512
  context_window: 3900
  temperature: 0.1     #The temperature of the model. Increasing the temperature will make the model answer more creatively. A value of 0.1 would be more factual. (Default: 0.1)

embedding:
  mode: ollama

ollama:
  llm_model: llama3.2
  embedding_model: nomic-embed-text
  api_base: http://100.115.28.90:11434
  embedding_api_base: http://100.115.28.90:11434  # change if your embedding model runs on another ollama
  keep_alive: 5m
  tfs_z: 1.0              # Tail free sampling is used to reduce the impact of less probable tokens from the output. A higher value (e.g., 2.0) will reduce the impact more, while a value of 1.0 disables this setting.
  top_k: 40               # Reduces the probability of generating nonsense. A higher value (e.g. 100) will give more diverse answers, while a lower value (e.g. 10) will be more conservative. (Default: 40)
  top_p: 0.9              # Works together with top-k. A higher value (e.g., 0.95) will lead to more diverse text, while a lower value (e.g., 0.5) will generate more focused and conservative text. (Default: 0.9)
  repeat_last_n: 64       # Sets how far back for the model to look back to prevent repetition. (Default: 64, 0 = disabled, -1 = num_ctx)
  repeat_penalty: 1.2     # Sets how strongly to penalize repetitions. A higher value (e.g., 1.5) will penalize repetitions more strongly, while a lower value (e.g., 0.9) will be more lenient. (Default: 1.1)
  request_timeout: 120.0  # Time elapsed until ollama times out the request. Default is 120s. Format is float.

vectorstore:
  database: qdrant

qdrant:
  path: local_data/private_gpt/qdrant

AnythingLLM (Llama 3.2 + LanceDB + Anything LLM Embedder)

AnythingLLM Desktop is distributed as an AppImage which installed on an Ubuntu 24.40 desktop without a GPU knowing I would point at a remote Ollama server over my tailnet given it is on a different wireless network.

I configured Ollama at the Ryzen PI describe above.

Vector store after ingesting the NIST standard.

This is the first time I’ve used LanceDB

Anything LLM is based on MiniLM (a CPU model, which I assume is far less capable than Nomic.

Document Ingestion

Of course on Gemini (ingested from Google Drive or added to the Gemini chat window on my ChromeBook Plus) had no issues with ingestion of the nearly 6 MB NIST standard. Private GPT was the slowest taking at least 30–45 minutes to ingest the document, but it at least shows progress bar to know it was doing something. I left to go grab lunch and it was done when it came back. The initial estimate was 46 seconds but it went way over and about 600MB of GPU VRAM was used during the embedding process. There was minimal CPU usage on the PrivateGPT VM or the Baremetal PC hosting Ollama. AnythingLLM took about 4–5 minutes to ingest, but didn’t indicate how long it would take to ingest. As I discovered after the ingestion, AnythingLLM ingestions and embedding does not even use the GPU.

Listing the Top Control Families

What are the top level NIST Control Families

Gemini Advanced

Gemini was the only LLM to properly pull out the 20 controls families listed on Page 8 in the standard. None of the other tools could do this “simple” task.

AnythingLLM

I got two different answers from AnythingLLM, the first with examples of controls.

I created a new thread and it gave me these 15 controls only getting Access Control right.

Understanding the (-1) Control in Each Family

Anyone that has has had to suffer through NIST 800–53 knows the first control is Policies and Procedures. I was curious if any of the LLM’s would get this so I came up with this not great prompt to see how they would respond.

What is the similarity across the -1 control in AC-1, CA-1, AU-1, etc.

Gemini Advanced

Gemini quickly and easily answered the prompt, getting the intent of my question.

PrivateGPT

PrivateGPT also answers the question with an up-front caveat.

AnythingLLM

AnythingLLM really struggled to get the question.

Vulnerability Scanning

I crafted this intentionally vague prompt to see what the LLMs could understand about vulnerability scanning best practices.

What vulnerability scanning practices should be implemented to ensure most controls are covered?

Gemini Advanced

Shockingly, I found Gemini’s response was the most “word salad” among the three, although it started off well, but went downhill, not really understanding “organization-defined frequency” which is littered throughout NIST 800–53. However, it did mention CVSS and that scanners needed to updated and did mention classes of assets (meaning scan targets) in reasonable manner.

PrivateGPT

The markdown response with bold-faced, numbered list provided by llama3.2 + ChromaDB used by PrivateGPT was more help. It also didn’t really understand “organization-defined frequency” but it did suggest monthly. It captured that different types of vulnerabilities exist (software flaws and improper configurations) as well as that the results need to be reviewed and communicated. Genius! It did pick up the idea of “scan coverage” but not “control coverage”

AnythingLLM

Like PrivateGPT, AnythingLLM returned a numbered bulleted list with short explanations. It did the best on this question compared to the others and I actually liked the fact that it capture system vs. component (even though it really doesn’t understand what that means) as well as the idea that scanning should be continuous and that you should focus on most vulnerable systems. Given how poorly this configuration performed on the more basic questions, I surprised that it did this well.

TLDR: Pass on AnythingLLM and stick with python-llm for inference and native tools for RAG

I got better results using nomic with gemma2 but then I tried configuring AnythingLLM with OpenAI and the document uploading and embedding was just as problematic. The Workspace UX and threading interface is just janky and moving the embedded files between workspaces doesn't work reliably enough!

Given all the Plugins supported by Python-LLM I’ll stick with that. It is much easier to just pick a different model for your query, and obviously allows you to directly pass commands (or cat files) to the llm through the shell.

$ llm models
OpenAI Chat: gpt-3.5-turbo (aliases: 3.5, chatgpt)
OpenAI Chat: gpt-3.5-turbo-16k (aliases: chatgpt-16k, 3.5-16k)
OpenAI Chat: gpt-4 (aliases: 4, gpt4)
OpenAI Chat: gpt-4-32k (aliases: 4-32k)
OpenAI Chat: gpt-4-1106-preview
OpenAI Chat: gpt-4-0125-preview
OpenAI Chat: gpt-4-turbo-2024-04-09
OpenAI Chat: gpt-4-turbo (aliases: gpt-4-turbo-preview, 4-turbo, 4t)
OpenAI Chat: gpt-4o (aliases: 4o)
OpenAI Chat: gpt-4o-mini (aliases: 4o-mini)
OpenAI Chat: o1-preview
OpenAI Chat: o1-mini
OpenAI Completion: gpt-3.5-turbo-instruct (aliases: 3.5-instruct, chatgpt-instruct)
GeminiPro: gemini-pro
GeminiPro: gemini-1.5-pro-latest
GeminiPro: gemini-1.5-flash-latest
GeminiPro: gemini-1.5-pro-001
GeminiPro: gemini-1.5-flash-001
GeminiPro: gemini-1.5-pro-002
GeminiPro: gemini-1.5-flash-002
GeminiPro: gemini-1.5-flash-8b-latest
GeminiPro: gemini-1.5-flash-8b-001
Ollama: codegeex4:latest (aliases: codegeex4)
Ollama: hermes3:8b
Ollama: nemotron-mini:latest (aliases: nemotron-mini)
Ollama: nomic-embed-text:latest (aliases: nomic-embed-text)
Ollama: llama3.2:latest (aliases: llama3.2)
Ollama: llava:7b
Ollama: llava:13b
Ollama: llama3.1:8b (aliases: llama3.1:latest, llama3.1)
Ollama: mistral:7b (aliases: mistral:latest, mistral)
Ollama: gemma2:9b (aliases: gemma2:latest, gemma2)
Ollama: phi3:3.8b
Ollama: qwen2:7b
Ollama: gemma:2b
Ollama: qwen2.5:14b
Ollama: granite-code:8b
Ollama: qwen2.5-coder:latest (aliases: qwen2.5-coder)
Ollama: yi-coder:latest (aliases: yi-coder)
Ollama: qwen:4b
Ollama: codegemma:2b
Ollama: phi:latest (aliases: phi)

As for RAG (summarization and querying) for now, I’ll stick with native tooling. Primarily with Gemini since I don’t actually have a ChatGPT subscription. It looks like I can get Gemini Plus through my Google One subscription and I can get metered Gemini through Google AI Studio, so nice try AnythingLLM but I’m good!

At some point over the last few years I discovered DuckDB as an alternative lightweight SQL database that is super fast and has support for a wide variety of file formats, but until now I’d only used CSV and Parquet, but recently a pcap extension was released, so I thought I’d try it out! Fortunately now there are binaries available so you don’t have to go through the headache of a building at DuckDB extension.

Installation

If you don’t already have it installed, install DuckDB 1.0 on on your platform.

Following the instructions from the updated blog post at the bottom, you then install the pre-compiled extension.

$ duckdb -unsigned
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D SET custom_extension_repository='https://w3c2.c20.e2-5.dev/ppcap/latest';
D install ppcap;
D load ppcap;

If you want to see all the extensions available, use the following query:

D select extension_name from duckdb_extensions() where loaded = True;
┌────────────────┐
│ extension_name │
│    varchar     │
├────────────────┤
│ autocomplete   │
│ fts            │
│ icu            │
│ inet           │
│ jemalloc       │
│ json           │
│ parquet        │
│ ppcap          │
│ shell          │
│ tpch           │
├────────────────┤
│    10 rows     │
└────────────────┘

This creates the following files in ~/.duckdb

$ ls -alR
.:
total 12
drwxr-xr-x  3 mfranz mfranz 4096 Aug 31 12:54 .
drwxr-x--- 41 mfranz mfranz 4096 Aug 31 12:56 ..
drwxr-xr-x  3 mfranz mfranz 4096 Aug 31 12:54 extensions

./extensions:
total 12
drwxr-xr-x 3 mfranz mfranz 4096 Aug 31 12:54 .
drwxr-xr-x 3 mfranz mfranz 4096 Aug 31 12:54 ..
drwxr-xr-x 3 mfranz mfranz 4096 Aug 31 12:54 v1.0.0

./extensions/v1.0.0:
total 12
drwxr-xr-x 3 mfranz mfranz 4096 Aug 31 12:54 .
drwxr-xr-x 3 mfranz mfranz 4096 Aug 31 12:54 ..
drwxr-xr-x 2 mfranz mfranz 4096 Aug 31 12:55 linux_amd64_gcc4

./extensions/v1.0.0/linux_amd64_gcc4:
total 22980
drwxr-xr-x 2 mfranz mfranz     4096 Aug 31 12:55 .
drwxr-xr-x 3 mfranz mfranz     4096 Aug 31 12:54 ..
-rw-rw-r-- 1 mfranz mfranz 19712350 Aug 31 12:54 httpfs.duckdb_extension
-rw-rw-r-- 1 mfranz mfranz      131 Aug 31 12:54 httpfs.duckdb_extension.info
-rw-rw-r-- 1 mfranz mfranz  3797542 Aug 31 12:55 ppcap.duckdb_extension
-rw-rw-r-- 1 mfranz mfranz      144 Aug 31 12:55 ppcap.duckdb_extension.info

Loading your PCAP

You don’t have to install the extension each time so the next time you run, you just load it it then create a table from your pcap

$ duckdb -unsigned
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D 
D load ppcap;
D 
D create table bridged as from read_pcap("~/bridged.pcap");

All your normal SQL commands work

D describe bridged;
┌─────────────┬─────────────┬──────┬─────┬─────────┬───────┐
│ column_name │ column_type │ null │ key │ default │ extra │
├─────────────┼─────────────┼──────┼─────┼─────────┼───────┤
│ timestamp   │ TIMESTAMP   │ YES  │     │         │       │
│ source_ip   │ VARCHAR     │ YES  │     │         │       │
│ dest_ip     │ VARCHAR     │ YES  │     │         │       │
│ source_port │ INTEGER     │ YES  │     │         │       │
│ dest_port   │ INTEGER     │ YES  │     │         │       │
│ length      │ INTEGER     │ YES  │     │         │       │
│ tcp_session │ VARCHAR     │ YES  │     │         │       │
│ source_mac  │ VARCHAR     │ YES  │     │         │       │
│ dest_mac    │ VARCHAR     │ YES  │     │         │       │
│ protocols   │ VARCHAR[]   │ YES  │     │         │       │
│ payload     │ BLOB        │ YES  │     │         │       │
│ tcp_flags   │ VARCHAR[]   │ YES  │     │         │       │
│ tcp_seq_num │ UINTEGER    │ YES  │     │         │       │
└─────────────┴─────────────┴──────┴─────┴─────────┴───────┘

D select count(*) from bridged;
┌──────────────┐
│ count_star() │
├──────────────┤
│ 104851       │
└──────────────┘

D .mode line
D pragma database_size;
database_name = memory
database_size = 0 bytes
   block_size = 0
 total_blocks = 0
  used_blocks = 0
  free_blocks = 0
     wal_size = 0 bytes
 memory_usage = 114.6 MiB
 memory_limit = 37.5 GiB

Slicing and Dicing with SQL

What are the IP protocols active on the network. Mostly UDP! (I do wish this field had been an INTEGER instead of a VARCHAR.

D select protocols, count(*) as cnt from bridged group by protocols order by cnt desc;
┌─────────────────────────┬────────┐
│        protocols        │  cnt   │
├─────────────────────────┼────────┤
│ [Ethernet, IP, UDP]     │ 101644 │
│ [Ethernet, IP, TCP]     │ 2831   │
│ [Ethernet, IP, Unknown] │ 346    │
│ [Ethernet, IP, ICMP]    │ 30     │
└─────────────────────────┴────────┘

Let’s find all the IPs on my local network

D SELECT distinct (source_ip) from bridged where source_ip like '192.168.12%';
┌────────────────┐
│   source_ip    │
├────────────────┤
│ 192.168.12.131 │
│ 192.168.12.159 │
│ 192.168.12.113 │
│ 192.168.12.1   │
└────────────────┘

Let’s go down to the link layer and see where most of the frames are going from!

D SELECT DISTINCT (source_mac, dest_mac) from bridged;
┌────────────────────────────────────────┐
│     main.row(source_mac, dest_mac)     │
├────────────────────────────────────────┤
│ (00:16:3e:9b:21:27, 33:33:00:00:00:16) │
│ (00:16:3e:7d:5c:ca, 33:33:00:00:00:16) │
│ (d6:06:10:3e:e9:ed, 56:1c:4b:ac:c3:15) │
│ (3a:99:83:4e:f6:9f, 33:33:00:00:00:16) │
│ (c8:99:b2:e1:47:17, 01:80:c2:00:00:13) │
│ (3a:99:83:4e:f6:9f, 33:33:00:00:00:02) │
│ (56:1c:4b:ac:c3:15, d6:06:10:3e:e9:ed) │
│ (56:1c:4b:ac:c3:15, 01:00:5e:7f:ff:fa) │
│ (c8:99:b2:e1:47:17, 33:33:00:00:00:01) │
│ (3a:99:83:4e:f6:9f, 01:00:5e:00:00:fb) │
│ (84:47:09:19:c3:16, 33:33:00:00:00:16) │
│ (00:16:3e:53:19:f5, 33:33:00:00:00:16) │
│ (00:16:3e:aa:1b:f3, 33:33:00:00:00:16) │
│ (52:54:00:8b:d8:ac, 33:33:00:00:00:16) │
│ (56:1c:4b:ac:c3:15, c8:99:b2:e1:47:17) │
│ (d6:06:10:3e:e9:ed, 33:33:00:00:00:16) │
│ (c8:99:b2:e1:47:1c, 01:80:c2:00:00:13) │
│ (c8:99:b2:e1:47:17, 56:1c:4b:ac:c3:15) │
│ (3a:99:83:4e:f6:9f, 33:33:00:00:00:fb) │
│ (3a:99:83:4e:f6:9f, 01:00:5e:00:00:16) │
│ (c8:99:b2:e1:47:17, 33:33:ff:45:46:08) │
└────────────────────────────────────────┘

D select dest_mac, count(*) as cnt from bridged group by dest_mac order by cnt desc;
┌───────────────────┬───────┐
│     dest_mac      │  cnt  │
├───────────────────┼───────┤
│ 56:1c:4b:ac:c3:15 │ 52469 │
│ d6:06:10:3e:e9:ed │ 51857 │
| c8:99:b2:e1:47:17 │ 492   │
│ 33:33:00:00:00:16 │ 16    │
│ 01:00:5e:7f:ff:fa │ 4     │
│ 33:33:00:00:00:fb │ 3     │
│ 01:00:5e:00:00:fb │ 3     │
│ 01:80:c2:00:00:13 │ 2     │
│ 01:00:5e:00:00:16 │ 2     │
│ 33:33:00:00:00:02 │ 1     │
│ 33:33:ff:45:46:08 │ 1     │
│ 33:33:00:00:00:01 │ 1     │
└───────────────────┴───────┘

The first entry is my workstation so it makes sense most of the comms are with my router.

D select source_ip, count(*) as cnt from bridged where dest_mac = '56:1c:4b:ac:c3:15' and source_ip like '192.168.12%' group by source_ip order by cnt desc limit 25;
┌────────────────┬───────┐
│   source_ip    │  cnt  │
├────────────────┼───────┤
│ 192.168.12.159 │ 51977 │
│ 192.168.12.1   │ 36    │
└────────────────┴───────┘

Let’s see the unique ports

D select distinct(dest_port) from bridged where dest_mac = 'c8:99:b2:e1:47:17' and source_port != 0;
┌───────────┐
│ dest_port │
├───────────┤
│ 1900      │
│ 3478      │
│ 443       │
│ 80        │
│ 53        │
│ 5351      │
└───────────┘

D select dest_port, count(*) as cnt from bridged where dest_mac = 'c8:99:b2:e1:47:17' and source_port != 0 group by dest_port order by cnt desc;
┌───────────┬─────┐
│ dest_port │ cnt │
├───────────┼─────┤
│ 443       │ 181 │
│ 3478      │ 71  │
│ 80        │ 37  │
│ 53        │ 26  │
│ 5351      │ 8   │
│ 1900      │ 2   │

Looking at the packets!

Getting used to switching output formats is useful depending on your screen resolution.

D select * from bridged where length < 15 limit 3;
  timestamp = 2024-08-31 16:56:25
  source_ip = 192.168.12.131
    dest_ip = 209.177.145.120
source_port = 46017
  dest_port = 443
     length = 14
tcp_session = 192.168.12.131:46017 -> 209.177.145.120:443
 source_mac = 56:1c:4b:ac:c3:15
   dest_mac = c8:99:b2:e1:47:17
  protocols = [Ethernet, IP, TCP]
    payload = \x00\x00\x01\x01\x08\x0A\xF5\xB3K.\x8B\xFB\x96\x1C
  tcp_flags = [ACK]
tcp_seq_num = 3986401156

  timestamp = 2024-08-31 16:56:25
  source_ip = 192.168.12.159
    dest_ip = 192.168.12.131
source_port = 24679
  dest_port = 6443
     length = 14
tcp_session = 192.168.12.159:24679 -> 192.168.12.131:6443
 source_mac = d6:06:10:3e:e9:ed
   dest_mac = 56:1c:4b:ac:c3:15
  protocols = [Ethernet, IP, TCP]
    payload = \x00\x00\x01\x01\x08\x0A\xAE\x87H\xD5\xBC\xF6d\x12
  tcp_flags = [ACK]
tcp_seq_num = 1817817319

  timestamp = 2024-08-31 16:56:25
  source_ip = 192.168.12.159
    dest_ip = 192.168.12.131
source_port = 24679
  dest_port = 6443
     length = 14
tcp_session = 192.168.12.159:24679 -> 192.168.12.131:6443
 source_mac = d6:06:10:3e:e9:ed
   dest_mac = 56:1c:4b:ac:c3:15
  protocols = [Ethernet, IP, TCP]
    payload = \x00\x00\x01\x01\x08\x0A\xAE\x87H\xD5\xBC\xF6d\x12
  tcp_flags = [ACK]
tcp_seq_num = 1817817319

Originally published on https://blog.mdfranz.com/

<script defer data-domain=”mdfranz.medium.com” src=”https://plausible.io/js/script.js"></script>