CloudWatch Logs Insights and Cloud Logging queries, hands-on

Two log stores, two query languages

Every cloud keeps its own logs and gives you a box to question them. In AWS (Amazon Web Services) the box is CloudWatch Logs Insights, which reads log groups in Amazon CloudWatch Logs, including the CloudTrail events a trail delivers there. In Google Cloud it is the Logs Explorer, which reads Cloud Logging, where Google's own audit logs arrive with no setup. These are the first queries an incident responder writes, often before any security information and event management (SIEM) system exists, and the same queries turn into simple alerts. Azure's equivalent, Log Analytics, uses Kusto Query Language (KQL), which has its own guide.

The two languages differ in kind. A Logs Insights query is a pipeline: commands joined by pipes that filter, extract, count and sort, so it can answer "how many, and per what". The Logging query language is a filter: one true-or-false expression that decides which log entries to show, and nothing more. It cannot count, group, or relate one entry to another. In Google Cloud, counting lives in log-based metrics and in Observability Analytics, which takes SQL (Structured Query Language), taught on the SQL guide.

The question before the query

Both languages read only what you point them at, and you point outside the query. In Logs Insights you select log groups and a time range above the editor. In the Logs Explorer it is a log scope, which normally starts as the current project, and a time range. A query run against the wrong place returns an empty result with no error, exactly as a query with nothing to find does. Here is one question in each language, first Logs Insights, then Cloud Logging:

filter eventName = "StopLogging"
| fields eventTime, userIdentity.userName, sourceIPAddress

jsonPayload.eventName="StopLogging"

The first keeps the events whose eventName is StopLogging and shows three of their fields; the pipe, |, hands the events from one command to the next. The second is a whole query: an entry is shown if the expression is true for it, and the Logs Explorer decides how to display it. In Cloud Logging your JSON (JavaScript Object Notation) sits under jsonPayload, because Logging wraps every entry in an envelope of its own fields, such as logName, timestamp and severity. If the time range missed the event, or StopLogging was never there, neither query will say so. Most of this page is about telling those apart.

The lab: one leaked key, five detections

The lab is 24 CloudTrail events from one morning, trimmed to the fields this guide needs but keeping CloudTrail's real names and nesting. The access key of build-bot, a continuous integration (CI) user, has leaked. From 203.0.113.50 someone checks whose key it is, tries to list Identity and Access Management (IAM) users, Elastic Compute Cloud (EC2) instances and Secrets Manager secrets, is mostly refused, and lists the Simple Storage Service (S3) buckets. Then they create a user, backup-svc, attach AdministratorAccess to it, give it an access key, and use it to stop CloudTrail logging. Around that runs an ordinary morning: sign-ins, one without multi-factor authentication (MFA) and one failed; an administrator creating a service account; an application refused the same S3 object three times; a CI role describing instances.

Both walk-throughs build the same five detections:

• D1, trail tampering: CloudTrail logging stopped, deleted or changed.
• D2, denied-call burst: one source address refused five or more times.
• D3, new user given a key: a user created and handed an access key within 10 minutes.
• D4, console login without MFA: a successful console sign-in without multi-factor authentication.
• D5, administrator policy attached: AdministratorAccess attached to a user.

Both services refuse or hide events as old as these (step 2 of the first half and step 1 of the second say how), so both loaders give every event the current time and leave its own time untouched in eventTime. The same thing happens when you load exported logs into a new place during an investigation, so the walk-throughs keep the two times apart throughout.

Walk-through 1: CloudWatch Logs Insights

1. Make a throwaway log group

Work in an account where you may experiment, in the Region your command-line interface (CLI) uses (aws configure get region prints it). No local emulator stands in for Logs Insights: LocalStack needs a LocalStack account, and the open-source moto library's StartQuery (version 5.2.5) reads only fields, sort and limit and silently skips filter and stats, so every filter there returns every event.

mkdir -p ~/log-queries-lab && cd ~/log-queries-lab
aws logs create-log-group --log-group-name csoh-lab-cloudtrail
aws logs put-retention-policy --log-group-name csoh-lab-cloudtrail --retention-in-days 1
aws logs create-log-stream --log-group-name csoh-lab-cloudtrail --log-stream-name lab

A log group holds log streams, and a stream holds log events. The retention policy makes events eligible for deletion after a day (AWS says the deletion itself can take up to 72 hours more), and step 9 deletes the group outright. Each command prints nothing when it succeeds.

2. Load the lab

