Splunk Search

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

Silmarillion197
Explorer

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"
Tags (3)
0 Karma

nabeel652
Builder

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>]
0 Karma

Silmarillion197
Explorer

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.

0 Karma

nabeel652
Builder

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 ]

0 Karma

nabeel652
Builder

Please mark the answer as accepted if it works. Thanks

0 Karma

Silmarillion197
Explorer

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).

0 Karma

Sukisen1981
Champion

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

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...