topic Re: search subfield from field in Alerting

How to search subfield from field?

jayeshrajvir — Wed, 08 Feb 2023 17:41:30 GMT

I have a field EXT-ID[48] of 18 bytes, where the first three bytes should contain an identifier as OCT, positions 8-10 will contain the value 000 to 100, and position 11 will contain values 1-3.

SPLUNK log as follows

For example, I have an identifier received as OCT but position 8-10 is blank and the 11th position has value.

I need a SPLUNK query where I would like to check that position 1-3 has value OCT and position 8-10 contain value 000 to 100, basically position 8-10 has a nonblank value in EXT-ID[48]

EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 1]

I have tried this query but it's not working

index=au_axs_common_log source=*Visa* "EXT-ID[48] FLD[Additional Data, Priva..]" | rex field=_raw "(?s)(.*?FLD\[Additional Data, Priva.*?DATA\[(?<F48>[^\]]*).*)" | search F48="OCT%"

@SPL

Re: search subfield from field

gcusello — Wed, 08 Feb 2023 09:40:12 GMT

Hi @jayeshrajvir ,

could you share a sample of your data to test the regex?

Ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Wed, 08 Feb 2023 11:53:24 GMT

I have provided two sample data, the first example I have the identifier OCT in positions 1-3 and in positions 8-10 is spaces. I want to extract where position 1-3 has OCT and position 8-10 has value from 000 to 1000

Sample 1

+EXT-ID[43.1] FLD[43-1 ATM Location] FRMT[FIXED] LL[0] LEN[25] DATA[PAYPAL*GORTON STEPHANIE K] +EXT-ID[43.2] FLD[43-2 City Name] FRMT[FIXED] LL[0] LEN[13] DATA[Sydney ] +EXT-ID[43.3] FLD[43-3 Country Code] FRMT[FIXED] LL[0] LEN[2] DATA[AU] EXT-ID[44] FLD[Additional Response Da..] FRMT[LVAR-Bin-Group-..] LL[1] LEN[1] DATA[C] +EXT-ID[44.1] FLD[44-1 Response Source o..] FRMT[FIXED] LL[0] LEN[1] DATA[C] EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 1]

Sample 2

+EXT-ID[37.2] FLD[RRN Stan] FRMT[FIXED] LL[0] LEN[6] TYPE[String] CHS[ASCII] DATA[457991] EXT-ID[38] FLD[Authorization Identifi..] FRMT[FIXED] LL[0] LEN[6] TYPE[String] CHS[EBCDIC] DATA[275162] EXT-ID[39] FLD[Response Code] FRMT[FIXED] LL[0] LEN[2] TYPE[String] CHS[EBCDIC] DATA[00] EXT-ID[41] FLD[Card Acceptor Terminal..] FRMT[FIXED] LL[0] LEN[8] TYPE[String] CHS[EBCDIC] DATA[00000001] EXT-ID[42] FLD[Card Acceptor Identifi..] FRMT[FIXED] LL[0] LEN[15] TYPE[String] CHS[EBCDIC] DATA[Netflix ] EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[21] TYPE[String] CHS[EBCDIC] DATA[MNetflix Subscription]

Re: search subfield from field

gcusello — Wed, 08 Feb 2023 13:27:25 GMT

Hi @jayeshrajvir,

sorry but it isn't clear:

I see OCT only at the end of the first sample.

could you highlight in bold or underline only the parts to extract?

Ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Wed, 08 Feb 2023 16:45:35 GMT

Hi,

I want to extract the position 8-10 value when position 1-3 has the value OCT. In the example below position 8-10 has a value of 090.

EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 0901]

Positions 8-10 can have a value from 000-100.

Extract position 8-10 value if position 1-3 has OCT and position 8-10 should have the value 000-100

Re: search subfield from field

jayeshrajvir — Thu, 09 Feb 2023 04:12:07 GMT

This is my sample data

develop a Splunk query
when EXT-ID[3.1] = 26 and ( EXT-ID[19] <> 036 AND +EXT-ID[43.3] <> 'AU' AND EXT-ID[49] <> '036' ) and EXT-ID[48] position 1-3 = OCT and EXT-ID[48] position 8-10 should have the value 000-100.

Please find the data below

+EXT-ID[3.1] FLD[Transaction Type] FRMT[FIXED] LL[0] LEN[2] DATA[26]
+EXT-ID[3.2] FLD[From Account Type] FRMT[FIXED] LL[0] LEN[2] DATA[00]
+EXT-ID[3.3] FLD[To Account Type] FRMT[FIXED] LL[0] LEN[2] DATA[00]
EXT-ID[19] FLD[Acquiring Institution ..] FRMT[FIXED] LL[0] LEN[3] DATA[702]
EXT-ID[43] FLD[Card Acceptor Name or ..] FRMT[FIXED-Group] LL[0] LEN[40] DATA[PAYPAL*GORTON STEPHANIE KSydney AU]
+EXT-ID[43.1] FLD[43-1 ATM Location] FRMT[FIXED] LL[0] LEN[25] DATA[PAYPAL*GORTON STEPHANIE K]
+EXT-ID[43.2] FLD[43-2 City Name] FRMT[FIXED] LL[0] LEN[13] DATA[Sydney ]
+EXT-ID[43.3] FLD[43-3 Country Code] FRMT[FIXED] LL[0] LEN[2] DATA[SG]
EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 0901]
EXT-ID[49] FLD[Currency Code, Transac..] FRMT[FIXED] LL[0] LEN[3] DATA[840]

