I have six eventtype's that each check Juniper router logs for an Interface bounce (an up/down event). These are working good. Here is an example, the other five are just variations of this (different routers and interfaces):
sourcetype="syslog" host_rdns="lo0.router1.domain.com" AND SNMP AND "xe-0/0/1" NOT "0/3/1.*"
I am doing the following search during business hours (08:00 to 20:30/7days a week) as a timechart that spans one day, and displays each eventtype as the "office#" site name with how many flaps per hour occurred during the business hours:
sourcetype="syslog" (eventtype="office1" OR eventtype="office2" OR eventtype="office3" OR eventtype="office4" OR eventtype="office5" OR eventtype="office6") NOT UI_CMDLINE | eval date_hourmin=strftime(_time, "%H%M") | eval date_numday = strftime(_time, "%w") | eval date_dow=strftime(_time, "%A") | eval full_datew = _time." ".date_dow| eval mytime=strftime(_time, "%Y-%m-%d, %A") | search (date_hourmin>=0800 date_hourmin<=2030 AND date_numday>=0 date_numday<=6) | timechart span=1d count as "Interface Flap" by eventtype | eval time=strftime(_time, "%m/%d/%Y, %A") | fields - _time | rename office1 as "Home Office", office2 as "Seattle", office3 as "Portland", office4 as "Dallas", office5 as "Chicago", office6 as "New York", time as "Day, Date"
This is working as I want and expect it to, like so:
But I cannot figure out how to display all six eventtype's (sites) at all times, including the eventtype's with ZERO counts. I've tried everything I can think of - fillnull, adding fake results (maybe I am doing that wrong?) but I cannot figure out what I am missing/doing wrong.
Can someone provide pointers for the best way to address this issue?
Only moments later I found this Splunk forum post = https://community.splunk.com/t5/Splunk-Search/how-to-use-timechart-count-to-return-0-when-value-is-n...
I added the following right after the timechart portion
table _time, office1, office2, office3, office4, office5, office6, time | fillnull office1, office2, office3, office4, office5, office6
The total search is working with this:
sourcetype="syslog" (eventtype="office1" OR eventtype="office2" OR eventtype="office3" OR eventtype="office4" OR eventtype="office5" OR eventtype="office6") NOT UI_CMDLINE | eval date_hourmin=strftime(_time, "%H%M") | eval date_numday = strftime(_time, "%w") | eval date_dow=strftime(_time, "%A") | eval full_datew = _time." ".date_dow| eval mytime=strftime(_time, "%Y-%m-%d, %A") | search (date_hourmin>=0800 date_hourmin<=2030 AND date_numday>=0 date_numday<=6) | timechart span=1d count as "Interface Flap" by eventtype | table _time, office1, office2, office3, office4, office5, office6, time | fillnull office1, office2, office3, office4, office5, office6 | eval time=strftime(_time, "%m/%d/%Y, %A") | fields - _time | rename office1 as "Home Office", office2 as "Seattle", office3 as "Portland", office4 as "Dallas", office5 as "Chicago", office6 as "New York", time as "Day, Date"
Only moments later I found this Splunk forum post = https://community.splunk.com/t5/Splunk-Search/how-to-use-timechart-count-to-return-0-when-value-is-n...
I added the following right after the timechart portion
table _time, office1, office2, office3, office4, office5, office6, time | fillnull office1, office2, office3, office4, office5, office6
The total search is working with this:
sourcetype="syslog" (eventtype="office1" OR eventtype="office2" OR eventtype="office3" OR eventtype="office4" OR eventtype="office5" OR eventtype="office6") NOT UI_CMDLINE | eval date_hourmin=strftime(_time, "%H%M") | eval date_numday = strftime(_time, "%w") | eval date_dow=strftime(_time, "%A") | eval full_datew = _time." ".date_dow| eval mytime=strftime(_time, "%Y-%m-%d, %A") | search (date_hourmin>=0800 date_hourmin<=2030 AND date_numday>=0 date_numday<=6) | timechart span=1d count as "Interface Flap" by eventtype | table _time, office1, office2, office3, office4, office5, office6, time | fillnull office1, office2, office3, office4, office5, office6 | eval time=strftime(_time, "%m/%d/%Y, %A") | fields - _time | rename office1 as "Home Office", office2 as "Seattle", office3 as "Portland", office4 as "Dallas", office5 as "Chicago", office6 as "New York", time as "Day, Date"
Before the rename, have you tried inserting this
| fillnull value=0 office1 office2 office3 office4 office5 office6
@ITWhisperer beautiful! Your suggestion worked perfectly too! Thank you very much, it works great as well.