Search for more than 1 url hit on a src_ip

[fdevera]

Hi I have this search here where I want to limit the results to only events that have more than 1 url hit on an src_ip. How do I do that?

index=security sourcetype=malware (connect OR disconnect OR recv)
| transaction src_ip
| lookup dnslookup clientip as src_ip OUTPUT clienthost as fqdn
| rex field=fqdn "(?<hostname>[^.]+)\."
| rex field=_raw recv\:\s+User-Agent\:\s+(?<user_agent>.*)
| rex field=_raw recv\:\s+Host\:\s+(?<url>.*)
| eval url=replace(url,"\.","[.]")
| where isnotnull(url)
| table _time hostname url user_agent src_ip fqdn dest_port

[scelikok · Accepted solution]

Hi @fdevera,

You can add it to where condition; please try below.

index=security sourcetype=malware (connect OR disconnect OR recv)
| lookup dnslookup clientip as src_ip OUTPUT clienthost as fqdn
| rex field=fqdn "(?<hostname>[^.]+)\."
| rex field=_raw recv\:\s+User-Agent\:\s+(?<user_agent>.*)
| rex field=_raw recv\:\s+Host\:\s+(?<url>.*)
| eval url=replace(url,"\.","[.]")
| where isnotnull(url)
| eventstats dc(url) as url_count by src_ip
| where url_count>1 OR url="www.badurl.com"
| transaction src_ip
| table _time hostname url user_agent src_ip fqdn dest_port url_count

[scelikok]

Hi @fdevera,

Please try below;

index=security sourcetype=malware (connect OR disconnect OR recv)
| transaction src_ip
| lookup dnslookup clientip as src_ip OUTPUT clienthost as fqdn
| rex field=fqdn "(?<hostname>[^.]+)\."
| rex field=_raw recv\:\s+User-Agent\:\s+(?<user_agent>.*)
| rex field=_raw recv\:\s+Host\:\s+(?<url>.*)
| eval url=replace(url,"\.","[.]")
| where isnotnull(url)
| eventstats dc(url) as url_count by src_ip
| where url_count>1
| table _time hostname url user_agent src_ip fqdn dest_port url_count

[fdevera]

Thanks that worked. Just had to move transaction lower to get it to work:

index=security sourcetype=malware (connect OR disconnect OR recv)
| lookup dnslookup clientip as src_ip OUTPUT clienthost as fqdn
| rex field=fqdn "(?<hostname>[^.]+)\."
| rex field=_raw recv\:\s+User-Agent\:\s+(?<user_agent>.*)
| rex field=_raw recv\:\s+Host\:\s+(?<url>.*)
| eval url=replace(url,"\.","[.]")
| where isnotnull(url)
| eventstats dc(url) as url_count by src_ip
| where url_count>1
| transaction src_ip
| table _time hostname url user_agent src_ip fqdn dest_port url_count

One question though. What if there's a specific url, e.g. www.badurl.com that I don't want  to be part of the url_count>1? I want it to show in the results even though it's 1 count but the only URL that should do that.

Also this combination of url count and transaction seems to have an unwanted effect of excluding a URL (www.badurl2.com) from results even though we have multiple hits on it back to back.