th_CVE-2022-22954.sql

-- Cloudflare threat hunting query

SELECT EDGE_START_TIMESTAMP                                                        EVENT_TIME,
       CLIENT_REQUEST_HOST                                                         CLIENT_REQUEST_HOST,
       CLIENT_REQUEST_PATH                                                         CLIENT_REQUEST_PATH,
       CLIENT_REQUEST_URI                                                          CLIENT_REQUEST_URI,
       CLIENT_REQUEST_REFERER                                                      CLIENT_REQUEST_REFERER,
       CLIENT_REQUEST_METHOD                                                       CLIENT_REQUEST_METHOD,
       ORIGIN_IP                                                                   ORIGIN_IP,
       CLIENT_REQUEST_USER_AGENT                                                   CLIENT_REQUEST_USER_AGENT,
       COALESCE(ORIGIN_RESPONSE_STATUS,
               CACHE_RESPONSE_STATUS)                                              RESPONSE_CODE,
       REGEXP_SUBSTR(CLIENT_REQUEST_URI,'&deviceUdid=([^$]+)', 1, 1, 'e',1)        PAYLOAD,
       IFF(RESPONSE_CODE='400','Successful','Failed attempt')                      VULN_STATUS
       FROM RAW.CLOUDFLARE_HTTP
WHERE CLIENT_REQUEST_URI ILIKE ANY ('%/catalog-portal/ui/oauth/verify?error=&deviceUdid=%24%7b%22%66%',
                                    '%/catalog-portal/ui/oauth/verify?error=&deviceUdid=%freemarker.template.utility%')
 AND CLIENT_REQUEST_METHOD='GET';
                                                                     
                                    
  -- AWS WAF threat hunting query
  
  SELECT EVENT_TIME                                                                EVENT_TIME,
       HTTP_REQUEST_CLIENT_IP                                                    HTTP_REQUEST_CLIENT_IP,
       HTTP_REQUEST_COUNTRY                                                      HTTP_REQUEST_COUNTRY,
       HTTP_REQUEST_HEADERS                                                      HTTP_REQUEST_HEADERS,
       HTTP_REQUEST_URI                                                          HTTP_REQUEST_URI,
       REGEXP_SUBSTR(HTTP_REQUEST_URI,'&deviceUdid=([^$]+)', 1, 1, 'e',1)        PAYLOAD,
       ACTION
       FROM RAW.AWS_WAF
WHERE HTTP_REQUEST_URI ILIKE ANY ('%/catalog-portal/ui/oauth/verify?error=&deviceUdid=%freemarker.template.utility%',
                                  '%/catalog-portal/ui/oauth/verify?error=&deviceUdid=%24%7b%22%66%')
  AND HTTP_REQUEST_HTTP_METHOD='GET';