How to search subfield from field?

[jayeshrajvir]

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

[jayeshrajvir]

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

[jayeshrajvir]

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]

[gcusello]

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

[gcusello]

Hi @jayeshrajvir ,

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

Ciao.

Giuseppe

[jayeshrajvir]

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]

[gcusello]

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

[jayeshrajvir]

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]

[gcusello]

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

[jayeshrajvir]

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]

[jayeshrajvir]

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

[gcusello]

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

[jayeshrajvir]

Thanks for your response. It looks good

[gcusello]

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 😉

[jayeshrajvir]

Can i write in a better way?

I tried but its not working

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

[gcusello]

Hi @jayeshrajvir,

what do you mean with "better way"?

the regex is correctly working in regex101.

Ciao.

Giuseppe

[gcusello]

Hi @jayeshrajvir,

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

ciao.

Giuseppe