Splunk Search

need help on dashboard to display count by query string

Contributor

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 "fieldname" |stats count by fieldname

but i am unable to figure out how to save these query strings in fieldname. 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?

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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.

0 Karma

SplunkTrust
SplunkTrust

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Contributor

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.

0 Karma

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Contributor

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.

I ran the code as below
| inputlookup searchstring.csv
| table search
string
| sort 0 - searchstring
| format "(" "" "" "" "OR" ")"
| rex field=search mode=sed ""s/search
string=//g s/ OR /|/g"

and got the below results
( "thread(s) in total in the server that may be hung" | "problems occurred during startup for" | "java.sql.SQLException: Cannot obtain connection:" | "java.lang.OutOfMemoryError for" | "com.ibm.websphere.ce.cm.StaleConnectionException:" | "WSVR0607W" | "WSVR0605W" | "UnsatisfiedLinkError" | "Too many open files" | "The port may already be in use" | "SSLHandshakeException" | "SSL Handshake Failed" | "SQLException: Failed to start database" | "SECJ0418I" | "SECJ0384E: Trust Association Init Error" | "OutOfMemoryError" | "HMGR0152W: CPU Starvation detected" | "EJPEJ0069E" | "Communication function detecting the error:" | "CPU Starvation detected" )

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

index=abc sourcetype=xyz | rex field=raw [
| inputlookup search
string.csv
| table searchstring
| sort 0 - search
string
| format "(" "" "" "" "OR" ")"
| rex field=search mode=sed ""s/searchstring=//g s/ OR /|/g/ s/(/(?searchstring/g"]
|stats count by search_string, host

0 Karma

Contributor

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:

index = abc sourcetype="xyz" [|inputlookup searchstring.csv | return 100 $searchstring]
| rename raw as rawText
| eval match
string=[|inputlookup searchstring.csv |stats values(searchstring) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval matchstring=split(matchstring,",")
| mvexpand matchstring
| where like(rawText,"%"+match
string+"%")
| stats values(host) AS HostName count by match_string

0 Karma