Time ranges - 'if' 'then' logic to control x-axis limits

[lanode]

Dear All,

I've got a tricky little problem that I'm wrestling with. I have the following code, it plots the distribution of outbound telephone calls from a given extension number on a hourly basis from the previous business day.

Business days are from Monday to Friday. Should the chart be viewed on a Monday then the previous business is a Friday and Fridays data is used to construct the chart. Should the chart be viewed on, say, Thursday then of course the previous business day is Wednesday so Wednesdays chart is constructed.

The final 3 lines within the {searchstring} remove saturday and sunday from any chart that is produced thus showing only the day requested.

This functionality is great and all works well.

{row}
  {chart}
    {searchString}
    sourcetype="Telephone Log" 217 NOT "I" earliest=-3d@d+8h latest=@d-5h
     | eval day_value = case(date_wday=="saturday","0", date_wday="sunday", "0", date_wday=="monday", "1", date_wday== "tuesday", "2", date_wday=="wednesday", "3", date_wday=="thursday","4", date_wday=="friday", "5")
     | eventstats max(day_value) as high_value
     | where day_value=high_value 
     | timechart span=1h count as "Total Calls"

     | eval dayname = strftime(_time, "%a") 
     | search dayname!=Sat dayname!=Sun
     | fields - dayname 
   {/searchString} 
          {title}Outbound Calls in Past 24 Hours - Distribution{/title}
          {option name="charting.axisLabelsX.majorUnit"} P0Y0M0DT1H0M0S{/option}
          {option name="charting.axisTitleX.text"}Time{/option}
          {option name="charting.axisTitleY.text"}No. of Calls{/option}
          {option name="charting.chart"}column{/option}
          {option name="charting.scaleX"}1{/option}
  {/chart}
{/row}

The problem is, and it's only an aesthetic one, I would like the resultant chart to only show the hours from 8am until 7pm. Normally this would not be a problem as I could use 'earliest=-24h@d+8h' and latest=@d-5h'. However, with the above code and with regard to the 'earliest' and 'latest' functions within the {searchstring}, should the search be carried out on Monday then the resultant charts range is from 8am (earliest=-3d@d+8h) until midnight (end of the day). And should the search be carried out on any other business day then the resultant chart is from the start of the day until 7pm (latest=@d-5h).

The problem stems from the 'earliest' and 'latest' functions being spread over 3 days, which is necessary to ensure Fridays data is plotted when requested on a Monday. So when I'm viewing data from a Friday then I'm effectively at the start of these 3 days and therefore capturing the '+8h' in the 'earliest=-3d@d+8h' code but not the '-5h' in the 'latest' and if I'm viewing any other business day then I'm effectively at the end of the 3 day time range so capturing the '-5h' in the 'latest=@d-5h' code but not the '+8h' in the 'earliest'.

So I need to develop some way to compensate for this. Somethink like perhaps - 'if' previous business day is yesterday 'then' 'earliest=-24h@d+8h latest=@d-5h'' but 'if' previous business is a Friday 'then' 'earliest=-3d@d+8h latest=-2d@d-5h'.

Can this be done? Any help, once again, is very much appreciated.

[_d_]

Note that you can use the date_hour field in your base search to limit the scope of the search onto a daily 8a-7p window. The indexers will return only the matching events and your search will perform better as it doesn't have to filter afterwards. Perhaps, this can give you further ideas to continue your investigation.

index=my_index sourcetype="Telephone Log" _other_search_terms_ date_hour>7 date_hour<19

d.

[lanode]

Mr. Martin_Mueller pointed me in the right direction on this one.

I think I've solved it now.
Essentailly just expanded the code that removes saturday and sunday from any chart to also now encompass the hours I don't want to show.

 | eval dayname = strftime(_time, "%a") 
 | eval hour = strftime(_time, "%H:%M")
 | search dayname!=Sat dayname!=Sun
 | search hour=08:00 OR hour=09:00 OR hour=10:00 OR hour=11:00 OR hour=12:00 OR hour=13:00 OR hour=14:00 OR hour=15:00 OR hour=16:00 OR hour=17:00 OR hour=18:00
 | fields - dayname hour

Will carry on testing, but this appears to work.

Thank you Martin

---

On further testing the above does NOT provide a solution.
Further testing shows x-axis range does NOT reside within the limits desired. Don't yet know exactly why.
Shall continue to investiage.

---

[sowings]

Note that this will always require something like a 'bucket span=1h' before this, as your %M won't always be :00. You could force "top of the hour" values by using strftime(_time, "%H:00").

[martin_mueller]

A naive way to filter out events occurring after 7pm would be to filter based on date_hour and throw out anything >=19 - quite similar to how you filter out weekends.

[lanode]

Martin, thank you.

You provided me with a different way to view the problem.
I think I've solved it now.
See my posted answer - thanks again.