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