Splunk Search

Find events that not occurred daily by servername

indeed_2000
Motivator

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

Labels (5)
Tags (5)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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.

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

0 Karma

indeed_2000
Motivator

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.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...