How do I create daily sparklines that start with the beginning of the day?

[MonkeyK]

I asked a previous question about how to get daily sparklines and learned a bit there. But discovered that the resulting sparklines on each row represent the entire data set, not just the (daily) data set for the row. What do I need to to so that the sparkline only represents the day's data?
Below are screenshots of what I see.

The first one shows the full dataset with a sparkline spanning a week
The second one shows the same dataset, with daily summaries. but the sparkline for each day includes blank space for the other days. This makes visual comparisons of trends more difficult. It also increases the amount of data represented by the sparkline so that over a larger dataset, the last data gets chopped off.

Query for the first screenshot:

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic 
 WHERE All_Traffic.dest_ip=134.170.30.203 
 BY _time span=1m | eval Day=strftime(_time,"%F")
 | chart sparkline(sum(count),2h) as countTrend sum(count) as Count min(_time) as First max(_time) as Last
 | eval First=strftime(First,"%m/%d/%y %H:%M") 
 | eval Last=strftime(Last,"%m/%d/%y %H:%M")

Query for the second screenshot

    | tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic 
     WHERE All_Traffic.dest_ip=134.170.30.203 
     BY _time span=1m | eval Day=strftime(_time,"%F")
     | chart sparkline(sum(count),2h) as countTrend sum(count) as Count min(_time) as First max(_time) as Last by Day  
    | eval First=strftime(First,"%m/%d/%y %H:%M") 
    | eval Last=strftime(Last,"%m/%d/%y %H:%M")

[MonkeyK · Accepted solution]

I have submitted an enhancement request to make sparklines in charts by time honor their time buckets (or at least that there be a way to do so)
The request references this question, so hopefully if something can be done, we will see an update.

[dlamb_splunk]

Your sparkline is actually limited to a 2 hour span in both searches: sparkline(sum(count),2h)

Have you tried adjusting that? Or just removing the span? I believe it defaults to 24 hours.

[MonkeyK]

it's too bad that sparkline cannot let me define the x-axis because comparing the graphs in chronological order provides more info

[MonkeyK]

I am concluding that timechart is not the right way to handle this because it treats the x-axis as continuous time for all events. I want to overlay data treating each day as its own series.

So I think that I need to define my own X axis and use xyseries to create this sort of graph:

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic  WHERE All_Traffic.dest_ip=134.170.30.203 BY _time, span=30m | eval Day=strftime(_time,"%F") | eval Time=strftime(_time,"%T") | xyseries Time Day count

[MonkeyK]

I am definitely open to other visualization techniques. How would I do something similar with a timechart ?

[MonkeyK]

Pretty sure that the 2h in "sparkline(sum(count),2h)" makes the data points in the sparkline at a 2 hour resolution, rather than simply representing a two hour period. If your point is that I would have a 24 hour period by removing, then that result would not be useful at all as each record is a 24 hour period already, so my sparkline would only have 1 data point. The count already tells me that there were events on that day.

The period in each record of the daily query is being shown using data for the entire week but zeroing out all other days than the current record.

This is intuitively clear by comparing the two screenshots: you can visually line up all the sparklines from the daily results and see that they match the weekly result. Another clue is that the first daily record (12/27) has a first and last time in the evening while it's sparkline suggests events towards the beginning or the record period; and the last record (1/2) has 8 events over the day, while it's sparkline suggests events only towards the end of the record period

What I would hope for is that the sparkline for each daily record would start at the beginning of that day. this would allow me to quickly look for any daily patterns as well as not waste a bunch of the sparkline on days that are not relevant to the current record.

[dlamb_splunk]

Ah true, my apologies. From what I am gathering it's kind of a limitation of the sparkline, the fact that you cannot set the span of the entire sparkline. That is controlled by the stats and chart commands tied to it so that might be an option but seems a bit tricky and would most likely skew results. I haven't found a way to specifically rewrite your search to get the desired results though I have a few other ideas that might work. If you aren't married to using sparklines you might be able to use timechart to overlay the results in a line graph or something similar?