Security Analytics Without a SIEM: Querying 200,000 Access Control Events in SQLite

Enterprise Security Information and Event Management (SIEM) systems cost tens of thousands of dollars per year and often require dedicated administrators. For a mid-size security operation, we built a functional analytics layer over 200,000+ access control events using SQLite—a free, serverless database that runs on any machine. Here’s what we learned.

The Data Source

Modern physical access control systems generate a continuous event log: every badge tap, door open, door held, alarm trigger, and override. Over time this becomes a rich dataset for understanding traffic patterns, identifying anomalies, and analyzing incidents. The problem is that most access control software provides only basic reporting—you can filter by date and door, but complex queries require exporting and analyzing the data elsewhere.

We exported the full event history—over 200,000 records—and ingested it into a SQLite database. The resulting file is under 100MB. Every record has a timestamp, event type, door identifier, and cardholder information.

What You Can Answer

With a properly indexed SQLite database and a query library, questions that would normally require a SIEM become simple:

• How many access events occurred on each floor over the past 30 days?
• Which doors had the most alarm events in the last week?
• What was the first and last access time for a specific cardholder on a given date?
• Are there any access events outside normal business hours?
• What’s the trend in denied access attempts by door over the past quarter?

All of these run in milliseconds on a standard laptop against 200,000+ records, with no server infrastructure required.

Full-Text Search on Event Data

We added FTS5 full-text indexing on event descriptions, which allows natural-language queries against the event log: search for “door held” across all events, or find all events matching a cardholder name without knowing the exact ID. This is particularly useful for incident investigation where you know roughly what happened but not the exact query parameters.

AI-Assisted Analysis

The most powerful use case is passing query results to an AI system for narrative analysis. Instead of presenting a table of numbers, you can ask: “Given these 50 access events for this cardholder over the past week, is there anything anomalous?” The AI can surface patterns—like a cardholder accessing a door at 2am that they’ve never accessed before—that would take significant manual review to identify.

The Cost Comparison

Enterprise SIEM solutions for a deployment this size typically cost $15,000–50,000 per year, require dedicated infrastructure, and demand specialized administrator training. Our SQLite-based system cost zero in licensing, runs on existing hardware, and was built and deployed by one person in two days. The query capability is a subset of what a full SIEM provides—but for the specific analytics needs of this operation, it covers everything that matters.

The lesson: before purchasing enterprise software, ask whether the actual queries you need to run could be answered by a well-structured local database. For many organizations, the answer is yes.