I am hoping one of you can help me figure out how to calculate time duration between the below sample events. These are the two events that get logged when a session is being blocked on DB server and the other when the alert gets cleared. I would like to create a bar chart showing the time duration of each blocking events. Thanks.
TIMESTAMP=Oct 2, 2015 2:57:57 AM EDT
Message=Total db time 223 seconds is consumed by 1 sessions blocked by session SID,SERIAL:268,23207,LOCK_TYPE:TX,PROGRAM:sqlplus@sysdb-ux01 (TNS V1-V3),MODULE:sqlplus@sysdb-ux01 (TNS V1-V3),ACTION:XXX_PROGRAM,MACHINE:sysdb-ux01,OSUSER:oracle,USERNAME:SYS.
TIMESTAMP=Oct 2, 2015 3:02:57 AM EDT
Message=Alert for Blocking Session DB Time for 4980745_756827_268_23207 is cleared
You can use
your search | transaction Host,Target_Name, "other common unique fields for these two transaction" startswith="sessions blocked by session" endswith="is cleared"|timechart duration
your search|stats first(_time) as End,last(_time) as Start by "common unique fields in the transaction"|eval Difference=End-Start|chart Difference
You might need to adjust the time format for time difference in the second one.
Trying the first method:
| transaction host, PID startswith="Code=ImportRequestStart" endswith="Code=ImportRequestEnd"
| timechart duration
I get this error:
Error in 'timechart' command: The specifier 'duration' is invalid. It must be in form <func>(<field>). For example: max(size).
The problem is the Numeric serials are in 2 different events. When I use IFX it extracts Numeric values for the field from only one event and does not allow me to extract values for same field from the other event. So I am unable to group the events using transaction command for the common field I am trying to extract.
You need a common field or you need to join the transaction with time dependency which might also not work for you. Try extracting the SERIAL as different fields and then create a field alias and use the field alias in transaction
However this is grouping the next event in series. The events have common SERIAL IDs in both the events.
For e.g SERIAL:23207 in the event which alerts blocking of session\
23207 - in the event which displays the blocking was cleared.
How do i use these common values to group the events?
If it's not a field, extract it and use it in transaction
your search | transaction SERIAL startswith="sessions blocked by session" endswith="is cleared"|timechart duration
your search|stats first(_time) as End,last(_time) as Start by SERIAL|eval Difference=End-Start|timechart Difference