Getting Data In

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

gbdp
Engager

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

Tags (3)
1 Solution

gbdp
Engager

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 solution in original post

0 Karma

gbdp
Engager

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...