Approved

NDR - Protocol Report (BARS)

-- NAME:        NDR - Protocol Report (BARS)
-- CATEGORY:    NDR
-- DESCRIPTION: Detection for identifying protocols used and how often
--              NOTE: avg_pcr is the Producer Consumer Ratio (PCR) (-1 Pure Push to +1 Pure Pull)
--              NOTE: mac_addresses is a list of the top 100 macs by total bytes in the monitored flows for the protocol
-- SOURCE:      Data Lake

-- VARIABLE $$Application Protocol$$ STRING
-- VARIABLE $$Network Protocol$$     STRING
-- VARIABLE $$MAC Address$$          STRING
-- VARIABLE $$meta_hostname$$        DEVICE_NAME

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)||'].app_protocol') AS VARCHAR) app_protocol,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].net_protocol') AS VARCHAR) net_protocol,
      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(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(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)",

      FORMAT('%,.0f',CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].num_flows') AS DOUBLE)) num_flows,
      FORMAT('%,.4f',CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].avg_pcr') AS DOUBLE)) avg_pcr,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].mac_addresses') AS VARCHAR) mac_addresses,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].mac_count') AS VARCHAR) mac_count	
   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) = 'protocolCount'
   )

--SELECT * FROM NDR_Data

SELECT 
   Day, 
--   Report_Name, 
--   Description, 
   net_protocol, 
   app_protocol, 
   CASE WHEN avg_pcr = 'null' THEN '' ELSE avg_pcr END avg_pcr,
   "Download (MB)", 
   "Upload (MB)",
   num_flows,
   mac_count Devices,
   REPLACE(mac_addresses,',',CHR(10)) mac_addresses,

   -- Here we will take a closer look at the top contributor to the activity by looking up the first MAC_address in the list vs XDR Data
   ELEMENT_AT(SPLIT(mac_addresses,','),1) "Examine_First_Entry ->",
      -- XDR data --
   CASE WHEN XDR.meta_hostname > '' THEN 'XDR_Installed' ELSE CASE WHEN mac_addresses > '' THEN 'NOT Managed' ELSE '' END END XDR_Enabled,
   ARRAY_JOIN(ARRAY_AGG(DISTINCT XDR.meta_hostname),CHR(10)) meta_hostname, 
   ARRAY_JOIN(ARRAY_AGG(DISTINCT XDR.meta_os_platform),CHR(10)) os_platform,
   ARRAY_JOIN(ARRAY_AGG(DISTINCT XDR.meta_endpoint_type),CHR(10)) endpoint_type, 
   ARRAY_JOIN(ARRAY_AGG(DISTINCT XDR.meta_username),CHR(10)) username

FROM NDR_Data 
   LEFT JOIN xdr_data XDR ON LOWER(XDR.meta_mac_address) = LOWER(ELEMENT_AT(SPLIT(mac_addresses,','),1))
   -- Include the Stream_Ingest_date match to the NDR_Data.Day info to determine if XDR was installed at the time of the NDR data or not
   AND XDR.stream_ingest_date = NDR_Data.Day
WHERE CASE WHEN app_protocol > '' THEN LOWER(app_protocol) LIKE LOWER('%$$Application Protocol$$%') ELSE CASE WHEN '$$Application Protocol$$' = '%' THEN CAST('1' AS BOOLEAN) ELSE CAST('0' AS BOOLEAN) END END
   AND CASE WHEN net_protocol > '' THEN LOWER(net_protocol) LIKE LOWER('%$$Network Protocol$$%') ELSE CASE WHEN '$$Network Protocol$$%' = '%' THEN CAST('1' AS BOOLEAN) ELSE CAST('0' AS BOOLEAN) END END
   AND CASE WHEN mac_addresses > '' THEN LOWER(mac_addresses) LIKE LOWER('%$$MAC Address$$%') ELSE CASE WHEN '$$MAC Address$$' = '%' THEN CAST('1' AS BOOLEAN) ELSE CAST('0' AS BOOLEAN) END END
   AND CASE WHEN XDR.meta_hostname > '' THEN LOWER(XDR.meta_hostname) LIKE LOWER('%$$meta_hostname$$%') ELSE CASE WHEN '$$meta_hostname$$' = '%' THEN CAST('1' AS BOOLEAN) ELSE CAST('0' AS BOOLEAN) END END
GROUP BY 1,2,3,4,5,6,7,8,9,10,11  
ORDER BY Day DESC, "Download (MB)" DESC, "Upload (MB)", net_protocol ASC, app_protocol ASC

Sample Output: