I am trying to track a set of service desk ticket status across time. The data input is a series of ticket updates that come in as changes occur. Here is a snapshot:
What I'd like to do with this is get a timechart with the status at each time point, however, I have an issue of the "blank" time events being filled in with zeros, whereas I need the last valid value instead. My naive query is:
index="jsm_issues"
| sort -_time
| dedup _time key
| timechart count(fields.status.name) by fields.status.name
Which gives me:
How can I query to get these zeros filled in with the last valid count ticket statuses?
Some things I've tried with no success:
Some filldown kludges
usenull=f on the timechart
A million other suggestions on this forum that usually involve a simpler query
Any suggestions? Thanks!
Can you explain the physical significance of "last valid count"? If you fill those zero values with any given non-zero value, where do you set the boundary? The very first zero following a previous non-zero? Why is that a valid representation of your data?
If you mean to simply connect non-zero values with a line, just set those 0 to null.
index="jsm_issues"
| dedup _time key
| timechart count by fields.status.name
| foreach *
[eval <<FIELD>> = if(<<FIELD>> > 0, <<FIELD>>, null())]
(Two pointers: When using timechart, there is no need to sort _time. Also I don't see a point of count(fields.status.name) when groupby is the field itself.
Then, in Visualization -> Format, set Null values to connect
Here is an emulation.
index=_internal sourcetype=splunkd thread_name=* earliest=-1h@h latest=-0h@h-30m
| timechart count by thread_name
| foreach *
[eval <<FIELD>> = if(<<FIELD>> > 100, sqrt(<<FIELD>>), 0)]
``` the above emulates
index="jsm_issues"
| dedup _time key
| timechart count by fields.status.name
```
Without setting 0 to null:
Set 0 to null without connecting dots
Connect the dots
Can you explain the physical significance of "last valid count"? If you fill those zero values with any given non-zero value, where do you set the boundary? The very first zero following a previous non-zero? Why is that a valid representation of your data?
If you mean to simply connect non-zero values with a line, just set those 0 to null.
index="jsm_issues"
| dedup _time key
| timechart count by fields.status.name
| foreach *
[eval <<FIELD>> = if(<<FIELD>> > 0, <<FIELD>>, null())]
(Two pointers: When using timechart, there is no need to sort _time. Also I don't see a point of count(fields.status.name) when groupby is the field itself.
Then, in Visualization -> Format, set Null values to connect
Here is an emulation.
index=_internal sourcetype=splunkd thread_name=* earliest=-1h@h latest=-0h@h-30m
| timechart count by thread_name
| foreach *
[eval <<FIELD>> = if(<<FIELD>> > 100, sqrt(<<FIELD>>), 0)]
``` the above emulates
index="jsm_issues"
| dedup _time key
| timechart count by fields.status.name
```
Without setting 0 to null:
Set 0 to null without connecting dots
Connect the dots
A lot of that query wasn't cleaned up from previous exploration queries, but thanks for the response. It looks like your suggestion is almost working for me except that the statement errors on the columns that are multi-worded
| foreach * [eval <<FIELD>> = if(<<FIELD>> > 0, <<FIELD>>, null())]
I was able to get it to work with this final query:
index="jsm_issues"
| dedup _time key
| timechart count by fields.status.name
| foreach *
[eval <<FIELD>> = if('<<FIELD>>' > 0, '<<FIELD>>', null())]
| filldown *
Thank you so much for your answer.