How to remove null entries from chart?

[chavarfa]

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

[mayurr98 · Accepted solution]

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!

[chavarfa]

Thanks for your help

[chaker]

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

[chavarfa]

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.