How To Get = sum(set A events) / sum (set B events).

[htkwan]

Hello,
I'm new to splunk. I need to evaluate result = sum(set A events) / sum (set B events). I've tried:

sourcetype="rtdata" Category1="CLC" ID2="TOTAL_CHW_LOAD" | stats sum(Value) as Total_Load | append [ search sourcetype="rtdata" ID2="TOTAL_CH_KW" | stats sum(Value) as Total_KW ] \

I'm stuck. How do I evaluate the = Total_KW / Total_Load? Pls. advise. Thanks

[htkwan]

Hello,
Thanks for all your prompt help. My colleague has helped me. We've found the answer:
sourcetype="rtdata" Category1="CLC" ID2="TOTAL_CHW_LOAD" | stats sum(Value) as Total_Load | appendcols [ search Category1="CLC" sourcetype="rtdata" ID2="TOTAL_CH_KW" | stats sum(Value) as Total_KW ] | eval Total_Eff=Total_KW/Total_Load | eval ID="Total_CH_Eff" | table ID, Total_KW, Total_Load, Total_Eff

[acharlieh]

I converted your comment to an answer as you are providing an answer to your own question. Appendcols is indeed another potential solution to the problem, the end goal of course having both numerator & denominator on the same result so you can use eval to calculate the ratio.
Make sure to accept the answer that solved your issue (even if it's your own) to mark your question as solved. (Also if you felt any other's posts were particularly helpful in getting you to your solution here or elsewhere on the site, make sure to up vote them as well to show appreciation for good content)

[htkwan]

Hello Acharlieh,
Noted.

[acharlieh]

So you currently have 2 results each with a single value, you could take your existing search and append something like

... | stats first(*) as * | eval result = Total_KW / Total_Load

Here we use stats to get the first value for each field across all 2 results. Since how your search is constructed, the first value is guaranteed to have a Total_Load and not a Total_KW field and the second result produced by the append is exactly the opposite this works. Now with both fields on a single event we can use eval to calculate the ratio of the two numbers easily.

You can do similar without append like so:

sourcetype="rtdata" (Category1="CLC" ID2="TOTAL_CHW_LOAD") OR ID2="TOTAL_CH_KW" Value=* | eval x=1 | chart sum(Value) by x, ID2 | eval result = TOTAL_CH_KW / TOTAL_CHW_LOAD

Here we pull back all events in our base search, and create a dummy field with a common value. Using chart we get the statistics onto a single line (by the dummy field) and the other fields being named for the values of ID2. Again with a single result the ratio is simple

[richgalloway]

Try this

sourcetype="rtdata" Category1="CLC" ID2="TOTAL_CHW_LOAD" | stats sum(Value) as Total_Load | append [ search sourcetype="rtdata" ID2="TOTAL_CH_KW" | stats sum(Value) as Total_KW ] | eval x = Total_KW / Total_Load