topic Re: How to remove null entries from chart? in Splunk Search

How to remove null entries from chart?

chavarfa — Mon, 02 Apr 2018 23:00:58 GMT

hi to all,

I have a query that produces a chart of hosts, speeds and connection types,

index=*  
| table  host, speed, connection_type
| chart eval(round(avg(speed),2)) OVER host by connection_type

Where my events show that connection_type can be either: T1, T2 or T3

However, I am looking someway to tag (found_all ) or remove rows that don't have all the categories. Is that possible?

my objective is :
1. identify that host4 and host5 don't have all the connection types (remove them from the table if possible) and do an general average for (host1,2,3,6,7)

Thanks in advance

Re: How to remove null entries from chart?

chaker — Tue, 29 Sep 2020 18:50:58 GMT

Try this:

index=*

| table host, speed, connection_type
| where isnotnull(connection_type)
| chart eval(round(avg(speed),2)) OVER host by connection_type

Also, you could fill them with a null value like "0"or "-" using fillnull
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Fillnull

Re: How to remove null entries from chart?

mayurr98 — Tue, 03 Apr 2018 11:16:51 GMT

hey, try this run anywhere search

| makeresults | eval host="host1",T1=null(),T2="1",T3="2" | append [| makeresults | eval host="host1",T1="2",T2="4",T3="5"]| append [| makeresults | eval host="host1",T1="2",T2=null(),T3="5"]| append [| makeresults | eval host="host1",T1=null(),T2=null(),T3="6"]| eval found_all=if(isnull(T1) OR isnull(T2) OR isnull(T3),"No","Yes")

In your environment, you should try

index=* 
| table host, speed, connection_type 
| chart eval(round(avg(speed),2)) OVER host by connection_type 
| eval found_all=if(isnull(T1) OR isnull(T2) OR isnull(T3),"No","Yes")

let me know if this helps!

Re: How to remove null entries from chart?

chavarfa — Tue, 03 Apr 2018 15:13:16 GMT

Thanks for your help

Re: How to remove null entries from chart?

chavarfa — Tue, 03 Apr 2018 15:16:22 GMT

Thanks for your help, however I never capture events connection_type as Null, since they are T1 , T2 or T3. The nulls are seen after the chart is created.