Hello,
I`m trying to find a solution for this problem. The result of the following SPL query should show every day of the week in a diagram. So the diagram should show Mon ... Tue ... Thu ... Wed ... Fri ... Sat ... Sun (x-axis) always, even if there is no data at all (seems that if no avg or no sum can be aggregated then the specific day of the week will not show up).
=> I have tried to fillnull, value=0 or coalesce ifnull but this is not working for me, as the day of week with no result is never shown.
_time is overwritten because events loaded into splunk are deferred sometimes, so I use the real START_TIME of Event as base for calculations. This is working so far, except if a "day of week" has no data:
index="rw_trail_complete"
| eval StartEpoch=strptime(START_TIME, "%Y-%m-%d %H:%M:%S.%Q")
| eval _time=StartEpoch
| eval EndEpoch=strptime(END_TIME, "%Y-%m-%d %H:%M:%S.%Q")
| eval DayOfWeekName=strftime(_time, "%a")
| eval DayOfWeekNumber=strftime(_time, "%u")
| eval Dur = EndEpoch - StartEpoch
| bucket _time span=1d
| search SCRIPT_NAME=$scriptName$
| eventstats count AS "Num Events per day" by DayOfWeekNumber
| table DayOfWeekName, DayOfWeekNumber, "Num Events per day", Dur
| stats avg(Dur) AS "Avg Dur per Day of Week" by DayOfWeekNumber, DayOfWeekName, "Num Events per day"
| table DayOfWeekName, "Avg Dur per Day of Week", "Num Events per day"
... View more