Splunk Search

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

rprior
Explorer

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:

Splunk-timechart-business_hours.png

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?

Labels (4)
0 Karma
1 Solution

rprior
Explorer

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"

View solution in original post

0 Karma

rprior
Explorer

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"

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Before the rename, have you tried inserting this

| fillnull value=0 office1 office2 office3 office4 office5 office6

rprior
Explorer

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

0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...