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"
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>]
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 ]
Please mark the answer as accepted if it works. Thanks
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 can
t 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