Splunk Search

How to have eval use results of accum

phoenixdigital
Builder

I have a chicken and egg issue here which I am having trouble resolving.

I have a search which returns data for each month.

[base_search] | eval monthlyCost = ((annualCost - totalPaid)/days_left_in_year) * daysInMonth | accum monthlyCost as totalPaid

Now this will not work as totalPaid doesnt exist for the first event, but subsequent ones it should have a value.

This can easily be done in Excel but I am having real trouble replicating in Splunk.

Currently the above search returns nothing for monthlyCost or totalPaid in all events.

I tried adding this before the eval but it didn't work and it always set totalPaid = 0.
| eval totalPaid = case(isnum(totalPaid),totalPaid,!isnum(totalPaid),0)

Any ideas?

Tags (2)
0 Karma
1 Solution

phoenixdigital
Builder

So it turns out this is not possible with Splunk as the results are passed from pipe through to the next pipe. I was trying to have the results from one events calculations and evals effect the results of the next event and Splunks searches just dont work this way.

I ended up using this python script and it works perfectly.

# Copyright (C) 2005-2009 Splunk Inc.  All Rights Reserved.  Version 3.0
import csv
import sys
import splunk.Intersplunk
import string
import datetime

# open logfile
f = open('/tmp/distribution-calc.log', 'w+')
f.write('Starting\n')
f.write('argv length ' + str(len(sys.argv)) + '\n')

(isgetinfo, sys.argv) = splunk.Intersplunk.isGetInfo(sys.argv)

if isgetinfo:
    splunk.Intersplunk.outputInfo(False, False, True, False, None)
    sys.exit()

    try:

        # get results from Splunk
        f.write('Getting results from Splunk\n')
    results = splunk.Intersplunk.readResults(None, None, True)
    f.write('Success\n')
    f.write('Size of resultset' + str(len(results)) + '\n')

    # zero out runningTotal
    runningTotal = 0

    # loop through all results
    for i in range(len(results)):
        monthlyCost = (float(results[i]['annualCost']) - runningTotal) / float(results[i]['daysLeftInYear']) * float(results[i]['daysInMonth'])
        runningTotal = runningTotal + monthlyCost
        results[i]['monthlyCost'] = monthlyCost
        results[i]['paidToDate'] = runningTotal

    f.close()

    splunk.Intersplunk.outputResults(results)

except Exception, e:
    splunk.Intersplunk.generateErrorResults("Unhandled exception:  %s" % (e,))

View solution in original post

0 Karma

phoenixdigital
Builder

So it turns out this is not possible with Splunk as the results are passed from pipe through to the next pipe. I was trying to have the results from one events calculations and evals effect the results of the next event and Splunks searches just dont work this way.

I ended up using this python script and it works perfectly.

# Copyright (C) 2005-2009 Splunk Inc.  All Rights Reserved.  Version 3.0
import csv
import sys
import splunk.Intersplunk
import string
import datetime

# open logfile
f = open('/tmp/distribution-calc.log', 'w+')
f.write('Starting\n')
f.write('argv length ' + str(len(sys.argv)) + '\n')

(isgetinfo, sys.argv) = splunk.Intersplunk.isGetInfo(sys.argv)

if isgetinfo:
    splunk.Intersplunk.outputInfo(False, False, True, False, None)
    sys.exit()

    try:

        # get results from Splunk
        f.write('Getting results from Splunk\n')
    results = splunk.Intersplunk.readResults(None, None, True)
    f.write('Success\n')
    f.write('Size of resultset' + str(len(results)) + '\n')

    # zero out runningTotal
    runningTotal = 0

    # loop through all results
    for i in range(len(results)):
        monthlyCost = (float(results[i]['annualCost']) - runningTotal) / float(results[i]['daysLeftInYear']) * float(results[i]['daysInMonth'])
        runningTotal = runningTotal + monthlyCost
        results[i]['monthlyCost'] = monthlyCost
        results[i]['paidToDate'] = runningTotal

    f.close()

    splunk.Intersplunk.outputResults(results)

except Exception, e:
    splunk.Intersplunk.generateErrorResults("Unhandled exception:  %s" % (e,))
0 Karma

phoenixdigital
Builder

Here is an example anyone can run which shows the ?bug? without needing my data.

| gentimes start=01/01/2013 | eval date_month = strftime(starttime, "%b") | dedup date_month

| eval daysInMonth = strftime(relative_time(starttime, "+1mon@mon-1day"), "%d") 
| eval days_left_in_year = 365 + 1 - tonumber(strftime(starttime, "%j")) 
| eval annualCost = case(date_month=="Mar",14000,date_month=="Apr",17000,1==1,15000)

| fillnull value=0 totalPaid
| eval monthlyCost = ((annualCost - totalPaid)/days_left_in_year) * daysInMonth  
| accum monthlyCost as totalPaid

totalPaid in the eval for monthlyCost is zero instead of the value that accumulates as the months go by.

the annualCost eval is just a simulation of my data in which the annual cost may change from month to month.

The bug here is noticable in the monthlyCost which just keeps going up and up and up (due to totalPaid being zero all the time)... when it should actually be stable around the $1500 mark.

So just to clarify...

  1. The accum is working and totalPaid is being calculated and output
  2. However for the eval of monthlyCost it uses totalPaid = 0 for every event even though it is increasing in value as the months tick by.

Is this a bug? It is an incredibly easy thing to do in a normal spreadsheet.

0 Karma

phoenixdigital
Builder

I spoke to soon it doesnt work.

[base_search] | fillnull value=0 totalPaid | eval monthlyCost = ((annualCost - totalPaid)/days_left_in_year) * daysInMonth | accum monthlyCost as totalPaid

It seems the eval uses totalPaid=0 every time.

Anyone got any other ideas?

I even looked at using streamstats but it appears eval'd values are not retrievable from previous events.

0 Karma

phoenixdigital
Builder

I spoke to soon it doesnt work.

[base_search] | fillnull value=0 totalPaid | eval monthlyCost = ((annualCost - totalPaid)/days_left_in_year) * daysInMonth | accum monthlyCost as totalPaid

It seems the eval uses totalPaid=0 every time.

Anyone got any other ideas?

0 Karma

dmlee
Communicator

have You tried " | fillnull value=0 totalPaid " ?

0 Karma

phoenixdigital
Builder

Nice work. Thanks for that. All works as expected now.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...