# A demonstration run against the live log estate that audit records can be reduced, sorted and searched on demand by event criteria — the standing saved queries, the query that ran, and the report it returned

> FedRAMP Consolidated Rules for 2026 v2026.07.14.01 · updated 2026-07-14
> Canonical page: /collect/audit-reduction-and-report-generation

Recipe id: `audit-reduction-and-report-generation` · cadence quarterly · partial

> **Authored opinion.** AWS overlay v3.0.0, written
> against dataset 2026.07.14.01. The upstream
> FedRAMP rules name none of these tools; this mapping is ours.

## What it proves

- KSI `KSI-MLA-OSM`
- KSI `KSI-MLA-LET`
- control `au-7`
- control `au-7.1`

## Collection

Kind: `cli`

```sh
# describe-query-definitions
aws logs describe-query-definitions --query 'queryDefinitions[].{id:queryDefinitionId,name:name,language:queryLanguage,logGroups:logGroupNames}'
# logs-insights-start-query
aws logs start-query --log-group-names <LOG_GROUP_NAME> --start-time <START_EPOCH> --end-time <END_EPOCH> --query-string 'fields @timestamp, userIdentity.arn, eventName, sourceIPAddress | filter eventName = "ConsoleLogin" | sort @timestamp desc | limit 1000'
# get-query-results
aws logs get-query-results --query-id <QUERY_ID>
# list-event-data-stores
aws cloudtrail list-event-data-stores --query 'EventDataStores[].{name:Name,arn:EventDataStoreArn,status:Status,retentionDays:RetentionPeriod,multiRegion:MultiRegionEnabled,organization:OrganizationEnabled,terminationProtection:TerminationProtectionEnabled}'
# lake-start-query
aws cloudtrail start-query --query-statement "SELECT eventTime, eventName, userIdentity.arn, sourceIPAddress FROM <EVENT_DATA_STORE_ID> WHERE eventName = 'ConsoleLogin' ORDER BY eventTime DESC LIMIT 1000" --delivery-s3-uri s3://<EVIDENCE_BUCKET>
# start-query-execution
aws athena start-query-execution --work-group primary --query-execution-context Database=cloudtrail_logs --result-configuration OutputLocation=s3://audit-reports-bucket/athena/ --query-string "SELECT eventtime, useridentity.arn, eventname, sourceipaddress FROM cloudtrail_logs WHERE eventname = 'ConsoleLogin' ORDER BY eventtime DESC LIMIT 1000"
```

## Expected output

First the saved CloudWatch Logs Insights query definitions — queryDefinitionId, name, queryLanguage of CWLI, SQL or PPL, the queryString itself and the log groups each is scoped to — which is the standing reduction-and-reporting capability as configured rather than as claimed. Then a queryId, and against it the matching records: at most 100,000 log events per query and 10,000 returned per get-query-results call, one query spanning at most 50 log groups, a 60-minute runtime ceiling and a Region-wide limit of 100 concurrent Insights queries. Then the event data stores with Status (CREATED, ENABLED, PENDING_DELETION, STARTING_INGESTION, STOPPING_INGESTION or STOPPED_INGESTION), RetentionPeriod in days from 7 to 3,653, MultiRegionEnabled, OrganizationEnabled and TerminationProtectionEnabled. Then a QueryId from CloudTrail Lake whose results are delivered to the S3 URI you named, and a QueryExecutionId from Athena whose results land in the workgroup's OutputLocation. Keep the query text beside its output — the pair is the evidence and neither half is evidence alone.

## Assertions

_No machine-checkable assertion is authored for this recipe._

## GovCloud

All three query paths run in both AWS GovCloud (US) Regions. Athena's only documented difference is that granting AWS Lake Formation permissions to Athena users who authenticate through the JDBC or ODBC driver with a SAML identity provider is unavailable. CloudWatch Logs is available with Live Tail missing and the logGroupNamePattern parameter unsupported on DescribeLogGroups — neither affects an Insights query. CloudTrail Lake is available, but Lake integrations, query generation, query results summarization, event data stores for AWS Config configuration items, AWS Audit Manager evidence and events from outside AWS, and the Activity summary widget are not: you write the SQL yourself, and non-AWS audit records cannot be pulled into the same store for reduction. One scoping trap: since 22 November 2021 CloudFront, IAM and AWS STS events are recorded in AWS GovCloud (US-West), so a single-Region search from US-East silently misses every global-service event unless the trail is multi-Region. Calls must use SSL (HTTPS) and ARNs use partition arn:aws-us-gov