cat > cloudtrail-lab.jsonl <<'EOF'
{"eventTime":"2026-09-15T08:55:12Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"responseElements":{"ConsoleLogin":"Success"},"additionalEventData":{"MFAUsed":"Yes"}}
{"eventTime":"2026-09-15T09:02:40Z","eventSource":"iam.amazonaws.com","eventName":"CreateUser","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"requestParameters":{"userName":"svc-reporting"},"readOnly":false}
{"eventTime":"2026-09-15T09:03:05Z","eventSource":"s3.amazonaws.com","eventName":"ListBuckets","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"readOnly":true}
{"eventTime":"2026-09-15T09:10:22Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.10","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/ci-deploy/runner-4821"},"readOnly":true}
{"eventTime":"2026-09-15T09:15:47Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.21","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/bob","userName":"bob"},"responseElements":{"ConsoleLogin":"Success"},"additionalEventData":{"MFAUsed":"No"}}
{"eventTime":"2026-09-15T09:20:03Z","eventSource":"signin.amazonaws.com","eventName":"ConsoleLogin","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.77","userAgent":"Mozilla/5.0","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"errorMessage":"Failed authentication","responseElements":{"ConsoleLogin":"Failure"},"additionalEventData":{"MFAUsed":"No"}}
{"eventTime":"2026-09-15T09:31:10Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T09:47:55Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T10:01:30Z","eventSource":"s3.amazonaws.com","eventName":"GetObject","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.20","userAgent":"Boto3/1.35.10","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/reports-app/i-0a12b34c56d78e90f"},"errorCode":"AccessDenied","requestParameters":{"bucketName":"finance-reports","key":"2026/09/summary.csv"},"readOnly":true}
{"eventTime":"2026-09-15T10:02:05Z","eventSource":"sts.amazonaws.com","eventName":"GetCallerIdentity","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"readOnly":true}
{"eventTime":"2026-09-15T10:02:31Z","eventSource":"iam.amazonaws.com","eventName":"ListUsers","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:02:38Z","eventSource":"iam.amazonaws.com","eventName":"ListRoles","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:02:52Z","eventSource":"iam.amazonaws.com","eventName":"GetAccountAuthorizationDetails","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:03:05Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:09Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeVpcs","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:14Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeSecurityGroups","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"Client.UnauthorizedOperation","readOnly":true}
{"eventTime":"2026-09-15T10:03:22Z","eventSource":"secretsmanager.amazonaws.com","eventName":"ListSecrets","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"errorCode":"AccessDenied","readOnly":true}
{"eventTime":"2026-09-15T10:03:40Z","eventSource":"s3.amazonaws.com","eventName":"ListBuckets","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"readOnly":true}
{"eventTime":"2026-09-15T10:06:12Z","eventSource":"iam.amazonaws.com","eventName":"CreateUser","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc"},"readOnly":false}
{"eventTime":"2026-09-15T10:06:30Z","eventSource":"iam.amazonaws.com","eventName":"AttachUserPolicy","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc","policyArn":"arn:aws:iam::aws:policy/AdministratorAccess"},"readOnly":false}
{"eventTime":"2026-09-15T10:06:51Z","eventSource":"iam.amazonaws.com","eventName":"CreateAccessKey","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/build-bot","userName":"build-bot"},"requestParameters":{"userName":"backup-svc"},"responseElements":{"accessKey":{"userName":"backup-svc","accessKeyId":"AKIAIOSFODNN7EXAMPLE","status":"Active"}},"readOnly":false}
{"eventTime":"2026-09-15T10:09:03Z","eventSource":"cloudtrail.amazonaws.com","eventName":"StopLogging","awsRegion":"us-east-1","sourceIPAddress":"203.0.113.50","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/backup-svc","userName":"backup-svc"},"requestParameters":{"name":"arn:aws:cloudtrail:us-east-1:111122223333:trail/org-trail"},"readOnly":false}
{"eventTime":"2026-09-15T10:40:18Z","eventSource":"ec2.amazonaws.com","eventName":"DescribeInstances","awsRegion":"us-east-1","sourceIPAddress":"192.0.2.10","userAgent":"aws-cli/2.17.40","userIdentity":{"type":"AssumedRole","arn":"arn:aws:sts::111122223333:assumed-role/ci-deploy/runner-4821"},"readOnly":true}
{"eventTime":"2026-09-15T11:40:26Z","eventSource":"iam.amazonaws.com","eventName":"CreateAccessKey","awsRegion":"us-east-1","sourceIPAddress":"198.51.100.20","userAgent":"AWS Internal","userIdentity":{"type":"IAMUser","arn":"arn:aws:iam::111122223333:user/alice","userName":"alice"},"requestParameters":{"userName":"svc-reporting"},"responseElements":{"accessKey":{"userName":"svc-reporting","accessKeyId":"AKIAI44QH8DHBEXAMPLE","status":"Active"}},"readOnly":false}
EOF
shasum -a 256 cloudtrail-lab.jsonl
cat > stamp.jq <<'EOF'
(now * 1000 | floor) as $t | split("\n") | map(select(length > 0))
| to_entries | map({timestamp: ($t + .key), message: .value})
EOF
jq -R -s -f stamp.jq cloudtrail-lab.jsonl > events.json
aws logs put-log-events --log-group-name csoh-lab-cloudtrail --log-stream-name lab --log-events file://events.json

The first cat writes everything up to EOF into cloudtrail-lab.jsonl, and shasum prints its hash, which should be 35b7c80582d0650ec4850acf84f84fa4b8e5d37169ac3f7276c76f29b67c3319. The jq program in stamp.jq reads the file as text and turns each line into a log event whose message is that line exactly as written and whose timestamp is the current time in milliseconds, one millisecond apart, because a batch must be in time order. The reply to put-log-events is a short JSON document, and the thing to check is that it has no rejectedLogEventsInfo.

3. Run a query, and look inside first

In the CloudWatch console, open Logs Insights, select csoh-lab-cloudtrail as the log group, set the time range to include the moment you loaded it (the last hour will do), paste a query and choose Run. Or run queries from the shell with this helper, which sends one query to the lab group over the last 24 hours, waits for it, and prints each result row as a line of JSON:

cat > lq <<'EOF'
#!/bin/bash
# lq: run one Logs Insights query, read from stdin, over the lab log group's
# last LQ_HOURS hours (default 24). Prints one JSON line per result row.
q=$(cat)
now=$(date +%s)
id=$(aws logs start-query --log-group-name csoh-lab-cloudtrail \
  --start-time $((now - 3600 * ${LQ_HOURS:-24})) --end-time "$now" \
  --query-string "$q" --query queryId --output text) || exit 1
while :; do
  r=$(aws logs get-query-results --query-id "$id") || exit 1
  case $(jq -r .status <<< "$r") in
    Complete) break ;;
    Failed|Cancelled|Timeout|Unknown) jq -r .status <<< "$r" >&2; exit 1 ;;
  esac
  sleep 1
