Splunk Search

Help with extraction to trim value?

uagraw01
Motivator

Hello Splunker !!

XBY-123-UTB

SVV-123-TBU

I want extract to trim the value according

Condition  :  for XBY-123-UTB I want to trim to XBY only 3 character

                      for SVV-123-TBU I want to trim the string till to 7 character

What I have tried :

Column name is : Employee_number

If(LIKE(Employee_number,"%SVV%"),substr(Employee_number,1,7), LIKE(ubstr(Employee_number,1,3))

 

But this is not working for me. Please help me in this and provide  me other approaches as well.

0 Karma
1 Solution

JacekF
Path Finder

Your "if" function is wrong. You should use something like this:

if(like(data,"SVV%"), substr(data, 1, 3), substr(data, 1, 7))

I've tested it with the following query:

|  makeresults
| eval data = "XBY-123-UTB,SVV-123-TBU"
| fields - _time
| makemv data delim=","
| mvexpand data
| eval result = if(like(data,"SVV%"), substr(data, 1, 3), substr(data, 1, 7))

 

View solution in original post

JacekF
Path Finder

Your "if" function is wrong. You should use something like this:

if(like(data,"SVV%"), substr(data, 1, 3), substr(data, 1, 7))

I've tested it with the following query:

|  makeresults
| eval data = "XBY-123-UTB,SVV-123-TBU"
| fields - _time
| makemv data delim=","
| mvexpand data
| eval result = if(like(data,"SVV%"), substr(data, 1, 3), substr(data, 1, 7))

 

uagraw01
Motivator

@JacekF @richgalloway @starcher I will try all the suggested solutions and let you all knows. Thanks for all responses.

 

0 Karma

uagraw01
Motivator

Actually issue with the field name, So when I renamed the field name and then apply all your suggestions, Its works. Thanks all for the suggestions

richgalloway
SplunkTrust
SplunkTrust

What, specifically, does "is not working" mean in this case?

Consider using a case statement, since you have multiple criteria for trimming the field.

| eval Employee_number = case(like(Employee_number,"SVV%"),substr(Employee_number,1,7), 
     like(Employee_number, "XBY%"), substr(Employee_number,1,3),
     1==1, Employee_number)

I believe the like function must be in lower case.  Also, the third argument to the if function must not be a boolean.

---
If this reply helps you, Karma would be appreciated.

starcher
Influencer
| makeresults 
| eval Employee_number="XBY-123-UTB"
| eval value=case(match(Employee_number,"(?i)SVV"),substr(Employee_number,1,7),1==1,substr(Employee_number,1,3))

 

and testing

| makeresults 
| eval Employee_number="SVV-123-TBU"
| eval value=case(match(Employee_number,"(?i)SVV"),substr(Employee_number,1,7),1==1,substr(Employee_number,1,3))
| makeresults 
| eval Employee_number="XBY-123-UTB"
| eval value=case(match(Employee_number,"(?i)SVV"),substr(Employee_number,1,7),1==1,substr(Employee_number,1,3))
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...