Approved

NDR - MAC IP correlation

-- NAME:        NDR - MAC IP correlation
-- DESCRIPTION: Detection for identifying all the IP addresses associated with a given MAC address
--              Excludes :: and 0.0.0.0 
--              The query also checks for the MAC Address in the data lakes XDR Data to determine if the devices is managed or not.
-- SOURCE:      Data Lake

-- VARIABLE $$IP Address$$    IP_ADDRESS
-- 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
      -- Perform some ugly string search and replace to create a list of IPS excluding :: and 0.0.0.0 
      RTRIM(LTRIM(LTRIM(RTRIM(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].ips') AS VARCHAR),'::',''),'0.0.0.0',''),',',','||CHR(10)),CHR(10)||','||CHR(10),''),','),','),CHR(10)),CHR(10)),','),',') IP_List,
      CAST(JSON_EXTRACT(raw,'$.detection_context['||CAST(A.x AS VARCHAR)||'].mac') AS VARCHAR) MAC_Address
   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) = 'macIpCorrelation'
   GROUP BY 1,2,3,4,5
   )

-- SELECT * FROM  NDR_Data -- USED TO DEBUG errors in the above NDR_Data select

SELECT 
   -- NDR data --
   NDR.Day, 
--   NDR.Report_Name, 
--   NDR.Description, 
   -- Perform some ugly array work to get the distinct list of Source_IPs for the group
   ARRAY_JOIN(ARRAY_DISTINCT(SPLIT(REPLACE(ARRAY_JOIN(ARRAY_AGG(DISTINCT NDR.IP_List),CHR(10)),',',''),CHR(10))),CHR(10)) IP_Address_List, 
   CARDINALITY(ARRAY_DISTINCT(SPLIT(REPLACE(ARRAY_JOIN(ARRAY_AGG(DISTINCT NDR.IP_List),CHR(10)),',',''),CHR(10)))) IP_Address_Count,
   NDR.Mac_Address, 

   -- XDR data --
   CASE WHEN XDR.meta_hostname > '' THEN 'XDR_Installed' ELSE CASE WHEN NDR.Mac_Address > '' 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 NDR 
   LEFT JOIN xdr_data XDR ON LOWER(XDR.meta_mac_address) = LOWER(NDR.Mac_Address) 
      -- Include the Stream_Ingest_date match to the NDR.Day info to determine if XDR was installed at the time of the NDR data or not
      AND XDR.stream_ingest_date = NDR.Day 
WHERE CASE WHEN NDR.IP_List > '' THEN LOWER(NDR.IP_List) LIKE LOWER('%$$IP Address$$%') ELSE CASE WHEN '$$IP Address$$' = '%' THEN CAST('1' AS BOOLEAN) ELSE CAST('0' AS BOOLEAN) END END
   AND CASE WHEN NDR.Mac_Address > '' THEN LOWER(NDR.Mac_Address) 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 NDR.Day, NDR.Report_Name, NDR.Description, NDR.Mac_Address, 5 -- 7 If you enable the Name and description for results change the group by col number
ORDER BY NDR.Day DESC, XDR_Enabled DESC, IP_Address_Count DESC

Sample Output: