Splunk Enterprise

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

njcreatives23
Engager

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You don't need the makeresults

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

0 Karma

njcreatives23
Engager

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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,"&#13;","")
|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
0 Karma

njcreatives23
Engager

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_0-1646003751030.png

 

0 Karma

njcreatives23
Engager

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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"

0 Karma

njcreatives23
Engager

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:

70225008126XXXXXCLAS/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:

70225008126XXXXX

CLAS/ISIT/TD

 

MRKT//PRCT/100,

70225008126XXXXX

CLAS/ISIT/GOVT

MATU//20240715

MRKT//PRCT/96,992

70225008126XXXXX

CLAS/ISIT/GOVT

MATU//20240630

MRKT//PRCT/98,387

70225008126XXXXX

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is what my solution does.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

True dat.

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...