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.
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].*)"
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].*)"
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
....
| delta Time_CST as duration
| where duration > 9000
....
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
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
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
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.