Splunk Search

How do you count the difference in an ongoing count for a given time period?

stcrispan
Communicator

I have a JMX search going on which tracks orders placed every 30 seconds.

index=dot_jmx mbean_property_destinationName=RTGOrderProcessed |  stats values(Messages_Enqueue) AS Orders by jvmDescription, mbean_property_destinationName 

The search produces and output like this:

Server  Name        Orders
SRV1    Processed   11238 
SRV1    Processed   11239 
SRV1    Processed   11240 
SRV1    Processed   11241 
SRV1    Processed   11242 
SRV1    Processed   11243 
SRV1    Processed   11244 
SRV1    Processed   11246 
SRV1    Processed   11247 
SRV1    Processed   11248 
SRV1    Processed   11249 
SRV1    Processed   11250 

This goes in a dashboard with a picker, and for the time period provided by the picker, there should be a way to output a visualization which counts the difference between the earliest count number and the latest count number — the goal is to display the total new orders for the time period requested. (So - displays the count for the last 15 minutes or the last week.)

The guy before me had it like this:

index=dot_jmx destinationName=RTGOrderProcessed |  stats values(Messages_Enqueue) AS Orders by jvmDescription,destinationName| delta Orders as Orders p=1 | search Orders < 100| stats sum(Orders) As "Total Process Orders Today"

...but for some reason, when I added the time picker, that stopped working...but the time picker is now a requirement, and the guy is gone.

Any suggestions?

0 Karma

landen99
Motivator

It most likely stopped working due to something other than the time picker addition. I would have to webex with you to look more closely for the issue which stopped the search from working. I recommend checking that the data is still coming in, that the fields are still being extracted, and that there are no time issues with the search.

The search query provided looks like it will do the job, but I would optimize it a bit to make stats do more of the work:

index=dot_jmx destinationName=RTGOrderProcessed | bucket _time span=1d | stats range(Messages_Enqueue) AS "Total Process Orders Today" by jvmDescription destinationName _time

The fact that he screened out large orders indicates a potential worry about catching a period when the Messages_Enqueue value resets. If that does not happen at midnight, consideration will need to be made for that.

0 Karma

stcrispan
Communicator

Landen99, thanks for your answer.

This works, but I'm still getting the jvmDescription in my Singlevalue display.

0 Karma

landen99
Motivator

It sounds like you might want a slightly different format for your single value:
Assuming that you are only searching "today", _time can be removed.

index=dot_activemq_jmx mbean_property_destinationName=RTGOrder | stats range(Messages_Enqueue) AS "Total Process Orders Today" by jvmDescription

Is there more than one value of jvmDescription? If not, then you can get rid of that too in the stats command.

0 Karma

stcrispan
Communicator

Interesting...I only recently added a time picker to the dashboard, will removing the _time still allow the search to use the time specified by the time picker?

0 Karma

stcrispan
Communicator
index=dot_activemq_jmx mbean_property_destinationName=RTGOrder | bucket _time span=1d | stats range(Messages_Enqueue) AS "Total Process Orders Today" by jvmDescription mbean_property_destinationName _time
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Okay, the query doesn't make a whole lot of sense to me, and I'm usually pretty good at guessing this stuff.

Based on the assumption that "Messages_Enqueue" is the value that is showing up in your "Order" column - even though your code will do nothing like that...then you can do something like this...

 index=dot_jmx destinationName=RTGOrderProcessed
 | eventstats min(Messages_Enqueue) as MinInRange
 | eval NetDiff=Messages_Enqueue - MinInRange
 | timechart span=15m max(NetDiff) as CumulativeTotal

So via the above, you can timechart the value of NetDiff.


However, I notice some gaps in your numbers, and the above ignores those missing numbers. So, you might need to do something like this...

 index=dot_jmx destinationName=RTGOrderProcessed
 | fields _time Messages_Enqueue
 | sort 0 _time  
 | dedup Messages_Enqueue
 | streamstats count as CumulativeTotal
 | timechart span=15m max(CumulativeTotal) as CumulativeTotal

