Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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, an upvote would be appreciated.
0 Karma
Highlighted

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

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
Highlighted

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

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, an upvote would be appreciated.
0 Karma
Highlighted

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

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

Highlighted

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

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","MINIMUMPURCHASEQTYNOT1 MINIMUMPURCHASEQTYNOT1","null null"

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

0 Karma
Highlighted

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

Community Manager
Community Manager

Hi @arunsubram

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

0 Karma
Highlighted

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

Explorer

Thanks a lot. worked perfect.

0 Karma