topic Re: How can you restrict a timechart to display only weekdays? in Splunk Search

How can you restrict a timechart to display only weekdays?

philallen1 — Wed, 27 Nov 2013 11:26:40 GMT

This has been asked before, over 18 month's ago, and there was no answer to it.
http://answers.splunk.com/answers/44743/how-can-you-restrict-timechart-to-display-only-weekdays

Has anyone got any idea how to remove certain days from a timechart? (In my case: weekends) I can remove them from the search, by using

date_wday!=saturday date_wday!=sunday

...but this is then displayed on the timechart like so (i.e. with the Sat and Sun data as "zero":

I want the timechart to exclude the Sat and Sun from the x axis completely. Any ideas?

Re: How can you restrict a timechart to display only weekdays?

gfuente — Wed, 27 Nov 2013 11:39:39 GMT

Hello

Have you tried to use a chart instead of a timechart command. Something like:

your search ... date_wday!=saturday date_wday!=sunday | bin span=1d _time | chart count by _time

Regards

Re: How can you restrict a timechart to display only weekdays?

philallen1 — Wed, 27 Nov 2013 12:02:28 GMT

Your answer does what I want - so thanks a lot for responding so promptly.

I do have a slight issue: because I use a timepicker on my dashboard, the x axis gets very crowded when I select a wide time range. The other issue is when I select a small time range (i.e. last 24 hours) because the data is bucketed into 'days' I only get 1 point on my chart - whereas before the timechart would cleverly adjust the x axis into hours.

If there's a solution to exclude weekends from a timechart, rather than a chart, then I'm all ears still and will award karma points.

Re: How can you restrict a timechart to display only weekdays?

somesoni2 — Wed, 27 Nov 2013 12:08:25 GMT

Try this

| your time chart search | eval date_wday=lower(strftime(_time,"%A")) |where NOT (date_wday=saturday OR date_wday=sunday) | fields - date_wday

Re: How can you restrict a timechart to display only weekdays?

davebrooking — Wed, 27 Nov 2013 12:19:52 GMT

I don't have a Splunk instance to check this on, but could you replace the span=1d in the bin command with the bins= option?

Re: How can you restrict a timechart to display only weekdays?

philallen1 — Wed, 27 Nov 2013 13:25:34 GMT

Hi Dave
What exactly do you mean? I've tried googling what you suggest but can't fine anything. Could you give me a pointer to some documentation? I couldn't see anything in the 'bin' docs...

Re: How can you restrict a timechart to display only weekdays?

philallen1 — Wed, 27 Nov 2013 13:27:53 GMT

Hi somesoni2
I couldn't get this to work...
My current search is:

sourcetype="UserLogs" UserName=%star% App=%star% "started in" OR "initialised in" | timechart count(App)

I've tried adding in your 'eval', 'where' and 'fields' commands in after and before the 'timechart' command, but with no luck...

Re: How can you restrict a timechart to display only weekdays?

martin_mueller — Wed, 27 Nov 2013 14:04:21 GMT

Try again putting his pipes after the timechart, and put the saturday and sunday after the equal signs in quotes:

... | timechart ... | eval date_wday=lower(strftime(_time,"%A")) |where NOT (date_wday="saturday" OR date_wday="sunday") | fields - date_wday

Re: How can you restrict a timechart to display only weekdays?

davebrooking — Wed, 27 Nov 2013 14:16:06 GMT

Hi Phil
In the search reference for the bin command, http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Bin there are 4 bucketing options :
bins=
minspan=
span= |

Dave

Re: How can you restrict a timechart to display only weekdays?

gfuente — Wed, 27 Nov 2013 14:21:08 GMT

Hello,

I don't see the point of your first question if you only get data from last 24 hours, as you won't need to exclude data.

Anyway, i think you could calculate the buckets size dinamycally using this:
addinfo : to include searh earliest and latest time in epoch. Then substract the earliest to the latest, you get the difference in seconds. Then use eval with a case like:
case(diff<86000,"1h",diff>86000,"1d"). This will calculate the buckets size for your bin command

Regards

Re: How can you restrict a timechart to display only weekdays?

philallen1 — Wed, 27 Nov 2013 14:38:03 GMT

That works Martin. Thanks a lot guys.