Splunk Search

| pivot and eval

anjafischer
Path Finder

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?

Tags (2)

mattness
Splunk Employee
Splunk Employee

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.

mattness
Splunk Employee
Splunk Employee

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.

0 Karma

anjafischer
Path Finder

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.. 😞

0 Karma

mattness
Splunk Employee
Splunk Employee

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.

mattness
Splunk Employee
Splunk Employee

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

anjafischer
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...