topic Re: How to use the values of a CSV within a search in Splunk Search

How to use the values of a CSV within a search?

TolTest — Thu, 15 Jun 2023 17:38:44 GMT

Hi all,

Very new to Splunk here. I'm hoping to get some help. I'm trying to use some of the values in my CSV file as search filters. I suck at explaining, but I'll try anyway. This is what my CSV file looks like:

errorMessage,errorMessageFilter,errorCode,errorAlertValue
Test1,Value1,1,10
Test2,Value2,2,10
Test3,Value3,3,100
Test4,Value4,5,100
Test5,Value5,5,100

And my search is structured like this:
index=testing application=app1 msg=??

What I'm trying to accomplish is that the ?? part gets populated with values from the CSV file. Specifically, all the values found in errorMessageFilter (so Value1, Value2 etc.)

What do I need to do to make it so the end result would look something like this (using the CSV file):
index=testing application=app1 msg="Value1" OR "Value2" OR "Value3" OR "Value4" OR "Value5"

Thanks!

Re: How to use the values of a CSV within a search

ITWhisperer — Wed, 14 Jun 2023 09:55:07 GMT

index=testing application=app1 [|inputlookup yourfile.csv | rename errorMessageFilter as msg | table msg]

Re: How to use the values of a CSV within a search

TolTest — Wed, 14 Jun 2023 11:10:14 GMT

Hey, thanks for helping!

I've tried this and I don't think this is what I'm looking for maybe? Or it doesn't give me the results I was hoping for. That's kinda my fault for being so unclear. What I'm trying to accomplish is that the values in errorMessageFilter (from the csv file) are used to filter out the results.

This is what I did BEFORE using a csv file:
index=testing application=app1 msg="Value1" OR "Value2" OR "Value3" OR "Value4" OR "Value5"

This is what I want it to be:
index=testing application=app1 msg=somethingsomethingusingCSVfile(but same results as old query)

I hope this makes more sense?

Re: How to use the values of a CSV within a search

ITWhisperer — Wed, 14 Jun 2023 11:18:29 GMT

Not really any clearer!

index=testing application=app1 msg="Value1" OR "Value2" OR "Value3" OR "Value4" OR "Value5"

means search your index (testing) where field application = "app1" and field msg = "Value1" or "Value2" appears in the text of the event, or "Value3" appears in the text of the event, etc.

If you want to do text search filtering based on your csv file, try something like this

index=testing application=app1 [|inputlookup yourfile.csv | rename errorMessageFilter as query | table query]

Re: How to use the values of a CSV within a search

TolTest — Wed, 14 Jun 2023 11:45:17 GMT

Oh, sorry I can't get it any clearer for you (not a native English speaker).

I also noticed I made an error in my old query. It is as follows:
index=testing application=app1 msg="Value1" OR msg="Value2" OR msg="Value3" OR msg="Value4" OR msg="Value5"

This query gives me back a list of events of which msg is equal to "Value1", "Value2" etc., which is correct.

But if I try your suggestion:
index=testing application=app1 [|inputlookup yourfile.csv | rename errorMessageFilter as query | table query]

I don't get any events returned. It says "no results found".

Re: How to use the values of a CSV within a search

ITWhisperer — Wed, 14 Jun 2023 11:53:09 GMT

I presume you did replace "yourfile.csv" with actual name of your csv file!

Try this with the format at the end

index=testing application=app1 [ | inputlookup yourfile.csv | rename errorMessageFilter as msg | table msg | format]

Re: How to use the values of a CSV within a search

TolTest — Wed, 14 Jun 2023 11:56:27 GMT

I did and unfortunately the format didn't work. Still no results. 😞

Re: How to use the values of a CSV within a search

ITWhisperer — Wed, 14 Jun 2023 12:03:41 GMT

It may be down to your data, which you have not or cannot share, so you will have to debug it yourself.

Start with just this

| inputlookup yourfile.csv | rename errorMessageFilter as msg | table msg

To see if you get the values in the msg field you were expecting

Then try your index search to see if any of your events should have matched

Re: How to use the values of a CSV within a search

TolTest — Wed, 14 Jun 2023 12:19:11 GMT

| inputlookup yourfile.csv | rename errorMessageFilter as msg | table msg

This seems to work fine on its own. It returns a list of the values from the CSV file. I can also manually insert the values from the CSV file after the query:

index=testing application=app1 msg="Value1"

This also works as expected.

Do you perhaps know what happens with the query itself?

index=testing application=app1 [ | inputlookup yourfile.csv | rename errorMessageFilter as msg | table msg | format]

Does it add all the values it finds under errorMessageFilter like this: "Value1" OR "Value2"? Or maybe "Value1" "Value2"? Because if it's the latter I don't think it'll work in my case.

Re: How to use the values of a CSV within a search

ITWhisperer — Wed, 14 Jun 2023 12:46:01 GMT

format should create a string like this

( ( msg="Value1" ) OR ( msg="Value2") )

Re: How to use the values of a CSV within a search

TolTest — Wed, 14 Jun 2023 12:59:53 GMT

I found the error!

For some reason Splunk decided to add an additional "/" in front of my values. Ie. "Value1" became "/Value1". Not sure why, but it's fixed now! Thank you so much for your time!