identify host not in a list...

[sjringo]

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 ?

[sjringo]

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.

[gcusello]

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:

| metasearch index=_internal host In (host1, host2, host3)
| stats count BY host
| append [ | makeresults | eval host=host1, count=0 | fields host count ]
| append [ | makeresults | eval host=host2, count=0 | fields host count ]
| append [ | makeresults | eval host=host3, count=0 | fields host count ]
| stats sum(count) AS total By host
| where total=0

I hint to use the lookup!

Ciao.

Giuseppe

[yuanliu]

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.

[sjringo]

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 !

[sjringo]

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 ?

[gcusello]

Hi @sjringo,

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

| metasearch index=_internal 
| stats count BY host
| append [ | metasearch index=_internal  earliest=-30d@d latest=now | stats count BY host | eval count=0 | fields host count]
| stats sum(count) AS total
| where total=0

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

[sjringo]

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 ?

[sjringo]

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 ]

[gcusello]

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

[sjringo]

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 !

[gcusello]

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 😉

[gcusello]

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:

| metasearch index=_internal
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup perimeter.csv | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total By host
| where total=0

Ciao.

Giuseppe