Splunk Search

Help returning stats with a value of 0

johnward4
Communicator

I'm trying to return an inventory dashboard panel that shows event count by data source for the given custom eventtype. I need to be able to return the data sources in the panel EVEN if they return 0 events per data source. Looking for advice on the best way to accomplish this output and have started to play around with stats(eval solutions but haven't gotten my desired output yet.

Example search:

 (index=checkpoint sourcetype=opsec) OR index=ids_logs 
    | eval a=mvfilter(eventtype == "network_ids" OR eventtype== "network_ips") 
    | search a=* 
    | stats values(Division) as Division count by a, vendor_product
0 Karma

aohls
Contributor

Fillnull might be what you want to do.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/fillnull

It should allow you to set a value, 0, if there are no results.

0 Karma

johnward4
Communicator

@niketnilay so I'm working on a Compliance related dashboard and say my dashboard has 6 different categories (each having their own panel) the shows a table of data sources that fall under each category and the number of events for each data source return for the past 24 hrs.

So I'm looking for ideas to I guess set the expected data sources for each category (possibly using a lookup table) and then query a stats count of events by the data source. If there are 0 events found for any of the expected data sources then I need it to show 0.

0 Karma

johnward4
Communicator

@niketnilay I've been testing using lookup tables.. I'm trying to build a master lookup table that has the following columns :

index, sourcetype, eventtype, host

I want the lookup table to populate based off a saved search that appends new row data when new sourcetypes are found/added into my Splunk environment. The idea is to build the lookup dynamically. One issue I ran into is that I can't use index=* to build this master lookup efficiently. So I thought to create a separate lookup table that has just a column list of the eventtypes that I want to query index=* against.

Do you have suggestions on how I can use the eventtype lookup to do my index=* search for? This query doesn't seem to be working for me :

| inputlookup auditdash_eventtype_lookup.csv 
| append [search index=* | dedup 1 host | eval host=lower(host) 
| eval eventtype=lower(eventtype)] 
| stats count by index, sourcetype, eventtype, host 
| fields - count 
| table index, sourcetype, eventtype, host
0 Karma

niketn
Legend

@johnward4 instead of performing

 | eval a=mvfilter(eventtype == "network_ids" OR eventtype== "network_ips") 
 | search a=* 

You can directly filter eventtype in your main search

(index=checkpoint sourcetype=opsec) OR index=ids_logs  eventtype IN ("network_ids", "network_ips")

| stats values(Division) as Division count by eventtype, vendor_product
However, I fail to understand where the output is not matching your expectation. Can you share more data example like, what are the values of Division and vendor_product and what is the output of your query vs what is expected output?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...