How to calculate duration between two events and plot this on a bar chart?

[saurabhkunte]

HI All,

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
Host=sysdb-ux01.xxx.com
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.
Target_Type=Database Instance
Target_Name=DB_01
Severity=Critical
Metric=db_time
Num_Keys=1
Key_Value_Name=db_time_id__

Key_Value=223___

TIMESTAMP=Oct 2, 2015 3:02:57 AM EDT
Host=sysdb-ux01.xxx.com
Message=Alert for Blocking Session DB Time for 4980745_756827_268_23207 is cleared
Target_Type=Database Instance
Target_Name=DB_01
Severity=Clear
Metric=db_time
Num_Keys=1
Key_Value_Name=db_time_id__
Key_Value=___

[renjith_nair]

You can use transaction command

http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Transaction

your search | transaction Host,Target_Name, "other common unique fields for these two transaction" startswith="sessions blocked by session" endswith="is cleared"|timechart duration

Or use stats

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.

[unitedmarsupial]

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

Using avg(duration) works...

[saurabhkunte]

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.

[renjith_nair]

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

[saurabhkunte]

Thanks Renjith.nair.

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?

[renjith_nair]

If it's not a field, extract it and use it in transaction
ie

 your search | transaction SERIAL startswith="sessions blocked by session" endswith="is cleared"|timechart duration

OR

 your search|stats first(_time) as End,last(_time) as Start by SERIAL|eval Difference=End-Start|timechart Difference