topic Re: Need help writting a rex query in Splunk Search

Need help writting a rex query

phamxuantung — Thu, 10 Nov 2022 09:36:48 GMT

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.

Re: Need help writting a rex query

gcusello — Thu, 10 Nov 2022 10:01:21 GMT

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

Re: Need help writting a rex query

phamxuantung — Thu, 10 Nov 2022 10:24:30 GMT

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

Re: Need help writting a rex query

gcusello — Thu, 10 Nov 2022 12:07:13 GMT

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

Re: Need help writting a rex query

phamxuantung — Thu, 10 Nov 2022 16:46:29 GMT

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?

Re: Need help writting a rex query

gcusello — Mon, 05 Dec 2022 08:47:22 GMT

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