You've shown me the right way 🙂 I've adapted your query like this :
sourcetype="ossec" | stats earliest(_time) AS firstTime latest(_time) AS lastTime latest(status) AS currentState by reporting_host | search currentState=Disconnected | eval secondsInCurrentState = lastTime - firstTime | eval time_offline=tostring(secondsInCurrentState, "duration") | convert timeformat="%m/%d/%Y" ctime(lastTime) AS last_checkin, ctime(firstTime) AS first_checkin | table reporting_host, firstTime, first_chekin, last_checkin, time_offline, currentState
However for some reasons the convert for first_checkin doesn't work, it gives me an empty field. Also I have removed the dedup because I believe it will stop at the first occurence and won't find the earliest event (unless I'm mistaken)
Here is the output I get :
reporting_host firstTime first_chekin lastTime last_checkin time_offline currentState
XXXXX 1422767034 1441122904 09/01/2015 212+10:51:10 Disconnected
Thanks for your help !
... View more