done
jq -c '.results[] | map(select(.field != "@ptr") | {(.field): .value}) | add' <<< "$r"
jq -r '"(\(.results | length) rows, \(.statistics.recordsScanned | floor) records scanned)"' <<< "$r"
EOF
chmod +x lq

Save a query in a file and run ./lq < query.txt, or run ./lq, paste, and press Ctrl-D. The last line it prints counts the rows and the log events the query read. Zero records scanned is what you see when the time range or the log group missed your data, but not only then: Logs Insights can pass over events that cannot match a filter without reading them, and counts those separately, as estimatedRecordsSkipped, so filter eventName = "NoSuchEvent" reads none of the lab's events. A query with no filter reads every event in range. Start with one, and check what arrived:

stats count(*) as events, count(eventName) as parsed

Expect one row: events 24 and parsed 24. stats computes totals; count(*) counts events, and count(eventName) counts only the events that have a field called eventName. Logs Insights finds the fields of a JSON event by itself and names nested ones with dots, such as userIdentity.userName, so every CloudTrail field is ready to filter on. If some events had not been read as JSON, parsed would come out smaller, and a filter on a field that was never found matches nothing without complaint. Two limits sit behind that: Logs Insights extracts at most 200 fields from one JSON event (the parse command reads the rest from @message, the raw event), and a field whose value is JSON written as a string stays one opaque value until jsonParse() opens it.

4. Filter: the trail was stopped (D1)

filter eventName in ["StopLogging", "DeleteTrail", "UpdateTrail", "PutEventSelectors"]
| fields eventTime, eventName, userIdentity.userName, sourceIPAddress

Expect one row: 2026-09-15T10:09:03Z, StopLogging, backup-svc, 203.0.113.50. filter keeps the events for which its condition is true, in tests a value against a list of complete strings, and fields chooses what to show. These four calls stop a trail, delete it, or change what it records. One happened, made by the identity created minutes before, and the other 23 events stay out, which matters as much. To match part of a value, use like or =~ with a regular expression between slashes. Both are case-sensitive unless the pattern starts with (?i), so filter eventName like /User/ finds four events (CreateUser twice, ListUsers, AttachUserPolicy) and /user/ finds none.

