Need help writting a rex query

[phamxuantung]

Hello,

For starter, I'm an amateur in regex query, so I use Field Extraction, but it's very clunky and cannot extract all the fields I want and also sometime have wrong extraction.

The event that I want to extract is

2022-11-09 17:36:05 BANK_CITAD_ID="79303001", SOTIEN_CONLAI="150000000", UPDATED_DATE="2022-11-09 17:36:05.0", FILE_NAME="GTCG_dinhky_20221109.xlsx", STATUS="DATA_ERROR", ERROR_MSG="NOT_ALLOW_LIMIT", SOTIENTANG="0", SOTIENGIAM="0", LOAI_FILE="DK", STT="2", BANK_CODE="STB", ID="6829", NOTE="DC1:2286754104070,1,10/11/2022 00:00:00;DC2:10000000000,1501000000,10/11/2022 00:00:00,1000001;DEL: 1501000001,10/11/2022 00:00:00;DEL: 1501000001,10/11/2022 00:00:00;"

There are a total of 8 fields that I want to extract, the field name can be field1, field2..., at least that I can handle.

Thank you in advance.

[gcusello]

hI @phamxuantung,

I suppose that you said 8 fields meaning that each red highlighted string is divided in two fields by comma, is it correct?

in this case, you could try something like this:

| rex "NOTE\=\"\w+\d:(?<field1>\d+),(?<field2>\d+)[^;]+;\w+\d:(?<field3>\d+),(?<field4>\d+).*?DEL:\s+(?<field5>\d+),(?<field6>[^;]*;)DEL:\s+(?<field7>\d+),(?<field8>[^;]*);"

that you can test at https://regex101.com/r/GTz8Dd/1

Ciao.

Giuseppe

[phamxuantung]

Hi @gcusello,

Your rex only work if the event content all the fields. I forgot to mention that field5, field6, field7, field8 sometime don't apper, field1->field4 always there. The event could be (without DEL)

2022-11-10 13:46:12 BANK_CITAD_ID="79303001", SOTIEN_CONLAI="150000000", UPDATED_DATE="2022-11-10 13:46:12.0", FILE_NAME="GTCG_dinhky_20221110.xlsx", STATUS="DATA_ERROR", ERROR_MSG="NOT_ALLOW_LIMIT", SOTIENTANG="0", SOTIENGIAM="0", LOAI_FILE="DK", STT="2", BANK_CODE="STB", ID="6838", NOTE="DC1:1,1,11/11/2022 00:00:00;DC2:1501000000,1501000000,11/11/2022 00:00:00,999999;"

Or it only have 1 DEL, like

2022-11-09 17:36:05 BANK_CITAD_ID="01202001", SOTIEN_CONLAI="67499999", UPDATED_DATE="2022-11-09 17:36:05.0", FILE_NAME="GTCG_dinhky_20221109_2.xlsx", STATUS="DATA_ERROR", ERROR_MSG="NOT_ALLOW_LIMIT", SOTIENTANG="0", SOTIENGIAM="0", LOAI_FILE="DK", STT="1", BANK_CODE="BIDV", ID="6831", NOTE="DC1:3350000000000,1,10/11/2022 00:00:00;DC2:250000000000,675000000,10/11/2022 00:00:00,11;DEL: 675000001,10/11/2022 00:00:00;"

[gcusello · Accepted solution]

Hi @phamxuantung,

in this case, you have to use two rex commands:

| rex "NOTE\=\"\w+\d:(?<field1>\d+),(?<field2>\d+)[^;]+;\w+\d:(?<field3>\d+),(?<field4>\d+)"
| rex "NOTE\=\"\w+\d:(?<field1>\d+),(?<field2>\d+)[^;]+;\w+\d:(?<field3>\d+),(?<field4>\d+).*?DEL:\s+(?<field5>\d+),(?<field6>[^;]*;)DEL:\s+(?<field7>\d+),(?<field8>[^;]*);"

Ciao.

Giuseppe

[phamxuantung]

So what if I try to rex the date from DC1 and DC2 as well, I try to modify your rex to

| rex "NOTE\=\"\w+\d:(?<field1>\d+),(?<field2>\d+),(?<field3>[^;]*);[^;]+;\w+\d:(?<field4>\d+),(?<field5>\d+)(?<field6>[^;]*);" 
| rex "NOTE\=\"\w+\d:(?<field1>\d+),(?<field2>\d+),(?<field3>[^;]*);[^;]+;\w+\d:(?<field4>\d+),(?<field5>\d+)(?<field6>[^;]*);.*?DEL:\s+(?<field7>\d+),(?<field8>[^;]*;)DEL:\s+(?<field9>\d+),(?<field10>[^;]*);"

but it broke the rex completely.

Could you tell me where do I write it wrong and how could I fix this?

[gcusello]

Hi @phamxuantung,

the approach should be of putting the data source in regex101.com and test or adapt the regex there.

Then you can bring it and test again in Splunk search.

Ciao.

giuseppe