topic Re: splunk visualization separate lines per day in a line graph in Splunk Dev

splunk visualization separate lines per day in a line graph

mmdacutanan — Tue, 29 Sep 2020 20:49:28 GMT

I have a query that counts events from 30 days ago to current day but I filter the results so that I am only getting the count of events for the days in those 30 days that equals the current week day. So that if today is Wednesday, I would only see the count of events for all the Wednesdays in the last 30 days. Query is below:

index=abc sourcetype=abc_proxy(Action=InteractionQueued OR Action=InteractionDequeued) earliest=-30d@d latest=now()| timechart span=1d count| eval day_of_week = lower(strftime(_time, "%A")), now = lower(strftime(now(), "%A"))| where day_of_week = now|fields - day_of_week - now

Table is kind of big because I am doing hourly breakdowns but a portion of the output looks like this:

_time count
2018-07-11T00:00 7872
2018-07-11T01:00 5741
2018-07-11T02:00 6480
2018-07-11T03:00 10198
2018-07-11T04:00 11394
2018-07-11T05:00 17033
2018-07-11T06:00 17464
2018-07-11T07:00 21961
2018-07-11T08:00 28636
2018-07-11T09:00 27801
2018-07-11T10:00 28537
2018-07-11T11:00 27996
2018-07-11T12:00 24798
2018-07-11T13:00 27681
2018-07-11T14:00 25653
2018-07-11T15:00 32204
2018-07-11T16:00 32450
2018-07-11T17:00 23217
2018-07-11T18:00 23988
2018-07-11T19:00 22152
2018-07-11T20:00 19021
2018-07-11T21:00 19446

My problem now is with the visualization. Right now I get one continuous line for all the week days that match current weekday. I would like to have a line graph where each day is a separate line in the graph and where each line has an hourly granularity.

I wish I could attach images but I don't have enough points. BUt basically whatever the output is of the query, switch to visualization tab and select line graph.

Thanks in advance

Re: splunk visualization separate lines per day in a line graph

DalJeanis — Sat, 11 Aug 2018 04:51:42 GMT

First, there is a timewrap visualization that may be exactly what you need, avoiding any of the tricks I'm going to give you. Try that first.

https://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Timewrap

Here's a run-anywhere example of how you could do it if that didn't work for you...

index=_audit  
| fields _time date_wday 
| rename COMMENT as "Limit to the fields you need"

| rename COMMENT as "do a preliminary stats at the granularity level you need - here 1 hour"
| bin _time   span=1h 
| stats first(date_wday) as date_wday count as hourcount by _time

| rename COMMENT as "limit to the day you want.  Here we assume that you want the day of week of the last date"
| eventstats latest(date_wday) as last_wday
| where date_wday = last_wday
| fields - last_wday

| rename COMMENT as "calculate the date, the last date, and how many weeks back each date is"
| bin _time as day span=1d 
| eventstats max(day) as maxday
| eval weeksback=(maxday-day)/604800 

| rename COMMENT as "move the date forward an even number of weeks, to match the last date"
| eval _time = _time + weeksback*604800

| rename COMMENT as "pretty up the week names, and drop unneeded fields"
| eval weeksback=if(weeksback=0,"Current ".date_wday,weeksback." Weeks Back")
| fields - day maxday
| timechart span=1h sum(hourcount) by weeksback

| rename COMMENT as "get rid of any completely empty records at the beginning or end"
| addtotals row=t col=f
| where isnotnull(Total)
| fields - Total

Re: splunk visualization separate lines per day in a line graph

woodcock — Sat, 11 Aug 2018 20:40:10 GMT

I agree. The timewrap command is great but if you are on an older release, you may need the (somewhat buggy) app:

https://splunkbase.splunk.com/app/1645/