How get a stats count and split a string to get a total count?

[Sureshp191]

Please help me, below is my query 
index="myIndex" app_name="myappName"  My.Message = "*failed to retrieve the workOrder*"

This returns say 6 splunk events,  with following message:

Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12666 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12666 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12771 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12888 failed to retrieve the workOrder. Error DataBase mapping incorrect.

In the above-mentioned splunk query,
1) How I can get Total count using the stats to show visually
2) How to get the Order (eg 12345) and total count of a particular order? 
3) How to get a table like below
"Order#" # of times failed"

Thanks

[yeahnah]

Hi @Sureshp191 

You could something like this...

index="myIndex" app_name="myappName"  My.Message="failed to retrieve the workOrder"
| rex "(?<Order>\d+)"  ``` the Order ID may already be an extracted field so this may not be needed. If necessary, change the stats BY clause etc to match the extracted order ID field name ```
| stats count BY Order
| addcoltotals count
| rename Order AS "Order#", count AS "# of times failed"

 Hope that helps

[Sureshp191]

Hi @yeahnah 

Thanks for the solution but it didnt work.  My.Message has various strings  but the ones I am looking for are "*failed to retrieve the workOrder*", 
Please help me to understand what  rex "(?<Order>\d+)" does on the 

Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.

Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.
I am looking for "Order#" , "# of times failed" in this case it should show 

Order#    # of times failed

12345            2

[yeahnah]

Hi @Sureshp191 

Based on what you have provided my solution would work.  Here is a run anywhere example showing it working...

| makeresults | eval event="Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 12666 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 12345 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 12666 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 12771 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 12888 failed to retrieve the workOrder. Error DataBase mapping incorrect."
| eval event=split(event, "
")
| mvexpand event
  ``` the above just creates dummy events to test the following code ```
| rex field=event "(?<Order>\d+)"
| stats count BY Order
| addcoltotals count
| rename Order AS "Order#", count AS "# of times failed"

 If the real raw events look different to the ones above then please provide a valid example.

[Sureshp191]

@yeahnah 

thanks when I ran the below one it worked, Thank you. 

The one I am working is slight different. The order# is Alphanumeric like 1AB5
I am querying 
index="myIndex" app_name="myappName"  My.Message = "*failed to retrieve the workOrder*"

the My.Message returns many strings dynamically and I am only looking for the string that contains "Order <Alphanumeric>  failed to retrieve the workOrder. Error DataBase mapping incorrect." in this scenario, what should be the regex, please help.

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 2MB5 failed to retrieve the workOrder. Error DataBase mapping incorrect.

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

[yeahnah]

Yes, the rex syntax needs to change to match alphanumerics.  Here's some updated example code.

| makeresults 
| eval event="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 2MB5 failed to retrieve the workOrder. Error DataBase mapping incorrect.
Order 1MB1 failed to retrieve the workOrder. Error DataBase mapping incorrect."
| eval event=split(event, "
")
| mvexpand event
| rename event AS _raw
  ``` the above just creates dummy events to test the following code ```
| rex "Order (?<Order>[^\s]+)"
| stats count BY Order
| addcoltotals count
| rename Order AS "Order#", count AS "# of times failed"

 In this case, the rex will match the ID value after the word "Order ", up to the next whitespace.

[yeahnah · Accepted solution]

Also, the rex command is using a regex command to extract the order ID from the _raw event field and naming the field Order.  The Order ID value can then be used by the stats command to group the results.  Here the Splunk docs on rex command.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Rex

It is very useful but does require understanding how to use regular expression, or regex, syntax.

Hope that helps