topic Re: How to get Two Sum in the same query in Splunk Search

How to get Two Sum in the same query

samsplunkd — Mon, 28 Sep 2020 13:04:08 GMT

Hi,

My dataset is like below:

01/05/2013 23:58:00 -0800, search_name=foo, search_now=1357459200.000, info_min_time=1357459080.000, info_max_time=1357459200.000, info_search_time=1357459425.558, Count=1, apiName="footest", appName="bartest", clRT=70, status=401, svRT=68

01/05/2013 23:58:00 -0800, search_name=foo, search_now=1357459200.000, info_min_time=1357459080.000, info_max_time=1357459200.000, info_search_time=1357459425.558, Count=10, apiName="footest1", appName="bartest1", clRT=50, status=200, svRT=52

Here I would like to get a sum of field "Count" for all the Txns with "status" between 200-400(say Total_one ) and in the same query I also want to get total "Count" no matter what the status is (say, Total_two). And eventually I want to get (Total_one/Total_two).
here "status" has multiple values between 200-500. Above are just for sample data.

Can you please help?

Thanks

Re: How to get Two Sum in the same query

martin_mueller — Wed, 09 Jan 2013 08:42:58 GMT

I'm not sure what you mean by status having multiple values (your examples only list one per event), but in general you're probably thinking of something like this:

...  | stats count by status | eventstats sum(count) as total | eval ratio = count / total

This counts events for every status individually, then adds the total to every line and computes the ratio between each status and the total.

Re: How to get Two Sum in the same query

samsplunkd — Wed, 09 Jan 2013 09:02:29 GMT

Thanks for your reply. Yes status has only one value per event but different values in different events..
Above query would not calculate the sum of field "Count" Also I need to get only one value as a result which is ratio only for status between 200-400 so something like (sum of "Count" for status between 200-400)/Total sum(Count).
I tried something like this but couldn't figure out a way to get only one value as explained above.

| stats sum(Count) AS Total by responseCode| eventstats sum(Total) as TotalTxns| eval ratio=Total/TotalTxns

Re: How to get Two Sum in the same query

martin_mueller — Wed, 09 Jan 2013 09:10:53 GMT

Ah, I guess I missed that you already have sort-of aggregated values in your event. How about something like this:

... | stats sum(Count) as s by status  | eventstats sum(s) as total | where status >= 200 AND status <= 400 | eventstats sum(s) as subtotal | head 1 | eval ratio = subtotal / total | fields + ratio

This first calculates a total of all sums, then kicks out the rows for status<200 and status>400, then calculates a total of those remaining, and evals the ratio.

Re: How to get Two Sum in the same query

samsplunkd — Wed, 09 Jan 2013 09:37:33 GMT

Awesome. worked.
Thanks for the quick help.