Also, I notice in the prior guy's code that he may be splitting by something or other, so in that case, you might need to do something like this...

 index=dot_jmx destinationName=RTGOrderProcessed
 | fields _time SomeSplitByField Messages_Enqueue
 | sort 0 _time  
 | dedup SomeSplitByField Messages_Enqueue
 | streamstats count as CumulativeTotal by SomeSplitByField
 | timechart span=15m max(CumulativeTotal) as CumulativeTotal by SomeSplitByField

Obviously, you'll need to replace SomeSplitByField with your actual split-by field(s)

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @stcrispan ,

were you able to check out the above answer? Did it work for you? If so, would you mind approving it so others can learn from it? Or, if it didn't work, would you mind updating us on your problem?

Thanks!

0 Karma

stcrispan
Communicator

I'm still muddling my way through these sophisticated queries you've provided. I can say that they're not working like I was hoping they would work. 😞 The one where someone took the series of numbers, picked the first number, then picked the last number (by time), and then did an eval of the difference between seemed so promising...but didn't actually work. I'm not sure any more if it's my query or if it's my dataset....but the dataset does provide a list of numbers, I just need to get the delta of the change between first and last.

0 Karma

landen99
Motivator

A little warning, with sort, dedup, and streamstats before the first reporting command, timechart, that search will perform poorly.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

When you say "stopped working", what do you mean? What does it do now?

0 Karma

stcrispan
Communicator

DalJeanis,

The original was supposed to provide a Single Value in the Visualization window.

What it returns instead is the jvmDescription field.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@stcrispan,
Your requirement and what the guy before you have done is not matching :-). However, as per your statement, difference between the earliest count number and the latest count number , you could use eventstats

 index=dot_jmx mbean_property_destinationName=RTGOrderProcessed |  stats values(Messages_Enqueue) AS Orders by jvmDescription, mbean_property_destinationName |eventstats earliest(Orders) as earliest,latest(Orders) as latest by jvmDescription, mbean_property_destinationName|eval diff=latest-earliest 

Please lets know whats the changes you are looking for

Happy Splunking!
0 Karma

stcrispan
Communicator

renjith.nair, thank you for the fast response!

Unfortunately, this is not working for me. As I understand it, this was supposed to use the expression "eval diff" to take the difference between the earliest order and the latest order and then return that result?

When I return the result in Visualization as a Single Value - I get back the jvmDescription.

If I strip the command down, just to get the list of order counts, then run the stats command on it, I get the list of order counts in my Visualization as my Single Value.

index=dot_jmx mbean_property_destinationName=RTGOrderProcessed |  stats values(Messages_Enqueue) as Orders | eventstats earliest(Orders) as earliest,latest(Orders) as latest |eval diff=latest-earliest

returns

11413,11414,11415,11417,11418,11419,11420,11421,11422,11423,11424,11425,11426,11427,11428,11429,11431

Thoughts?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@stcrispan, so you dont need earliest and latest but just the delta between the last two values.

After adding the time picker, are you getting the value for the below search ?

 index=dot_jmx destinationName=RTGOrderProcessed |  stats values(Messages_Enqueue) AS Orders by jvmDescription,destinationName| delta Orders as Orders p=1 

If yes, then the filter search Orders < 100 is removing your results because for a larger time period the delta could be more.

Happy Splunking!

stcrispan
Communicator

I actually do need the difference between the earliest and latest.

This query -

index=dot_activemq_jmx mbean_property_destinationName=RTGOrder |  stats values(Messages_Enqueue) AS Orders by jvmDescription, mbean_property_destinationName |eventstats earliest(Orders) as earliest,latest(Orders) as latest by jvmDescription, mbean_property_destinationName|eval diff=latest-earliest 

Does a listing of orders, incrementing, but does not populate the Single Value with that count, it populates it with the jvmDescription field.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Okie, try moving the count field as first field . for eg. yur search | fields count, other fields. If you are looking for a trend , then it normally against a time series. see : http://docs.splunk.com/Documentation/SplunkCloud/7.0.3/Viz/SingleValueGenerate

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...