Aggregate function ignore null values

Ropermark — Mon, 20 Aug 2018 14:49:06 GMT

Hello all,

I am new to splunk,

By following string i get a graph of risk:

index="iniatva_linux" Risk=Critical OR Risk=High OR Risk=Medium OR Risk=Low | where like(Name, "%Unsupported%") | chart count by Risk

But the problem is for Risk (high, medium and low) the value is zero that why i see even see the field in graph.
How i can see the field with zero value also in graph..

Thanks in advance

Re: Aggregate function ignore null values

niketn — Tue, 29 Sep 2020 21:00:16 GMT

@Ropermark, before suggesting a solution to your issue, first you should move the where clause as search filter in your main query i.e. instead of adding | where like(Name, "%Unsupported%") you should use Name="*Unsupported*" wildcard filter in your base query.

Coming to your question if you want to bring in the series which have 0 count you can use append to add dummy rows for each Risk type with count as 0. Then use a dedup by Risk names to retain only one row per risk which will keep rows with count > 0 and missing rows with count as 0.

index="iniatva_linux" Risk=Critical OR Risk=High OR Risk=Medium OR Risk=Low Name="*Unsupported*"
| chart count by Risk
| append 
    [| makeresults
    | fields - _time
    | eval data="Risk=Critical,count=0;Risk=High,count=0;Risk=Medium,count=0;Risk=Low,count=0;"
    | makemv data delim=";"
    | mvexpand data
    | rename data as _raw
    | KV
    | table Risk count]
| dedup Risk

Following is a run anywhere search example based on Splunk's _internal index which use log_level INFO, WARN, ERROR and FATAL, similar to your request for Risk. Please try out and confirm!

index=_internal sourcetype=splunkd log_level!=INFO 
| chart count by log_level 
| append 
    [| makeresults
    | fields - _time
    | eval data="log_level=INFO,count=0;log_level=ERROR,count=0;log_level=WARN,count=0;log_level=FATAL,count=0;"
    | makemv data delim=";"
    | mvexpand data
    | rename data as _raw
    | KV
    | table log_level count]
| dedup log_level

topic Re: Aggregate function ignore null values in Splunk Enterprise Security

Aggregate function ignore null values

Re: Aggregate function ignore null values