5. Nested fields: a sign-in without MFA (D4)

Whether a console sign-in used MFA is recorded in additionalEventData.MFAUsed. The obvious rule:

filter eventName = "ConsoleLogin" and additionalEventData.MFAUsed = "No"
| fields eventTime, userIdentity.userName, sourceIPAddress, responseElements.ConsoleLogin

Expect two rows: bob at 09:15:47 from 198.51.100.21, and alice at 09:20:03 from 192.0.2.77, whose responseElements.ConsoleLogin is Failure. The second is someone failing to sign in as alice: there was no session for MFA to protect, and a rule that fires on it is noise. The outcome decides it:

filter eventName = "ConsoleLogin" and responseElements.ConsoleLogin = "Success"
  and additionalEventData.MFAUsed = "No"
| fields eventTime, userIdentity.userName, sourceIPAddress

Expect bob only. The alarm that AWS Security Hub CSPM (cloud security posture management) checks for in its control CloudWatch.3 also requires a successful sign-in by an IAM user (userIdentity.type is IAMUser), and it tests MFAUsed != "Yes" where this page tests = "No".

6. Count, then threshold: the burst that never fires (D2)

A first draft of a rule for a leaked key being tried out: count AccessDenied errors per source address, and alert at five.

filter errorCode = "AccessDenied"
| stats count(*) as denied by sourceIPAddress
| filter denied >= 5

No results. Delete the last line and you see two rows: 203.0.113.50 with 4 and 192.0.2.20 with 3, so nobody reaches five. A quiet morning, or a rule that cannot see what happened? Take the threshold away and ask which error codes exist:

filter ispresent(errorCode)
| stats count(*) as events by errorCode

Expect AccessDenied 7 and Client.UnauthorizedOperation 3. EC2 reports a refusal as Client.UnauthorizedOperation, so the draft counted four of the attacker's seven refusals, one short of its own threshold. Other services spell a refusal differently again: the metric filter Security Hub CSPM's control CloudWatch.2 expects matches any code ending in UnauthorizedOperation or starting with AccessDenied, such as AccessDeniedException.

filter errorCode like /AccessDenied|UnauthorizedOperation/
| stats count(*) as denied, values(errorCode) as codes by sourceIPAddress
| filter denied >= 5

Expect one row: 203.0.113.50, 7, and both codes. The regular expression also catches prefixed and suffixed spellings that an exact list would miss, values() shows whoever reads the result which codes were counted, and the application's three refusals stay under the threshold, which is the negative case. A filter after stats tests the totals, because stats hands on one row per group.

7. Time: whose clock?

@timestamp is the time CloudWatch Logs holds for each event, and it is the time the range selector and the bin() function use. Here it is the moment you loaded the lab, all 24 events within a few milliseconds, so binning on it puts everything in one bucket. Each event's own time is in eventTime, as text. parseDate() turns text into milliseconds since 1970 using a Java date pattern, fromMillis() turns that into a timestamp, and datefloor() rounds it down to the start of its ten-minute slot:

filter errorCode like /AccessDenied|UnauthorizedOperation/
| fields datefloor(fromMillis(parseDate(eventTime, "yyyy-MM-dd'T'HH:mm:ssX")), 10m) as slot
| stats count(*) as denied by sourceIPAddress, slot
| sort slot asc

Expect four rows: 192.0.2.20 once each in the 09:30, 09:40 and 10:00 slots, and 203.0.113.50 seven times at 10:00. The attacker's refusals came within a minute of each other; the application's are one every quarter of an hour, something retrying rather than someone exploring. Slots start on the clock, so a burst straddling 10:10 would be split between two. When CloudTrail delivers to a log group itself, compare @timestamp with eventTime on a few events before you use either for windows. Where they agree, stats count(*) by bin(10m) does this job in one word. One trap in both functions: AWS caps the number in front of each unit, so 600s is quietly read as 60 seconds (the cap is 60 for s and m, 24 for h), and a ten-minute window has to be written 10m.

8. Two events, one user: a key within ten minutes (D3)

Creating a user and immediately giving it an access key is a way to keep access after the leaked key is revoked. That is two events, and a stats grouped by the user both calls name puts them side by side. Arithmetic on its totals, such as the latest time minus the earliest, can go inside stats itself; after it, a command can use only what stats defined:

filter eventName in ["CreateUser", "CreateAccessKey"]
| fields parseDate(eventTime, "yyyy-MM-dd'T'HH:mm:ssX") as t
| stats countDistinct(eventName) as calls, (max(t) - min(t)) / 1000 as gapSeconds by requestParameters.userName

