topic Re: Relative time and stats in Splunk Search

Relative time and stats

asarolkar — Thu, 13 Dec 2012 19:24:38 GMT

I have a somewhat complicated question about how the now() method applies in the context of stats.

I have a splunk search to weed out accounts with no transactions in the last 24 hours
I am using now() to determine a cutoff date for weeding out those accounts.
lastTransactionProcessed is provided as a field by sourcetype="banklog" and is always populated

sourcetype="banklog" | eval cutOffTime=relative_time(now(),"-48h") | where lastTransactionProcessed>cutOffTime |  dedup accountNum | table accountNum

The above query works as expected which is great.

The problem is that I am expected to create a trend (using stats) for the last 4 days for this search and when I get now() involved with stats (see below), things dont work out as I hoped.

sourcetype="banklog" | eval cutOffTime=relative_time(now(),"-48h") | where lastTransactionProcessed>cutOffTime | bucket span=1d _time | dedup accountNum _time | eval target=15 | eventstats dc(accountNum) as "successCount" | eval failures=target-successCount | stats first(target) as Target, c(successCount) as Success by _time | eval failures=15-Success

I am interested in simply plotting success versus failure here and I schedule the time interval for this search for -4d@d to now.

I only get results for one day (which is the last 24 hours).

Any clues so as to what I might be doing wrong here ?

Re: Relative time and stats

lguinn2 — Thu, 13 Dec 2012 21:29:49 GMT

You are working with 4 different time fields, yes?

now() is the time that this search started

cutOffTime is 48 hours before this search started

lastTransactionProcessed is found in the event

_time is the timestamp of the event

Your where command is cutting off a number of events.
I might have done it like this instead

sourcetype=banklog
| eval accountStatus = if(lastTransactionProcessed > relative_time(_time,"-48h"),"Active","Inactive")
| bucket _time span=1d
| stats count by accountNum accountStatus _time

My stats command is probably wrong, but I am having difficulty figuring out what target means, as well as the definition of success or failure. You could also

sourcetype=banklog
| eval accountStatus = if(lastTransactionProcessed > relative_time(_time,"-48h"),"Active","Inactive")
| bucket _time span=1d
| dedup accountNum accountStatus
| stats count by accountStatus _time

Re: Relative time and stats

asarolkar — Thu, 13 Dec 2012 21:35:31 GMT

I am indeed working with four different time fields.

The Target is a constant value (15 = represents the total number of transactions that occur).

Re: Relative time and stats

lguinn2 — Thu, 13 Dec 2012 21:37:10 GMT

How can there be only 15 transactions total? This is very unclear.

Re: Relative time and stats

asarolkar — Thu, 13 Dec 2012 22:16:32 GMT

So the Target number of transactions is 15 - we want to see that as a steady constant in the graph, the rest should be active and inactive.