Getting Data In

How to filter results by CSV Lookup?

driva
Path Finder

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

0 Karma
1 Solution

driva
Path Finder

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

View solution in original post

0 Karma

driva
Path Finder

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
0 Karma

manjunathmeti
Champion

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

0 Karma

driva
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...