Expect two rows: backup-svc with 2 calls and gapSeconds 39, and svc-reporting with 2 calls and 9466. That second user is alice doing her job: she created it at 09:02 and gave it a key two hours and 37 minutes later. The detection is the window:

filter eventName in ["CreateUser", "CreateAccessKey"]
| fields parseDate(eventTime, "yyyy-MM-dd'T'HH:mm:ssX") as t
| stats countDistinct(eventName) as calls, (max(t) - min(t)) / 1000 as gapSeconds by requestParameters.userName
| filter calls = 2 and gapSeconds <= 600

Expect backup-svc only: thirty-nine seconds, by the leaked identity, from the attacker's address. The span does not check which call came first. Logs Insights also has a join command, limited to one per query and to equality, for matching events in one log group with events in another; within one group, stats by a shared value is the simpler tool.

9. Make it an alarm, test it both ways, clean up

A Logs Insights query runs when you ask it to. Scheduled queries can rerun one on a timetable and send the results to Amazon S3 or Amazon EventBridge, where you would build the alert yourself. The long-standing way to alert on CloudTrail in a log group is a metric filter, which turns matching events into a number, and a CloudWatch alarm on that number: the pair Security Hub CSPM's controls CloudWatch.1 to CloudWatch.14 look for. A metric filter is written in a third syntax, the filter pattern, in which $. selects a JSON field, * in a quoted value is a wildcard, and && and || join terms. This is the pattern CloudWatch.2 expects for refused application programming interface (API) calls:

aws logs put-metric-filter --log-group-name csoh-lab-cloudtrail --filter-name refused-calls \
  --filter-pattern '{($.errorCode="*UnauthorizedOperation") || ($.errorCode="AccessDenied*")}' \
  --metric-transformations metricName=RefusedCalls,metricNamespace=CSOHLab,metricValue=1
aws cloudwatch put-metric-alarm --alarm-name csoh-lab-refused-calls \
  --alarm-description "Five or more refused AWS API calls in ten minutes. ATT&CK T1580, T1087.004" \
  --namespace CSOHLab --metric-name RefusedCalls --statistic Sum --period 600 \
  --evaluation-periods 1 --threshold 5 --comparison-operator GreaterThanOrEqualToThreshold \
  --treat-missing-data notBreaching

The alarm adds up refusals across the whole log group, not per address as D2 does, and it has no action, so its state changes but nobody is told; --alarm-actions with an Amazon Simple Notification Service (SNS) topic does the telling. Its description carries the mapping to MITRE ATT&CK, MITRE's knowledge base of adversary techniques: Cloud Infrastructure Discovery (T1580) and Account Discovery: Cloud Account (T1087.004).

A metric filter never looks back: AWS documents that it publishes data only for events that happen after it was created, so the ten refusals already in the group count for nothing. Test the negative case first, by replaying the application's three refusals with fresh timestamps. Wait about five minutes, then read the alarm's state:

grep -F '"sourceIPAddress":"192.0.2.20"' cloudtrail-lab.jsonl | jq -R -s -f stamp.jq > replay.json
aws logs put-log-events --log-group-name csoh-lab-cloudtrail --log-stream-name lab --log-events file://replay.json
aws cloudwatch describe-alarms --alarm-names csoh-lab-refused-calls --query 'MetricAlarms[0].StateValue' --output text

Expect OK (straight after you create it, an alarm reads INSUFFICIENT_DATA until its first evaluation). Wait at least ten more minutes, so that the next replay cannot share a ten-minute period with this one, then replay the attacker's seven:

grep -F '"sourceIPAddress":"203.0.113.50"' cloudtrail-lab.jsonl | grep -F '"errorCode"' | jq -R -s -f stamp.jq > replay.json
aws logs put-log-events --log-group-name csoh-lab-cloudtrail --log-stream-name lab --log-events file://replay.json
aws cloudwatch describe-alarms --alarm-names csoh-lab-refused-calls --query 'MetricAlarms[0].StateValue' --output text

Expect ALARM once the alarm has evaluated the new data, a few minutes later; run the last line again until it changes. Then remove everything this half created:

aws cloudwatch delete-alarms --alarm-names csoh-lab-refused-calls
aws logs delete-metric-filter --log-group-name csoh-lab-cloudtrail --filter-name refused-calls
aws logs delete-log-group --log-group-name csoh-lab-cloudtrail

