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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...