topic Re: How do I create a sparkline for each day in a chart? in Splunk Search

How do I create a sparkline for each day in a chart?

MonkeyK — Tue, 29 Sep 2020 12:12:46 GMT

I am trying to summarize network traffic to or from an IP address. I would like to look for daily patterns and thought that a sparkline would help to call those out. I cannot figure out how to make a sparkline for each day.

What I have so far:

traffic counts to an IP address by the minute:

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.dest_ip=134.170.30.203
BY _time, All_Traffic.src_ip, All_Traffic.dest_ip, All_Traffic.dest_port span=1m

Which I can summarize over each day with

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.dest_ip=134.170.30.203
BY _time, All_Traffic.src_ip, All_Traffic.dest_ip, All_Traffic.dest_port span=1m
|timechart sum(count) as Count min(_time) as First max(_time) as Last span=1d
| eval First=strftime(First,"%m/%d/%y %H:%M") | eval Last=strftime(Last,"%m/%d/%y %H:%M")

The initial span of a minute is just there so that I can get 1 minute resolution to the first and last times of each day. I actually use an intermediate time chart so that I can save daily fist and last times

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.dest_ip=134.170.30.203
BY _time, All_Traffic.src_ip, All_Traffic.dest_ip, All_Traffic.dest_port span=1m
| timechart sum(count) as minCount earliest(_time) as minFirst latest(_time) as minLast span=1m
| timechart sum(minCount) as Count min(minFirst) as First max(minLast) as Last span=1d
| eval First=strftime(First,"%m/%d/%y %H:%M") | eval Last=strftime(Last,"%m/%d/%y %H:%M")

So the above gives me a record for each day with the date, number of network events, first network event, and last network event, looking something like:
_time..............Count.....First........................Last
2016-12-25....30...........12/25/16 04:25.....12/25/16 23:24
2016-12-26....42...........12/26/16 02:18.....12/26/16 09:14
2016-12-27....430.........12/27/16 03:51.....12/27/16 20:13
2016-12-28....48...........12/28/16 03:51.....12/28/16 10:20
2016-12-29....48...........12/29/16 05:27.....12/29/16 08:23

I would like to add a sparkline indicating how the network events were distributed across the day each day. Can someone help me figure out how to do this?

Re: How do I create a sparkline for each day in a chart?

somesoni2 — Thu, 29 Dec 2016 19:28:25 GMT

Give this a try
Updated

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic 
WHERE All_Traffic.dest_ip=134.170.30.203 
BY _time, All_Traffic.src_ip, All_Traffic.dest_ip, All_Traffic.dest_port span=1m
| timechart sum(count) as minCount earliest(_time) as minFirst latest(_time) as minLast span=1m 
| eval Day=strftime(_time,"%F")
| stats sparkline(sum(minCount),5m) as countTrend sum(minCount) as Count min(minFirst) as First max(minLast) as Last by Day
| eval First=strftime(First,"%m/%d/%y %H:%M") | eval Last=strftime(Last,"%m/%d/%y %H:%M")
| eval _time=strptime(Day,"%F") | table _time countTrend Count First Last

Re: How do I create a sparkline for each day in a chart?

MonkeyK — Thu, 29 Dec 2016 19:36:38 GMT

I got:
Error in 'stats' command: Sparklines not specific to a field must use the "count" aggregator

Re: How do I create a sparkline for each day in a chart?

somesoni2 — Thu, 29 Dec 2016 19:47:50 GMT

Try the updated answer.

Re: How do I create a sparkline for each day in a chart?

MonkeyK — Thu, 29 Dec 2016 20:39:23 GMT

Thank you! That gets me a sparkline.

Oddly the graph that it creates seems to have no basis in reality. For example on one of the days, first and last times are 5:30-8:30am, while the sparkline notes all events at the end of the day.

Are sparklines supposed to actually represent something?

Also, with your Day eval, I don't need the first timechart. I swap out minCount for count and use _time instead of minFirst and minLast. That is pretty neat.

Re: How do I create a sparkline for each day in a chart?

MonkeyK — Thu, 29 Dec 2016 21:58:15 GMT

Actually, I see what has happened. It looks like the sparkline is taking into account all datapoints from my search even though the row's span is only one day. So all of the zeros from day one are included in the sparkline for day 2. Concequently, the second day results start to the right for the first day results , and the third day results to the right of that.

To make matters worse, it looks like sparklines may have a max number of datapoints that they can represent, when those are exceeded, the rest of the sparkline is truncated and my last day may show no data at all.

is there a way to make the sparkline only consider the day that the row represents?

Re: How do I create a sparkline for each day in a chart?

MonkeyK — Thu, 05 Jan 2017 20:56:48 GMT

I am accepting this answer and asking about where the sparkline start in a different question
https://answers.splunk.com/answers/484433/how-do-i-create-daily-sparklines-that-start-with-t.html

I think that one indicates that Splunk says there is a limitation in sparklines that prevents them from only showing data relevant to a time span record.