One thing stays: the RefusedCalls metric in the CSOHLab namespace. CloudWatch metrics cannot be deleted. AWS charges for a custom metric only in hours when data is sent to it, so this one costs nothing from now on; it drops out of the console after two weeks without data and expires after 15 months.

Walk-through 2: the Cloud Logging query language

1. Write the lab to a throwaway log

Use a Google Cloud project that is not production; if you own it, you have every permission this half needs. There is nothing to create, because writing to a log name makes the log. In the folder from the first half (or after running the cat heredoc from its step 2):

cd ~/log-queries-lab
gcloud config get project
while IFS= read -r event; do gcloud logging write cloudtrail-lab "$event" --payload-type=json; done < cloudtrail-lab.jsonl

Check that the second line prints the project you mean before the loop writes anything. The loop writes one entry per line of the file, in order, each with the current time, and --payload-type=json makes the line the entry's jsonPayload, so its fields can be queried. Writing the lab with its own times would fail without a word: the Logging API accepts entries dated further back than a log bucket keeps logs, but no bucket stores them, and a project's _Default bucket keeps 30 days unless someone changed it.

2. Find what arrived

In the Google Cloud console open Logging, then Logs Explorer, paste this into the query editor, set the time range to Last 1 hour, and run it:

log_id("cloudtrail-lab")

Expect 24 entries. log_id() picks a log by its short name. The long form is logName="projects/PROJECT_ID/logs/cloudtrail-lab", in which any slash inside the log's own name must be written %2F; a log name copied without that encoding matches nothing. The time range is the first trap: since December 2025 the Logs Explorer opens on the last five minutes, not the last hour, so a lab loaded ten minutes ago is already out of view. A query that names timestamp switches the selector off and uses its own range. From the shell, gcloud logging read takes the same query language:

gcloud logging read 'log_id("cloudtrail-lab")' --format='value(jsonPayload.eventName)' | wc -l

Expect 24. gcloud logging read has a default of its own, --freshness=1d: unless the query names a timestamp, it reads only the last day, so the same command next week prints 0. Counting lines in the shell is the honest way to count here, because the query language selects entries and has no way to count them.

3. Filter: the trail was stopped (D1)

log_id("cloudtrail-lab")
jsonPayload.eventName=("StopLogging" OR "DeleteTrail" OR "UpdateTrail" OR "PutEventSelectors")

Expect one entry: the StopLogging by backup-svc from 203.0.113.50. Comparisons on separate lines are joined by an implicit AND, and a parenthesised list on the right applies the field and the operator to each value in it. Three rules differ from Logs Insights:

• String comparisons ignore case, so jsonPayload.eventName="stoplogging" finds the same entry.
• Field names under jsonPayload keep their case. jsonPayload.eventname is a field no entry has, and Google documents that a comparison on a missing field fails silently, so it matches nothing.
• = compares the whole value, while : ("has") matches any part of it. jsonPayload.eventName:"user" finds four entries (CreateUser twice, ListUsers, AttachUserPolicy), and jsonPayload.eventName="user" finds none.

4. Nested fields, and the precedence trap (D4)

log_id("cloudtrail-lab")
jsonPayload.eventName="ConsoleLogin"
jsonPayload.additionalEventData.MFAUsed="No"

Expect two entries, bob's sign-in and the failed attempt on alice, for the reason step 5 of the first half gives. Add the outcome:

log_id("cloudtrail-lab")
jsonPayload.eventName="ConsoleLogin"
jsonPayload.responseElements.ConsoleLogin="Success"
jsonPayload.additionalEventData.MFAUsed="No"

Expect bob only. Now two rules that catch people who know other languages. The operators AND, OR and NOT must be written in capitals: lowercase and, or and not are read as search terms, each matching any entry with that text anywhere. And NOT binds tightest, then OR, then AND, so OR groups before AND, the reverse of most other query languages. This query is meant to find the stopped trail or an administrator policy attachment:

log_id("cloudtrail-lab")
jsonPayload.eventName="StopLogging" OR jsonPayload.eventName="AttachUserPolicy" AND jsonPayload.requestParameters.policyArn:"AdministratorAccess"

It returns only the attachment. Cloud Logging reads it as "(StopLogging or AttachUserPolicy) and a policyArn containing AdministratorAccess", and the StopLogging event has no policyArn. Google's documentation asks for parentheses wherever AND and OR meet. Take it literally:

log_id("cloudtrail-lab")
(jsonPayload.eventName="StopLogging" OR (jsonPayload.eventName="AttachUserPolicy" AND jsonPayload.requestParameters.policyArn:"AdministratorAccess"))

