Please enable JavaScript.

Coggle requires JavaScript to display documents.

WAF DASHBOARD, Name: "create_waf_log_table", Description: "…

WAF DASHBOARD

View AWS::Athena::NamedQuery

CREATE OR REPLACE VIEW "waf_logs_view" AS 
SELECT
  from_unixtime((timestamp / 1000)) "time"
, "formatversion" "formatversion"
, "webaclid" "webaclid"
, "terminatingruleid"
, "terminatingruletype" "terminatingruletype"
, "action" "action"
, "httpsourcename" "httpsourcename"
, "httpsourceid" "httpsourceid"
, httprequest.uri "uri"
, httprequest.country "country"
, httprequest.httpversion "httpversion"
, httprequest.clientip "clientip"
, httprequest.httpmethod "httpmethod"
, httprequest.args "args"
, labels[1].name "labels"
, "region" "region"
FROM
  "waf_logs_table"
WHERE parse_datetime("date", 'yyyy/MM/dd') BETWEEN parse_datetime(
    format_datetime(
      (
        date_trunc('month', current_timestamp) - INTERVAL '1' MONTH
      ),
      'yyyy/MM/dd'
    ),
    'yyyy/MM/dd'
  ) AND parse_datetime(
    format_datetime(current_timestamp, 'yyyy/MM/dd'),
    'yyyy/MM/dd'
  )

Table AWS::Athena::NamedQuery

