How do you compare 2 fields from 2 sourcetypes?

[dleveque]

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 ?

[solarboyz1]

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.

[dleveque]

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 ?