topic How get a total count based on the substring value? in Splunk Search

How get a total count based on the substring value?

Sureshp191 — Wed, 14 Jun 2023 23:47:06 GMT

Below is the splunk query, (My.Message has many various types of messages but the below one is what I wanted)

index="myIndex" app_name="myappName" My.Message = "*symbolName:*"

When I run the above query, I get the below results:

myappstatus got Created, symbolName: AAPL ElapsedTime: 0.0002009

myappstatus got Ended, symbolName: GOOGL ElapsedTime: 0.0005339

myappstatus got Created, symbolName: AAPL ElapsedTime: 0.0005339

Please help on the following:
1) How to get the Total count of the query (Visualization) only for My.Message = "*symbolName:*"
2) How to split the string "myappstatus got Created, symbolName: AAPL ElapsedTime: 0.0002009"
3) How to create a table for "symbolName", "Total Count", "ElapsedTime"

(for example, symbolName: AAPL, Total Count = 2 and ElapsedTime = 0.0007348 (0.0002009 + 0.0005339)

Thanks

Re: How get a total count based on the substring value

yeahnah — Wed, 14 Jun 2023 01:13:51 GMT

Hi @Sureshp191

Based on the example events provided, here's some demonstration run anywhere code showing a method to do what you want...

| makeresults | eval raw="myappstatus got Created, symbolName: AAPL ElapsedTime: 0.0002009 myappstatus got Ended, symbolName: GOOGL ElapsedTime: 0.0005339 myappstatus got Created, symbolName: AAPL ElapsedTime: 0.0005339" | eval raw=split(raw, " ") | mvexpand raw | rename raw AS _raw ``` the above is just creating dummy events to test the following SPL code with ``` | rex "symbolName: (?<symbolName>\w+) ElapsedTime: (?<ElapsedTime>[^\s]+)" | eventstats count AS "Total Count" list(ElapsedTime) AS listElapsedTime sum(ElapsedTime) AS "Total ElapsedTime" BY symbolName | table symbolName "Total Count" "Total ElapsedTime"

Hope that helps

Re: How get a total count based on the substring value

Sureshp191 — Wed, 14 Jun 2023 04:22:49 GMT

Thanks, I got the output but symbolName AAPL is duplicated.

Re: How get a total count based on the substring value

yeahnah — Wed, 14 Jun 2023 05:07:16 GMT

Hi @Sureshp191

OK, to dedup results do it this way...

index="myIndex" app_name="myappName" My.Message = "*symbolName:*" | rex "symbolName: (?<symbolName>\w+) ElapsedTime: (?<ElapsedTime>[^\s]+)" | stats count AS "Total Count" sum(ElapsedTime) AS "Total ElapsedTime" BY symbolName