Hi everyone! I've been tasked with setting up our new Akamai Prolexic reports for the security team, and I'm a bit stuck on the "actionable" part. The dashboard graphs are nice for a high-level view, but our analysts keep asking for data they can actually *do* something with—like lists of blocked IPs to add to our own blocklists, or specific attack patterns tied to our application endpoints.
Right now, I'm pulling data via the Reporting API into a Snowflake table (using a Python script orchestrated with Airflow). I can get the raw logs, but turning them into something useful feels... messy. For example:
* The security team wants a daily digest of top attacking countries, but filtered to only show incidents that targeted our production subnets.
* They also want a simple CSV of suspicious IPs from the last 24 hours that exceeded a certain request threshold, ready for our firewall team.
I've got the connection working, but my SQL queries are getting super complex and the output still needs a lot of manual cleaning. 😅
Has anyone built a similar pipeline? I'm wondering:
* Do you process the raw logs directly, or use a specific Prolexic report type as a starting point?
* How do you structure the data (maybe in a star schema?) to make it easier to slice by our internal assets?
* Any tips on automating the generation of those "ready-to-use" CSV reports?
I'm probably overcomplicating this. Grateful for any pointers or even screenshots of how you've structured your queries or tables!
null
Start with the L3/L4 traffic reports from the API. They have the granular IP and subnet data you need for blocklists, not just aggregate graphs.
For the daily digest, your SQL is getting complex because you're joining raw logs against your production subnet list. We do this: pipe the logs to a staging table, then run a view that handles the filter and aggregation. It keeps the Airflow DAG simple and the view is what the security team queries directly.
Skip the manual cleaning. That Python script should output the CSV in the exact format the firewall team's automation expects. If their tool needs column A as IP, column B as timestamp, don't give them anything else.
Benchmarks or bust.
The view layer approach is a good one, but I've found it can introduce latency issues if the security team needs near-real-time data for active threat response. We tried a similar setup, but the view's join on our dynamic production subnet list (which updates frequently) caused query times to spike during peak attack periods.
We moved to a materialized view refreshed every 15 minutes, which trades a bit of data freshness for consistent performance. More importantly, we added a second output from the Python script: a JSON payload for their SOAR platform, not just the CSV for the firewall. The timestamp format they needed for the CSV was different from what their Tines workflows consumed.
p-value < 0.05 or bust
Your point about the materialized view trade-off is valid, but I'd benchmark that 15-minute lag against your actual mean-time-to-remediate. If the firewall team's automation loop is hourly, you're over-optimizing. The performance spike during peak attacks is the real issue you solved.
We addressed a similar latency problem by moving the subnet filter *into* the data ingestion stage. Our Python script fetches the latest production subnet list from our CMDB first, *then* uses it to filter the Prolexic API response before writing to Snowflake. This pre-joins the data, so the view just reads a filtered staging table. It shifts the compute load to the orchestration layer, which scales better than the database during a query surge.
The dual output for CSV and JSON is crucial, but you can take it a step further by generating a third artifact: a SQLite file. Some of our threat hunters use it for ad-hoc correlation on their laptops without needing live database access.