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 ] \
I'm stuck. How do I evaluate the = TotalKW / TotalLoad? Pls. advise. Thanks
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
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
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
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)