NDR - Top 100 most trafficked hostname

-- NAME:        NDR - Top 100 most trafficked hostnames
-- CATEGORY:    NDR
-- DESCRIPTION: Detection for identifying the top 100 most trafficked hostnames by traffic volume
-- SOURCE:      Data Lake

-- VARIABLE $$Destination IP Address$$ IP ADDRESS
-- VARIABLE $$Hostname$$               URL

WITH NDR_Data AS (
   SELECT
      CAST(JSON_EXTRACT(raw,'$.ingest_date') AS VARCHAR) Day,
      CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR) Report_Name,
      CAST(JSON_EXTRACT(raw,'$.description') AS VARCHAR) Description,

      -- Detection Context
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].hostname') AS VARCHAR) hostname,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].avg_pcr_payload') AS VARCHAR) avg_pcr_payload,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].dest_ips_and_unpopular') AS VARCHAR) dest_ips_and_unpopular,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].percent_unpopular') AS VARCHAR) percent_unpopular,

      FORMAT('%,.2f',CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].download_bytes') AS DOUBLE)/(1024*1024)) "Download (MB)",
      FORMAT('%,.2f',CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].upload_bytes') AS DOUBLE)/(1024*1024)) "Upload (MB)",
      FORMAT('%,.2f',CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].total_bytes') AS DOUBLE)/(1024*1024)) "Total (MB)"
   FROM mdr_ioc_all, UNNEST(SEQUENCE(0,JSON_ARRAY_LENGTH(JSON_EXTRACT(raw,'$.detection_context'))-1)) AS A(x)
   WHERE ioc_worker_id = 'worker_ndr' 
      AND CAST(JSON_EXTRACT(raw, '$.name') AS VARCHAR) = 'topHostnames'
   )

SELECT 
   Day, 
--   Report_Name, 
--   Description, 
   FORMAT('%.2f',CAST(avg_pcr_payload AS DOUBLE)) Avg_PCR_payload,
   CAST(CAST(CAST(percent_unpopular AS DOUBLE)*100 AS BIGINT) AS VARCHAR) percent_unpopular,
   hostname, 
   "Download (MB)", 
   "Upload (MB)", 
   "Total (MB)",
   REPLACE(dest_ips_and_unpopular,'),',')'||CHR(10)) Dest_IP_and_Unpopular
FROM NDR_Data
WHERE CASE WHEN dest_ips_and_unpopular > '' THEN LOWER(dest_ips_and_unpopular) LIKE LOWER('%$$Destination IP Address$$%') ELSE CAST('1' AS BOOLEAN) END
   AND CASE WHEN hostname > '' THEN LOWER(hostname) LIKE LOWER('%$$Hostname$$%') ELSE CAST('1' AS BOOLEAN) END
ORDER BY Day DESC

Sample Output: