How to have eval use results of accum

[phoenixdigital]

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?

[phoenixdigital · Accepted solution]

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,))

[phoenixdigital]

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.

[phoenixdigital]

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.

[phoenixdigital]

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?

[dmlee]

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

[phoenixdigital]

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