NDR - Number of Monitored Hosts

-- NAME:        NDR - Number of Monitored Hosts
-- CATEGORY:    NDR
-- DESCRIPTION: Detection for identifying the number of private, public, and unknown hosts being monitored by 
-- SOURCE:      Data Lake

-- VARIABLE $$Category$$ STRING

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
      CASE 
         WHEN CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].category') AS VARCHAR) > '' THEN CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].category') AS VARCHAR)
         ELSE 'UNKNOWN'
      END category,
      MAX(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].num_hosts') AS VARCHAR)) num_hosts
   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) = 'numMonitoredHosts'
   GROUP BY 1,2,3,4
   )

SELECT 
   Day, 
--   Report_Name, 
--   Description, 
   category, 
   num_hosts,
   CASE 
      WHEN CAST(SQRT(CAST(num_hosts AS BIGINT)) AS BIGINT) = 0 THEN '|'
      ELSE RPAD('',CAST(SQRT(CAST(num_hosts AS BIGINT)) AS BIGINT),'█') 
   END ||' '||CAST(num_hosts AS VARCHAR) num_hosts_Bar
FROM NDR_Data
WHERE LOWER(category) LIKE LOWER('%$$Category$$%')
ORDER BY Day DESC, category ASC

Sample Output: