Splunk Search

Is there a way to extract a part of a string using a lookup table?

cindygibbs_08
Communicator

Hello friends,

Thank you so much for your help in advance.
I have a field named "ERROR_COLAB" in which a series of responses are concatenated into a single long string, because of  the nature of the ERRORS that can be present there is no a formal, objective, efficient way to "split" the values in  "ERROR_COLAB" to classify the responses concatenated in them.
So I was thinking about what if I can create a lookup table with the values that I need to extract to later on "parse" them into a regex formula in order to extract them.. to illustrate my idea:

Lets say I have this lookup table

code_errormeaning
po_R83No_call_bak
?OPcard_nofunds
HOTELARCH78overbookings


and I have the following values in  "ERROR_COLAB"

ERROR_COLAB
?OP_ERR7+JSU8.OIJK1
po_R83_io
IOS_NEVER:300SSSS
HOTELARCH78?123-


I would like to know if the first part of the string is equal to any of the values on the field "error code" of the lookup table. So my desired result would look like this:

ERROR_COLABcode_error_extractedmeaning
?OP_ERR7+JSU8.OIJK1?OPcard_nofunds
po_R83_iopo_R83No_call_bak
IOS_NEVER:300SSSSN.AN.A
HOTELARCH78?123-HOTELARCH78overbookings

 

Thank you so much guys

 

Kindly,

Cindy

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

If your lookup isn't too large, you could combine it into a multi-value field, add it to all your events, expand it, and compare the events against each of the values.

 

| makeresults
| eval _raw="ERROR_COLAB
?OP_ERR7+JSU8.OIJK1
po_R83_io
IOS_NEVER:300SSSS
HOTELARCH78?123-"
| multikv forceheader=1 
| fields - _* linecount

| streamstats count as row
| appendpipe

    [| eval error_meaning="po_R83|No_call_bak
?OP|card_nofunds
HOTELARCH78|overbookings"
    | rex field=error_meaning max_match=0 "(?<error_meaning>.*)\n?"]
| where isnotnull(error_meaning)
| mvexpand error_meaning
| eval code_error=mvindex(split(error_meaning,"|"),0)
| eval meaning=mvindex(split(error_meaning,"|"),1)
| fields - error_meaning
| eval match=if(like(ERROR_COLAB,code_error."%"),code_error,null)
| eventstats list(match) as match by row
| fillnull value="N/A" match
| eval meaning=if(code_error=match,meaning,if(match="N/A",match,null))
| stats values(match) as code_error_extracted values(meaning) as meaning values(ERROR_COLAB) as ERROR_COLAB by row
| table ERROR_COLAB code_error_extracted meaning

 

The first part is to be replaced with your search for ERROR_COLAB. Then give every event a row number (for grouping later). Then appendpipe your lookup table (inputlookup), combining the entries into a single field before collecting the values into a multi-value field. Get rid of the events without the multivalue field (as they are no longer needed). Expand the multi-value field and split back into two fields. Now see if the string matches the beginning of the ERROR_COLAB field. Mark all those which didn't match anything. Now, collect the matching error code and meaning for each row.

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

If your lookup isn't too large, you could combine it into a multi-value field, add it to all your events, expand it, and compare the events against each of the values.

 

| makeresults
| eval _raw="ERROR_COLAB
?OP_ERR7+JSU8.OIJK1
po_R83_io
IOS_NEVER:300SSSS
HOTELARCH78?123-"
| multikv forceheader=1 
| fields - _* linecount

| streamstats count as row
| appendpipe

    [| eval error_meaning="po_R83|No_call_bak
?OP|card_nofunds
HOTELARCH78|overbookings"
    | rex field=error_meaning max_match=0 "(?<error_meaning>.*)\n?"]
| where isnotnull(error_meaning)
| mvexpand error_meaning
| eval code_error=mvindex(split(error_meaning,"|"),0)
| eval meaning=mvindex(split(error_meaning,"|"),1)
| fields - error_meaning
| eval match=if(like(ERROR_COLAB,code_error."%"),code_error,null)
| eventstats list(match) as match by row
| fillnull value="N/A" match
| eval meaning=if(code_error=match,meaning,if(match="N/A",match,null))
| stats values(match) as code_error_extracted values(meaning) as meaning values(ERROR_COLAB) as ERROR_COLAB by row
| table ERROR_COLAB code_error_extracted meaning

 

The first part is to be replaced with your search for ERROR_COLAB. Then give every event a row number (for grouping later). Then appendpipe your lookup table (inputlookup), combining the entries into a single field before collecting the values into a multi-value field. Get rid of the events without the multivalue field (as they are no longer needed). Expand the multi-value field and split back into two fields. Now see if the string matches the beginning of the ERROR_COLAB field. Mark all those which didn't match anything. Now, collect the matching error code and meaning for each row.

cindygibbs_08
Communicator

Thank you so much my love ..like thanks a million this is great this is excellent 

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...