AWSTemplateFormatVersion: '2010-09-09'
Metadata:
  'AWS::CloudFormation::Interface':
    ParameterGroups:
    - Label:
        default: 'Request rate limits by action category'
      Parameters:
      - UserCreationRateLimit
      - SignInRateLimit
      - AccountRecoveryRateLimit
      - IPReputationRateLimit
      - DefaultRateLimit
    - Label:
        default: 'Test mode'
      Parameters:
      - TestMode
    - Label:
        default: 'WAF logging and rate analysis'
      Parameters:
      - EnableWAFLogsAndRateAnalysis
      - WAFLogsS3Bucket
      - WorkgroupName
      - DatabaseName
      - WAFLogsTableName
      - WAFLogsProjectionStartDate

Parameters:
  DefaultRateLimit:
    Type: Number
    Description: "The default rate limit applied to requests that don't have specific rate limits. This is the maximum number of requests from a single IP address that are allowed in a five-minute period. Must be between 100 and 2,000,000,000."
    MinValue: 100
    MaxValue: 2000000000
    Default: 2000

  IPReputationRateLimit:
    Type: Number
    Description: "The rate limit applied to requests that originate from IP addresses typically associated with bots and other threats. This is the maximum number of requests from a single IP address that are allowed in a five-minute period. Must be between 100 and 2,000,000,000."
    MinValue: 100
    MaxValue: 2000000000
    Default: 100

  UserCreationRateLimit:
    Type: Number
    Description: "The rate limit applied to User Creation actions (sign up, confirm sign up). This is the maximum number of requests from a single IP address that are allowed in a five-minute period. Must be between 100 and 2,000,000,000."
    MinValue: 100
    MaxValue: 2000000000
    Default: 100

  AccountRecoveryRateLimit:
    Type: Number
    Description: "The rate limit applied to Account Recovery actions (change password, forgot password, confirm forgot password). This is the maximum number of requests from a single IP address that are allowed in a five-minute period. Must be between 100 and 2,000,000,000."
    MinValue: 100
    MaxValue: 2000000000
    Default: 100

  SignInRateLimit:
    Type: Number
    Description: "The rate limit applied to Sign In actions (initiate auth, respond to auth challenge). This is the maximum number of requests from a single IP address that are allowed in a five-minute period. Must be between 100 and 2,000,000,000."
    MinValue: 100
    MaxValue: 2000000000
    Default: 1000

  TestMode:
    Description: "Set this to Yes to test rules by overriding rule actions to COUNT, No to apply the default actions for rules after you've tested the impact of these rules."
    Type: String
    Default: 'Yes'
    AllowedValues:
      - 'Yes'
      - 'No'

  EnableWAFLogsAndRateAnalysis:
    Description: "Set this to Yes to enable logging for the WAF webACL to an S3 bucket and create resources for request rate analysis, No to disable WAF logging and skip creating resources for rate analysis. If No, the rest of the parameter values in this section are ignored. If Yes, choose values for the rest of the parameters in this section."
    Type: String
    Default: 'Yes'
    AllowedValues:
      - 'Yes'
      - 'No'

  WAFLogsS3Bucket:
    Description: "Name of an existing S3 bucket where WAF logs are delivered. Bucket name must start with 'aws-waf-logs-' and can end with any suffix. Only used if parameter 'EnableWAFLogsAndRateAnalysis' is Yes."
    Type: String
    Default: "aws-waf-logs-"

  DatabaseName:
    Description: "Name of the Glue database to create, which will contain the request rate analysis tables created by this template (**cannot contain hyphens**). Only used if parameter 'EnableWAFLogsAndRateAnalysis' is Yes."
    Type: String
    Default: rate_analysis

  WorkgroupName:
    Description: "Name of the Athena workgroup to create for rate analysis. Only used if parameter 'EnableWAFLogsAndRateAnalysis' is Yes."
    Type: String
    Default: rate_analysis

  WAFLogsTableName:
    Description: "Name of the Glue table for WAF Logs. Only used if parameter 'EnableWAFLogsAndRateAnalysis' is Yes."
    Type: String
    Default: waf_logs

  WAFLogsProjectionStartDate:
    Description: "Earliest date to analyze WAF logs in format YYYY/MM/DD (example: 2023/02/28). Only used if parameter 'EnableWAFLogsAndRateAnalysis' is Yes."
    Type: String
    Default: 'YYYY/MM/DD'

Conditions:
  IsTestMode: !Equals
    - !Ref TestMode
    - 'Yes'

  IsEnableWAFLogsAndRateAnalysis: !Equals
    - !Ref EnableWAFLogsAndRateAnalysis
    - 'Yes'



