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 ?
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.
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
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.
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 !
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 ?
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
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 ?
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 ]
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
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 !
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 😉
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