How to create search to split the events for multiple fields?

[njcreatives23]

Can someone please give me a splunk query to split the events for multiple fields?

| rex field=_raw "
:16R:FIN :35B:ISIN ABC1234567 :93B::AGGR//UNIT/488327,494 :93B::AVAI//UNIT/488326, :16S:FIN :16R:FIN :35B:ISIN CDE1234567 :93B::AGGR//FAMT/352000, :93B::AVAI//FAMT/352001,  :16S:FIN
"

I need table as below, i've added max_match in my rex command, but when i input mvexpand for each rex individually they don't split.

ISIN                                                         AGGR                                              AVAI
ABC1234567                                     488327,494                                488326,
CDE1234567                                     352000 ,                                        352001,

Report:

|rex field=_raw max_match=0 "35B:ISIN(?<ISIN>.{10})"
|rex field=_raw max_match=0 "AGGR//(?<AGGR>.{1,20})"
|rex field=_raw max_match=0 "AVAI//(?<AVAI>.{1,20})"

|table ISIN AGGR AVAI

[ITWhisperer · Accepted solution]

| makeresults
| eval _raw=":16R:FIN :35B:ISIN ABC1234567 :93B::AGGR//UNIT/488327,494 :93B::AVAI//UNIT/488326, :16S:FIN :16R:FIN :35B:ISIN CDE1234567 :93B::AGGR//FAMT/352000, :93B::AVAI//FAMT/352001,  :16S:FIN"
| rex max_match=0  ":16R:FIN (?<line>.+?):16S:FIN"
| mvexpand line
| rex field=line "35B:ISIN(?<ISIN>.{10})"
| rex field=line "AGGR//(?<AGGR>.{1,20})"
| rex field=line "AVAI//(?<AVAI>.{1,20})"
| table ISIN AGGR AVAI

[njcreatives23]

I'm searching this within an index how do i write so it looks at the whole message. I need to further use other filters (search). Which is not working along with this. You've defined the _raw as content, i need to look at a specific rex field i've defined in midst of the query.

| makeresults
index=abc
|eval _raw=replace(_raw,"&#13;","")
|rex field=_raw "{2:O(?<MessageType>.{3})"
| rex max_match=0 ":16R:FIN (?<line>.+?):16S:FIN"
| mvexpand line
| rex field=line "35B:ISIN(?<ISIN>.{12})"
| rex field=line "AGGR//(?<AGGR>.{1,20})"
| rex field=line "AVAI//(?<AVAI>.{1,20})"
|search MessageType="535"
| table ISIN AGGR AVAI

[ITWhisperer]

You don't need the makeresults

The makeresults and eval _raw were just setting up sample data.

[njcreatives23]

Below isn't working as well what am i doing wrong? When i remove these rex entries i am resulting in 1500 events. But 0 when add them back on.

0 events:

index=abc
|eval _raw=replace(_raw,"&#13;","")
|rex field=_raw "{2:O(?<MessageType>.{3})"
| rex max_match=0 ":16R:FIN (?<line>.+?):16S:FIN"
| mvexpand line
| rex field=line "35B:ISIN(?<ISIN>.{12})"
| rex field=line "AGGR//(?<AGGR>.{1,20})"
| rex field=line "AVAI//(?<AVAI>.{1,20})"
|search MessageType="535"
| table ISIN AGGR AVAI

1500 events:

index=abc
|eval _raw=replace(_raw,"&#13;","")
|rex field=_raw "{2:O(?<MessageType>.{3})"
|search MessageType="535"
| table ISIN AGGR AVAI

[ITWhisperer]

If it works up to the search, then it is probably the rex extract of line which isn't working. This rex matches the example you gave, but perhaps it doesn't match with your actual events. Please check your events that they match the ":16R:FIN " start and ":16S:FIN" patterns.

