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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!