Splunk Search

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

htkwan
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="TOTAL_CHW_LOAD" | stats sum(Value) as Total_Load | append [ search sourcetype="rtdata" ID2="TOTAL_CH_KW" | stats sum(Value) as Total_KW ] \

alt text

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

Tags (2)
0 Karma

htkwan
Path Finder

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

0 Karma

acharlieh
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

htkwan
Path Finder

Hello Acharlieh,
Noted.

0 Karma

acharlieh
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 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

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...