Splunk Search

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

corti77
Communicator

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

Labels (1)
Tags (1)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote and "Accept as Solution" is appreciated.

View solution in original post

corti77
Communicator

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

0 Karma

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote and "Accept as Solution" is appreciated.

corti77
Communicator

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.

 

0 Karma

corti77
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...