I managed to get the SPL search up and running with the help of a colleague, but I thought I share this information, as I am sure, other people might be interested in the solution as well.
The exception is that it cannot handle alarms that overrun more than 2 days, in that case the day in the middle is not reported on.
Als the result is in a slightly different format than I initially was looking for, but it actually works better for what I need it for:
Date..............Text..........DiffTime
2018/09/22....Alarm A....04:00
2018/09/23....Alarm A....01:30
2018/09/22....Alarm B....07:00
2018/09/23....Alarm B....02:45
The SPL search is looking like this:
index=test source="AlarmsRaw.txt" sourcetype="csv"
| eval Day=strftime(_time,"%Y-%m-%d")
, DayVal=round(strptime(Day,"%Y-%m-%d"))
, DateVal=strptime(Date,"%Y/%m/%d %H:%M:%S")
, NextDayVal=DayVal+60*60*24
, NextDay=strftime(NextDayVal,"%Y/%m/%d %H:%M:%S")
| sort -_time
| streamstats window=1 current=f global=f
last(Date) as NextTime
last(State) as NextState by Text
| sort _time
| streamstats window=1 current=f global=f
last(Date) as PrevTime
last(State) as PrevState by Text
| eval PrevTimeVal=round(strptime(PrevTime,"%Y/%m/%d %H:%M:%S"))
, NextTimeVal=round(strptime(NextTime,"%Y/%m/%d %H:%M:%S"))
, PrevDiff=DateVal-PrevTimeVal
, NextDiff=NextTimeVal-DateVal
| eval ActivatedTimeVal=if(State="Activated",DateVal,if(PrevState="Activated",PrevTimeVal,null))
, ActivatedTimeVal=if(ActivatedTimeVal
, ActivatedTime=**strftime**(ActivatedTimeVal,"%Y/%m/%d %H:%M:%S")
, ResolvedTimeVal=**if**(State="Resolved",DateVal,**if**(NextState="Resolved",NextTimeVal,**if**(**isnull**(NextState),NextDayVal,null)))
, ResolvedTimeVal=**if**(ResolvedTimeVal>NextDayVal,NextDayVal,ResolvedTimeVal)
, ResolvedTime=strftime(ResolvedTimeVal,"%Y/%m/%d %H:%M:%S")
| eval DiffSeconds=ResolvedTimeVal-ActivatedTimeVal
| search DiffSeconds>0
| table Day, Text, PrevTime, PrevState, Date, State, NextTime, NextState, ActivatedTime, ResolvedTime, DiffSeconds
| dedup Day, Text, ActivatedTime, ResolvedTime, DiffSeconds
| table Day, Text, ActivatedTime, ResolvedTime, DiffSeconds
| stats sum(DiffSeconds) as TotalSeconds by Day, Text
| eval DiffHr=floor(TotalSeconds/(60*60))
, DiffMin=floor((TotalSeconds-DiffHr*60*60)/60)
, DiffSec=floor(TotalSeconds-DiffHr*60*60-DiffMin*60)
, DiffTime=strftime(strptime(DiffHr+":"+DiffMin+":"+DiffSec,"%H:%M:%S"),"%H:%M:%S")
| table Day, Text, DiffTime
| sort Text, Date
... View more