index=abc
|eval _raw=replace(_raw,"
","")
|rex field=_raw "{2:O(?<MessageType>.{3})"
|search MessageType="535"
| rex max_match=0 ":16R:FIN (?<line>.+?):16S:FIN"
| mvexpand line
| rex field=line "35B:ISIN(?<ISIN>.{12})"
| rex field=line "AGGR//(?<AGGR>.{1,20})"
| rex field=line "AVAI//(?<AVAI>.{1,20})"
| table ISIN AGGR AVAI

[njcreatives23]

The start and end is definitely in the events. Does it matter as they are in different line?
|rex field=_raw "16R:FIN(?<fin>[^\{]*)"

Some content of the event as below using rex field, value of fin:

:16R:FIA
:12A::CLAS/ISIT/CORP
:98A::MATU//20291201
:92A::INTR//2,375
:16S:FIA
:90A::MRKT//PRCT/12,356
:93B::AGGR//UNIT/0000,
:19A::HOLD//123,12
:16S:FIN
:16R:FIN

[njcreatives23]

|eval _raw=replace(_raw,"(?m)\s+"," ")

It was the line breaks i have in my _raw causing the issue. I was able to remove line breaks, and it's done the trick.

[PickleRick]

mvexpand is not the way to go. Even if you had multivalued fields, mvexpand over each field would give you a cartesian product of those fields (with 3 2-valued fields you'll get 8 different combinations as an output and that's probably not what you want).

If your events always contain the fields in this order, you should capture them in a single regex.

Like

| rex max_match=0 field=_raw "35B:ISIN(?<ISIN>.{10}).*AGGR//(?<AGGR>.{1,20}).*AVAI//(?<AVAI>.{1,20})"

I'd also check if the regex cannot be  improved - for example, ".{1,20}" part in the middle of the string will always match 20 characters so maybe you want to restrict it to some character class. Or maybe you want te enumerate possible "links" between ISISN and AGGR so your regex doesn't "run away"

[njcreatives23]

They're not in same order, some fields are repeated and some not, but each block open and close with 16R:FIN. Close with 16S:FIN. This is not working, and characters between each tag 93B is not constant.  I need them in unique line as the data before16S: is constant and should append to all reiterations.

How can i split the below:

702250081

26XXXXX

CLAS/ISIT/TD CLAS/ISIT/GOVT CLAS/ISIT/GOVT CLAS/ISIT/GOVT

MATU//20240715 MATU//20240630 MATU//20240715

MRKT//PRCT/100, MRKT//PRCT/96,992 MRKT//PRCT/98,387 MRKT//PRCT/98,414

Expected events:

702250081	26XXXXX	CLAS/ISIT/TD		MRKT//PRCT/100,
702250081	26XXXXX	CLAS/ISIT/GOVT	MATU//20240715	MRKT//PRCT/96,992
702250081	26XXXXX	CLAS/ISIT/GOVT	MATU//20240630	MRKT//PRCT/98,387
702250081	26XXXXX	CLAS/ISIT/GOVT	MATU//20240715	MRKT//PRCT/98,414

_raw:

:20C::SEME//0000000702250081  :97A::SAFE//26XXXXX :16R:FIA :12A::CLAS/ISIT/TD :16S:FIA :90A::MRKT//PRCT/100, :16S:FIN :16R:FIN :16R:FIA :12A::CLAS/ISIT/GOVT :98A::MATU//20240715 :16S:FIA :90A::MRKT//PRCT/96,992 :16S:FIN :16R:FIN :16R:FIA :12A::CLAS/ISIT/GOVT :98A::MATU//20230630 :16S:FIA :90A::MRKT//PRCT/98,387 :16S:FIN :16R:FIN :16R:FIA :12A::CLAS/ISIT/GOVT :98A::MATU//20260815 :16S:FIA :90A::MRKT//PRCT/98,414 :16S:FIN :16S:SUBSAFE

[PickleRick]

Sorry, won't work this way. You have no relation between multivalued fields. So if one of the values is empty, all the remaining values would get "squished" to fill the space.

The proper approach would be to first extract whole "subevents" starting with 16r:fin, ending with 16s:fin, then do a mvexpand to make separate events from them. This way you'd have a full set of your fields per event. Then apply your regexes extracting single fields.

[ITWhisperer]

This is what my solution does.

[PickleRick]

True dat.

Didn't notice. Focused on OP's response. 🙂