Re: search subfield from field

gcusello — Thu, 09 Feb 2023 07:48:18 GMT

Hi @jayeshrajvir,

I didn't understand the conditions to define, anyway, this is a regex to extract all fields,

| rex "EXT-ID\[(?<ext_id>[^\]]+)\]\s+FLD\[(?<fld>[^\]]+)\]\s+FRMT\[(?<frmt>[^\]]+)\]\s+LL\[(?<ll>[^\]]+)\]\s+LEN\[(?<len>[^\]]+)\]\s+DATA\[(?<data>[^\]]+)\]"

so you can add all your conditions.

You can test the regex at https://regex101.com/r/XH05sh/1

Ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Thu, 09 Feb 2023 11:26:07 GMT

Thanks. It is possible for you to provide a query in the highlighted position that has a valid value[000-100]. In the example below, we are receiving 090

EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 0901]

Re: search subfield from field

gcusello — Thu, 09 Feb 2023 11:58:04 GMT

Hi @jayeshrajvir,

using the above regex, and an additional regex, you can extract the three digits to check:

| rex "EXT-ID\[(?<ext_id>[^\]]+)\]\s+FLD\[(?<fld>[^\]]+)\]\s+FRMT\[(?<frmt>[^\]]+)\]\s+LL\[(?<ll>[^\]]+)\]\s+LEN\[(?<len>[^\]]+)\]\s+DATA\[(?<data>[^\]]+)\]" | rex field=data "=CT\s+(?<oct>\d\d\d)"

then after these regexes, if the oct field is present you can apply all the controls you like, e.g.

| search oct="090"

Ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Wed, 15 Feb 2023 12:51:18 GMT

Thanks

\d\d\d matches a digit (equivalent to [0-9])

In my example, the first three bytes OCT, from positions 4-7 can have spaces and anything and 8-10 positions should have digits. How do I check if position 1-3 must have value OCT and position 8-10 has /d/d/d

How do I extract the 8-10 value characters from a field?

EXT-ID[48] FLD[Additional Data, Priva..] FRMT[LVAR-Bin] LL[1] LEN[11] DATA[OCT 2001]

Re: search subfield from field

gcusello — Wed, 15 Feb 2023 13:03:03 GMT

Hi @jayeshrajvir,

which are, in your sample the chars to extract? please highlight them.

ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Wed, 15 Feb 2023 13:17:11 GMT

Something like this. Would you please simplify this query, so that it can run efficiently

index=au_axs_common_log source=*Visa* "EXT-ID[48] FLD[Additional Data, Priva..]" | rex field=_raw "(?s)(.*?FLD\[Additional Data, Priva.*?DATA\[(?<F48>[^\]]*).*)"
|eval cli3=substr(F48, 1 ,3) |where cli3 = "OCT" |eval cli10=substr(F48, 8 ,10)| where cli10 >=0 and <=100

Re: search subfield from field

gcusello — Wed, 15 Feb 2023 13:34:48 GMT

Hi @jayeshrajvir,

I cannot test the regex so I assume it's correct, anyway the last condition isn't correct:

index=au_axs_common_log source=*Visa* "EXT-ID[48] FLD[Additional Data, Priva..]" | rex field=_raw "(?s)(.*?FLD\[Additional Data, Priva.*?DATA\[(?<F48>[^\]]*).*)" | eval cli3=substr(F48,1,3), cli10=substr(F48,8,10) | where cli3="OCT" AND cli10>=0 AND cli10<=100

You have to declare the field in each condition, then the AND operator must be in uppercase and you can collapse the last three conditions in one statement.

Ciao.

Giuseppe

Re: search subfield from field

jayeshrajvir — Thu, 16 Feb 2023 05:39:31 GMT

Thanks for your response. It looks good

Re: search subfield from field

gcusello — Thu, 16 Feb 2023 07:24:57 GMT

Hi @jayeshrajvir,

if one answer solves your need, please accept one answer for the other people of Community or tell me how I can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: search subfield from field

jayeshrajvir — Mon, 20 Feb 2023 04:39:52 GMT

Can i write in a better way?

I tried but its not working

https://regex101.com/r/XH05sh/1

Re: search subfield from field

gcusello — Mon, 20 Feb 2023 06:40:02 GMT

Hi @jayeshrajvir,

what do you mean with "better way"?

the regex is correctly working in regex101.

Ciao.

Giuseppe