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,
| 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.
thanks for giving me an idea. i'll try to apply it in my query.
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