Resources:
# IPV4 IPSets
  WAFAllowListIPV4:
    Type: 'AWS::WAFv2::IPSet'
    Properties:
      Scope: 'REGIONAL'
      IPAddressVersion: 'IPV4'
      Name: !Sub ${AWS::StackName}-AllowListIPV4
      Description: 'Allow-list for IPV4 addresses'
      Addresses: []

  WAFDenyListIPV4:
    Type: 'AWS::WAFv2::IPSet'
    Properties:
      Scope: 'REGIONAL'
      IPAddressVersion: 'IPV4'
      Name: !Sub ${AWS::StackName}-DenyListIPV4
      Description: 'Deny-list for IPV4 addresses'
      Addresses: []

# IPV6 IPSets
  WAFAllowListIPV6:
    Type: 'AWS::WAFv2::IPSet'
    Properties:
      Scope: 'REGIONAL'
      IPAddressVersion: 'IPV6'
      Name: !Sub ${AWS::StackName}-AllowListIPV6
      Description: 'Allow-list for IPV6 addresses'
      Addresses: []

  WAFDenyListIPV6:
    Type: 'AWS::WAFv2::IPSet'
    Properties:
      Scope: 'REGIONAL'
      IPAddressVersion: 'IPV6'
      Name: !Sub ${AWS::StackName}-DenyListIPV6
      Description: 'Deny-list for IPV6 addresses'
      Addresses: []

  RateBasedRuleGroup:
    Type: AWS::WAFv2::RuleGroup
    Properties:
      Capacity: 50
      Name: !Sub ${AWS::StackName}-RateBasedRuleGroup
      Scope: REGIONAL
      VisibilityConfig:
        SampledRequestsEnabled: true
        CloudWatchMetricsEnabled: true
        MetricName: !Sub ${AWS::StackName}-RateBasedRuleGroup
      Rules:
        - Name: 'IP-DenyList'
          Priority: 10
          Action:
            Block: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-IP-DenyList
          Statement:
            OrStatement:
              Statements:
                - IPSetReferenceStatement:
                    Arn: !GetAtt WAFDenyListIPV4.Arn
                - IPSetReferenceStatement:
                    Arn: !GetAtt WAFDenyListIPV6.Arn
        - Name: 'IP-AllowList'
          Priority: 11
          Action:
            Allow: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-IP-AllowList
          Statement:
            OrStatement:
              Statements:
                - IPSetReferenceStatement:
                    Arn: !GetAtt WAFAllowListIPV4.Arn
                - IPSetReferenceStatement:
                    Arn: !GetAtt WAFAllowListIPV6.Arn

        - Name: 'SignIn-Rate-Limit'
          Priority: 101
          Action:
            Block: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-SignIn-Rate-Limit
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: !Ref SignInRateLimit
              ScopeDownStatement:
                OrStatement:
                  Statements:
                    - RegexMatchStatement:
                        RegexString: 'AWSCognitoIdentityProviderService.InitiateAuth|AWSCognitoIdentityProviderService.RespondToAuthChallenge'
                        FieldToMatch:
                          SingleHeader:
                            Name: "x-amz-target"
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'
                    - RegexMatchStatement:
                        RegexString: '\/login|\/oauth2\/authorize'
                        FieldToMatch:
                          UriPath: {}
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'



        - Name: 'AccountRecovery-Rate-Limit'
          Priority: 102
          Action:
            Block: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-AccountRecovery-Rate-Limit
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: !Ref AccountRecoveryRateLimit
              ScopeDownStatement:
                OrStatement:
                  Statements:
                    - RegexMatchStatement:
                        RegexString: 'AWSCognitoIdentityProviderService.ForgotPassword|AWSCognitoIdentityProviderService.ConfirmForgotPassword'
                        FieldToMatch:
                          SingleHeader:
                            Name: "x-amz-target"
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'
                    - RegexMatchStatement:
                        RegexString: '\/forgotPassword|\/confirmForgotPassword'
                        FieldToMatch:
                          UriPath: {}
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'


        - Name: 'UserCreation-Rate-Limit'
          Priority: 103
          Action:
            Block: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-UserCreation-Rate-Limit
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: !Ref UserCreationRateLimit
              ScopeDownStatement:
                OrStatement:
                  Statements:
                    - RegexMatchStatement:
                        RegexString: 'AWSCognitoIdentityProviderService.SignUp|AWSCognitoIdentityProviderService.ConfirmSignUp|AWSCognitoIdentityProviderService.ResendConfirmationCode'
                        FieldToMatch:
                          SingleHeader:
                            Name: "x-amz-target"
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'
                    - RegexMatchStatement:
                        RegexString: '\/signup|\/confirmUser|\/resendcode'
                        FieldToMatch:
                          UriPath: {}
                        TextTransformations:
                        - Priority: 0
                          Type: 'NONE'

        - Name: 'Default-Rate-Limit'
          Priority: 200
          Action:
            Block: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-Default-Rate-Limit
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: !Ref DefaultRateLimit

  CognitoWebACL:
    Type: AWS::WAFv2::WebACL
    Properties:
      Name: !Sub ${AWS::StackName}-CognitoWebACL
      Scope: REGIONAL
      Description: Protects Cognito User Pool endpoints
      DefaultAction:
        Allow: {}
      VisibilityConfig:
        SampledRequestsEnabled: true
        CloudWatchMetricsEnabled: true
        MetricName: !Sub ${AWS::StackName}-CognitoWebACL
      Rules:
        - Name: 'IP-Reputation'       # AWS Managed IP Reputation Rule in COUNT mode (to get labels)
          Priority: 10
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-IP-Reputation
          OverrideAction:
            Count: {}
          Statement:
            ManagedRuleGroupStatement:
              VendorName: AWS
              Name: AWSManagedRulesAmazonIpReputationList

        - Name: 'IP-Reputation-Rate-Limit'
          Priority: 11
          Action:
            !If
            - IsTestMode
            - Count: {}
            - Block: {}

          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-IP-Reputation-Rate-Limit
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: !Ref IPReputationRateLimit
              ScopeDownStatement:
                OrStatement:
                  Statements:
                    - LabelMatchStatement:
                        Scope: LABEL
                        Key: 'awswaf:managed:aws:amazon-ip-list:AWSManagedIPReputationList'
                    - LabelMatchStatement:
                        Scope: LABEL
                        Key: 'awswaf:managed:aws:amazon-ip-list:AWSManagedReconnaissanceList'
                    - LabelMatchStatement:
                        Scope: LABEL
                        Key: 'awswaf:managed:aws:amazon-ip-list:AWSManagedIPDDoSList'

        - Name: 'RateBasedRuleGroup'
          Priority: 12
          OverrideAction:
            !If
            - IsTestMode
            - Count: {}
            - None: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-RateBasedRuleGroup
          Statement:
            RuleGroupReferenceStatement:
              Arn: !GetAtt RateBasedRuleGroup.Arn

  AdvancedCognitoRuleGroup:
    Type: AWS::WAFv2::RuleGroup
    Properties:
      Description: !Sub ${AWS::StackName}-AdvancedCognitoRuleGroup
      Scope: REGIONAL
      Capacity: 50
      VisibilityConfig:
        SampledRequestsEnabled: true
        CloudWatchMetricsEnabled: true
        MetricName: !Sub ${AWS::StackName}-AdvancedCognitoRuleGroup
      Rules:
        - Name: LoginCaptcha
          Priority: 100
          Action:
            Captcha: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-LoginCaptcha
          Statement:
            AndStatement:
              Statements:
                - ByteMatchStatement:
                    FieldToMatch:
                      UriPath: {}
                    PositionalConstraint: "CONTAINS"
                    SearchString: "login"
                    TextTransformations:
                      - Priority: 10
                        Type: NONE
                - OrStatement:
                    Statements:
                      - LabelMatchStatement:
                          Scope: LABEL
                          Key: 'awswaf:managed:aws:anonymous-ip-list:AnonymousIPList'
                      - LabelMatchStatement:
                          Scope: LABEL
                          Key: 'awswaf:managed:aws:amazon-ip-list:AWSManagedIPReputationList'
          RuleLabels:
            - Name: loginCaptcha
        - Name: AppClientIdRule
          Priority: 101
          Action:
            Block:
              CustomResponse:
                ResponseCode: 429
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-AppClientIdRule
          Statement:
            RateBasedStatement:
              AggregateKeyType: 'IP'
              Limit: 1000
              ScopeDownStatement:
                AndStatement:
                  Statements:
                    - ByteMatchStatement:
                        FieldToMatch:
                          SingleHeader: {
                            Name: x-amzn-cognito-client-id
                          }
                        PositionalConstraint: "CONTAINS"
                        SearchString: "yourappclientid"
                        TextTransformations:
                          - Priority: 10
                            Type: NONE
                    - GeoMatchStatement:
                        CountryCodes:
                          - US
        - Name: LoginCaptchaBot
          Priority: 102
          Action:
            Captcha: {}
          VisibilityConfig:
            SampledRequestsEnabled: true
            CloudWatchMetricsEnabled: true
            MetricName: !Sub ${AWS::StackName}-LoginCaptchaBot
          Statement:
            AndStatement:
              Statements:
                - ByteMatchStatement:
                    FieldToMatch:
                      UriPath: {}
                    PositionalConstraint: "CONTAINS"
                    SearchString: "login"
                    TextTransformations:
                      - Priority: 10
                        Type: NONE
                - OrStatement:
                    Statements:
                      - LabelMatchStatement:
                          Scope: LABEL
                          Key: 'awswaf:managed:aws:anonymous-ip-list:AnonymousIPList'
                      - LabelMatchStatement:
                          Scope: LABEL
                          Key: 'awswaf:managed:aws:amazon-ip-list:AWSManagedIPReputationList'
                - LabelMatchStatement:
                    Scope: LABEL
                    Key: 'awswaf:managed:aws:bot-control:signal:non_browser_user_agent'
          RuleLabels:
            - Name: loginCaptchaBot

  WAFLogsToS3:
    Type: AWS::WAFv2::LoggingConfiguration
    Condition: IsEnableWAFLogsAndRateAnalysis
    Properties:
      LogDestinationConfigs:
      - !Sub 'arn:aws:s3:::${WAFLogsS3Bucket}'
      ResourceArn: !GetAtt CognitoWebACL.Arn

  AthenaWorkgroup:
    Type: AWS::Athena::WorkGroup
    Condition: IsEnableWAFLogsAndRateAnalysis
    Properties:
      Description: 'Workgroup to analyze request rates from WAF logs'
      Name: !Ref WorkgroupName
      RecursiveDeleteOption: TRUE
      State: ENABLED
      WorkGroupConfiguration:
        # EnforceWorkGroupConfiguration is True , Athena uses the workgroup-wide settings for query
        # results location and encryption. It also overrides any other settings that you specified
        # for the query in the console, by using the API operations, or with the drivers.
        # This affects you only if you run queries in this workgroup. If you do, workgroup settings are used
        EnforceWorkGroupConfiguration: TRUE
        EngineVersion:
            SelectedEngineVersion: 'Athena engine version 3'
        PublishCloudWatchMetricsEnabled: FALSE
        RequesterPaysEnabled: FALSE
        ResultConfiguration:
          OutputLocation: !Sub 's3://${WAFLogsS3Bucket}/athena_results'
          EncryptionConfiguration:
            EncryptionOption: SSE_S3
  # Create an AWS Glue database
  GlueDatabase:
    Type: AWS::Glue::Database
    Condition: IsEnableWAFLogsAndRateAnalysis
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: !Ref DatabaseName
        Description: Database to hold tables for AWS service logs
  # Create an AWS Glue table
  TableWAFLogs:
    # Creating the table waits for the database to be created
    DependsOn: GlueDatabase
    Type: AWS::Glue::Table
    Condition: IsEnableWAFLogsAndRateAnalysis
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref DatabaseName
      TableInput:
        Name: !Ref WAFLogsTableName
        Description: Analyze WAF logs
        TableType: EXTERNAL_TABLE
        Parameters:
          EXTERNAL: 'TRUE'
          projection.date.type: date
          projection.region.type: enum
          projection.region.values: !Ref AWS::Region
          projection.date.format: yyyy/MM/dd
          projection.date.interval: '1'
          projection.date.interval.unit: DAYS
          projection.enabled: 'true'
          projection.date.range: !Join [ ',' , [!Ref WAFLogsProjectionStartDate,'NOW']]
          storage.location.template: !Join
            - ''
            - - 's3://'
              - !Ref WAFLogsS3Bucket
              - '/AWSLogs/'
              - !Ref AWS::AccountId
              - '/WAFLogs/${region}/'
              - !Sub ${AWS::StackName}-CognitoWebACL
              - '/${date}/'
            #s3://aws-waf-logs-suffix/AWSLogs/655804061234/WAFLogs/${region}/CognitoWAF02-CognitoWebACL/${date}/

        PartitionKeys:
        - Name: region
          Type: string
        - Name: date
          Type: string
        StorageDescriptor:
          Columns:
          - Name: timestamp
            Type: bigint
          - Name: formatversion
            Type: int
          - Name: webaclid
            Type: string
          - Name: terminatingruleid
            Type: string
          - Name: terminatingruletype
            Type: string
          - Name: action
            Type: string
          - Name: terminatingrulematchdetails
            Type: array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>
          - Name: httpsourcename
            Type: string
          - Name: httpsourceid
            Type: string
          - Name: rulegrouplist
            Type: array<struct<rulegroupid:string,terminatingrule:struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>>,nonterminatingmatchingrules:array<struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>>>,excludedrules:string>>
          - Name: ratebasedrulelist
            Type: array<struct<ratebasedruleid:string,limitkey:string,maxrateallowed:int>>
          - Name: nonterminatingmatchingrules
            Type: array<struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>,captcharesponse:struct<responsecode:string,solvetimestamp:string>>>
          - Name: requestheadersinserted
            Type: array<struct<name:string,value:string>>
          - Name: responsecodesent
            Type: string
          - Name: httprequest
            Type: struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string>
          - Name: labels
            Type: array<struct<name:string>>
          - Name: captcharesponse
            Type: struct<responsecode:string,solvetimestamp:string,failureReason:string>

          Location: s3://bucket/AWSLogs/accountID/WAFLogs/region/webACL
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

          SerdeInfo:
            SerializationLibrary: org.openx.data.jsonserde.JsonSerDe
            Parameters:
              serialization.format: '1'

  PeakRequestCountByCategory:
    Type: AWS::Athena::NamedQuery
    Condition: IsEnableWAFLogsAndRateAnalysis
    Properties:
      Name: "Peak request rate per 5-minute period by source IP and action category"
      Description: "Use this query to estimate rate limits for the rate-based rules in the WebACL"
      Database: !Ref DatabaseName
      WorkGroup: !Ref AthenaWorkgroup
      QueryString: |

        -- Gets the top 5 source IPs sending the most requests in a 5-minute period per action category
        -- NOTE: change the start and end timestamps to match the duration of interest
        SELECT action_category, from_unixtime(time_bin*60*5) AS date_time, client_ip, request_count FROM (
          SELECT *, row_number() OVER (PARTITION BY action_category ORDER BY request_count DESC, time_bin DESC) AS row_num FROM (
            SELECT
              CASE
                WHEN ip_reputation_labels.name IN (
                  'awswaf:managed:aws:amazon-ip-list:AWSManagedIPReputationList',
                  'awswaf:managed:aws:amazon-ip-list:AWSManagedReconnaissanceList',
                  'awswaf:managed:aws:amazon-ip-list:AWSManagedIPDDoSList'
                ) THEN 'IPReputation'
                WHEN target.value IN (
                  'AWSCognitoIdentityProviderService.InitiateAuth',
                  'AWSCognitoIdentityProviderService.RespondToAuthChallenge'
                ) THEN 'SignIn'
                WHEN target.value IN (
                  'AWSCognitoIdentityProviderService.ResendConfirmationCode',
                  'AWSCognitoIdentityProviderService.SignUp',
                  'AWSCognitoIdentityProviderService.ConfirmSignUp'
                ) THEN 'UserCreation'
                WHEN target.value IN (
                  'AWSCognitoIdentityProviderService.ForgotPassword',
                  'AWSCognitoIdentityProviderService.ConfirmForgotPassword'
                ) THEN 'AccountRecovery'
                WHEN httprequest.uri IN (
                  '/login',
                  '/oauth2/authorize'
                ) THEN 'SignIn'
                WHEN httprequest.uri IN (
                  '/signup',
                  '/confirmUser',
                  '/resendcode'
                ) THEN 'UserCreation'
                WHEN  httprequest.uri IN (
                  '/forgotPassword',
                  '/confirmForgotPassword'
                ) THEN 'AccountRecovery'
                ELSE 'Default'
              END AS action_category,
              httprequest.clientip AS client_ip,
              FLOOR("timestamp"/(1000*60*5)) AS time_bin,
              COUNT(*) AS request_count
            FROM waf_logs
              LEFT OUTER JOIN UNNEST(FILTER(httprequest.headers, h -> h.name = 'x-amz-target')) AS t(target) ON TRUE
              LEFT OUTER JOIN UNNEST(FILTER(labels, l -> l.name like 'awswaf:managed:aws:amazon-ip-list:%')) AS t(ip_reputation_labels) ON TRUE
            WHERE
              from_unixtime("timestamp"/1000) BETWEEN TIMESTAMP '2022-01-01 00:00:00' AND TIMESTAMP '2023-01-01 00:00:00'
            GROUP BY 1, 2, 3
            ORDER BY 1, 4 DESC
          )
        ) WHERE row_num <= 5 ORDER BY action_category ASC, row_num ASC
