Find events that not occurred daily by servername

[indeed_2000]

Hi

How can I find events that not occurred daily? Here is the scenario 

I have two field on my logfile <servername> <CLOSESESSION> need to know when CLOSESESSION is 0 each day by servername.
everyday I expect CLOSESESSION appear on my server logs, if one or more server has no CLOSESESSION it means something going wrong.

need two search here, first extract all server names from file name that exist in path from metadata for faster result, then in second query check which one has not CLOSESESSION

FYI: I don’t like to use lookup in csv file for first step, prefer do it with multi search and join table.

something like this:

1- first search return list of all log files exist (per server)
| metadata type=sources index=my_index | table source

2-second search filter lines contain CLOSESESSION
index="my_index" | search CLOSESESSION
| rex extracted server names of field "source" from STEP 1
| rex extract count of CLOSESESSION 

join them and just show those hasn’t CLOSESESSION

here is the logs: servernames not exist in log, extract from log file name, i put it in log with different color for clear the main goal)

23:54:00.957 app server 1 module: CLOSESESSION

23:54:00.958 app server 3 module: CLOSESESSION

23:54:00.959 app server 4 module: CLOSESESSION

Expected output step 1:

servernames

server 1

server 2

server 3

server 4

Expected output step 2:

Servername     cause

Server2               NOCLOSESESSION

[PickleRick · Accepted solution]

That was kinda close but you still think about Splunk too much in database terms 😉

You need no joins. Turn the thinking around.

The second search should be quite simple and count all occurences of CLOSESESSION

index=my_index CLOSESESSION | stats count by host

 This way you'll get a summary of all hosts that did have some CLOSESESSION events.

So now you want to get another search to find all the hosts. It was quite OK but we'll modify it to be "compatible" with the other one.

| metadata type=hosts index=my_index | eval count=0 | table host count

Now you'll have two table - one will have a non-zero entry for each host for which the CLOSESESSION event occured, the other will have a zero entry for each of your hosts. What to do with them now? Append and sum.

| metadata type=hosts index=my_index
| eval count=0
| table host count
| append
  [ search index=my_index CLOSESESSION
   | stats count by host ]
| stats sum(count) as number by host

This way you'll have a number of CLOSESESSION events per host regardless of whether the host sent any or not (in that case you'll have zero).  Now you can of course add

| where number=0

And get only the list of hosts which haven't send anything throughout the search period.

One caveat though - it will not (because it can't) search events from hosts that never sent anything to the index.

[venkatasri]

Hi @indeed_2000 

You could try this approach, need to update rex as per your format.

| makeresults 
| eval _raw="23:54:00.957 app server1 module: CLOSESESSION" 
| rex "^\d+:\d+:\d+\.\d+\s+\S+\s+(?<servername>\S+)"
| eval cause=if(match(_raw,"CLOSESESSION"),"CLOSESESSION", "NOCLOSESESSION") | bin span=1d _time
| stats values(cause) as cause by _time, servername

--

An upvote would be appreciated if this reply helps!

[indeed_2000]

thank you for answer, main issue here is comine two search result.

1-servername must be extract from metadata

2-count of event extract from my_index

I modify the post for clear it more.