Combine dataset of File Path XDR-dataset and PANNGFW

[Pattarachai-FTH]

Hi Expert ,

I would like to know how to combine or join dataset between XDR-data and PANNGFW  I make xql of PANNGFW  when found, alert wildfire or antivirus will show file-sha256 by the way I want to combine dataset of xdr-agent to find file-path this example my rule of panngfw 

dataset in (panw_ngfw_threat_raw )
| alter user= source_user ,Src_IP = source_ip , Dst_IP= dest_ip , Threat_name = threat_name ,Threat_category=threat_category,Port= dest_port,File_SHA256=file_sha_256,Serveritys = severity ,File_Name=file_name
|filter File_SHA256 != Null
| fields _time , user , Src_IP , Dst_IP, Threat_name,Threat_category, Serveritys ,Port,File_Name,File_SHA256

Thank you

[nsinghvirk]

Hello @Pattarachai-FTH 

Thanks for reaching out on LiveCommunity!

You can use 'join' stage to combine results from two datasets. Please refer to below document to learn about it. There is also an example query included within it.

https://docs-cortex.paloaltonetworks.com/r/Cortex-XDR/Cortex-XDR-XQL-Language-Reference/Join

[zarnous · Accepted solution]

Hi @Pattarachai-FTH 

Looking at your query, you can join that with XDr data (using file sha256 as the common criteria as i understood ) and can be written as below:

dataset = panw_ngfw_threat_raw
| alter user= source_user ,Src_IP = source_ip , Dst_IP= dest_ip , Threat_name = threat_name ,Threat_category=threat_category,Port= dest_port,File_SHA256=file_sha_256,Serveritys = severity ,File_Name=file_name
|filter File_SHA256 != Null
| fields _time , user , Src_IP , Dst_IP, Threat_name,Threat_category, Serveritys ,Port,File_Name,File_SHA256

| join (dataset = xdr_data ) as XDR xdr.action_file_sha256 = File_SHA256


For the above query , you can the fields of interest and also stage more filter to get to the final requirment you are trying to build for your use case.

I Hope that helps and shed some light, please do let me know if you have any questions, and if this answers your question, feel free to mark this as a resolution so others can benefit from! 

Best
Z

[Pattarachai-FTH]

Hi @zarnous 

Thank you for your recommend I will follow for this