Searching Splunk logs and looking for occurrences of values fed from a CSV file

[carpe_diem12]

Hi, All,
Here's what I have:
I have a csv file (1 column, 1000 values) which I've uploaded to the lookup dir:

"/opt/splunk/etc/users/my-user/my-app/lookups/test.csv"

I have a search result that displays (currently) all of the occurrences of 1 criteria item:

index="my-app" process="my-process" "string-condition-1" "main-string-condition"

Here's what I'm trying to achieve:
What I want to do is to replace the "main-string-condition" by the items in the csv file. So basically creating sort of a loop where Splunk will search the app log and look each time for a value from the CSV file, until it processes all the 1000 values in the CSV.
There would be many occurrences for each item of the CSV file, so I'd like to limit that to e.g. 1 occurrence per CSV file item. I know I can do that with | head 1 , but in this complex search not sure where to put it.

I've never worked with inputlookup yet, and not sure if that is the correct command to use, also seams like I need to use a sub-search here, but no sure how to bring it all together.

In the end I'd also like to export the results, but that I believe I can do simply from the results view.

Appreciate your help on this.

[somesoni2]

Try like this. The subsearch would return giant OR expressions with values of lookup column as string. like this: (("string1") OR ("string2") OR...)

 index="my-app" process="my-process" "string-condition-1" [| inputlookup test.csv | table YourColumnNameInCSV | rename YourColumnNameInCSV as search ]

[carpe_diem12]

Hi, that doesn't seem to work. Originally I had no headers in the CSV, since it's a CSV with 1 column only. I thought it would search it by default. so I added a first cell in the CSV with value "name" (without quotes) and uploaded the file again.
Then I fired the following search:
index="my-app" process="my-process" "string-condition-1" [| inputlookup test.csv | table name | rename name as search ]
There was no result and the search was quite quick though the time range was 30 days, and we've got a lot of log data.
How am I making sure it does pickup that CSV file from the lookups folder? Since I didn't specify the entire path, does Splunk in fact knows to pick it up from: /opt/splunk/etc/users/my-user/my-app/lookups/test.csv ?

Appreciate the help!

[somesoni2]

Can you just run this search to see if the lookup table is recognized?

| inputlookup test.csv | table name

Also, instead of placing in the filesystem, I would suggest adding it from the UI. (Login to Splunk, Go to your App, then Setting->Lookups->Lookup Table Files->Add New)

[carpe_diem12]

Hi,
The CSV is uploaded exactly as you mentioned, and it lands exactly at the path I've pasted in the description of my problem.
I did run the command as you suggested and it does provide me with all the items from the CSV file in the Statistics tab of the search result.
So how I go from here to my original search query, that is the question. 🙂
This search:
index="my-app" process="my-process" "string1" [| inputlookup test.csv | table "name" | rename "name" as search] - still doesn't give me any result. 😞

[somesoni2]

And it works when you just run this?

index="my-app" process="my-process" "string1"

Also, what type of values you've in your lookup and how are those values appearing in your raw data (from index="my-app")?

[carpe_diem12]

If I run the above query I get over 1Mil results in a 30day timeframe (it's a live system).
If I add the string2 (which what I'm actually looking for) - just pick one item out of the CSV and manually add it as string2 I get 50+ items (basically here I want to limit it to 1 by using | head 1).

I'm not sure I got your 2nd question. The list from the search is a long text string with various data (i.e. update log) where I also have the info I need (when I use string2 in the search).
The csv file contains a bunch of comma separated names, I'm not how I can see its data type.

[somesoni2]

Can you provide a sample rows from the lookup table and a sample search on how you'll use that in your search? Since you're able to get data from lookup, it more seems like a formatting issue on how the lookup values are rendered in search, and the sample search would help understand that.

[carpe_diem12]

I can't provide any real data from the system, from the data type these are simple strings representing names. And lets say string one is "Update", and string2 (for which we have the CSV) is the name being updated.
What bothers me more is when I run the search query as:
index="my-app" process="my-process" "string-condition-1" [| inputlookup test.csv | table YourColumnNameInCSV | rename YourColumnNameInCSV as search ]
Splunk finalizes the job within a few seconds, like it is not searching at all, and we are talking about 30 day period which contains a lot of logs.
When I tried to do a search in this way:
index="my-app" process="my-process" "string-condition-1" | search [| inputlookup test.csv | table YourColumnNameInCSV | rename YourColumnNameInCSV as search ]
The search is very long with lots of matched events (but not matched items) and the search doesn't seem to end - more than 2mil events matched.
I must be doing something wrong.
Btw, why do we do the "rename username as search" portion in the search above?

Thanks!

[carpe_diem12]

Hi, I still can't get any results.
Here's an example of my search for:

index="my-app" process="my-process" "string-condition-1" "main-string-condition":
the contents of the Event column has:

Feb 28 00:49:05 some_text more_text bla.bla.profile.updater: I, [2017-02-28_more_date_related_text]  INFO -- : Updater: cached_user.update_action for **some_username** (*some_userID*) took 148.2ms

My CSV file that I have uploaded, contains only 1 column (I read somewhere in Splunk doc that there has to be 2 columns minimum) which has a header: username.

I'm trying to match the values under the CSV->Username column cells, against that big textual event, and I need to find the matched events between CSV->username and some_username (sub)string from the Events results.

Can you suggest on how I can achieve that?