## Notes

AU-7 asks for a capability, so the honest evidence is a query that ran rather than a configuration that exists — run one and keep the query text beside its output. AU-7(1), processing and sorting records by event criteria, is exactly what the filter and sort clauses in the Insights query and the WHERE and ORDER BY in the Lake and Athena statements demonstrate; pick criteria an assessor cares about — a named principal, a source IP, an event name, a bounded window — rather than a bare SELECT *. The half of AU-7 that no output proves is the requirement that reduction not alter the original content or time ordering of the records: these are read APIs, and Athena queries the CloudTrail objects in place in S3 rather than rewriting them, but that is an argument from the API contract, not a line in the result set — the property is actually carried by log file validation and object immutability, which belong to the integrity recipe, not this one. Two limits deserve to be read before the query power is. A query can only reduce records that reached the log group or the event data store, so a 90-day retention makes an annual report impossible no matter how good the SQL, which is why the retention fields are pulled alongside. And the Insights ceilings — 100,000 events per query, 10,000 per page, 50 log groups, 60 minutes — mean a broad search across a year of CloudTrail truncates silently rather than failing, so a result set sitting exactly at the limit is a truncated report and must not be filed as a complete one; Athena and CloudTrail Lake have no such row ceiling and are the right tools for a long look-back. Whether the resulting report actually supports after-the-fact investigation is a human judgement about the query, which is what keeps this partial rather than full.

## References

- {"title":"AWS CLI: logs start-query (log group and time-range parameters, queryId, 100 concurrent queries, 100,000 event and 10,000 page limits, 50 log groups, 60-minute timeout)","url":"https://docs.aws.amazon.com/cli/latest/reference/logs/start-query.html"}
- {"title":"AWS CLI: logs describe-query-definitions (saved Insights queries — queryDefinitionId, name, queryLanguage, queryString, logGroupNames)","url":"https://docs.aws.amazon.com/cli/latest/reference/logs/describe-query-definitions.html"}
- {"title":"AWS CLI: cloudtrail start-query (CloudTrail Lake SQL query against an event data store, --delivery-s3-uri, returns QueryId)","url":"https://docs.aws.amazon.com/cli/latest/reference/cloudtrail/start-query.html"}
- {"title":"AWS CLI: cloudtrail list-event-data-stores (Status, RetentionPeriod 7–3653 days, MultiRegionEnabled, OrganizationEnabled, TerminationProtectionEnabled)","url":"https://docs.aws.amazon.com/cli/latest/reference/cloudtrail/list-event-data-stores.html"}
- {"title":"AWS CLI: athena start-query-execution (--query-string, --work-group, --query-execution-context, OutputLocation; returns QueryExecutionId)","url":"https://docs.aws.amazon.com/cli/latest/reference/athena/start-query-execution.html"}
- {"title":"Amazon Athena: query AWS CloudTrail logs (query log files directly from Amazon S3 by LOCATION)","url":"https://docs.aws.amazon.com/athena/latest/ug/cloudtrail-logs.html"}
- {"title":"Amazon Athena in AWS GovCloud (US) — Region availability and documented differences","url":"https://docs.aws.amazon.com/govcloud-us/latest/UserGuide/govcloud-athena.html"}
- {"title":"Amazon CloudWatch Logs in AWS GovCloud (US) — Live Tail and logGroupNamePattern unavailable","url":"https://docs.aws.amazon.com/govcloud-us/latest/UserGuide/govcloud-cwl.html"}
- {"title":"AWS CloudTrail in AWS GovCloud (US) — CloudTrail Lake feature carve-outs and global service events recorded in us-gov-west-1","url":"https://docs.aws.amazon.com/govcloud-us/latest/UserGuide/govcloud-ct.html"}