Splunk: find what a user is searching for?

Question

I am trying to write a Splunk SPL query that will show me the most popular search terms that a user is looking for in one of my web apps. I have the logs already in Splunk but I am having a hard time extract the search parameter from the event. The event shows the full SQL select statement that looks like the query below:

select result from table where search_term = 'searched for this text'

How can I have this:

index=my_app search_term | top result

How do I actually capture the search term?

Thank you

Answer 1

You can use rex to extract the search term. Something like this

index=my_app | rex "search_term = '(?<search_term>[^']+)"

If you want individual words then use the split function followed by mvexpand to make each word a separate event.

... | eval words=split(search_term, " ") | mvexpand words