Need help in time difference for events

[jerinvarghese]

Hi All,

Pleas help me in getting a query to display the time difference from the events that mentioned below

index=opennms nodelabel="GQML2-WANRTC001" "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown"
 | rename _time as Time_CST 
 | sort - Time_CST
 | fieldformat Time_CST=strftime(Time_CST,"%x %X")
 | table nodelabel,eventuei,  Time_CST

output of the above query is

nodelabel   eventuei    Time_CST
GQML2-WANRTC001 uei.opennms.org/nodes/nodeUp    02/27/20 04:41:00
GQML2-WANRTC001 uei.opennms.org/nodes/nodeDown  02/27/20 04:40:00

Another separate query I use.

| rex field=eventuei "uei.opennms.org/nodes/node(?<State>.+)"
| rename _time as Time_CST
| fieldformat Time_CST=strftime(Time_CST,"%x %X")
| dedup nodelabel sortby - Time_CST 
| table nodelabel State  Time_CST

Output for this query is

 nodelabel      State   Time_CST
GQML2-WANRTC001 UP  02/27/20 04:41:00

Expected output is below is Up event came.

nodelabel       Status  downtime
GQML2-WANRTC001 UP      00:01

Expected output if Up event not came.

nodelabel       Status  downtime
GQML2-WANRTC001 Down    

Let me know all the possibilities of this.

[to4kawa · Accepted solution]

Sample:

| makeresults
| eval _raw="nodelabel,eventuei,Time_CST
GQML2-WANRTC001,uei.opennms.org/nodes/nodeUp,02/27/20 04:41:00
GQML2-WANRTC001,uei.opennms.org/nodes/nodeDown,02/27/20 04:40:00"
| multikv forceheader=1
| table nodelabel,eventuei,Time_CST
| eval Time_CST=strptime(Time_CST,"%m/%d/%y %T")
| fieldformat Time_CST=strftime(Time_CST,"%m/%d/%y %T")
| sort Time_CST
| delta Time_CST as duration
| eval duration=tostring(round(duration),"duration")
| rex field=eventuei "(?<Status>[A-Z].*)"

recommend:

 index=opennms nodelabel="GQML2-WANRTC001" "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown"
| eval Time_CST=_time
| sort Time_CST
| delta Time_CST as duration
| eval duration=tostring(round(duration),"duration")
| rex field=eventuei "(?<Status>[A-Z].*)"

[jerinvarghese]

Current code that am using based on your suggestion..

index=opennms "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown" AND "AOKBT-WANRTC002"
 | eval Time_CST=_time
 | sort Time_CST
 | delta Time_CST as duration
 | eval duration=tostring(round(duration),"duration")
 | fieldformat Time_CST=strftime(Time_CST,"%x %X")
 | rex field=eventuei "(?<Status>[A-Z].*)"
 | dedup nodelabel sortby - Time_CST 
 | table nodelabel, duration, Status, Time_CST

Output is...

nodelabel   duration    Status  Time_CST
AOKBT-WANRTC002 00:15:38    Up  03/23/20 10:01:22

If i keep it for all devices, I used to get duration for 1 min also.. please help me filter or remove all those below 15 mins. I want to display only those devices with duration above 15 mins.
please help me

[to4kawa]

 ....
 | delta Time_CST as duration
 | where duration > 9000
....

[HiroshiSatoh]

Try this!

(your search)
| transaction nodelabel startswith=eval(State="Down") endswith=eval(State="Up") keepevicted=true
| eval downtime=if(closed_txn=1,duration,null)
| eval downtime=tostring(downtime, "duration")
| fillnull value="" downtime
| eval Status=if(closed_txn=1,"Up","Down")
| table nodelabel,Status,downtime

[jerinvarghese]

HI Hiroshi,

The code is not giving an output.

index=opennms nodelabel="GQML2-WANRTC001" "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown"
 | rename _time as Time_CST 
 | sort - Time_CST
 | fieldformat Time_CST=strftime(Time_CST,"%x %X")
 | rex field=eventuei "uei.opennms.org/nodes/node(?<State>.+)"

 | eval downtime=if(closed_txn=1,duration,null)
 | eval downtime=tostring(downtime, "duration")
 | fillnull value="" downtime
 | eval Status=if(closed_txn=1,"Up","Down")
 | table nodelabel,Status,downtime

output:

nodelabel   Status  downtime
GQML2-WANRTC001 Down    
GQML2-WANRTC001 Down    

when am adding the transaction line, no output is there.

index=opennms nodelabel="GQML2-WANRTC001" "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown"
 | rename _time as Time_CST 
 | sort - Time_CST
 | fieldformat Time_CST=strftime(Time_CST,"%x %X")
 | rex field=eventuei "uei.opennms.org/nodes/node(?<State>.+)"
| transaction nodelabel startswith=eval(State="Down") endswith=eval(State="Up") keepevicted=true
 | eval downtime=if(closed_txn=1,duration,null)
 | eval downtime=tostring(downtime, "duration")
 | fillnull value="" downtime
 | eval Status=if(closed_txn=1,"Up","Down")
 | table nodelabel,Status,downtime

[HiroshiSatoh]

Because there is no _time.

 index=opennms nodelabel="GQML2-WANRTC001" "uei.opennms.org/nodes/nodeUp" OR "uei.opennms.org/nodes/nodeDown"
  | rex field=eventuei "uei.opennms.org/nodes/node(?<State>.+)"
 | transaction nodelabel startswith=eval(State="Down") endswith=eval(State="Up") keepevicted=true
  | eval downtime=if(closed_txn=1,duration,null)
  | eval downtime=tostring(downtime, "duration")
  | fillnull value="" downtime
  | eval Status=if(closed_txn=1,"Up","Down")
  | table nodelabel,Status,downtime

[jerinvarghese]

Hi Hiroshi,

One more help,
My output comes as below
GQPCW-WANINF001 Up 00:15:40.019
SGSNGSS13-WLNSGW001 Up 00:04:18.466
NGUSN-LANCUA018 Up 00:00:30.598

am getting that micro second also,
please help in removing that value and keep it as HH:MM:SS

and also is it possible to show only contents that duration is above 15 mins. like whatever below 15 mins should be ignored or not displayed.