Expect two entries.

5. Counting happens elsewhere (D2)

log_id("cloudtrail-lab")
jsonPayload.errorCode:("AccessDenied" OR "UnauthorizedOperation")

Expect ten entries, the attacker's seven and the application's three. Because : matches part of a value and ignores case, this list also catches the longer spellings. To count per address, take the entries out of Logging:

gcloud logging read 'log_id("cloudtrail-lab") jsonPayload.errorCode:("AccessDenied" OR "UnauthorizedOperation")' --format='value(jsonPayload.sourceIPAddress)' | sort | uniq -c

Expect 3 for 192.0.2.20 and 7 for 203.0.113.50. Write jsonPayload.errorCode="AccessDenied" instead and the counts are 3 and 4, the same under-count as in the first half. For a standing detection, Google's tool is a log-based metric: a counter of the entries that match a filter, with labels taken from fields such as the source address, watched by a Cloud Monitoring alerting policy with a threshold. To ask the question directly, Observability Analytics runs SQL over your log data.

6. Two events: where the language stops (D3)

Pairing a CreateUser with a CreateAccessKey for the same user means looking at two entries at once, which a filter cannot express. List both kinds and compare the times yourself:

gcloud logging read 'log_id("cloudtrail-lab") jsonPayload.eventName=("CreateUser" OR "CreateAccessKey")' --format='table(jsonPayload.eventTime, jsonPayload.eventName, jsonPayload.requestParameters.userName)'

Expect four rows, newest first: the key for svc-reporting at 11:40:26, the key for backup-svc at 10:06:51, backup-svc created at 10:06:12, and svc-reporting created at 09:02:40. Thirty-nine seconds against two and a half hours is easy to see in four rows and impossible in four thousand, which is when the window belongs in Observability Analytics.

7. Make it a log-based alert, test it both ways, clean up

A log-based alerting policy opens an alert in Cloud Monitoring, and notifies whoever its channels name, when a new entry matches its filter. It suits D1, where one event is enough, and Google documents that it cannot count. Save the policy and create it:

cat > trail-tamper-alert.json <<'EOF'
{
  "displayName": "CSOH lab: CloudTrail logging stopped, deleted or changed",
  "documentation": {
    "content": "A trail was stopped, deleted or changed by ${log.extracted_label.actor}. ATT&CK T1685.002 Disable or Modify Cloud Log.",
    "mimeType": "text/markdown"
  },
  "conditions": [{
    "displayName": "Log match condition",
    "conditionMatchedLog": {
      "filter": "log_id(\"cloudtrail-lab\") jsonPayload.eventName=(\"StopLogging\" OR \"DeleteTrail\" OR \"UpdateTrail\" OR \"PutEventSelectors\")",
      "labelExtractors": {"actor": "EXTRACT(jsonPayload.userIdentity.userName)"}
    }
  }],
  "combiner": "OR",
  "alertStrategy": {"notificationRateLimit": {"period": "300s"}, "autoClose": "1800s"}
}
EOF
gcloud monitoring policies create --policy-from-file=trail-tamper-alert.json

The filter is step 3's query, with its quotes escaped for JSON. labelExtractors copies the actor's name into the notification, notificationRateLimit allows one notification per five minutes, and autoClose closes the alert after 30 minutes, the shortest Google allows. The policy lists no notificationChannels, so its alerts appear only on Monitoring's Alerting page until you add one. Its documentation names the ATT&CK technique, T1685.002 Disable or Modify Cloud Log, under the Defense Impairment tactic, which ATT&CK added in version 19. The command prints the policy's name, projects/PROJECT_ID/alertPolicies/POLICY_ID; keep it for the clean-up. Like a metric filter, the policy matches entries as they arrive, so the StopLogging already in the log does not fire it. Write the negative case, wait a few minutes and see that no alert opens, then write the positive case:

gcloud logging write cloudtrail-lab "$(grep -F GetCallerIdentity cloudtrail-lab.jsonl)" --payload-type=json
gcloud logging write cloudtrail-lab "$(grep -F StopLogging cloudtrail-lab.jsonl)" --payload-type=json

After the second, expect an alert for the policy on the Alerting page within a minute or so, naming backup-svc in its summary. Then delete the policy (paste the name the create command printed) and the log, both without a confirmation prompt:

gcloud monitoring policies delete projects/PROJECT_ID/alertPolicies/POLICY_ID --quiet
gcloud logging logs delete cloudtrail-lab --quiet

8. The same questions in your own audit logs

