Splunk Search

How to ignore case and remove characters?

Explorer

I occasionally use Splunk as part of my job to research issues, but am very much a novice. The query below charts the stored procedures and maps their average run times (and it works).

index=X sourcetype IN Y source IN Z
| spath "TotalDuration"              
| search "TotalDuration"="*" 
| chart avg(TotalDuration) as average over ProcName

The issue I'm running into is that the procs may be called with slight variations, such as:
sp_DoAThing
sp_doathing
[sp_doathing]

What I'd like to do is ignore case and remove brackets, so that all three of the examples above return as one proc with one average duration, instead of three. It says I don't have enough karma points to post links so I can't share the similar questions I've read through, but the answers I've seen (for ignoring case) seem as simple as,

| eval ProcName = lower(ProcName)

but I have tried that in many locations and many variations and cannot get it to work. Any help would be greatly appreciated.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

You're on the right track with lower to normalize case. I'm interested in what you get when you try it.
Removing brackets is done with the trim function.

 index=X sourcetype IN Y source IN Z
 | spath "TotalDuration"              
 | search "TotalDuration"="*" 
 | eval ProcName=trim(lower(ProcName),"[]")
 | chart avg(TotalDuration) as average over ProcName
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

SplunkTrust
SplunkTrust

You're on the right track with lower to normalize case. I'm interested in what you get when you try it.
Removing brackets is done with the trim function.

 index=X sourcetype IN Y source IN Z
 | spath "TotalDuration"              
 | search "TotalDuration"="*" 
 | eval ProcName=trim(lower(ProcName),"[]")
 | chart avg(TotalDuration) as average over ProcName
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Explorer

Once I figured out the single quote issue above your solution worked great! Thank you!

0 Karma

Explorer

For some reason this doesn't work for me:

| eval ProcName = lower(ProcName)

It tells me no results are found. I simplified the Proc Name field for the purposes of asking the question. The actual field name is more like:

| eval A.B.C = lower(A.B.C)

Is it possible the periods are messing with it?

0 Karma

Explorer

Yup... that was it. I needed to put single quotes around it.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!