topic Re: How to remove comms and parenthesis in Splunk Search

How to remove comms and parenthesis?

Sureshp191 — Thu, 15 Jun 2023 18:02:38 GMT

index="myIndex" app_name="myappName" My.Message = "*failed to retrieve the workOrder*"
| rex "Order (?<Order>[^\s]+)"
| stats count BY Order
| addcoltotals count
| rename Order AS "Order#", count AS "# of times failed"

on the Events tab, My.Message returns

Order 1AB5 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 1MB1 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 2MB5 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 2MB6 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 1MB1 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 1MB7 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 1MB9 failed to retrieve the workOrder. Error DataBase mapping incorrect.

On the Statics Tab I see the below values

Order#
(1AB5,
(1MB1,
(2MB6,
1MB7

1MB9

How to remove ( and ,

Thanks

Re: How to remove comms and parenthesis

yeahnah — Thu, 15 Jun 2023 03:55:57 GMT

Hi @Sureshp191

Based on the what you presented that could not happen. Can you maybe provide a screen shot of the search and results in the Splunk UI.

Re: How to remove comms and parenthesis

Sureshp191 — Thu, 15 Jun 2023 04:34:43 GMT

Sorry posted a wrong splunk query, here is the correct one

index="myIndex" appname="myapp" msg.result.message ="*PortfolioSymbol:*"
| rex "PortfolioSymbol: (?<PortfolioSymbol>[^\"]*) Records: (?<Records>[^\"]*) Elapsed: (?<Elapsed>[^\"]*)"
| stats count BY PortfolioSymbol, Records, Elapsed
| addcoltotals count
| rename count AS "Refresh Counts"
When I run the above query, I get the below results:

InsertIntoDatabase Ended, PortfolioSymbol: 2MB5, Records: 317, Elapsed: 0.0500421

InsertIntoDatabase Ended, PortfolioSymbol: 2MB6, Records: 330, Elapsed: 0.0505221

InsertIntoDatabase Ended, PortfolioSymbol: 2MB7, Records: 417, Elapsed: 0.0508121

Below is the Statics tab output:
PortfolioSymbol Records Elapsed

2MB5, 317, 0.0500421

2MB6, 330, 0.0505221

2MB7, 417, 0.0508121

How to remove the comms, Thanks

Re: How to remove comms and parenthesis

yeahnah — Thu, 15 Jun 2023 04:56:18 GMT

Hi @Sureshp191

Not sure how that regex could work as there is no double quote for the exclusion group to match against.

This is the regex you need to not put the , in the field value...

... | rex "PortfolioSymbol: (?<PortfolioSymbol>[^,]+) Records: (?<Records>[^,]+) Elapsed: (?<Elapsed>[^\s]+)" ...

The regex [^,]+ syntax means match anything that is not a comma. Once it matches a comma the capture group match stops and moves to the next match, etc.

Hope that helps

Re: How to remove comms and parenthesis

yeahnah — Thu, 15 Jun 2023 04:58:24 GMT

Also, please use the Insert/Edit code sample button when adding event or SPL code content as it helps make the question clearer and prevents the web browser view doing any strange formatting to the example data.

Cheers

Re: How to remove comms and parenthesis

yuanliu — Fri, 16 Jun 2023 04:30:27 GMT

The key to extract rigidly formatted data is to not use regex and not think about symbols. All you need is extract aka kv.

| kv pairdelim="," kvdelim=":"

You should get

Elapsed	PortforlioSymbol	Records
0.0500421	2MB5	317
0.0505221	2MB6	330
0.0508121	2MB7	417

Re: How to remove comms and parenthesis

Sureshp191 — Fri, 16 Jun 2023 14:42:39 GMT

@yuanliu

Thanks for the reply, as per your suggestion I replaced rex with kv, even though I got 250 plus events but zero statics

index="myIndex" appname="myapp" msg.result.message ="*PortfolioSymbol:*" | kv pairdelim="," kvdelim=":" | stats values(Elapsed) AS Elapsed BY _time PortfolioSymbol, Records | addcoltotals count | rename count AS "Refresh Counts"

Re: How to remove comms and parenthesis

Sureshp191 — Fri, 16 Jun 2023 14:41:56 GMT

@yeahnah

Thanks for the solution, even though I see 290 plus events but zero statics.

Only when I give as below, I get 250 plus statics but with comma

| rex "PortfolioSymbol: (?<PortfolioSymbol>[^\"]*) Records: (?<Records>[^\"]*) Elapsed: (?<Elapsed>[^\"]*)"

Re: How to remove comms and parenthesis

yuanliu — Fri, 16 Jun 2023 16:30:19 GMT

Could you share some raw events that comes back from your search (without stats)? That is the key to solving a data problem, regex or not.

Re: How to remove comms and parenthesis

Sureshp191 — Fri, 16 Jun 2023 17:53:05 GMT

This is partial from raw
"Results":{"Message":"InsertIntoDatabase Ended, PortfolioSymbol: 2MB5, Records: 455, Elapsed: 0.0869895","Elapsed":0,"TraceLevel":"Information"},

Re: How to remove comms and parenthesis

yuanliu — Sat, 17 Jun 2023 05:33:11 GMT

@Sureshp191 wrote:
This is partial from raw
"Results":{"Message":"InsertIntoDatabase Ended, PortfolioSymbol: 2MB5, Records: 455, Elapsed: 0.0869895","Elapsed":0,"TraceLevel":"Information"},

This is clearly part of a JSON object. If the part "Result" is a first-level key, Splunk would have given you fields like Results.Message (which contains the information you are trying to parse), Results.Elapsed, Results.TraceLevel. Is this correct? If you indicated that the string you wanted to parse is in a named field such as Results.Message, the solution would be obvious:

| rename _raw as temp; Results.Message as _raw | kv pairdelim="," kvdelim=":" | rename _raw as Results.Messages, rename temp as _raw ``` just in case you still want _raw ```

The snippet would give you something like

Elapsed	PortfolioSymbol	Records	Results.Elapsed	Results.Message	Results.TraceLevel
0.0869895	2MB5	455	0	InsertIntoDatabase Ended, PortfolioSymbol: 2MB5, Records: 455, Elapsed: 0.0869895	Information

Here is an emulation of your snippet that you can play with and compare with real data.

| makeresults | eval _raw = "{\"Results\":{\"Message\":\"InsertIntoDatabase Ended, PortfolioSymbol: 2MB5, Records: 455, Elapsed: 0.0869895\",\"Elapsed\":0,\"TraceLevel\":\"Information\"}}" | spath ``` data emulation above ```