A Licensing Model Snow Can't See
An enterprise telecom client was running IBM QRadar as their SIEM platform — the backbone of their security event monitoring. QRadar's licensing model is fundamentally different from most software: it's based on Events Per Second (EPS) and total log source counts. Not installations. Not users. Not processor cores. Throughput.
Snow License Manager is excellent at tracking traditional licensing metrics. But it has no concept of EPS. The SAM team had zero visibility into QRadar compliance: Are we within our contracted EPS limit? How many log sources are actively sending events? Is the security team adding new sources faster than the contract allows?
These aren't academic questions. A SIEM platform is critical security infrastructure. According to IBM's licensing terms, exceeding the EPS limit can result in overages or — worse — the system may start dropping events, creating gaps in your security monitoring. Non-compliance here doesn't just mean a financial risk; it means potential blind spots in threat detection.
The ask was clear: bring QRadar's metering data into Snow so compliance is visible alongside every other product the SAM team manages.
The Architecture: API to CSV to SQL Server
I built a nightly ETL pipeline with five stages.
Data Extraction — Python + QRadar REST API
The core of the connector is a Python script that authenticates against QRadar's REST API using a security token and fetches the complete log source inventory. Every configured log source comes back with its name, enabled/disabled status, average EPS, creation date, and last event timestamp. The API returns the full dataset — every source the SIEM is configured to receive events from.
Data Transformation
QRadar's data needs translation before SQL Server can use it. Timestamps arrive as milliseconds since epoch — I convert them to SQL Server datetime format. Boolean status fields get translated to human-readable strings. The script produces two CSV outputs: a raw export preserved as an audit archive, and a transformed import-ready file with columns alphabetically ordered to match the SQL staging table structure.
That alphabetical ordering detail matters more than it sounds. SQL Server's BULK INSERT maps CSV columns positionally, not by header name. If the Python output and the SQL table definition drift out of sync, data silently lands in the wrong columns. No error, no warning — just corrupted compliance data. I locked the column ordering to alphabetical on both sides to eliminate this class of bug entirely.
Database Import — SQL Server BULK INSERT
Stored procedures handle the import side. Each run drops and recreates the staging tables — a deliberate idempotent design. The nightly pipeline can't afford to accumulate stale data from previous runs. Drop-and-recreate is aggressive, but it eliminates duplicate rows, schema drift, and every other state accumulation bug that haunts batch ETL systems.
The CSV is bulk-imported with explicit UTF-8 encoding, and string data is transformed to proper SQL types: integers for IDs and EPS values, datetime for timestamps.
Compliance Calculation
A second set of stored procedures takes the imported metering data and runs it against configurable contractual limits. The calculation is straightforward: sum up the total EPS across all enabled log sources, count the active sources, and compare both against the contracted maximums.
The output is a compliance table with the numbers that matter: actual EPS total, actual source count, the difference from each limit, and a clear compliant/non-compliant status per metric. I built the contract thresholds as variables in the procedure, not hardcoded values — when contracts get renegotiated, the SAM team updates two numbers without touching the rest of the SQL.
Two Custom Reports in Snow
The pipeline feeds two reports directly inside Snow License Manager's UI:
A metering report showing every log source with its EPS data, filterable by name and status. This gives the security team granular visibility into which sources consume the most capacity.
A compliance summary comparing actual consumption against contractual limits — the dashboard the SAM team checks to know whether they're in good standing.
Both reports refresh nightly. Windows Task Scheduler triggers the Python extraction, then SQL Server Agent runs the import and compliance procedures as part of Snow's standard data refresh cycle. No manual intervention.
The First Data Pull
This is where the connector paid for itself before it even ran a second time.
The initial import immediately showed that active log sources exceeded the contractual limit. For months, the SAM team had been managing QRadar licensing based on the original contract terms, with no mechanism to verify actual consumption. The security team had been adding log sources as operational needs demanded — exactly as they should — but nobody was tracking the cumulative impact on licensing.
The very first compliance report made the gap visible. Not a subtle drift over time, but clear overuse that had been invisible because no tool was measuring it.
This is exactly the scenario that throughput-based licensing creates. Traditional SAM catches an unlicensed installation because the software shows up in an inventory scan. But EPS overuse doesn't show up anywhere unless you build the connector to measure it. The data existed in QRadar; it just wasn't reaching the people who manage the contracts.
The discovery enabled the SAM and Security teams to jointly plan optimization — evaluating which log sources provided the most security value per EPS consumed, and whether the right move was to right-size the contract or decommission low-value sources.
Implementation Challenges
Timestamp gymnastics. QRadar stores timestamps as milliseconds since epoch. SQL Server expects datetime strings. The conversion seems trivial until you account for time zones and precision — a millisecond-level timestamp truncated to seconds can cause subtle sorting issues in reports when two events appear simultaneous. I validated the conversion against QRadar's own UI to ensure the dates matched exactly.
Idempotent by necessity. The import runs every night. Any state left over from a previous run — orphaned rows, partially imported data, schema changes — creates silent data quality issues that only surface when someone questions a compliance number. Drop-and-recreate on every cycle is the only design I trust for nightly batch ETL.
Configurable compliance thresholds. Contract terms change. The first version had the EPS and source limits as variables at the top of the compliance procedure — no hunting through SQL logic to find a hardcoded number. When the client renegotiates their QRadar contract, they update two values and the compliance report adjusts immediately.
Dual-audience reporting. The Security team wants to see individual log sources and their EPS contributions. The SAM team wants to see a single compliance status. Building two reports from the same underlying data — one detailed, one summarized — ensures both teams get the view they need without maintaining separate data pipelines.
What I Learned
The first data pull is the deliverable. For compliance connectors, the initial import often reveals more value than months of ongoing monitoring. The client had been flying blind on QRadar licensing. One data pull changed the entire conversation — from "we think we're fine" to "we need to optimize." Every compliance connector I've built since follows this pattern: get the first data in fast, because that's when the surprises surface.
EPS is the licensing metric nobody tracks. Traditional SAM tools think in installations and users. But throughput-based licensing — EPS, bandwidth, API calls — is a growing model across the industry, especially for security and infrastructure products. These metrics live in operational APIs that SAM tools don't connect to by default. Building connectors for them is where SAM teams need the most help.
Idempotent ETL or nothing. Nightly batch jobs that accumulate state eventually break. It might take weeks or months, but a row that should have been deleted, a table that didn't get refreshed, a schema that drifted — these bugs are the hardest to diagnose because the system "worked fine yesterday." Drop-and-recreate is aggressive, but I've never had an accumulation bug in a pipeline that starts from zero every cycle.
Make compliance thresholds configurable from day one. Contracts get renegotiated. Vendors change their licensing terms. If your compliance calculation has hardcoded limits, every contract change requires a developer to modify SQL. Soft-coding the thresholds means the SAM team is self-sufficient — they update the numbers when the contract changes, and the reports adjust immediately.