topic Re: How to filter results by CSV Lookup? in Getting Data In

How to filter results by CSV Lookup?

driva — Tue, 28 Jan 2020 12:52:47 GMT

Hi guys,

I'm working with the syslogs of a basic web filter. I have a CSV file containing "bad words searched" that I'd like to report on. Essentially, I'd like to create a Table with Username, IP and the "Bad Word Searched". Currently I can create a a table with Username, IP and ALL words searched but not able to filter results based on the lookup.

My current search is:

index=WebFilter | stats count by Web_username, Web_IP, Web_GoogleSearch | sort -count

How could I use a lookup file to filter the results?

Ideally I'd like something like this but I can't get the syntax right as I'm not sure how to apply this to the results properly:

index=WebFilter [| inputlookup BadWords.csv | rename BadWordsList as Web_BadWords] | stats count by Web_username, Web_IP, Web_GoogleSearch, Web_BadWords | sort -count

Any guidance would be amazing, thank you.

Best wishes,
D

Re: How to filter results by CSV Lookup?

manjunathmeti — Wed, 30 Sep 2020 03:49:51 GMT

You can try:

index=WebFilter | lookup BadWords.csv BadWordsList as Web_BadWords OUTPUT BadWordsList | stats count by Web_username, Web_IP, Web_GoogleSearch, BadWordsList | sort -count

Re: How to filter results by CSV Lookup?

driva — Wed, 30 Sep 2020 03:56:01 GMT

Hi, thanks for your response! I tried the search which didn't throw up an error this (which is good) however I get back 0 results. Would this be because the Web_GoogleSearch contains the bad word within the string and not an exact match in the CSV file? If this is the case, is there anything I can do to say 'if Web_GoogleSearch contains word from list, output the results'? Thanks for your help.

Re: How to filter results by CSV Lookup?

driva — Tue, 28 Jan 2020 16:18:51 GMT

I managed to solve it, the search needed was:

 index=WebFilter [| inputlookup BadWords.csv | eval BadWordsList="*"+BadWordsList+"*" | rename BadWordsList as web_GoogleSearch] | stats count by web_GoogleSearch, web_User, _time