How can I add a matched lookup field to my stats command output?

[danfinan]

Hi all,

My apologies if the title was a bit vague, wasn't sure how to word it!

I have a search which identifes keywords (.csv lookup) in a URL (SSL decrpytion from our web filter) and it outputs the user & URL with the stats command - great for safeguarding but not easy on the eye when trying to see the keyword trigger amongst the URL.

index="web_filtering" [ | inputlookup high_risk_keywords.csv | eval url="*".HighRiskWords."*" | fields url | format ] | stats count by user, url

What I'd like to be able to do is list the matched keyword alongside the user and URL. I guess that the column in my CSV ('HighRiskWords') also needs to become a field? So when I run the stats command I see: User, URL, Keyword.

Hopefully that makes sense, any help would be greatlty appreciated!

[vishaltaneja070]

Hello @danfinan

I think you can join command which can help you here: I am assuming, there is keyword field is common in index=web_filtering and high_risk_keywords lookup:

    index="web_filtering" | join type=left keyword [ | inputlookup high_risk_keywords.csv | eval url="*".HighRiskWords."*" | fields url format keyword] | stats count by user url

if you want multiple matches then use max=0 as well with join.