topic Re: Display ALL eventtypes in a timechart, including the ALL ZERO count eventtypes in Splunk Search

How to display all eventtypes in a timechart, including the all zero count eventtypes?

rprior — Tue, 24 Jan 2023 15:26:02 GMT

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?

Re: Display ALL eventtypes in a timechart, including the ALL ZERO count eventtypes

ITWhisperer — Tue, 24 Jan 2023 11:17:08 GMT

Before the rename, have you tried inserting this

| fillnull value=0 office1 office2 office3 office4 office5 office6

Re: Display ALL eventtypes in a timechart, including the ALL ZERO count eventtypes

rprior — Tue, 24 Jan 2023 11:24:52 GMT

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-null/m-p/344266

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"

Re: Display ALL eventtypes in a timechart, including the ALL ZERO count eventtypes

rprior — Tue, 24 Jan 2023 13:44:53 GMT

@ITWhisperer beautiful! Your suggestion worked perfectly too! Thank you very much, it works great as well.