Getting Data In
Highlighted

How to compute duration for different rows

Explorer

Hi,

I'm trying to compute the duration between two rows. I need the duration for Battery_duration  and Battery_duration2.

 

NodeBTime = Alarm ID 22214 Occurtime

PowerTime = Alarm ID 25622 Occurtime

CellTime = Alarm ID 29245Occurtime

Battery_duration = NodeBTime - PowerTime

Battery_duration2 = CellTime - NodeBTime 

AlarmIDOccurtimeClearTimedurationNodeBTimeCellTimePowerTimeBattery_durationBattery_duration2
292453/07/2020 14:093/07/2020 14:13  3/07/2020 14:09   
256223/07/2020 9:013/07/2020 14:11   3/07/2020 9:01  
222143/07/2020 13:593/07/2020 14:11 3/07/2020 13:59    

 

Here is my query:

 

|fillnull ClearTime
|eval ClearTime=if(ClearTime=0,strftime(now(),"%Y-%m-%d %H:%M:%S"),ClearTime)
|eval dur_sec=strptime(ClearTime,"%Y-%m-%d %H:%M:%S.%N")-strptime(Occurtime,"%Y-%m-%d %H:%M:%S.%N")
|eval dur_sec=round((strptime(ClearTime,"%Y-%m-%d %H:%M:%S.%N")-strptime(Occurtime,"%Y-%m-%d %H:%M:%S.%N")))
|eval duration=tostring(dur_sec,"duration")
|convert num(duration)
|eval duration=round(duration/60,2)

| eval PowerTime=if((AlarmID="25622"),Occurtime,null)
| eval NodeBTime=if((AlarmID="22214"),Occurtime,null)
| eval CellTime=if((AlarmID="29245"),Occurtime,null)
| eval Battery_duration=strptime(NodeBTime,"%Y-%m-%d %H:%M:%S.%N")-strptime(PowerTime,"%Y-%m-%d %H:%M:%S.%N")
| eval Battery_duration=round((strptime(NodeBTime,"%Y-%m-%d %H:%M:%S.%N")-strptime(PowerTime,"%Y-%m-%d %H:%M:%S.%N")))
| table AlarmID Occurtime ClearTime duration NodeBTime, CellTime PowerTime Battery_duration Battery_duration2 State

Here is my result:

duration1111.PNG

It doesnt give me any result for Battery_duration and Battery_duration2.

What is missing?

Thanks,

Labels (1)
Tags (1)
0 Karma
Highlighted

Re: How to compute duration for different rows

SplunkTrust
SplunkTrust

@Noob_splunker,

That's because the values are in different events as you can see in the table.

Are there only these three events always? If not , is there an identifier which could co-relate these events?

For the existing data example, try below

 

your search
| eval PowerTime=if((AlarmID="25622"),Occurtime,null)
| eval NodeBTime=if((AlarmID="22214"),Occurtime,null)
| eval CellTime=if((AlarmID="29245"),Occurtime,null)
| eventstats values(PowerTime) as PowerTime,values(NodeBTime) as NodeBTime,values(CellTime) as CellTime
| " rest of your substraction calculations"

 

If you have multiple events and an common identifier to co-relate the events, you can add by that_identifier in the eventstats

Highlighted

Re: How to compute duration for different rows

Ultra Champion
| makeresults 
| eval _raw="AlarmID,Occurtime,ClearTime,duration,NodeBTime,CellTime,PowerTime,Battery_duration,Battery_duration2
29245,3/07/2020 14:09,3/07/2020 14:13,,,3/07/2020 14:09,, 
25622,3/07/2020 9:01,3/07/2020 14:11,,,,3/07/2020 9:01,,
22214,3/07/2020 13:59,3/07/2020 14:11,,3/07/2020 13:59,,"
| multikv forceheader=1
| table AlarmID,Occurtime,ClearTime,duration,NodeBTime,CellTime,PowerTime,Battery_duration,Battery_duration2

| eventstats max(eval(strptime(NodeBTime,"%d/%m/%Y %H:%M"))) as Battery_duration1_fm ,max(eval(strptime(PowerTime,"%d/%m/%Y %H:%M"))) as Battery_duration1_to
| eventstats max(eval(strptime(CellTime,"%d/%m/%Y %H:%M"))) as Battery_duration2_fm ,max(eval(strptime(NodeBTime,"%d/%m/%Y %H:%M"))) as Battery_duration2_to
| eval Battery_duration=if(isstr(PowerTime),tostring(round(Battery_duration1_fm - Battery_duration1_to),"duration"),NULL)
| eval Battery_duration2=if(isstr(CellTime),tostring(round(Battery_duration2_fm - Battery_duration2_to),"duration"),NULL)
| fields - *_fm *_to

Query's result and table does not look same.

Highlighted

Re: How to compute duration for different rows

Explorer

thanks for giving me an idea. i'll try to apply it in my query. 

0 Karma