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?
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.
Landen99, thanks for your answer.
This works, but I'm still getting the jvmDescription in my Singlevalue display.
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.
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?
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
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)
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!
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.
A little warning, with sort, dedup, and streamstats before the first reporting command, timechart, that search will perform poorly.
When you say "stopped working", what do you mean? What does it do now?
DalJeanis,
The original was supposed to provide a Single Value in the Visualization window.
What it returns instead is the jvmDescription field.
@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
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?
@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.
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.
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