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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...