topic Re: Require regex optimization for splunk query getting error from splunk in Dashboards & Visualizations

How to optimize regex for splunk query and why getting error from splunk?

Abhineet — Thu, 22 Jun 2023 17:51:08 GMT

Hi,

Getting below mentioned error while executing splunk query.

QUERY:

index=lsc_exacta_index source="L:\\ProgramData\\Bastian Software\\Logs\\ExactaImport\\ExactaImport.txt"
| rex field=_raw ".* Order \[(?<imWho>[\d-]+) - .*\] successfully assigned.*"
| rex field=_raw "\.* Bastian\.Exacta\.Interface\.Processes\.ExactaProductTranslatorBase - Validation of Message Successfull, Prepare to Insert\n.*ROWS ONLY;\@p0 = \'(?<imWho>[\d-]+)\'.*\[.*"
| rex field=_raw ".*\/line id \[(?<imWho>[\d-]+) -.* was cancelled successfully.\n.*"
| rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[.*(\n|.)*- Messages processed successfully.*"
| eval exactaDocTime = strftime(_time, "%Y-%m-%d %H:%M:%S")
| search imWho !=""
| eval exactaDocStatus = if(exactaDocTime != "","Created",NA)
| table imWho exactaDocTime exactaDocStatus

Help me to optimize regex used in above mentioned query to avoid error mentioned in screenshot.

Thanks

Abhineet Kumar

Re: Require regex optimization for splunk query getting error from splunk

gcusello — Thu, 22 Jun 2023 13:19:11 GMT

Hi @Abhineet,

you have to reduce the lenght of the second regex, maybe in this way it should still work:

| rex "ExactaProductTranslatorBase - Validation of Message Successfull, Prepare to Insert\n.*ROWS ONLY;\@p0 = \'(?<imWho>[\d-]+)\'.*\[.*"

| rex "Prepare to Insert\n.*ROWS ONLY;\@p0 = \'(?<imWho>[\d-]+)\'.*\[.*"

ciao.

Giuseppe

Re: Require regex optimization for splunk query getting error from splunk

Abhineet — Thu, 22 Jun 2023 13:35:58 GMT

Hi Giuseppe,

As per error issue is with regex mentioned below.

| rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[.*(\n|.)*- Messages processed successfully.*"

we have to capture two string "[Import Pick Orders]" and "Messages processed successfully" both the string are available in 130 lines single event.

Both the string are in two separate line.

between those two string there are multiple line.

Below mentioned is sample event. green highlighted are string and red highlighted need to capture in variable <imWho>

I have omitted 100's of line in between.

SAMPLE EVENT:

11:36:31.008 [Import Pick Orders] (null) DEBUG ExactaConnect.ProcessThreadBase - Found work...
11:36:31.024 [Import Pick Orders] (null) INFO Bastian.Exacta.Interface.Processes.ExactaOrderTranslatorBase - Validation of Message Successful, Prepare to Insert / Update
11:36:31.024 [Import Pick Orders] (null) DEBUG NHibernate.SQL - select order0_.ORDER_ID as order1_239_, order0_.ORDER_NAME as order2_239_, order0_.QC_REQUIRED as qc3_239_, order0_.ORDER_STATUS as order4_239_, order0_.ORDER_STATUS_AGGREGATE as order5_239_, order0_.ORDER_PRIORITY as order6_239_, order0_.CHAIN_ID as chain7_239_, order0_.ORDER_TYPE as order8_239_, order0_.ON_HOLD as on9_239_, order0_.ON_HOLD_REASON as on10_239_, order0_.DUE_DATE as due11_239_, order0_.INCOMPLETE_ORDER_RULE as incomplete12_239_, order0_.ORDER_CATEGORY as order13_239_, order0_.TRAILER_STOP as trailer14_239_, order0_.TRAILER_ROUTE as trailer15_239_, order0_.CREATED_DATE as created16_239_, order0_.PROCESS_FLAG as process17_239_, order0_.STOP_TYPE as stop18_239_, order0_.INVOICE_NUMBER as invoice19_239_, order0_.EXPECTED_SHIP_DATE as expected20_239_, order0_.START_DATE_TIME as start21_239_, order0_.CUSTOMER_CATEGORY as customer22_239_, order0_.QC_REASON_TYPE as qc23_239_, order0_.CUSTOMER_ID as customer24_239_, order0_.ROUTE_ID as route25_239_, order0_.OUTBOUND_SHIPMENT_ID as outbound26_239_, order0_.DOCUMENT_ID as document27_239_, order0_.COMPANY_ID as company28_239_, order0_.WAREHOUSE_ID as warehouse29_239_, order0_.DOCK_ID as dock30_239_ from ORDER_HEADER order0_ where order0_.ORDER_NAME=@p0 and order0_.ORDER_TYPE=@p1 ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;@p0 = '47704575' [Type: String (4000:0:0)], @p1 = 1 [Type: Int32 (0:0:0)]
11:36:31.024 [Import Pick Orders] (null) DEBUG Bastian.Exacta.Business.Persistance.SessionFactory - Closing NHibernate session...
11:36:31.149 [8] (null) INFO Performance - Committing database updates took 0.0021792 secs
11:36:31.149 [8] (null) DEBUG Bastian.Exacta.Business.Persistance.SessionFactory - Closing NHibernate session...
11:36:31.149 [Import Pick Orders] (null) INFO Performance - Committing database updates took 0.1071954 secs
11:36:31.149 [Import Pick Orders] (null) INFO Bastian.Exacta.Interface.Processes.ExactaOrderTranslatorBase - Firing import order event for order 214b0d35-7b7c-4cb9-bb48-b0210132a922 of type PICK
11:36:31.149 [Import Pick Orders] (null) DEBUG Bastian.Exacta.Business.Persistance.UnitOfWork - Committing updates to database from unit of work
11:36:31.149 [Import Pick Orders] (null) INFO Performance - Committing database updates took 0.000891 secs
11:36:31.149 [Import Pick Orders] (null) DEBUG Bastian.Exacta.Business.Persistance.SessionFactory - Closing NHibernate session...
11:36:31.149 [Import Pick Orders] (null) DEBUG ExactaConnect.ProcessThreadBase - Translated messages...
11:36:31.164 [Import Pick Orders] (null) DEBUG ExactaConnect.ProcessThreadBase - Messages processed successfully...
11:36:31.164 [Import Pick Orders] (null) DEBUG Bastian.Exacta.Business.Persistance.SessionFactory - Opening NHibernate session using the production factory...

Re: Require regex optimization for splunk query getting error from splunk

gcusello — Thu, 22 Jun 2023 13:57:56 GMT

Hi @Abhineet,

you can manage the multiline adding "(?ms)" to the start of your regex.

there's a thing that I don't understand in your requirement:

The green string must be used to filter events or must be extracted as fields?

if they want to use them only to filter events, you don't need to put them in the regex, you can use the in the main search to filter your events.

Instead the red string can be extracted as field with the regex.

Ciao.

Giuseppe

Re: Require regex optimization for splunk query getting error from splunk

ITWhisperer — Thu, 22 Jun 2023 14:00:16 GMT

The fact that all the lines are in a single event helps.

| streamstats count as event | multikv noheader=t | fields _time _raw event | rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\'" | rex field=_raw ".*\[Import Pick Orders\].*- (?<success>Messages processed successfully).*" | stats list(_raw) as _raw values(imWho) as imWho values(success) as success by event | where isnotnull(imWho) AND isnotnull(success) | eval _raw=mvjoin(_raw," ")

Re: Require regex optimization for splunk query getting error from splunk

Abhineet — Thu, 22 Jun 2023 14:02:25 GMT

Hi, Giuseppe

Thanks for your response.

I have resolved the regex issue replacing fourth regex of query with below mentioned regex.

rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[[\S\s]*- Messages processed successfully.*"

Thanks

Abhineet Kumar

Re: Require regex optimization for splunk query getting error from splunk

Abhineet — Thu, 22 Jun 2023 14:04:13 GMT

Hi, Giuseppe

Thanks for your response.

I have resolved the regex issue replacing fourth regex of query with below mentioned regex.

rex field=_raw ".*\[Import Pick Orders\].*ROWS ONLY;@p0 = \'(?<imWho>[\d-]+)\' \[[\S\s]*- Messages processed successfully.*"

Thanks

Abhineet Kumar

Re: Require regex optimization for splunk query getting error from splunk

gcusello — Thu, 22 Jun 2023 14:14:40 GMT

Hi @Abhineet,

good for you, see next time!

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