problem computing 2 sums in one search for 1 table row

[a356115]

I have the following multiple events:

date=08/07/11 time=14:58:29 app=surveyStartCall ct=1 q1=8 q2=5 q3=5 q4=5

date=08/07/11 time=14:58:30 app=surveyStartCall ct=2 q1=1 q2=5 q3=5 q4=5
.......

and want my search to return 2 values depending on the ct variable (ct can only equal 1 or 2)

value1 = average of (q1+q2+q3+q4) for all events where ct=1 and

value2 = average of (q1+q2+q3+q4) for all events where ct=2

However, I cant seem to get this returned in 1 table row (has to be one row). I tried eval with a by clause and tried append search but they all give 2 table rows.
A 'where' clause will not work as it deletes all events for which the where is false so I cant get my second value.

Can anyone help please ?

[lguinn2]

What about this?

yoursearchhere |
eval qSum=q1+q2+q3+q4 |
eval qSum1=if (ct==1, qSum, 0) |
eval qSum2=if (ct==2, qSum, 0) |
stats count(eval(ct==1)) as t1 count(eval(ct==2)) as t2 sum(qSum1) as s1 sum(qSum2) as s2 |
eval AvgCt1 = s1 / t1 | eval AvgCt2 = s2 / t2 |
fields AvgCt1 AvgCt2

[a356115]

acdevlin.
That's excellent !! Thanks very much for your persistence. I've learned a lot from this and it really helps me with my work.

I wanted it in this format as I'm displaying the information as a data table to provide managers a one-line, high level overview of the stats. Basically, it allows me display more information in one table rather than having to have lots of different tables (one per call type). I think its a tricky enough solution for what 'should be' a fairly straight forward query but obviously by lack of splunk experience doesn't help
Thanks again !!

[acdevlin · Accepted solution]

I think your question is a bit ambiguous; providing an example of what your table should look like and/or your current table-generating statement would really help clarify things.

Based on what I think you're asking (and my apologies if I am wrong), you should look into the "transpose" command: http://docs.splunk.com/Documentation/Splunk/4.2.3/SearchReference/Transpose

The sample query I built is as follows.

... | eventstats count as ctCount, sum(q1) as sumq1, sum(q2) as sumq2, sum(q3) as sumq3, sum(q4) as sumq4 by ct | eval myValue=sumq1+sumq2+sumq3+sumq4 | eval myAvg = myValue/ctCount |  stats values(myAvg) by ct | transpose

Does this help?

[acdevlin]

How about if we do separate calculations where ct==1 and ct==2?

... | eventstats count as ctCount, sum(q1) as sumq1, sum(q2) as sumq2, sum(q3) as sumq3, sum(q4) as sumq4 by ct | eval myValue1=if(ct==1, sumq1+sumq2+sumq3+sumq4, null) | eval myValue2=if(ct==2, sumq1+sumq2+sumq3+sumq4, null) | eval myAvg1 = myValue1/ctCount |  eval myAvg2 = myValue2/ctCount | stats values(myAvg1), values(myAvg2)

This does beg the question though: Why do you want data in this format? If you're trying to append this average to another table or chart, there are more efficient methods we could use.

[a356115]

Hi acdevlin.
The sample query you built wont work because I need a one line data table showing the 2 average values. I would have thought something like this should be simple to do. Its just 2 average value computations which need to use a where caluse.

So output data table should look like:
value1 = average of (summed values where ct=1) and value2 = average of (summed values where ct=2).

Maybe its just not possible...? (I am new to Splunk)

[acdevlin]

Yes, "where" is definitely not the command to use here.

Could you elaborate on why it's "still not working"? Like why the query's output is not what you are looking for and what should be changed? And, again, could you provide an example of the output you do want?

The more specific you are, the more someone can help you out 🙂

[a356115]

Still not working.

I need a search query that will return me 2 values in one row on a table. These values should be the average of 4 numbers summed over all events. An input event contains 5 numbers. The first number in the event, can be a 1 or a 2.
Using all the input events, the first value is the average of the last 4 numbers when the first value is 1 and the second number is the average of the last 4 numbers when the first value in the event line is a 2.

The problem is when I use a 'where' clause it automatically deletes all event that are false and so I cant compute my 2nd value.

[Drainy]

Maybe something like...

 host=blah ct=* | eval value = qt1+qt2+qt3+qt4

Does that work? I have made a few assumptions on your stuff. Does it have to be value1 and value2 exclusively?
As the qt values appear to always be the same you are always calculating the same sum.
If not you could always throw a where;

where ct=1 | eval value1 = qt1+... | where ct=2 | eval value2 = qt1...

Warning, my splunk system isn't quite there atm so this is provided without much testing. Let me know if its not quite what you're after (or my syntax is broken and I've missed it)

[a356115]

Thanks Draineh.
I tried to simplify my problem to make it clearer but probably confused things.

What I actually need to get is 2 averages. 1 average (q1+q2+q3+q4) where ct=1 and 1 where ct=2 and display these 2 average values in one table row.
I dont know how to use the where clause to do this...

I would be grateful for any help.