SPL querry help

[srujana96]

I have a lookup table X which contains list of Servers, my indexer(myserveridx) contains list of server which are up and running. i want to write a querry to get the name of server which are present in lookup table but not in index. 

[gcusello · Accepted solution]

Hi @srujana96,

assuming that the host field in lookup is named "host", you have to run a search like this:

| metasearch index=myserveridx
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup X | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total
| where total=0

In this way you can create an alert to periodically run (e.g. every 5 minutes) to check your perimeter.

You can use the same search (replacing the last row) to display a status of your infrastruture:

| metasearch index=myserveridx
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup X | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total
| eval status=if(total=0,"Down","Up")
| table host status

 Ciao.

Giuseppe

[srujana96]

Hi @gcusello 

Thanks for the reply.
the querry which you have sent is giving me the overall count.
but my expectation is to get the count of the Server which are in lookup table but not in my index
for example i have pasted the below excel sheet for your reference in that i want the count server that are not patched i have highlighted for your reference

In this below excel sheet the servers which are not patched are 1,3,5,7 and 9 so my count is 5.

Lookup	Index	Status		server patched	Success	Failure	server not patched
1	2	Success		5	3	2	5
2	4	Success
3	6	Success
4	8	Failure
5	10	Failure
6
7
8
9
10

[gcusello]

Hi @srujana96,

my first search gives you the list of servers that are in lookup but not in the search results.

You have to adapt the logic of this approach to your needs!

in other words; you have to define a search to find all the servers that match a condition (e.g. Status=Success) and then compare results with the lookup using my method.

So if you want to check which of your servers has Status=Success, you can run a search like this:

index=myserveridx Status=Success
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup X | eval host=lower(host), count=0 | fields host count ]
| stats sum(count) AS total
| where total=0

In this way you have all the servers from your list that haven't Status=Success.

Ciao.

Giuseppe