topic Re: | pivot and eval in Splunk Search

| pivot and eval

anjafischer — Tue, 03 Dec 2013 09:03:58 GMT

Hi there,

is there any way to combine table creation using an eval expression in combination with the accelerated pivot command?

What i am looking for is a query similar to this, which, unfortunately, does not work:
| pivot Test123 CICS_Root eval(sum(cputot)/sum(count)) as average splitrow _time

Writing a normal search query without making use of accelerated datamodels works just fine with the eval, but pivot searches don't and I just don't see how to make it work... 😞

Also, I thoughht that maybe i could create an eval attribute for my datamodel, but I can't for the expression above, it needs to calculate both sums over the specified time range and only THEN divide them. Any ideas?

Re: | pivot and eval

mattness — Tue, 03 Dec 2013 18:07:11 GMT

Try creating three eval attributes for your data model that are listed in this order:

An eval attribute named sumcpuot that is defined with the expression sum(cpuot).
An eval attribute named sumcount that is defined with the expression sum(count).
An eval attribute named average_splitrow_time that is defined with the expression sumcpuot/sumcount.

The order is important, as Splunk process the attributes from top to bottom.

Then you could create pivots that use that average_splitrow_time attribute in their calculations.

For more info, see the docs.

Re: | pivot and eval

anjafischer — Wed, 04 Dec 2013 07:29:05 GMT

Hi,

thanks for your suggestion. However, either it does not work or I am too stupid to make it work 😉

Within the data model definition, I clicked "add eval expression". Now, what do I type for the actual eval expression? "sum(cputot)", "eval(sum(cputot))", "eval sumcputot=sum(cputot)"?

None of these work, splunk always complains thatthe operator is not supported. Besides, "sum" is not listed as a valid eval function...

What am I doing wrong here?

Re: | pivot and eval

mattness — Wed, 04 Dec 2013 18:02:43 GMT

Yeah, you're right--sum is unsupported as an operator for eval. My mistake--I should have checked. Maybe you could show me the original search query that you're trying to duplicate? I'm not sure that the eval expression that you have in your original question would have worked either, since you use sum as well. You'd have to include stats or some other transforming command to make it work, I think.

http://docs.splunk.com/Documentation/Splunk/latest/Search/Usestatswithevalexpressionsandfunctions

Re: | pivot and eval

mattness — Wed, 04 Dec 2013 18:30:00 GMT

Ok, second try here...consider creating a new datamodel with a root search object that is defined with a search that includes:

...| stats sum(cputot) AS sumcputot, sum(count) AS sumcount

And then create an eval attribute for the object named average_splitrow_time with the definition sumcputot/sumcount. (you only need the eval expression in the Eval Expression field, you do not to include eval or the eval fieldname)

The primary problem with this solution is that you cannot accelerate data models based on search objects.

Re: | pivot and eval

anjafischer — Thu, 05 Dec 2013 07:50:19 GMT

thanks again 🙂

However, the whole reason for me to play with datamodels in the first place was to make use of the acceleration feature. It seems to me that what I want to do is just not possible with the pivot command, at least for now.. 😞

Re: | pivot and eval

mattness — Thu, 05 Dec 2013 18:26:45 GMT

Yeah...at the moment it's just not possible to pull off this particular computation in an accelerated data model. I learned yesterday that the Splunk dev team is considering putting this functionality into a future release, however...so stay tuned.