Splunk Search

How do you compare 2 fields from 2 sourcetypes?

dleveque
New Member

Hello,

I have a source with proxy log sent by syslog and another with hostname blacklisted get by a text file get every day.

I need to create a search to alert if a user tries to go on a website blacklisted by the company.

I tried to make join with this instruction :

index=proxies sourcetype=bluecoat
| stats count by cs_host | fields - count
| join cs_host [index=proxies sourcetype=blacklist | rename bl_host as cs_host | stats count by cs_host | fields - count]

This search returns no result even though I'm sure to have simulate the usecase.

Could you help me ?

0 Karma

solarboyz1
Builder

Why don't you just search your proxy logs based on the values in the blacklist:

index=proxies sourcetype=bluecoat [ search index=proxies sourcetype=blacklist | fields bl_host | dedup bl_host | rename bl_host as cs_host  ] 

This will results in a list of cs_hosts from the proxy logs, that were in the blacklist logs.

0 Karma

dleveque
New Member

Hello,
Thank you for your suggestion but it return 0 result

The solution that I have found, and which seems works but which can be optimize, I think is :

index=proxies sourcetype=bluecoat | rename cs_host as hostname
| join cs_host [search index=proxies (sourcetype=bluecoat OR sourcetype=blacklist) | eval hostname=coalesce(cs_host,bl_host) | stats dc(sourcetype) as occur by hostname | where occur >1 ]
| table _time, src_ip, user, hostname

Could you help me ?

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