How to edit the eval syntax in my search to assign a value based on the result of a subsearch?

[antoniofacchi]

Hi,

I'm working with Nagios events, with field "current_state" equal 2, Nagios is indicating a critical situation. The events with "current_state=2" are very few. My following search works fine if "current_state=2" events are found, but if they aren't found I get the error:
Error in 'eval' command: The expression is malformed. An unexpected character is reached at ',0)'.

index=app_nagios sourcetype=ydms_status   earliest=-0mon@mon SERVICESTATEID:sasv03qb:
 | eventstats earliest(_time) as start_period latest(_time) as end_period                    
 | eval duration_period=end_period - start_period
 |eval end_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
| sort - _time | head 1 | eval end_incident=strptime(last_time_critical,"%Y-%m-%d %H:%M:%S") |return $end_incident],0)|eval start_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
| sort - _time | head 1 | eval start_incident=strptime(last_time_ok,"%Y-%m-%d %H:%M:%S") |return $start_incident],0)

What I want to do, if there aren't "current_state=2" events, is to set at 0 the fields start_incident and end_incident.

Many thanks
Antonio

[somesoni2 · Accepted solution]

Give this a try

index=app_nagios sourcetype=ydms_status   earliest=-0mon@mon SERVICESTATEID:sasv03qb:
  | eventstats earliest(_time) as start_period latest(_time) as end_period                    
  | eval duration_period=end_period - start_period
  |eval end_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
 | sort - _time | head 1 | eval end_incident=strptime(last_time_critical,"%Y-%m-%d %H:%M:%S") | appendpipe[| stats count as end_incident | where end_incident=0]|return $end_incident],0)|eval start_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
 | sort - _time | head 1 | eval start_incident=strptime(last_time_ok,"%Y-%m-%d %H:%M:%S") | appendpipe[| stats count as end_incident | where end_incident=0]|return $start_incident],0)

Update

There were couple of typos as well in my previous attempt. Try this one

index=app_nagios sourcetype=ydms_status   earliest=-0mon@mon SERVICESTATEID:sasv03qb:
   | eventstats earliest(_time) as start_period latest(_time) as end_period                    
   | eval duration_period=end_period - start_period
   |eval end_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: current_state=2 | stats count latest(last_time_critical) as  last_time_critical | eval end_incident=if(isnull(last_time_critical),0,strptime(last_time_critical,"%Y-%m-%d %H:%M:%S"))|return $end_incident],0)|eval start_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: current_state=2 | stats count latest(last_time_ok) as  last_time_ok | eval start_incident=if(isnull(last_time_ok),0,strptime(last_time_ok,"%Y-%m-%d %H:%M:%S"))|return $start_incident],0)

[antoniofacchi]

Hi someone2,

it works very well!!!!

Thank you very much for your great support.

Regards
Antonio

[antoniofacchi]

Hi somesoni2,

I did two change at your search and it works very well!!!
Excuse me could you explain me the meaning of your:
appendpipe[| stats count as **start_incident* | where start_incident=0]*

Thank you very much for your great support!!!!
Antonio

index=app_nagios sourcetype=ydms_status   earliest=-0mon@mon SERVICESTATEID:sasv03qb:
   | eventstats earliest(_time) as start_period latest(_time) as end_period                    
   | eval duration_period=end_period - start_period
   |eval end_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
  | sort - _time | head 1 | eval end_incident=strptime(last_time_critical,"%Y-%m-%d %H:%M:%S") | appendpipe[| stats count as end_incident | where end_incident=0]|return $end_incident],0)|eval start_incident=if(current_state = 2,[search index=app_nagios sourcetype=ydms_status  earliest=-0mon@mon SERVICESTATEID:sasv03qb: | search current_state=2         
  | sort - _time | head 1 | eval start_incident=strptime(last_time_ok,"%Y-%m-%d %H:%M:%S") | appendpipe[| stats count as **start_incident** | where **start_incident**=0]|return $start_incident],0)

[aaraneta_splunk]

@antoniofacchi - Did the answer provided by somesoni2 help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept" below his answer.

[somesoni2]

The | stats count as start_incident in the appendpipe will return value 0 if there are no rows are available before that. In that case, since there are no rows before that, we'll keep the row returned by stats (thats why we've | where start_incident=0). The result of appendpipe-stats will not be used if there are rows available before that.