-- 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
-- NAME: NDR - Top 100 most trafficked hostnames (BARS)
-- CATEGORY: NDR
-- DESCRIPTION: Detection for identifying the top 100 most trafficked websites 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,
CASE
WHEN CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].download_bytes') AS DOUBLE) AS BIGINT) = 0 THEN ''
WHEN CAST(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].download_bytes') AS DOUBLE)/(1024*1024)) AS BIGINT) = 0 THEN '|'
ELSE RPAD('',CAST(SQRT(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].download_bytes') AS DOUBLE)/(1024*1024))) AS BIGINT),'█')
END || CAST(FORMAT(' %,.2f',CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].download_bytes') AS DOUBLE)/(1024*1024) AS DOUBLE)) AS VARCHAR) "Download (MB)",
CASE
WHEN CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].upload_bytes') AS DOUBLE) AS BIGINT) = 0 THEN ''
WHEN CAST(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].upload_bytes') AS DOUBLE)/(1024*1024)) AS BIGINT) = 0 THEN '|'
ELSE RPAD('',CAST(SQRT(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].upload_bytes') AS DOUBLE)/(1024*1024))) AS BIGINT),'█')
END || CAST(FORMAT(' %,.2f',CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].upload_bytes') AS DOUBLE)/(1024*1024) AS DOUBLE)) AS VARCHAR) "Upload (MB)",
CASE
WHEN CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].total_bytes') AS DOUBLE) AS BIGINT) = 0 THEN ''
WHEN CAST(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].total_bytes') AS DOUBLE)/(1024*1024)) AS BIGINT) = 0 THEN '|'
ELSE RPAD('',CAST(SQRT(SQRT(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].total_bytes') AS DOUBLE)/(1024*1024))) AS BIGINT),'█')
END || CAST(FORMAT(' %,.2f',CAST(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].total_bytes') AS DOUBLE)/(1024*1024) AS DOUBLE)) AS VARCHAR) "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:
