Splunk Search
Highlighted

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

Path Finder

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="TOTALCHWLOAD" | stats sum(Value) as TotalLoad | append [ search sourcetype="rtdata" ID2="TOTALCHKW" | stats sum(Value) as TotalKW ] \

alt text

I'm stuck. How do I evaluate the = TotalKW / TotalLoad? Pls. advise. Thanks

Tags (2)
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

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

Influencer

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 TotalLoad and not a TotalKW 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

0 Karma
Highlighted

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

Path Finder

Hello,
Thanks for all your prompt help. My colleague has helped me. We've found the answer:
sourcetype="rtdata" Category1="CLC" ID2="TOTALCHWLOAD" | stats sum(Value) as TotalLoad | appendcols [ search Category1="CLC" sourcetype="rtdata" ID2="TOTALCHKW" | stats sum(Value) as TotalKW ] | eval TotalEff=TotalKW/TotalLoad | eval ID="TotalCHEff" | table ID, TotalKW, TotalLoad, TotalEff

0 Karma
Highlighted

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

Influencer

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)

0 Karma
Highlighted

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

Path Finder

Hello Acharlieh,
Noted.

0 Karma