-- 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:
