Splunk query.

[biec1]

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

[DalJeanis]

 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

[DalJeanis]

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