Splunk Search

How to remove null entries from chart?

chavarfa
New Member

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)

alt text

Thanks in advance

0 Karma
1 Solution

mayurr98
Super Champion

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!

View solution in original post

0 Karma

mayurr98
Super Champion

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!

0 Karma

chavarfa
New Member

Thanks for your help

0 Karma

chaker
Contributor

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

0 Karma

chavarfa
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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, ...