Splunk Search

How to check if a word is in a CSV file, and if it is, display results in a table?

Communicator

I have some data, if the message contains a word which is in a csv file, then results should show in a table. How should I edit my search?

I have a csv file which contains keywords like:

kill
bomb 
gun 
drugs
Anthrax
Arms
Attack
Atomic

If the message contains more than one word like:

take your gun
kill him

And I search like this:

search | table message, id ,name

then results should look like this:

message  id  name
Tags (4)
0 Karma
1 Solution

Revered Legend

You would need to setup your CSV files as lookup table file in Splunk and then create a lookup transform to allow wildcarded lookup. You can add the CSV as lookup table file from UI Settings-> Lookups -> Lookup table files : New. Make sure to change the sharing permission appropriately (global if you want it to access from other apps as well). Say your lookup table name is yourlookup.csv with single column called keyword.
Once lookup table file is available, you would need to create a lookup transform using transforms.conf file on Search head.

 [keywordlookup]
 filename = yourlookup.csv
 match_type = WILDCARD(keyword)

A restart would be required for Splunk.

After that you can use this lookup transform like this

your base search | table message id name 
| lookup keyworklookup keyword as message OUTPUT keyword | where isnotnull(keyword)
| table message id name 

The second command will match the value of field message with keywords in the lookup and return the matched keyword. If there are no matched keywords, the where clause will filter those rows and will show only the matched ones.

Updated

Give this a try

your base search  [| inputlookup yourkeywordlookup.csv | table keyword | eval message="*".keyword."*" | table message | format ] | table message, id ,name

This should add *keyword* for all keywords in your base search against field message, filter events which are matching.

View solution in original post

Revered Legend

You would need to setup your CSV files as lookup table file in Splunk and then create a lookup transform to allow wildcarded lookup. You can add the CSV as lookup table file from UI Settings-> Lookups -> Lookup table files : New. Make sure to change the sharing permission appropriately (global if you want it to access from other apps as well). Say your lookup table name is yourlookup.csv with single column called keyword.
Once lookup table file is available, you would need to create a lookup transform using transforms.conf file on Search head.

 [keywordlookup]
 filename = yourlookup.csv
 match_type = WILDCARD(keyword)

A restart would be required for Splunk.

After that you can use this lookup transform like this

your base search | table message id name 
| lookup keyworklookup keyword as message OUTPUT keyword | where isnotnull(keyword)
| table message id name 

The second command will match the value of field message with keywords in the lookup and return the matched keyword. If there are no matched keywords, the where clause will filter those rows and will show only the matched ones.

Updated

Give this a try

your base search  [| inputlookup yourkeywordlookup.csv | table keyword | eval message="*".keyword."*" | table message | format ] | table message, id ,name

This should add *keyword* for all keywords in your base search against field message, filter events which are matching.

View solution in original post

Communicator

is there any other way, I don't have a admin permissions

0 Karma

Revered Legend

Try the updated answer.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!