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"
Create a lookup table with fields DayNumber, DayofWeekName
DayNumber DayOfWeek 0 Sunday 1 Monday ... 6 Saturday
| inpulookup daysofweek | join DayOfWeekName [| search <your search>]
This seems to be correct. But I have to figure out, on how to create a lookup table. At this time my created lookup table can`t be found in splunk search. It seems that a lookup has to be defined in the transformation.conf so that splunk can find ist (this file I can only change together with the splunk administrator). Thy so far.
Just create a .csv file and upload as a lookup through
Settings >> Lookups >> Lookup table Files
and then reference it with a .csv suffix
|inputloookup daysofweek.csv | join DayOfWeekName [| search ]
Sorry, this doesn
t work for me, as day of the week in my table is a row and not a column. Day of week is nevertheless shown in the chart as x-axis. If there is no event for a certain day of the week, you cant make splunk to invent one (only with makeresults which is not a suitable solution).
something like this,
here i have ticket priorities - pri1-pri4 and those volumes are not always there in the given time range, so I had to use something like this
|fields Pri1,Pri2,Pri3,Pri4|fillnull value=0 Pri1,Pri2,Pri3,Pri4
In your case you need to use something like - |fields Sun,Mon,Tue,Wed,Thu,Fri,Sat|fillnull value=0 Sun,Mon,Tue,Wed,Thu,Fri,Sat