The lab's paths are artificial: Google Cloud writes its own activity as Cloud Audit Logs entries, with an AuditLog object under protoPayload. The language stays the same and the paths change:

The nearest thing to D1 is a change to where logs go: a sink routes logs out of Logging, and an exclusion stops them being stored. Each change is an Admin Activity audit log, which is always on (add the same service's UpdateBucket and DeleteBucket methods if how long logs are kept matters too):

log_id("cloudaudit.googleapis.com/activity")
protoPayload.methodName=("google.logging.v2.ConfigServiceV2.DeleteSink" OR "google.logging.v2.ConfigServiceV2.UpdateSink" OR "google.logging.v2.ConfigServiceV2.CreateExclusion" OR "google.logging.v2.ConfigServiceV2.UpdateExclusion")

For D3, look for protoPayload.methodName="google.iam.admin.v1.CreateServiceAccountKey". For D5, this is the owner-grant half of the filter Google's Security Health Analytics expects a log-based metric to use:

log_id("cloudaudit.googleapis.com/activity")
protoPayload.serviceData.policyDelta.bindingDeltas.action="ADD"
protoPayload.serviceData.policyDelta.bindingDeltas.role="roles/owner"

bindingDeltas is a list, and a comparison on a list passes if any element matches, so one change that adds a viewer and removes an owner matches as well. Read the entry before you act on it.

Two of the five do not carry over cleanly. A refused call is recorded with protoPayload.status.code=7, but most reads, such as listing service accounts, are Data Access audit logs, and those are off by default for every service except BigQuery: an attacker refused while exploring may leave nothing to find, and where a refused read is logged, the caller's email can be redacted unless the caller is a service account. And sign-ins (D4) are not in a project's audit logs at all. Google Workspace Login Audit logs reach Cloud Logging at the organization level, as Data Access logs, and only if an administrator shares Workspace data with Google Cloud.

Hands-on exercises

filter eventName in ["AttachUserPolicy", "AttachGroupPolicy", "AttachRolePolicy"]
| filter requestParameters.policyArn like /AdministratorAccess$/
| fields eventTime, userIdentity.userName, eventName,
    coalesce(requestParameters.userName, requestParameters.groupName, requestParameters.roleName) as target

log_id("cloudtrail-lab")
jsonPayload.eventName="StopLogging" or jsonPayload.eventName="AttachUserPolicy"

log_id("cloudtrail-lab")
jsonPayload.eventName=("StopLogging" OR "AttachUserPolicy")

log_id("cloudtrail-lab")
NOT jsonPayload.errorCode="AccessDenied"

log_id("cloudtrail-lab")
jsonPayload.errorCode!="AccessDenied"

log_id("cloudtrail-lab")
jsonPayload.errorCode:* NOT jsonPayload.errorCode="AccessDenied"

stats countDistinct(userIdentity.arn) as identities, values(userIdentity.userName) as users by sourceIPAddress
| filter identities > 1

Common mistakes

• Querying the wrong place or time. Logs Insights reads only the log groups and time range you select; the Logs Explorer opens on its default scope and the last five minutes; gcloud logging read reads one day unless told otherwise. An empty result from any of them proves nothing until you have checked those.
• Trusting a load because the command succeeded. PutLogEvents refuses old and future events in a field of its reply, and Cloud Logging accepts entries older than a bucket's retention and stores none of them. Count what arrived.
• Copying old logs into a new log group. Logs Insights cannot read events dated before the group was created, even ones CloudWatch Logs accepted.
• Misspelling the first command. Logs Insights runs filtr eventName = "StopLogging" without an error and returns nothing, exactly as if no event matched, while a misspelled command after a |, such as fieldz, is refused as a malformed query. When a query that should find something finds nothing, read it again before you read the result.
• Filtering on a field that was never extracted. A non-JSON event, a field past the 200th, or JSON stored as a string all give a filter nothing to match. Compare count(*) with count(field).
• Counting one spelling of a refusal. A rule that counts only AccessDenied misses EC2's Client.UnauthorizedOperation, under-counts, and stays silent.
• Writing bin(600s). Logs Insights caps seconds at 60 and builds one-minute buckets without a word. Write 10m.
• Lowercase operators, or AND and OR without parentheses, in Cloud Logging. Lowercase and, or and not are search terms, and OR groups before AND.
• Reading NOT field=x and field!=x as the same. On an entry without the field, the first is true and the second is false.
• Assuming Google Cloud logged the reads. Data Access audit logs are off by default except for BigQuery, and a project's logs hold neither the organization's own changes nor sign-ins.