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
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
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?"