Splunk Search

Need help in time difference for events

Path Finder

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.

Labels (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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].*)"

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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].*)"

View solution in original post

0 Karma

Path Finder

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

0 Karma

SplunkTrust
SplunkTrust
 ....
 | delta Time_CST as duration
 | where duration > 9000
....
0 Karma

Champion

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

Path Finder

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

Champion

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

Path Finder

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!