Predefine query view

[MihaiBojin]

CREATE VIEW public.waf_events AS
SELECT 
    rayname,
    zone_id,
    datetime,
    data->>'ref' as ref,
    data->>'kind' as kind,
    data->>'action' as action,
    data->>'ruleId' as rule_id,
    data->>'source' as source,
    data->>'rayName' as ray_name,
    data->>'clientIP' as client_ip,
    data->>'metadata' as metadata,
    data->>'clientAsn' as client_asn,
    data->>'rulesetId' as ruleset_id,
    data->>'userAgent' as user_agent,
    data->>'matchIndex' as match_index,
    data->>'description' as description,
    (data->>'zoneVersion')::integer as zone_version,
    data->>'edgeColoName' as edge_colo_name,
    data->>'clientIPClass' as client_ip_class,
    (data->>'sampleInterval')::integer as sample_interval,
    data->>'clientCountryName' as client_country_name,
    data->>'clientRefererHost' as client_referer_host,
    data->>'clientRefererPath' as client_referer_path,
    data->>'clientRequestPath' as client_request_path,
    (data->>'contentScanNumObj')::integer as content_scan_num_obj,
    data->>'originatorRayName' as originator_ray_name,
    data->>'clientRefererQuery' as client_referer_query,
    data->>'clientRequestQuery' as client_request_query,
    (data->>'edgeResponseStatus')::integer as edge_response_status,
    data->>'clientRefererScheme' as client_referer_scheme,
    data->>'clientRequestScheme' as client_request_scheme,
    data->>'clientASNDescription' as client_asn_description,
    (data->>'contentScanHasFailed')::integer as content_scan_has_failed,
    (data->>'originResponseStatus')::integer as origin_response_status,
    data->>'apiGatewayMatchedHost' as api_gateway_matched_host,
    data->>'clientRequestHTTPHost' as client_request_http_host,
    data->>'contentScanObjResults' as content_scan_obj_results,
    data->>'contentScanObjSizes' as content_scan_obj_sizes,
    data->>'contentScanObjTypes' as content_scan_obj_types,
    data->>'apiGatewayMatchedEndpoint' as api_gateway_matched_endpoint,
    data->>'clientRequestHTTPProtocol' as client_request_http_protocol,
    (data->>'contentScanNumMaliciousObj')::integer as content_scan_num_malicious_obj,
    data->>'clientRequestHTTPMethodName' as client_request_http_method_name,
    data->>'leakedCredentialCheckResult' as leaked_credential_check_result
FROM public.cf_waf_logs_adaptive;

[MihaiBojin]

Extract events:

SELECT
  zone_id,
  data->>'kind' as kind,
  data->>'action' as action,
  data->>'clientIP' as client_ip,
  data->>'clientAsn' as client_asn,
  data->>'userAgent' as user_agent,
  data->>'description' as description,
  data->>'edgeColoName' as edge_colo_name,
  data->>'clientCountryName' as client_country_name,
  data->>'clientRequestPath' as client_request_path,
  data->>'clientRequestQuery' as client_request_query,
  data->>'clientRequestScheme' as client_request_scheme,
  data->>'clientASNDescription' as client_asn_description,
  data->>'clientRequestHTTPHost' as client_request_http_host,
  data->>'clientRequestHTTPProtocol' as client_request_http_protocol,
  data->>'clientRequestHTTPMethodName' as client_request_http_method_name,
  data->>'leakedCredentialCheckResult' as leaked_credential_check_result,
  count(*) as cnt
FROM public.cf_waf_logs_adaptive
-- WHERE datetime >= CURRENT_DATE - INTERVAL '1 day' AND datetime < CURRENT_DATE + INTERVAL '1 day'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
ORDER BY cnt DESC;