topic Re: How to find a host which is missing a specific value? in Splunk Search

How to find a host which is missing a specific value?

galindimitrov — Mon, 02 Dec 2019 09:30:21 GMT

Hi all,

My question is focused on open ports but the condition applies to a wide range of scenarios. My question is the following:

I need to create alerts for specific ports when they are not open, and my query looks like this

sourcetype=openPorts Port=2000  | search host=*foo*

This checks all the hosts with "foo" in their name for open port 2000. My question is, how do I define a search that returns the hosts that do NOT have the specified port open. When I try to amend the query with eith using "NOT" or "!=" I get all port values that are not 2000. How do I get the results that do not have that value at all?

Re: How to find a host which is missing a specific value?

to4kawa — Mon, 02 Dec 2019 11:48:20 GMT

sourcetype=openPorts Port IN (80,8080,10080,...)    host=*foo*

Hi, @galindimitrov
I think you can use IN

Re: How to find a host which is missing a specific value?

galindimitrov — Mon, 02 Dec 2019 12:39:17 GMT

@to4kawa,
It looks good on a first glance, but in my case I do not believe it is very applicable. For example host X may have the needed port open and it will show in the query, after some time something happens and the port is closed now. Using IN lets me filter by a range, but what I need is to know which host does not have the value in the query, like in the above example port 2000. But if I just look for results in a range then, I will potentially get hosts that also have the port open even though it may not be specified in the query,

Re: How to find a host which is missing a specific value?

to4kawa — Mon, 02 Dec 2019 12:54:24 GMT

Give examples of host and port status and indicate when you want results.

sourcetype=openPorts NOT Port IN (80,8080,10080,...)    host=*foo*

I think this is good.

Re: How to find a host which is missing a specific value?

galindimitrov — Mon, 02 Dec 2019 13:09:58 GMT

sourcetype=openPorts Port IN (8076,9999,5555,8283,8284,8092,8093,9899)  | search host=*511100471375* | table host Port

https://imgur.com/0qKvapm
This query will return all the hosts with 511100471375 in their names with open ports corresponding to the range given in the IN operator. However that does not mean that port 2000 is not open on any of the hosts in the results. So if my query looks like

    sourcetype=openPorts Port IN 8076,9999,5555,8283,8284,8092,8093,9899,2000)  | search host=*511100471375* | table host Port

I will get the same results as the last query, with an additional entry for each host that has port 2000 open. What I am looking to achieve is to set an alarm to be triggered when a port is no longer open or is not present in the open ports on a given host and I need to see which hosts no longer have the port open. Lets say the logic I am looking for is

sourcetype=openports return hosts that do not have Port=2000| table host Port

Re: How to find a host which is missing a specific value?

to4kawa — Mon, 02 Dec 2019 13:31:11 GMT

 sourcetype=openPorts host=*511100471375*
| stats values(Port) as Port by host
| where isnull(mvfind(Port,2000))

This query displays a list of open ports on the host and excludes those that contain port 2000.

Re: How to find a host which is missing a specific value?

tbavarva — Mon, 02 Dec 2019 13:59:15 GMT

Hi @galindimitrov,

You can create a lookup which contains pattern matching hosts and you can use it in your query.

sourcetype=openPorts Port=2000 | search host=foo NOT [| inputlookup lookup_filename.csv | fields host]

If it helps you, please accept it as an answer.

Regards,
Tejas

Re: How to find a host which is missing a specific value?

tbavarva — Mon, 02 Dec 2019 14:00:59 GMT

Correcting host matching pattern.

sourcetype=openPorts Port=2000 | search host="foo" NOT [| inputlookup lookup_filename.csv | fields host]

Regards,
Tejas

Re: How to find a host which is missing a specific value?

galindimitrov — Mon, 02 Dec 2019 15:24:02 GMT

Thank you, I will test it out tomorrow and let you know 🙂

Re: How to find a host which is missing a specific value?

galindimitrov — Mon, 02 Dec 2019 15:24:30 GMT

I will check tomorrow and let you know, thanks in advance 🙂

Re: How to find a host which is missing a specific value?

woodcock — Mon, 02 Dec 2019 21:18:47 GMT

Why is this not sufficient?

index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="openPorts"
| stats count(eval(Port="2000")) AS port2000 BY host
| where port2000=="0"