topic Re: How do you count the difference in an ongoing count for a given time period? in Splunk Search

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

stcrispan — Fri, 07 Sep 2018 15:46:17 GMT

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?

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

renjith_nair — Fri, 07 Sep 2018 16:20:30 GMT

@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

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

DalJeanis — Fri, 07 Sep 2018 16:21:26 GMT

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

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

stcrispan — Fri, 07 Sep 2018 20:31:12 GMT

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?

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

stcrispan — Fri, 07 Sep 2018 20:34:25 GMT

DalJeanis,

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

What it returns instead is the jvmDescription field.

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

renjith_nair — Sat, 08 Sep 2018 03:21:59 GMT

@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.

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

DalJeanis — Sat, 08 Sep 2018 22:53:37 GMT

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)

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

mstjohn_splunk — Tue, 11 Sep 2018 16:58:01 GMT

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!

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

stcrispan — Wed, 12 Sep 2018 21:02:23 GMT

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.

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

renjith_nair — Thu, 13 Sep 2018 03:25:40 GMT

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

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

stcrispan — Thu, 13 Sep 2018 12:37:47 GMT

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.

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

landen99 — Thu, 13 Sep 2018 13:00:21 GMT

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.

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

stcrispan — Thu, 13 Sep 2018 19:52:52 GMT

Landen99, thanks for your answer.

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

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

stcrispan — Thu, 13 Sep 2018 19:53:11 GMT

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

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

landen99 — Thu, 13 Sep 2018 21:31:03 GMT

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

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

landen99 — Thu, 13 Sep 2018 21:36:48 GMT

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.

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

stcrispan — Mon, 17 Sep 2018 15:19:17 GMT

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?