Splunk Search

Removing the "not found" results from a pie chart in csv format

anirudhduggal
Engager

Hello All,

I have csv data like this

ip address, Ports Open
192.168.1.1, 80
192.168.1.2, 81
192.168.1.3, none
192.168.1.4, none
192.168.1.5, none
192.168.1.6,
192.168.1.7,

I am able to create graphs to pull data from them, however, I want to remove data where "none" and " " fields appear.
So far i used this query

|where "Ports Open" !="none" | stats count by "Ports Open"

and

stats count by "Ports Open" | where "Ports Open" !="none"

But I am not able to remove the "none" or the " " fields.
Is there anything else I can do to remove them?

Thank you for reading this.

0 Karma
1 Solution

niketn
Legend

@anirudhduggal, if your query with multi-value Ports Open is working, please add the following to your existing search.

index=health sourcetype="csv"
| fields "Port Open" 
| makemv "Ports Open" delim="," 
| mvexpand "Port Open"
| search "Port Open"!="none"
| stats count by "Ports Open"

PS: mvexpand by Port Open will remove the fields where Port Number is null or not present. Then search filter will remove "Port Open"!="none"

Following is the run any where search based on tweaked data from the question to create multi-valued Open Ports

| makeresults
| eval data="192.168.1.1,80:22;192.168.1.2,81:23;192.168.1.3,none;192.168.1.4,none;192.168.1.5,none;192.168.1.6,;192.168.1.7,;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval "ip address"=mvindex(data,0),"Ports Open"=mvindex(data,1)
| fields - data
| eval "Ports Open"=replace('Ports Open',":",",")
| makemv "Ports Open" delim=","
| mvexpand "Ports Open"
| search "Ports Open"!="none"
| stats count by "Ports Open"

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@anirudhduggal, if your query with multi-value Ports Open is working, please add the following to your existing search.

index=health sourcetype="csv"
| fields "Port Open" 
| makemv "Ports Open" delim="," 
| mvexpand "Port Open"
| search "Port Open"!="none"
| stats count by "Ports Open"

PS: mvexpand by Port Open will remove the fields where Port Number is null or not present. Then search filter will remove "Port Open"!="none"

Following is the run any where search based on tweaked data from the question to create multi-valued Open Ports

| makeresults
| eval data="192.168.1.1,80:22;192.168.1.2,81:23;192.168.1.3,none;192.168.1.4,none;192.168.1.5,none;192.168.1.6,;192.168.1.7,;"
| makemv data delim=";"
| mvexpand data
| makemv data delim=","
| eval "ip address"=mvindex(data,0),"Ports Open"=mvindex(data,1)
| fields - data
| eval "Ports Open"=replace('Ports Open',":",",")
| makemv "Ports Open" delim=","
| mvexpand "Ports Open"
| search "Ports Open"!="none"
| stats count by "Ports Open"

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

anirudhduggal
Engager

worked! thank you 🙂

0 Karma

anirudhduggal
Engager

Hello deepashri_123 and elliotproebstel,

Thank you for replying. I'm afraid both of these do not work. I get an error from splunk which says that the data cannot be parsed.

The data i get has multiple ports like
192.168.1.1, 80,22
192.168.1.2, 81,23

so my query for now is

index=health sourcetype="csv" | makemv "Ports Open" delim="," | stats count by "Ports Open"

Regards,
Anirudh

0 Karma

deepashri_123
Motivator

Hey aniruddhduggal,

You can try using eval:
| fillnull value=empty "Ports Open" |rename "Ports Open" AS Ports |eval Ports=case(Ports="empty" OR Ports="none","none",'Ports') | where Ports!=none |stats count by Ports

Let me know if this helps!!

0 Karma

elliotproebstel
Champion

Given that you're only looking to preserve events where the "Ports Open" field contains a number (presumably), it might work to do this:

| regex 'Ports Open'="/d+"

I don't have access to Splunk at the moment, so that might need to be double-quotes around Ports Open. But that should filter you down to events where the value of Ports Open is a number.

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