topic Re: mvexpand multiple fields in Splunk Enterprise

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

njcreatives23 — Mon, 28 Feb 2022 17:39:05 GMT

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

Re: mvexpand multiple fields

PickleRick — Sun, 27 Feb 2022 11:20:17 GMT

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.

| 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"

Re: mvexpand multiple fields

ITWhisperer — Sun, 27 Feb 2022 12:01:25 GMT

| 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

Re: mvexpand multiple fields

njcreatives23 — Sun, 27 Feb 2022 22:38:45 GMT

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

Re: mvexpand multiple fields

PickleRick — Sun, 27 Feb 2022 20:49:34 GMT

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.

Re: mvexpand multiple fields

ITWhisperer — Sun, 27 Feb 2022 21:04:46 GMT

This is what my solution does.

Re: mvexpand multiple fields

PickleRick — Sun, 27 Feb 2022 22:16:12 GMT

True dat.

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

Re: mvexpand multiple fields

njcreatives23 — Sun, 27 Feb 2022 22:28:17 GMT

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,"","")
|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

Re: mvexpand multiple fields

ITWhisperer — Sun, 27 Feb 2022 22:48:05 GMT

You don't need the makeresults

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

Re: mvexpand multiple fields

njcreatives23 — Sun, 27 Feb 2022 22:53:51 GMT

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,"","")
|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,"","")
|rex field=_raw "{2:O(?<MessageType>.{3})"
|search MessageType="535"
| table ISIN AGGR AVAI

Re: mvexpand multiple fields

ITWhisperer — Sun, 27 Feb 2022 23:03:21 GMT

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

Re: mvexpand multiple fields

njcreatives23 — Sun, 27 Feb 2022 23:18:54 GMT

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

Re: mvexpand multiple fields

njcreatives23 — Mon, 28 Feb 2022 08:44:34 GMT

|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.