Splunk Search

Relative time and stats

asarolkar
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

lguinn2
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

lguinn2
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

asarolkar
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

lguinn2
Legend

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

0 Karma

asarolkar
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...