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?
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.
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.
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.. 😞
Try creating three eval attributes for your data model that are listed in this order:
sumcpuotthat is defined with the expression
sumcountthat is defined with the expression
average_splitrow_timethat is defined with the expression
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.
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.
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?