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.
@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!
@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!
worked! thank you 🙂
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
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!!
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.