Splunk Search

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

Sureshp191
Explorer

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

Labels (2)
Tags (2)
0 Karma
1 Solution

yeahnah
Motivator

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

View solution in original post

Tags (1)
0 Karma

yeahnah
Motivator

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

0 Karma

Sureshp191
Explorer

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

 

 

Tags (1)
0 Karma

yeahnah
Motivator

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.

Tags (1)
0 Karma

Sureshp191
Explorer

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

0 Karma

yeahnah
Motivator

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.

Tags (1)
0 Karma

yeahnah
Motivator

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

Tags (1)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...