topic How to compute duration for different rows in Getting Data In

How to compute duration for different rows

Noob_splunker — Sun, 05 Jul 2020 03:28:14 GMT

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,

Re: How to compute duration for different rows

renjith_nair — Sun, 05 Jul 2020 06:45:15 GMT

@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

Re: How to compute duration for different rows

to4kawa — Sun, 05 Jul 2020 08:29:24 GMT

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

Re: How to compute duration for different rows

Noob_splunker — Sun, 05 Jul 2020 22:42:15 GMT

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