Detect host connecting to malicious domains - PaloAlto + AD join statement

[corti77]

Hi,

I am trying to get a list of workstations trying to connect to malicious DNS using PaloAlto and Windows AD logs.

From PaloAlto logs I get the list of malicious domains detected and blocked with the following query

index="pan_logs" dns sourcetype="pan:threat" dest_zone=External dest_port=53 action=dropped OR action=blocked vendor_action=sinkhole
| dedup file_name
| table _time, file_name

_time                                    file_name

2023-02-09 11:42:59       d2azal32wgllwk.cloudfront.net 

2023-02-09 11:42:19       meeo.it

2023-02-09 11:15:51       iemlfiles4.com  

2023-02-09 10:26:42       jingermy.com

From AD logs I get the DNS requests. I renamed src_ip because the field also exists in paloalto logs.

index="msad" sourcetype="MSAD:NT6:DNS" | eval host_querying=src_ip | table _time, domain, host_querying

 Those two queries work fine.

_time                                    domain                                                                 host_querying

2023-02-09 12:23:32       media-waw1-1.cdn.whatsapp.net            192.168.20.215 

2023-02-09 12:23:32       scontent-otp1-1.xx.fbcdn.net                     8.8.4.4  

2023-02-09 12:23:32       scontent-otp1-1.xx.fbcdn.net                     192.168.20.27    

2023-02-09 12:23:32       scontent-otp1-1.xx.fbcdn.net                     8.8.4.4  

2023-02-09 12:23:32       scontent-otp1-1.xx.fbcdn.net                     192.168.20.27

Now, I would like to extract the culprit of that DNS request.

index="pan_logs" dns sourcetype="pan:threat" dest_zone=External dest_port=53 action=dropped OR action=blocked vendor_action=sinkhole
| dedup file_name
| table _time, file_name
| join type=left left=L right=R where L.file_name=R.domain
[search index="msad" sourcetype="MSAD:NT6:DNS" | eval host_querying=src_ip | fields domain, host_querying]
| table _time,file_name,host_querying

The result, empty columns for file_name (domain) and host_querying

_time                                    file_name            host_querying

2023-02-09 12:00:06      

2023-02-09 11:42:59      

Could someone point me out what I am doing wrong in the join statement? 

thanks

[corti77]

Hi @scelikok ,

your query does not return complete results. The host_querying has value only for the first domain, after that all are empty. 

_time                                    domain                                 host_querying

2023-02-09 15:33:00       style.itinsell.com              192.168.20.20

2023-02-09 15:33:00       style.itinsell.com              2001:503:d414::30

2023-02-09 15:33:00       style.itinsell.com              8.8.8.8

2023-02-09 15:33:00       style.itinsell.com              192.168.20.20

2023-02-09 15:33:00       style.itinsell.com              8.8.4.4

2023-02-09 15:33:00       style.itinsell.com              8.8.4.4

2023-02-09 15:33:00       style.itinsell.com              192.168.20.20

2023-02-09 15:33:00       style.itinsell.com              8.8.4.4

2023-02-09 15:33:00       style.itinsell.com              192.168.20.20

2023-02-09 15:12:00       prodaddkarl.com            

2023-02-09 14:45:27       meeo.it

2023-02-09 14:12:25       spacetrack.org 

2023-02-09 12:50:06       www.karlovy-vary.cz     

2023-02-09 12:00:06       atlhqmphssql1.eus         

2023-02-09 11:42:59       d2azal32wgllwk.cloudfront.net 

And I checked the first domain without host_querying and it has many  results.

index="msad" sourcetype="MSAD:NT6:DNS" direction=Snd  prodaddkarl.com | eval host_querying=src_ip | table  domain, host_querying

domain                                 host_querying

prodaddkarl.com             8.8.4.4

prodaddkarl.com             192.168.20.9

prodaddkarl.com             8.8.8.8

prodaddkarl.com             8.8.8.8

prodaddkarl.com             192.168.20.9

prodaddkarl.com             192.31.80.30

any other idea?

thanks a lot

[scelikok · Accepted solution]

Hi @corti77,

Could you please try below.  (Another usage of join )

index="pan_logs" dns sourcetype="pan:threat" dest_zone=External dest_port=53 action=dropped OR action=blocked vendor_action=sinkhole 
| dedup file_name 
| table _time file_name 
| rename file_name as domain 
| join max=0 type=left domain 
    [ search index="msad" sourcetype="MSAD:NT6:DNS" 
    | eval host_querying=src_ip 
    | fields domain, host_querying] 
| table _time domain host_querying

[corti77]

HI @scelikok 

I will close the question as I was struggling a lot with the standard DNS logs from AD.

I decided to deploy SYSMON in the workstations and the DNS is much more clear now. I still need to correlate events based in one field (domain) but I need to adjust the time, in order to identify (more or less) the originator. In case the domain was visited from several computers during the day.

Anyway, I upvoted for you, I much appreciate your help.

[corti77]

Hi again @scelikok 

I just realized that the number of results in the sub-search might be the root cause of the problem. If I reduce the time frame of the search I get results in the column host_querying for all the rows.

Any other approach that might not depend on the time selection?

thanks