Analyzing CloudWatch Logs Service

1. Summarize CloudWatch Logs API Calls

Counts all CloudWatch Logs API calls grouped by event name. Provides a high-level overview of which Logs APIs are being invoked most frequently in your environment.

SQL
SELECT eventName AS API,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventSource = 'logs.amazonaws.com'
GROUP BY eventName
ORDER BY CallCount DESC

2. Summarize CloudWatch Logs: Mutating API Calls Only

Filters out read-only operations (Get, List, Describe) to focus exclusively on mutating API calls. Useful for change auditing and understanding write activity against the Logs service.

SQL
SELECT eventSource AS AWS_Service,
       eventName AS API,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventSource = 'logs.amazonaws.com'
  AND eventName NOT LIKE '%Get%'
  AND eventName NOT LIKE '%List%'
  AND eventName NOT LIKE '%Describe%'
GROUP BY eventSource, eventName
ORDER BY CallCount DESC

3. Summarize CloudWatch Logs API Calls with Errors

Identifies all CloudWatch Logs API calls that returned an error, grouped by event name, error code, and error message. Helps pinpoint failing operations and their root causes.

SQL
SELECT eventName AS API,
       errorCode,
       errorMessage,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventSource = 'logs.amazonaws.com' AND errorCode IS NOT NULL
GROUP BY eventName, errorCode, errorMessage
ORDER BY CallCount DESC

4. Filter for CreateLogAnomalyDetector API Calls

Retrieves details of CreateLogAnomalyDetector API calls including the target log group ARN, detector name, evaluation frequency, and anomaly visibility time. Useful for auditing anomaly detection configurations.

SQL
SELECT eventTime,
       eventName AS API,
       `requestParameters.logGroupArnList.0` AS LogGroupARN,
       `requestParameters.detectorName` AS DetectorName,
       `requestParameters.evaluationFrequency` AS EvaluationFrequency,
       `requestParameters.anomalyVisibilityTime` AS AnomalyVisibilityTime
FROM `LogGroup`
WHERE eventName = 'CreateLogAnomalyDetector'
ORDER BY eventTime DESC

5. Filter for UpdateAnomaly API Calls & Anomaly Detector ID

Filters UpdateAnomaly calls for a specific anomaly detector ARN. Shows pattern IDs, suppression types, and baseline values to track how anomaly suppression rules are being modified.

SQL
SELECT eventTime,
       eventName AS API,
       `requestParameters.patternId` AS patternId,
       `requestParameters.suppressionType` AS SuppressionType,
       `requestParameters.baseline` AS Baseline
FROM `LogGroup`
WHERE eventName = 'UpdateAnomaly'
  AND `requestParameters.anomalyDetectorArn` LIKE '%anomaly-detector:193927d5-b84e-4bfb-a103-6fef48ec5360'
ORDER BY eventTime DESC

6. Filter for CreateLogStream API Calls

Lists all CreateLogStream API calls with their associated log group name, log stream name, user agent, and timestamp. Helps track which services or users are creating new log streams.

SQL
SELECT eventTime,
       eventName,
       userAgent,
       `requestParameters.logGroupName` AS Log_Group_Name,
       `requestParameters.logStreamName` AS LogStream
FROM `LogGroup`
WHERE eventName = 'CreateLogStream'
ORDER BY eventTime DESC

7. Summarize CreateLogStream API Calls by Log Group Name & Client IP

Aggregates CreateLogStream calls by log group name and source IP address. Identifies which log groups are receiving the most new streams and from which network origins.

SQL
SELECT `requestParameters.logGroupName` AS Log_Group_Name,
       sourceIPAddress AS Client_IP,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'CreateLogStream'
GROUP BY `requestParameters.logGroupName`, sourceIPAddress
ORDER BY CallCount DESC

8. Summarize DeleteLogGroup API Calls by UserAgent, Log Group Name & Client IP

Tracks DeleteLogGroup operations grouped by IAM identity, user agent, source IP, and log group name. Critical for auditing destructive operations against your log groups.

SQL
SELECT `userIdentity.arn` AS ARN,
       userAgent,
       sourceIPAddress AS Client_IP,
       `requestParameters.logGroupName`,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'DeleteLogGroup'
GROUP BY `userIdentity.arn`, userAgent, sourceIPAddress, `requestParameters.logGroupName`
ORDER BY CallCount DESC

9. Summarize DeleteSubscriptionFilter API Calls

Lists all DeleteSubscriptionFilter operations showing the IAM identity, source IP, target log group, and the subscription filter name being removed. Useful for tracking changes to log delivery pipelines.

SQL
SELECT `userIdentity.arn` AS IAM_Role_User,
       sourceIPAddress AS Client_IP,
       `requestParameters.logGroupName` AS Log_Group_Name,
       `requestParameters.filterName` AS Subscription_Filter
FROM `LogGroup`
WHERE eventName = 'DeleteSubscriptionFilter'

10. Summarize GetLogEvents API Calls by IAM Role, UserAgent, Client IP & Log Group Name 1

Counts GetLogEvents calls grouped by IAM role, user agent, source IP, and log group identifier. Reveals which identities and tools are reading log data most frequently.

SQL
SELECT `userIdentity.arn` AS IAM_Role_User,
       userAgent,
       sourceIPAddress AS Client_IP,
       `requestParameters.logGroupIdentifier` AS Log_Group_Name,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'GetLogEvents'
