Avg Day of Week does not show up, when no result found

[Silmarillion197]

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"

[nabeel652]

Create a lookup table with fields DayNumber, DayofWeekName
Like this:

DayNumber              DayOfWeek
0                          Sunday
1                          Monday
...
6                           Saturday

and

| inpulookup daysofweek | join DayOfWeekName [| search <your search>]

[Silmarillion197]

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.

[nabeel652]

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 ]

[nabeel652]

Please mark the answer as accepted if it works. Thanks

[Silmarillion197]

Sorry, this doesnt 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).

[Sukisen1981]

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