How to remove comms and parenthesis?

[Sureshp191]

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

[yeahnah]

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.

[Sureshp191]

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

[yuanliu]

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

[Sureshp191]

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

[yuanliu]

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.

[Sureshp191]

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

[yuanliu]

---

@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 ```

[yeahnah]

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

[Sureshp191]

@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>[^\"]*)"

[yeahnah]

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