Splunk Search

Splunk query.

biec1
Explorer

I would like to count the number of times a Server went down, based on up/down state field.
State field receives up or down for each server every five minutes.

index=live_index source="rest:" state=*
| stats latest(state) as state by hostname
| eval host_status = if(state=="up",0,1)
| join hostname[search index=temp_index | stats latest(host_status) as server_down latest(down_count) as down_count by hostname] 
| where host_status!=server_down
| eval down_count=down_count+1
| fields hostname host_status down_count

I am reading the live events from live_index and comparing the host_status with the previous host_status that are stored in temp_index.
When ever the state changes, i am incrementing down_count and storing latest hostname host_status down_count to temp_index.
I am running this query as a scheduled search, so that the results are stored in summary index temp_index.

PROBLEM THAT I HAVE AT PRESENT:-
Whenever the new servers are added OR for the initial run , the fields hostname host_status down_count should be created and populated in temp_index .
Currebtly i am doing this by using the follwing. But when a new server is added or for thr initial run down_count filed and hostname host_status fields needs to be created. Pls provide me a way to create these fields, so that i can use them in the first query.

index=live_index source="rest:" state=*
| stats latest(state) as state by hostname
| eval host_status = if(state=="up",0,1) 
| eval down_count=host_status
| fields hostname host_status down_count

SAMPLE DATA:-
Time| hostname|State
3:00 AM Host1 up
3:00 PM Host2 down
3:05 PM Host1 up
3:10 PM Host1 up
3:15 PM Host1 down
3:15 PM Host2 up
3:20 PM Host1 up
3:25 PM Host1 down
3:30 PM Host1 up
3:35 PM Host1 up
3:40 PM Host1 down
3:45 PM Host1 down
3:50 PM Host1 down
3:55 PM Host1 up
4:00 PM Host1 up

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust
 index=live_index source="rest:" state=*
 | table _time hostname state
 | sort 0 hostname _time
 | rename COMMENT as "The above extracts and sorts your current state report records for your requested time period."

 | rename COMMENT as "This determines initial state and when the state changes."
 | streamstats current=f last(state) as priorstate by hostname
 | eval newstate=if(isnull(priorstate) OR priorstate!=state,1,0)
 | streamstats sum(newstate) as stateno by hostname


 | rename COMMENT as "This rolls together all the consecutive records for a given state."
 | rename COMMENT as "and checks to see if we had any hiccups."
 | stats min(_time) as _time, max(_time) as max_time, first(state) as state, count as duration by hostname stateno 
 | eval duration = duration*300
 | eval time_span=300+max_time-_time
 | eval countflag=case(time_span>duration,"Records missing",time_span<duration,"Records duplicated", true(),null()) 
 | eval duration=if(duration<time_span,time_span,duration)
 | eval end_time=_time+duration
 | table _time end_time duration hostname state stateno countflag


 | rename COMMENT as "We can put out a file for investigating issues with our data "
 | appendpipe [ | where isnotnull(countflag) | outputcsv myproblems.csv | where false()]

 | rename COMMENT as "Now we can do stats directly on the records and various types of analysis"
 | stats count as statecount avg(duration) as avgduration by hostname state
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

A more descriptive title, preferably with a specific question, will get you more attention and helpful responses. Practically everything is a splunk query.

For example, "How do I set iniitial state for my Splunk Query of server status?"

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...