GROUP BY `userIdentity.arn`, userAgent, sourceIPAddress, `requestParameters.logGroupIdentifier`
ORDER BY CallCount DESC

11. Summarize GetLogEvents API Calls by IAM Role, UserAgent, Client IP & Log Group Name 2

Similar to Query 10 but filtered specifically on AssumedRole identity types. Isolates log read activity from assumed roles, excluding direct IAM user access.

SQL
SELECT `userIdentity.arn` AS IAM_Role,
       userAgent,
       sourceIPAddress AS ClientIP,
       `requestParameters.logGroupIdentifier` AS Log_Group_Name,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'GetLogEvents' AND `userIdentity.type` = 'AssumedRole'
GROUP BY `userIdentity.arn`, userAgent, sourceIPAddress, `requestParameters.logGroupIdentifier`
ORDER BY CallCount DESC

12. Timeseries: GetLogEvents API Calls by Second (Filtered on a Specific Log Group)

Creates a time-series count of GetLogEvents calls binned by second, filtered on a specific log group. Ideal for visualizing burst patterns in log read operations against a particular log group.

SQL
SELECT COUNT(*) AS CallCount,
       bin(1s) AS time_bin
FROM `LogGroup`
WHERE eventName = 'GetLogEvents'
  AND `requestParameters.logGroupIdentifier` LIKE '%/aws/lambda/CWT-Automate-CWL-Set-Retention'
GROUP BY bin(1s)
ORDER BY time_bin DESC

13. Timeseries: GetLogEvents API Calls by Second (Filtered on a Specific IAM Role or User)

Creates a time-series count of GetLogEvents calls binned by second, filtered on a specific IAM role. Useful for identifying burst patterns from a particular user or service role accessing logs.

SQL
SELECT COUNT(*) AS CallCount,
       bin(1s) AS time_bin
FROM `LogGroup`
WHERE eventName = 'GetLogEvents'
  AND `userIdentity.arn` LIKE '%assumed-role/Admin/rtaa-Isengard'
GROUP BY bin(1s)
ORDER BY time_bin DESC

14. Filter for StartQuery API Calls

Retrieves all StartQuery API calls with their CloudTrail event ID, time range, target log group, IAM identity, and the query string executed. Provides full visibility into Log Insights query activity.

SQL
SELECT eventID AS CTrailEventID,
       `requestParameters.startTime` AS StartTime,
       `requestParameters.endTime` AS EndTime,
       `requestParameters.logGroupName` AS LogGroupName,
       `requestParameters.logGroupNames.0` AS LogGroup,
       `userIdentity.arn` AS Role,
       `requestParameters.queryString` AS Query
FROM `LogGroup`
WHERE eventName = 'StartQuery'

15. Filter for StartQuery API Calls based on Log Group, Query String

Filters StartQuery calls for a specific log group and query string pattern. Useful for finding specific Log Insights queries run against targeted log groups, such as WAF logs with cookie-related queries.

SQL
SELECT eventID AS CTrailEventID,
       `requestParameters.startTime` AS StartTime,
       `requestParameters.endTime` AS EndTime,
       `requestParameters.logGroupNames.0` AS LogGroup,
       `requestParameters.queryString` AS Query
FROM `LogGroup`
WHERE eventName = 'StartQuery'
  AND `requestParameters.logGroupNames.0` LIKE '%aws-waf-logs-cpep-prd-cp%'
  AND `requestParameters.queryString` LIKE '%httpRequest.headers%Cookie%'

16. Summarize StartQuery API Calls by Client IP, UserAgent & Log Group Name

Aggregates StartQuery calls by source IP, user agent, and log group name. Reveals which tools and users are running the most Log Insights queries and against which log groups.

SQL
SELECT sourceIPAddress AS Client_IP,
       userAgent,
       `requestParameters.logGroupName` AS Log_Group_Name,
       `requestParameters.logGroupNames.0` AS Log_Group_Names,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'StartQuery'
GROUP BY sourceIPAddress, userAgent, `requestParameters.logGroupName`, `requestParameters.logGroupNames.0`
ORDER BY CallCount DESC

17. Summarize StartQuery API Calls (Filter for Log Group partial string)

Filters and aggregates StartQuery calls where the log group name contains a partial string match. Useful for scoping query activity to a specific set of related log groups.

SQL
SELECT sourceIPAddress AS Client_IP,
       userAgent,
       `requestParameters.logGroupNames.0` AS Log_Group_Name,
       COUNT(*) AS CallCount
FROM `LogGroup`
WHERE eventName = 'StartQuery'
  AND `requestParameters.logGroupNames.0` LIKE '%api-calls%'
GROUP BY sourceIPAddress, userAgent, `requestParameters.logGroupNames.0`
ORDER BY CallCount DESC

Usage Notes

  • Replace `LogGroup` with the name of your CloudTrail log group.
  • Replace IAM ARNs and log group names with values relevant to your environment.
  • These queries use the SQL query language — ensure your Log Insights console is set to SQL mode.
  • The bin() function in timeseries queries can be adjusted: bin(1m), bin(5m), or bin(1h) for different granularities.
  • Results can be visualized directly in a CloudWatch Dashboard using Log Insights widgets.