topic Re: Search set of strings from lookup and list count of occurance in Splunk Search

Search set of strings from lookup and list count of occurance

ma_anand1984 — Thu, 11 Oct 2012 12:18:24 GMT

Hi,

How can i search several string got as input from look-up and display a table with number of occurrence of each string

I'm trying to find this out for quite some time
Thank you,

Re: Search set of strings from lookup and list count of occurance

Ayn — Thu, 11 Oct 2012 12:27:32 GMT

How is this different from http://splunk-base.splunk.com/answers/61146/search-mutiple-strings-in-logs-and-give-count-of-respective-string ?

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Thu, 11 Oct 2012 13:23:04 GMT

I didnt get the answer i need. Thought my question was not clear enough. I closed the other question

Re: Search set of strings from lookup and list count of occurance

dart — Fri, 12 Oct 2012 12:45:10 GMT

Does
|inputlookup my_lookup | stats count by field
do what you need?

Re: Search set of strings from lookup and list count of occurance

SarahWKarvenz — Fri, 12 Oct 2012 14:42:26 GMT

No, it doesn't. There are several issues that we are dealing with:

First, the problem is that the search strings are not a field.
It would be great if we could make the search strings display as a field or set of fields, but again, a few issues that we need to resolve first. a. The logs are free form and there is no standard format in order to find the specified text. b. It is possible to have more than one matching string in an event.

Re: Search set of strings from lookup and list count of occurance

SarahWKarvenz — Mon, 28 Sep 2020 12:37:15 GMT

The other issue is one of performance. It may be possible to have over 100 strings that we are searching for and wanting to count....but also that this query has to be run every 2 minutes on a large set of data - looping through the results set for each string is not possible.

I am wondering how the splunk highlighting works - essentially we would like to make those highlighted words into a single multivalued field which we could then count.

We would like the final output to look like:
search_string count_of_times_in_last_two_minutes

Re: Search set of strings from lookup and list count of occurance

dart — Fri, 12 Oct 2012 15:24:01 GMT

Could you define the things you are searching for as eventtypes? Then count by eventtype

Re: Search set of strings from lookup and list count of occurance

SarahBOA — Mon, 15 Oct 2012 16:03:08 GMT

eventtype won't work unfortunately. Due to permissions on the splunk instance and server etc, we need to be able to pull the list (which will be changed by non-splunk admin users) in from a csv file.

Re: Search set of strings from lookup and list count of occurance

sideview — Wed, 17 Oct 2012 07:11:22 GMT

Are the strings just simple strings with no spaces, or are some of them multiple search terms (ie each one has spaces in it), or are they complex search expressions with parentheses, OR's and NOT's in them. And as a followup, if they do contain spaces, but not complex logic (parens, OR's, NOT's), then do you want the terms searched for as a single string, ie "foo bar baz", where it matches only if the exact string "foo bar baz" is in the raw event? or do you want it to match any event that has "foo", "bar" and "baz" as indexed terms anywhere in the event.

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Wed, 17 Oct 2012 07:53:28 GMT

@sideview
All simple strings without any space OR NOT

Re: Search set of strings from lookup and list count of occurance

sideview — Wed, 17 Oct 2012 08:23:58 GMT

Restating the goal ( to check that I understood it right)

given a big set of simple terms like "fred" and "mildred"
-- search a bunch of indexed events for all of these terms at once
-- match events that contain one or more of the terms
-- end up with a table showing the number of occurrences of each term in those events (assuming some events match more than one term)

Here's my nutty idea. First, in english:

a) use inputlookup in a subsearch to generate the searchterms
b) use a second inputlookup command in a second subsearch to actually glue ALL of the terms from the entire lookup onto each row of matched events, as a field called foo, with each set of terms separated from the others by some safe character..
c) back in the outer search, use the eval command to split the giant superset of all foo values, by our safe character, generating a multivalue field called foo.
c) then use mvexpand foo and now we instead of N lookup rows and M events, we have N*M events. (It becomes important that your number of terms doesn't get terribly large. 100 should be fine)
d) use a where clause to filter it down to just the rows where the _raw value contains the foo value. (We'll have to lowercase both sides because the like() function is case-sensitive)
e) stats count by foo.

Here it is again, in the search language. Note you'll have to change the name of your lookup and the name of the field in your lookup.

[| inputlookup your_lookup_here | rename yourStringFieldName as search | fields search | format] 
| rename _raw as rawText
eval foo=[| inputlookup your_lookup_here | stats values(yourStringFieldName) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""] 
| eval foo=split(foo,",") 
| mvexpand foo 
| eval foo=lower(foo)     
| eval rawText=lower(rawText) 
| where like(rawText,"%"+foo+"%")
| stats count by foo

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Thu, 18 Oct 2012 05:32:50 GMT

For those who look at this question

I get error in EVAL command. But it works fine in Nick's system. He tried a great deal in helping me, but could not succeed. If i succeed, I will drop a note. If others got some error with the above command and fixed it, please post the same here for the benefit of the community

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Mon, 28 Sep 2020 12:38:42 GMT

please check this post, the issue i had is fixed in the post http://splunk-base.splunk.com/answers/31842/why-cant-i-use-subsearch-in-the-case-function-in-the-eval-command

Eval part should be like below





eval foo=[| inputlookup your_lookup_here | stats values(yourStringFieldName) as query | eval query=mvjoin(query,",") | fields query | eval query = "\"".query."\""]

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Mon, 28 Sep 2020 12:38:45 GMT

Full query looks like





[| inputlookup your_lookup_here | rename yourStringFieldName as search | fields search | format] 

| rename _raw as rawText

eval foo=[| inputlookup your_lookup_here | stats values(yourStringFieldName) as query | eval query=mvjoin(query,",") | fields query  eval query = "\"".query."\""] 

| eval foo=split(foo,",") 

| mvexpand foo 

| eval foo=lower(foo)


| eval rawText=lower(rawText) 

| where like(rawText,"%"+foo+"%")

| stats count by foo

Re: Search set of strings from lookup and list count of occurance

ma_anand1984 — Fri, 19 Oct 2012 10:08:25 GMT

********check********
http://splunk-base.splunk.com/answers/62632/how-to-set-up-alert-on-splunk-that-notifies-on-occurrence-of-set-of-strings-above-threshold-value

for more ideas