Splunk Search

Relative time and stats

Builder

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 ?

0 Karma
1 Solution

Legend

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

View solution in original post

Legend

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

View solution in original post

Builder

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.

0 Karma

Legend

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

0 Karma

Builder

I am indeed working with four different time fields.

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!