Real-Time KPI Dashboards for File Scan Reporting with S3 as a Data Store
Following the deployment of a dual-engine ClamAV and Sophos file scanning pipeline within Salesforce, the same organisation needed operational visibility into scanning performance. Scan results were written to Amazon S3 as structured JSON files – tens of thousands of objects daily – with no reporting layer, no aggregation, and no way for security operations or leadership to understand throughput, threat trends, or SLA compliance. Standing up a dedicated database was ruled out on cost grounds. Applying its AI-First approach, Ksolves built a fully serverless analytics pipeline on raw S3 scan objects using AWS Glue, Athena, and Lambda – delivering 12 automated KPIs, scheduled HTML and PDF reports, and a QuickSight dashboard layer without a single running database.
Key Challenges
01
S3 as an Unstructured Data Layer: Thousands of scan result files written daily with no mechanism to filter, aggregate, or join data across objects without reading every file.02
No Schema Enforcement at Write Time: Scan result files from different engine integrations had inconsistent field names, optional fields, and varying date formats, making direct aggregation unreliable.03
KPI Definition and Ownership Gap: Security, operations, and leadership each required different metric views with no agreed definition layer across teams.04
Refresh Frequency Requirements: Dashboards needed near-real-time data for security operations and daily rollups for management - both streaming and batch pipelines required.05
Cost Sensitivity: A dedicated warehouse or RDS instance was ruled out. The solution had to stay within the S3 and Lambda cost envelope already approved.06
Report Distribution: KPI summaries needed to be automatically distributed to stakeholders as formatted reports on a scheduled basis without manual steps.Our Solution
Ksolves built a serverless analytics pipeline using Amazon Athena, AWS Glue, and Lambda that imposed a queryable schema on top of raw S3 scan result objects. The governing principle was schema-on-read: data written freely by upstream producers, structure enforced at query time by a Glue catalog, no transformation pipeline required before querying.
- 01 AWS Glue Data Catalog: Glue crawler automatically infers and registers schema of incoming S3 scan result partitions, normalising field names and data types across ClamAV and Sophos output formats.
- 02 Amazon Athena Query Layer: Serverless SQL querying directly over S3 partitions for KPI calculations - scan volumes, threat rates, engine agreement percentages - without data movement or a running database.
- 03 Lambda-Based Aggregation Engine: Scheduled Lambda functions execute parameterised Athena queries on daily and weekly cycles, materialising KPI summaries as structured JSON files written back to a reporting S3 prefix.
- 04 KPI Definition Layer: A shared configuration file defines all 12 metrics, their SQL formulas, and ownership tags, ensuring consistent KPI calculation across security operations and leadership dashboards.
- 05 Report Generation and Distribution: Lambda renders materialised KPIs into formatted HTML and PDF reports and distributes them to stakeholder email lists via Amazon SES on a scheduled basis.
- 06 Partition Strategy for Cost Optimisation: S3 data is partitioned by year/month/day and scan engine, reducing Athena query scan volumes by over 80% within the approved serverless budget envelope.
Solution Architecture
Technology Stack
| Category | Technology |
|---|---|
| Object Storage | Amazon S3 |
| Schema Management | AWS Glue |
| Query Engine | Amazon Athena |
| Compute / Orchestration | AWS Lambda |
| Report Distribution | Amazon SES |
| Visualisation | Amazon QuickSight |
- Report Generation Cut From 4-6 Hours to 15 Minutes: Automated pipeline produces daily KPI summaries and weekly reports within 15 minutes of the scheduled trigger with zero analyst effort.
- 12 Automated KPIs From Zero: Scan volume, threat detection rate, engine agreement, quarantine rate, and SLA compliance all now tracked automatically - no automated metrics existed before.
- 80% Reduction in Athena Query Cost: Partition-aware queries reduced data scanned per KPI run by over 80%, keeping monthly analytics costs within the approved serverless budget.
- Single Source of Truth Across All Stakeholders: Shared KPI definition layer ensures security operations and leadership draw from the same Athena-computed metrics, eliminating data discrepancies in review meetings.
- No Standing Database Required: Schema-on-read architecture delivered enterprise-grade KPI reporting entirely within the serverless cost envelope without a warehouse or ETL pipeline.
Client Testimonial
“Our scan results were piling up in S3 with no way to make sense of them. Every week someone spent half a day pulling files and building numbers in a spreadsheet – and they were always slightly different from last week. Ksolves turned that into a 15-minute automated report that everyone reads from the same numbers.”
– Head of Security Operations.
Conclusion
A technology services organisation accumulating tens of thousands of daily scan result files in S3 with no reporting layer and a 4 to 6-hour manual weekly process was transformed through Ksolves Big Data services. A serverless AWS Glue, Athena, and Lambda pipeline now delivers 12 automated KPIs, scheduled reports, and a QuickSight exploration layer from S3 as the sole data store. Report generation dropped from hours to 15 minutes. Analyst effort eliminated. Query costs reduced 80%. Every stakeholder reads from a single agreed source of truth.
