Splunk Search

Need help writting a rex query

phamxuantung
Communicator

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.

 

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

phamxuantung
Communicator

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Communicator

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?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...