I have data that is not always confined to one day, but my reports should report over whole days.

[gbdp]

I have data that is not always confined to one day, but my reports should report over whole days.

Not sure how to resolve this in Splunk.

I got the data available in a daily file, AvailableReport.csv:
Alarm A; Activated 2018/09/22 20:00:00; Resolved 2018/09/23 07:00:00
Alarm B; Activated 2018/09/22 21:30:00; Resolved 2018/09/22 23:00:00
Alarm B; Activated 2018/09/23 06:00:00; Resolved 2018/09/23 08:45:00

I might be able to get hold of the rawdata in a file RawData.csv:
2018/09/22 20:00:00; Alarm A; Activated
2018/09/22 21:30:00; Alarm B; Activated
2018/09/22 23:00:00; Alarm B; Resolved
2018/09/23 06:00:00; Alarm B; Activated
2018/09/23 07:00:00; Alarm A; Resolved
2018/09/23 08:45:00; Alarm B; Resolved

This is the Search Result I am after:
Date..............A...........B......
2018/09/22....04:00....01:30
2018/09/23....07:00....02:45

[gbdp · Accepted solution]

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

[richgalloway]

@gbdp If your problem is resolved, please accept the answer to help future readers.