Getting Data In

How to compute duration for different rows

Noob_splunker
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

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

Noob_splunker
Explorer

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

0 Karma

renjith_nair
Legend

@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

---
What goes around comes around. If it helps, hit it with Karma 🙂
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...