topic Re: need help on dashboard to display count by query string in Splunk Search

need help on dashboard to display count by query string

soumyasaha25 — Tue, 29 Sep 2020 16:58:29 GMT

i have a list of query strings (these are just strings not a field)
(eg. Too many open files, CPU Starvation detected, java.sql.SQLException: Cannot obtain connection, thread(s) in total in the server that may be hung, Trust Association Init Error, problems occurred during startup for, OutOfMemoryError)
My requirement is to save these strings in a field and then run a query like
index=abc sourcetype=xyz "field_name" |stats count by field_name

but i am unable to figure out how to save these query strings in field_name. My intention is to get a table with one column as query strings and the other column to hold the corresponding count values for each string.
Like:
field_name count
OutOfMemoryError 123
Too many open files 234
CPU Starvation detected 345
java.sql.SQLException: Cannot obtain connection 456
thread(s) in total in the server that may be hung 567
Trust Association Init Error 678
problems occurred during startup for 789

also it would be great if i can include the corresponding hosts as well for each string.
Any help/suggestions?

Re: need help on dashboard to display count by query string

DalJeanis — Wed, 29 Nov 2017 07:08:08 GMT

Hmmm.

That depends on where you are getting the strings.

Assuming that you have a fixed list of strings, then the way I would go about it is to put the list of strings into a csv or lookup file with one column.

You can then use the format command as part of a strategy to create your extract.

| inputlookcsv mylist.csv | table myfield 
| format "(" "" "" "" "OR" ")" 
| rex field=search mode=sed "s/myfield=//g"

The above will create a field called search that contains a value like

( "value1" OR "value2" OR ... )

If you put that into square braces as a subsearch, then the value of search will be dropped as a literal into the main search.

Next, you do the same thing again to create a rex that will match each literal. We sort the field in descending alpha order, because that automatically will cause longer items to sort before shorter items like this (<myfield>golfer|golf|go).

 | inputlookcsv mylist.csv 
 | table myfield 
 | sort 0 - myfield
 | format "(" "" "" "" "OR" ")" 
 | rex field=search mode=sed "s/myfield=//g s/ OR /|/g s/(/(?<myfield>/g"

After testing for reasonability, the above goes in a rex line as below.

| rex field=_raw [ the above lines of code]

And then you run it into your stats.

Re: need help on dashboard to display count by query string

niketn — Wed, 29 Nov 2017 07:14:14 GMT

@soumyasaha25 you would need to use regular expressions to extract the errors from log based on the pattern and save as field_name. For us to assist you would need to provide some sample events.

You would need to start with rex command and then convert the same to Field Extractions using Splunk's Interactive Filed Extraction and using regular expression from rex command.

If your Errors are limited to 7 listed above then there could be a bit expensive option to use searchmatch() function with eval command. But it is better to use Field Extraction if you know the pattern to extract fields using Regular Expressions. So kindly provide us with raw events as they appear in your logs and mask/anonymize any sensitive information.

Re: need help on dashboard to display count by query string

soumyasaha25 — Wed, 29 Nov 2017 08:35:43 GMT

Thanks for your suggestions.
Actually (if possible) i trying to get the logic incorporated in a single field (that holds multiple values).
my intention being if there are more strings coming up in the future i dont have to change the entire query.
i tried to use a lookup but i think somewhere i am missing something.
I have about 15 strings as of now. Hence looking for a solution so that the field values are dynamically populated on the search query without having to hard code it.
the regex approach is what i am using now is working fine.

Re: need help on dashboard to display count by query string

kamlesh_vaghela — Wed, 29 Nov 2017 08:49:52 GMT

Hi @soumyasaha25 ,

Can you please try this search?

index=abc sourcetype=xyz
| eval ErrorMsg=
case(
like(_raw,"%Too many open files%"),"Too many open files",
like(_raw,"%CPU Starvation detected%"),"CPU Starvation detected",
like(_raw,"%java.sql.SQLException: Cannot obtain connection%"),"java.sql.SQLException: Cannot obtain connection",
like(_raw,"%thread(s) in total in the server that may be hung%"),"thread(s) in total in the server that may be hung",
like(_raw,"%Trust Association Init Error%"),"Trust Association Init Error",
like(_raw,"%problems occurred during startup for%"),"problems occurred during startup for",
like(_raw,"%OutOfMemoryError%"),"OutOfMemoryError",
1=1,"Other"
)
|stats count by ErrorMsg

Thanks

Re: need help on dashboard to display count by query string

soumyasaha25 — Tue, 29 Sep 2020 16:58:32 GMT

i am trying to run the search as below
somehow the last part of the regex s/(/(?search_string/g throws an error - Error in 'rex' command: Regex: missing closing parenthesis. i tried using s/(/(?)/g, but that throws the same error.

Also, once this syntax issue is sorted the final query should look like (please correct me if i am wrong)

Re: need help on dashboard to display count by query string

soumyasaha25 — Wed, 29 Nov 2017 10:07:34 GMT

Thank you for your suggestion.
This is working perfectly, but i am also looking at the feasibility to use the lookup file so that in a rare event if there are any changes/addition/deletion to the query strings, no one touches the actual query, just a change/addition/deletion in the lookup file would be enough.

Re: need help on dashboard to display count by query string

niketn — Wed, 29 Nov 2017 11:20:21 GMT

As requested can you provide some sample events so that you can perform a field extraction?

Re: need help on dashboard to display count by query string

soumyasaha25 — Tue, 29 Sep 2020 18:14:16 GMT

I am currently using this query, it is working fine but now i need to add 2 more columns in the lookup file to display jvm name and cluster name
Below is the current query: