topic Re: How to use eval with Pivot? in Splunk Search

How to use eval with Pivot?

emoyoun — Fri, 18 Nov 2016 14:33:19 GMT

I need to generate a calculated field in Pivot with no luck.

I tried this:

| pivot Statistics HTTP sum(eval(count/3600))

I get this error:

The object 'HTTP' has no field 'eval(count/3600'.

Any ideas pls??

Re: How to use eval with Pivot?

pgreer_splunk — Fri, 18 Nov 2016 19:03:24 GMT

I believe it should be:

| pivot Statistics HTTP sum(HTTP) as HTTPSum | eval HTTPSum = 'HTTPSum'/3600

If that's what you're angling for....

Re: How to use eval with Pivot?

emoyoun — Fri, 18 Nov 2016 19:55:29 GMT

I need to sum the count over a Period of one hour and find the Transaction per second by dividing the sum(count) by 3600. Following your suggestion, I tried this bu it didn't work. Still getting errors:

| pivot Statistics HTTP sum(count) as HTTPSum | eval HTTPSum = 'HTTPSum'/3600 SPLITROW _time AS _time PERIOD hour

Re: How to use eval with Pivot?

rjthibod — Sun, 20 Nov 2016 10:06:42 GMT

Try this

| pivot Statistics HTTP sum(count) as HTTPSum SPLITROW _time AS _time PERIOD hour | eval HTTPSum = HTTPSum/3600

With pivot commands, you can only use SPLITROW and other pivot related options in the pipeline with the pivot command.

Re: How to use eval with Pivot?

emoyoun — Sun, 20 Nov 2016 16:20:41 GMT

Thanks rjthibod,

I tried your suggestion but pivot still doesn't like it. I get this error 😞 :
Error in 'PivotCell': The object 'HTTP' has no field '|'.

Any work around would be appreciated.

Re: How to use eval with Pivot?

Melstrathdee — Sun, 20 Nov 2016 20:04:53 GMT

Can you show us a sample of your data please. Thanks

Re: How to use eval with Pivot?

emoyoun — Mon, 21 Nov 2016 14:31:17 GMT

Hi Melstrathdee,

Here is an example of my events:
I need to sum the count over a Period of one hour and find the Transaction per second by dividing the sum(count) by 3600

Start Time In MS=1479738420000,Start Time Local=Mon Nov 21 14:27:00 GMT+00:00 2016,End Time In MS=1479738480000,End Time Local=Mon Nov 21 14:28:00 GMT+00:00 2016,Site=W0,Group=HTTP,SourceIP=127.0.0.1,Status=200,URL=http://127.0.0.1:8080/xmlapi/invoke,Count=24,Rate=0.4,Average Latency=1.29166

Re: How to use eval with Pivot?

Melstrathdee — Tue, 22 Nov 2016 00:29:25 GMT

Place the eval statement that calculates the HTTPSum in your search before you pipe the pivot. This should then make the field available. Hope this helps 🙂

Re: How to use eval with Pivot?

emoyoun — Tue, 22 Nov 2016 00:59:40 GMT

Unfortunately I already tried this,
eval HTTPSum = count/3600| pivot Statistics HTTP sum(HTTPSum) as "TPS" SPLITROW _time AS _time PERIOD hour

I'm still getting the error:
Error in 'PivotCell': The object 'HTTP' has no field 'HTTPSum'.

Re: How to use eval with Pivot?

Melstrathdee — Tue, 29 Sep 2020 11:51:19 GMT

When you are in search can you pipe the HTTPSum and HTTP into a table to show the value?
something like this?
source="tutorialdata.zip:*" sourcetype="vendor_sales/vendor_sales"
| eval HTTPSum = count/3600| stats count by HTTPSum Code

Re: How to use eval with Pivot?

emoyoun — Tue, 22 Nov 2016 01:08:21 GMT

I works in search, My problem is with Pivot!

Re: How to use eval with Pivot?

emoyoun — Tue, 22 Nov 2016 01:17:03 GMT

it seems that the new calculated attribute needs to be defined in the HTTP object to work with Pivot? I'm looking for a workaround!