Splunk Search

How do I edit my rex statement to extract fields from a raw string of repeated text into a table?

arunsubram
Explorer

Used the following command rex "(?\d+)\[(?\-?\d+\.?\d+)\]"| table ..., but the entire string gets extracted into a single column.

Raw String in the Log:

Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}

Desired table

AdId        reason       externalError
313131313   ERROR_400    null
313131313   ERROR_500    null
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try something like this (run anywhere sample, first two lines are to generate data, replace it with your search)

| gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
| rex max_match=0 "Status\{AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)"

Update
The extracted fields would be multivalued fields as there are multiple values in single events, thus the export formatting. Try something like to export each set as separate event.

Option 1

 | gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
 | rex max_match=0 "Status\{(?<temp>AdId='\d+',s*reason='[^']+',\s*externalError='[^']+)'" | table temp | mvexpand temp 
| rex field=temp "AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)" | table AdId Error externalError

Option 2

| gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
    | rex max_match=0 "Status\{AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)" | eval temp=mvzip(AdId,mvzip(Error,externalError,"##"),"##") | table temp | mvexpand temp 
| rex field=temp "(?<AdId>\d+)##(?<Error>.+)##(?<externalError>.+)" | table AdId Error externalError

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try something like this (run anywhere sample, first two lines are to generate data, replace it with your search)

| gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
| rex max_match=0 "Status\{AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)"

Update
The extracted fields would be multivalued fields as there are multiple values in single events, thus the export formatting. Try something like to export each set as separate event.

Option 1

 | gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
 | rex max_match=0 "Status\{(?<temp>AdId='\d+',s*reason='[^']+',\s*externalError='[^']+)'" | table temp | mvexpand temp 
| rex field=temp "AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)" | table AdId Error externalError

Option 2

| gentimes start=-1 | eval _raw="Status{AdId='313131313', reason='ERROR_400', externalError='null'},Status{AdId='313131313', reason='ERROR_500', externalOfferId='null'}" | table _raw
    | rex max_match=0 "Status\{AdId='(?<AdId>\d+)',s*reason='(?<Error>[^']+)',\s*externalError='(?<externalError>[^']+)" | eval temp=mvzip(AdId,mvzip(Error,externalError,"##"),"##") | table temp | mvexpand temp 
| rex field=temp "(?<AdId>\d+)##(?<Error>.+)##(?<externalError>.+)" | table AdId Error externalError

arunsubram
Explorer

Thank a ton. This solved the problem. In the above example AdId , corresponding Reason,corresponding externalError gets displayed in a row. When I export to csv I have this data in a single cell for each attribute. Is there any function to table in separate rows or should be handle through custom code snippet

Current format when exported to csv
"19284032 19221132","MINIMUM_PURCHASE_QTY_NOT_1 MINIMUM_PURCHASE_QTY_NOT_1","null null"

Desired Output when exported to csv
"19284032","MINIMUM_PURCHASE_QTY_NOT_1","null"
"19221132","MINIMUM_PURCHASE_QTY_NOT_1","null"

0 Karma

arunsubram
Explorer

Thanks a lot. worked perfect.

0 Karma

ppablo
Retired

Hi @arunsubram

If the answer by @somesoni2 solved the problem, resolve the post by clicking "Accept" directly below his answer please.

0 Karma

arunsubram
Explorer

Here is the entire string returned in search.

rejectReasons = OfferStatusUpdate{AdId='19284032', reason='MINIMUM_PURCHASE_QTY_NOT_1', externalOfferId='null'},OfferStatusUpdate{AdId='19221132', reason='MINIMUM_PURCHASE_QTY_NOT_1', externalOfferId='null'}

When I make a query as below

*****| rex field=_raw "AdId (?.*)" | timechart fixedrange=false values(AdId),values(reason),count(AdId)

only the first AdId and reason gets tabled. But in the same string, I have 2 AdIds.

These are not key value fields. hence need to extract from text.

Required output, each AdId and reason needs to extracted into single column. Let me know if I was able to articulate.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

To get all matches from rex, add the max_match option.

 rex field=_raw max_match=0 "AdId (?.*)" 
---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

The regex string doesn't match the sample data, but it's not clear what you are trying to do with it so I can't suggest any modifications. You may want to test the string at regex101.com.

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

Richfez
SplunkTrust
SplunkTrust

Do you have an example of what you mean by "the entire string gets extracted into a single column" ?

Do those two events have to be a single event? If we broke those incoming events differently and they came in as two events (Which would entirely solve the problem you pose), would that work for you in other ways?

0 Karma

Jeremiah
Motivator

Those fields are not automatically extracted? If you pipe your search to table, do you see the fields?

... | table AdId, reason, externalError

Since they are name value pairs, it seems like they should be extracted automatically. Is your raw string an example of a single event or two events?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...