Splunk Search
Highlighted

Help returning stats with a value of 0

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
Highlighted

Re: Help returning stats with a value of 0

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, vendorproduct
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?




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Help returning stats with a value of 0

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
Highlighted

Re: Help returning stats with a value of 0

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
Highlighted

Re: Help returning stats with a value of 0

Communicator

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