Splunk Search

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

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

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

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

worked! thank you 🙂

0 Karma

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

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

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