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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...