Automated Vulnerability Email Composer

An internal Streamlit tool built at Citi to replace manual vulnerability notification workflows. During L0 incidents with 48-hour remediation deadlines, notifying hundreds of app managers individually — each with their own findings — was the bottleneck between discovery and the start of remediation. This tool eliminated that bottleneck.

Note: Internal tool — no public demo or source code available.

The Problem

When a critical vulnerability is confirmed in production, the clock starts. For the Apache Tika XXE incident and the Oracle HTTP Server RCE incident — both L0 GEMs with 48-hour remediation deadlines — the first hours were being consumed by manual effort: filtering the scanner data by app manager, composing individual emails, attaching the right subset of findings to each one, and clicking send dozens or hundreds of times.

The tool was built to compress that entire process to a few clicks.

How It Works

Inputs

• Scanner export: CSV or XLSX file from the vulnerability scanner
• Email body: HTML or TXT template — file type is auto-detected and the correct encoding is applied automatically
• XLSX sheet selection: if the input is a workbook with multiple sheets, a dropdown lets you select the target sheet before any further configuration

Column Mapping

Before filtering or sending, you map the data columns to their roles:

• App manager SOEID (used to group findings per recipient)
• App manager name (used in personalised greeting and subject line)
• App ID (used in subject line and attachment filename)
• Dynamic CC column (per-row CC recipients pulled from the data)

Static CC and email subject prefix are set via text fields. Send-on-behalf-of is configurable for sending from a distribution list alias.

3-Layer Hierarchical Filtering

The most significant UX decision. Rather than requiring the analyst to pre-filter the Excel file before loading it, the tool provides three layers of multiselect filtering directly in the UI:

1. Layer 1 — select a column (e.g., region), then a multiselect appears with the unique values from that column. Select only the values you want (e.g., EMEA).
2. Layer 2 — select a second column (e.g., internet-facing flag), then a multiselect for its values. Filter to internet-facing assets only.
3. Layer 3 — select a third column (e.g., app manager name or business unit), then a multiselect for a final subset. Useful when only a specific group of app managers needs this particular notification run.

The same raw scanner export can be loaded once and used for multiple notification campaigns with different filter combinations — no manual Excel pre-processing between runs.

Personalised Emails and Attachments

After filtering, the tool iterates over each unique app manager in the filtered dataset:

• Constructs a personalised subject line with the app manager’s name and app ID
• Customises the greeting in the email body with their name
• Filters the full dataset down to only their findings
• Writes that subset to a temp file with a personalised name: [App Name] [App ID] - Vulnerability Findings.xlsx
• Attaches the temp file to their email

Each recipient receives only their own data — no risk of inadvertently sharing other teams’ findings.

Power-User Features

• Draft mode (default): all emails land in Outlook Drafts for review before sending. Standard behaviour for first use of a new campaign.
• Send immediately checkbox: once you’ve reviewed the first few drafts and are confident in the output, enable this and re-run to send the full batch without manual clicking — critical when sending 80+ emails against a deadline.
• High importance flag: marks emails as high priority in Outlook, appropriate for L0 incident notifications.
• Send on behalf of: sends from a configured distribution list alias rather than the analyst’s personal address.

Impact

During the Apache Tika XXE L0 GEM and the Oracle HTTP Server RCE L0 GEM — both carrying 48-hour internal remediation deadlines — this tool was used to notify all affected app managers within the first hours of incident confirmation. Each app manager received a personalised email with only their relevant findings attached, ready to action immediately.

The time saved was the difference between spending the first half of a 48-hour window on notifications versus spending it on remediation coordination.

Tech Stack