Splunk Search

How to send a partial string value to a lookup table and count the occurrences?

LWilliamson1
Explorer

I have a field that contains a sentence such as "I love wonderful food!" I want to be able to check each word against a lookup table to determine if the sentence was positive or negative. I need to be able to track the count of each good and bad word and give the sentence a score. So for the above sentence the score would be +2.

Lookup Table
Good | Bad
love | hate
wonderful|

Is this possible with a lookup table or would it potentially be easier to turn the entire event into raw text and search against a multi valued field?

0 Karma

jaredlaney
Contributor

Maybe try something like this:

sourcetype=word_parse | eval clean_sentence = replace(sentence, , ""> | eval parsed_sentence = split(clean_sentence, " ") | mvexpand parsed_sentence | lookup point_lookup word as parsed_sentence OUTPUT score | stats sum(score) as total

You might want to try a lookup table like this:

Lookup point_lookup:
word | score
Good | 1
Bad | -1
love | 1
hate | -1
wonderful| 1

LWilliamson1
Explorer

Edit: I think it's something in the back end throwing an error for this particular search.

I like your idea but for some reason it's giving no output. I changed your search to:

... |eval clean_sentence = replace(sentence,' ',"") | eval parsed_sentence = split(clean_sentence, " ") | mvexpand parsed_sentence | lookup point_lookup word as parsed_sentence OUTPUT score | stats sum(score) as total

but it appears to generate no output/matches.

0 Karma

jaredlaney
Contributor

Can you tell me what lookup you used and what the input sentence is?

0 Karma

LWilliamson1
Explorer

I recreated my lookup in the same manner as you did and named it point_lookup. The input sentence could be anything from "I love my iphone" to "I hate my iphone". The input is actually twitter data.

0 Karma
Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...