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

Happy Splunking!
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...