topic Re: identify host not in a list... in Splunk Search

identify host not in a list...

sjringo — Fri, 21 Jan 2022 13:11:06 GMT

I have a query that returns a set of hosts that have an event string.

index=anIndex sourcetype=aSourceType ("aString1" AND ( host = "aHostName*"))

| stats values(host) AS aServerList1

I have a list of servers ("Server1", "Server2", "Server3") <- ServerList2

What im trying to do is to find servers/hosts that are not returned from the initial query.

i.e. hosts that exists in ServerList2 but are not in ServerList1 ?

Re: identify host not in a list...

gcusello — Fri, 21 Jan 2022 13:14:45 GMT

Hi @sjringo,

you have to pust the list of host to monitor in a lookup (called e.g. perimeter.csv), with at least one field (host), and run a search like this:

Ciao.

Giuseppe

Re: identify host not in a list...

sjringo — Fri, 21 Jan 2022 13:20:09 GMT

I saw a posting about using a .csv file but I cannot put a file on the Splunk server it all needs to be in the Splunk query.

I made an assumption that the .csv would reside on the Splunk server the query is being executed on.

I could use two queries as I have another query that gets a unique list of hosts that have splunk traffic.

Same concept, instead of hardcoded list of hosts the list of hosts is generated from a query.

Re: identify host not in a list...

gcusello — Fri, 21 Jan 2022 13:31:25 GMT

Hi @sjringo,

putting the server list in the search it's possible if you have few server to monitor, otherwise it's complicated!

In this case, you could run something like this:

I hint to use the lookup!

Ciao.

Giuseppe

Re: identify host not in a list...

sjringo — Fri, 21 Jan 2022 14:24:20 GMT

I did not think of comparing the results of two queries earlier and that would be a better solution:

If I have the following two lists of hosts:

index=anIndex sourcetype=aSourceType ("aString1" AND ( host = "aHostName*"))

| stats values(host) AS aServerList1

index=anIndex sourcetype=aSourceType ("aString2" AND ( host = "aHostName*"))

| stats values(host) AS aServerList2

aServerList2 would be my list of all hosts and would like to find those hosts that exists in aServerList2 but do not exist in aServerList1 ?

Re: identify host not in a list...

gcusello — Fri, 21 Jan 2022 15:11:07 GMT

Hi @sjringo,

you could also use another search to have the list of servers to monitor, something like this:

but there's a limit in this solution: you could lack some servers because they aren't in the second list (they are missing in the last 30 days.

For this reason, I hinted to use a lookup with the list of servers to monitor.

Beware: if you use "value(host) As host", you have a multivalue field that you cannot use for monitoring.

Ciao.

Giuseppe

Re: identify host not in a list...

sjringo — Fri, 21 Jan 2022 16:39:06 GMT

The metadata search, I dont think is what I want. I found another post on set diff that compares two searches ? just having some troubles getting it to work.

The first query finds all hosts that have an event that matches "String1" and particular host name with a wildcard search.

Query 1:

search index=anIndex sourcetype=aSourceType ("String1" AND host="aHostName*") | stats count by host | table host

Query two finds all servers based on just the host name with a wild card search.

Query 2:

search index=anIndex sourcetype=aSourceType (host="aHostName*") | stats count by host | table host

Now my next step is to figure out how to get this to work with | set diff? or is there another way to find what is missing when comparing the two tables ?

Re: identify host not in a list...

sjringo — Fri, 21 Jan 2022 18:18:37 GMT

Here is my solution:

| set diff

[ | search index=anIndex sourcetype1=aSourceType ("aString1" AND (host="aHostName1*" OR host="aHostName2*"))

| stats count by host | table host ]

[ search index=anIndex sourcetype=aSourceType2 (host="aHostName1*" OR host="aHostName2*")

| stats count by host | table host ]

Re: identify host not in a list...

gcusello — Sat, 22 Jan 2022 07:00:35 GMT

Hi @sjringo,

this solution is less efficient than mine, but if you have the wanted results, you can use it; infact if you search in Community the most solutions are similar to mine.

In addition, using the "index=anindex" filter, you aren't sure of results because maybe a server could be missed in both the searches.

For this reason, I hint to use "index=_internal" because you're sure to have logs from active servers.

"| metadata" is a way to have a faster search but you cannot use it using words as a filter.

Ciao and happy spluking.

Giuseppe

Re: identify host not in a list...

yuanliu — Sat, 22 Jan 2022 09:15:23 GMT

This method is indeed more efficient. And it doesn't have to be complicated if mvappend(X,...) is used.

index=anIndex sourcetype=aSourceType ("aString1" AND ( host = "aHostName*")) | stats values(host) as hosts by sourcetype ``` not strictly neccessary but makes subsequent search faster if the event list is large ``` | append [ | makeresults | eval hosts = mvappend("Server1", "Server2", "Server3") ] | stats values(sourcetype) as sources by hosts | where isnull(sources)

The static server list can easily be extended.

Re: identify host not in a list...

sjringo — Sat, 22 Jan 2022 10:57:26 GMT

My first goal was to get something to work I will look at your suggestion to see if I can incorporate or change over to use this. I have around 25 servers that im trying to monitor.

Thanks for your input !

Re: identify host not in a list...

sjringo — Sat, 22 Jan 2022 11:00:48 GMT

I am using index=anIndex cause I need to first search for a particular string to identify which servers log a

known event. I then compare against a list of 25 known servers that are logging splunk traffic.

I will see if I can use any of your suggestions in my next iteration of this.

Thanks for your input !

Re: identify host not in a list...

gcusello — Mon, 24 Jan 2022 07:28:09 GMT

Hi @sjringo,

I hinted to use _internal instead of anindex because, to use anindex you must be sure that you always have logs in this index, instead using _internal, you're sure of the presence of logs because they are always generated by Splunk, if you haven't them it's the error condition you're searching.

Anyway, if you can I hint to use the lookup, to have more control on your monitoring, otherways une of the solutions hintd by myself or by @yuanliu can be your solution.

Please, when you'll solve the problem accept one solution for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