CREATE EXTERNAL TABLE `waf_logs_table`(
  `timestamp` bigint,
  `formatversion` int,
  `webaclid` string,
  `terminatingruleid` string,
  `terminatingruletype` string,
  `action` string,
  `terminatingrulematchdetails` array < struct < conditiontype: string,
  sensitivitylevel: string,
  location: string,
  matcheddata: array < string > > >,
  `httpsourcename` string,
  `httpsourceid` string,
  `rulegrouplist` 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,
  overriddenaction: string,
  rulematchdetails: array < struct < conditiontype: string,
  sensitivitylevel: string,
  location: string,
  matcheddata: array < string > > > > >,
  excludedrules: string > >,
  `ratebasedrulelist` array < struct < ratebasedruleid: string,
  limitkey: string,
  maxrateallowed: int > >,
  `nonterminatingmatchingrules` array < struct < ruleid: string,
  action: string,
  rulematchdetails: array < struct < conditiontype: string,
  sensitivitylevel: string,
  location: string,
  matcheddata: array < string > > >,
  captcharesponse: struct < responsecode: string,
  solvetimestamp: string > > >,
  `requestheadersinserted` array < struct < name: string,
  value: string > >,
  `responsecodesent` string,
  `httprequest` struct < clientip: string,
  country: string,
  headers: array < struct < name: string,
  value: string > >,
  uri: string,
  args: string,
  httpversion: string,
  httpmethod: string,
  requestid: string >,
  `labels` array < struct < name: string > >,
  `captcharesponse` struct < responsecode: string,
  solvetimestamp: string,
  failureReason: string >
)
PARTITIONED BY (`region` string, `date` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<waf_bucket_name>/AWSLogs/<account_id>/WAFLogs/<region>/<waf_name'
TBLPROPERTIES(
  'projection.enabled' = 'true',
  'projection.region.type' = 'enum',
  'projection.region.values' = 'us-east-1,us-west-2,eu-central-1,eu-west-1,ap-southeast-2',
  'projection.date.type' = 'date',
  'projection.date.range' = '2023/08/01,NOW',
  'projection.date.format' = 'yyyy/MM/dd',
  'projection.date.interval' = '1',
  'projection.date.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://<waf_bucket_name>/AWSLogs/<account_id>/WAFLogs/<region>/<waf_name>/${date}/'
)

AWS::QuickSight::DataSource

Properties:
      Type: ATHENA
      DataSourceId: waf-datasource-01_cft_test
      AwsAccountId: !Ref "AWS::AccountId"
      DataSourceParameters:
        AthenaParameters:
          WorkGroup: !If [WorkGroupProvided, !Ref AthenaWorkgroupName, "primary"]
      Name: WAFLogsDataset_cft_test
      SslProperties:
        DisableSsl: false
  WAFLogsAthenaSummaryDataset:
    Type: "AWS::QuickSight::DataSet"
    DependsOn:
      - CreateAthenaViewInitializer
    Properties:
      AwsAccountId: !Ref "AWS::AccountId"
      Name: waf_logs_table_dataset_cft_test
      DataSetId: waflathena-table-datasetid-01_cft_test
      ImportMode: SPICE
      PhysicalTableMap:
        WAFLogsPhysicalTableMap123:
          RelationalTable:
            DataSourceArn: !GetAtt WAFLogsAthenaDatasource.Arn
            Catalog: AwsDataCatalog
            Schema: !Ref ResourceDatabaseASL
            Name: waf_logs_view_test_cft
            InputColumns:
            - Name: time
              Type: DATETIME
            - Name: formatversion
              Type: INTEGER
            - Name: webaclid
              Type: STRING
            - Name: terminatingruleid
              Type: STRING
            - Name: terminatingruletype
              Type: STRING
            - Name: action
              Type: STRING
            - Name: httpsourcename
              Type: STRING
            - Name: httpsourceid
              Type: STRING
            - Name: uri
              Type: STRING
            - Name: country
              Type: STRING
            - Name: httpversion
              Type: STRING
            - Name: clientip
              Type: STRING
            - Name: httpmethod
              Type: STRING
            - Name: args
              Type: STRING
            - Name: labels
              Type: STRING
            - Name: region
              Type: STRING
      LogicalTableMap:
        WAFLogsLogicalTableMap123:
          Alias: waf_logs_view_test_cft
          Source:
            PhysicalTableId: WAFLogsPhysicalTableMap123
          DataTransforms:
            - TagColumnOperation:
                ColumnName: country
                Tags:
                  - ColumnGeographicRole: COUNTRY
            - TagColumnOperation:
                ColumnName: region
                Tags:
                  - ColumnGeographicRole: STATE
            - ProjectOperation:
                ProjectedColumns:
                  - time
                  - formatversion
                  - webaclid
                  - terminatingruleid
                  - terminatingruletype
                  - action
                  - httpsourcename
                  - httpsourceid
                  - uri
                  - country
                  - httpversion
                  - clientip
                  - httpmethod
                  - args
                  - labels
                  - region
      Permissions:
        - Principal: !Ref QuickSightUserArn
          Actions:
            - "quicksight:UpdateDataSetPermissions"
            - "quicksight:DescribeDataSet"
            - "quicksight:DescribeDataSetPermissions"
            - "quicksight:PassDataSet"
            - "quicksight:DescribeIngestion"
            - "quicksight:ListIngestions"
            - "quicksight:UpdateDataSet"
            - "quicksight:DeleteDataSet"
            - "quicksight:CreateIngestion"
            - "quicksight:CancelIngestion"

7 . AWS::QuickSight::Dashboard

 SourceEntity:
        SourceTemplate:
          DataSetReferences:
            - DataSetPlaceholder: waf_logs
              DataSetArn: !GetAtt WAFLogsAthenaSummaryDataset.Arn
          Arn: >-
            arn:aws:quicksight:us-east-1:<account_id>:template/waf-logs-template

aws quicksight create-dashboard --aws-account-id 570646387953 --name waf-dashboard --dashboard-id waf-dash --source-entity "{
  \"SourceTemplate\": {
    \"DataSetReferences\": [
      {
        \"DataSetPlaceholder\": \"waf_logs\",
        \"DataSetArn\": \"arn:aws:quicksight:us-east-1:570646387953:dataset/bcca5f4e-7a3a-4e1d-b516-bd9f0617ce8b\"
      }
    ],
    \"Arn\": \"arn:aws:quicksight:us-east-1:570646387953:template/waf-logs-template\"
  }
}" --permissions "[
         {
             \"Principal\": \"arn:aws:quicksight:us-east-1:570646387953:user/default/RCSL3Role/atin.mittal@rapyder.com\",
             \"Actions\": [
                     \"quicksight:DescribeDashboard\",
                     \"quicksight:ListDashboardVersions\",
                     \"quicksight:UpdateDashboardPermissions\",
                     \"quicksight:QueryDashboard\",
                     \"quicksight:UpdateDashboard\",
                     \"quicksight:DeleteDashboard\",
                     \"quicksight:DescribeDashboardPermissions\",
                     \"quicksight:UpdateDashboardPublishedVersion\"