Splunk Search

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

nagarjuna280
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

somesoni2
SplunkTrust
SplunkTrust

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

somesoni2
SplunkTrust
SplunkTrust

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.

nagarjuna280
Communicator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...