How to compute duration for different rows

[Noob_splunker]

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 

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

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:

It doesnt give me any result for Battery_duration and Battery_duration2.

What is missing?

Thanks,

[to4kawa]

| 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.

[Noob_splunker]

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

[renjith_nair]

@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