A 90-Day Privileged-User Logon Report That Times Out and Produces 8 GB
Auditors require a monthly report of successful and failed logons for 400 privileged users over a rolling 90-day period. QRadar retains events for 90 days and ingests 45,000 EPS. Your first attempt uses a single AQL report over 90 days with a username list and returns 8 GB of CSV; the report times out and the scheduled job fails. Auditors want a summary by user, day, and outcome, plus the ability to request raw events for a specific user and day. Monthly reports must be delivered by the 5th business day, and the QRadar environment runs 120 other scheduled reports, with the busiest reporting window between 08:00 and 10:00. The privileged-user list changes monthly as contractors join and leave, and the auditors will not accept a report that silently drops users who were removed from the list.
1. Why did the report fail, and how should the search and report be redesigned?
Read the worked answer
The report tried to do too much in one search: 90 days at 45,000 EPS, a 400-user list, raw events, and CSV output. That is both a scan-size and an output-size problem. Redesign:
- Filter early: event category/QID for logon success/failure, the authentication log source group, and the privileged-user reference set. Do not rely on a username regex.
- Aggregate:
SELECT username, DATEFORMAT(eventtime,'yyyy-MM-dd') AS day, outcome, COUNT(*) FROM events WHERE ... GROUP BY username, day, outcome. This returns thousands of rows, not 8 GB. - Split the schedule: run monthly per month rather than a single rolling 90-day query, or run weekly increments into a summary table/reference set.
- Move off-peak: schedule between 02:00 and 06:00, not 08:00-10:00, and stagger from the other 120 reports.
- Use a saved search as the report source so the logic is testable and reusable, and set a max-results guard.
- Confirm the 90-day retention boundary: a report that runs on the 5th and queries 90 days back will lose the oldest days if retention is exactly 90 days.
2. How do you reduce the output to a manageable size while preserving audit requirements?
Read the worked answer
Reduce output at the grain the auditors actually asked for: user, day, outcome.
- Aggregate with
COUNT(*), not raw events; one row per user/day/outcome is about 400 x 30 x 2 = 24,000 rows per month, well within CSV limits. - Drop payload, message, and raw event columns from the summary; keep username, day, outcome, count, and first/last seen.
- Use a reference set for privileged users and snapshot it monthly. Because the list changes and auditors will not accept silent drops, version the reference set: keep the monthly list as a dated reference set or reference map, and run the report against the list as of the report month, not the current list.
- Produce two deliverables: a PDF/CSV summary for the monthly pack, and a documented procedure for raw-event pull on request.
- Validate row counts against a known user/day before delivery, and include a 'users with zero events' section so omissions are visible.
- Retain the query and reference-set version with the report for audit reproducibility.
3. An auditor requests raw events for one user on one day. How do you satisfy that request with QRadar?
Read the worked answer
For a single user and day:
- Use an exact AQL query:
SELECT ... FROM events WHERE username = 'X' AND eventtime BETWEEN ... AND ...with the logon category/QID, over that day only. This is a small, index-friendly search. - Export from the search results to CSV; include QID, log source, source IP, destination IP, outcome, and timestamp. Preserve the raw event payload only for this targeted extract.
- Confirm the day is within retention. If it sits at the 90-day edge, check whether the event is still in the Ariel database before promising it; if not, state the retention limit in writing.
- Record the request, query, extraction time, and a hash of the CSV for chain of custody.
- If the auditor needs this repeatedly, save the search as a parameterized saved search and give them a documented runbook rather than ad-hoc queries.
Do not export the 8 GB dataset and ask the auditor to filter it; that is both a data-minimization and a performance problem.