How to combine data from 2 separate events?

[Hyperlemon]

I have the following scenario. An object transitions through multiple queues , I want to query the time spent in Queue 1 and group it by object type. Each object has unique id but it generates an event every time it transitions from queues. :

Event 1: 

id : 123

type : type1

status : IN_QUEUE_1

duration : 100

Event 1: 

id : 123

type : type1

status : IN_QUEUE_2

duration : 150

Type         average_time_in_queue1

type1          50

type2           ....

type3           ...

[johnhuang]

<base_search>
| chart avg(duration) AS avg_duration BY type status

[Hyperlemon]

i need to know the base search itself, its a bit complicated to figure out

[johnhuang]

Base search refers to the initial part of the query, for example, it may look like this:

index=<index_name> sourcetype=<source_type>
| table _time id, type, status, duration

As long as you have the fields ( d, type, status, duration) available, this should work:

<base_search>
| chart avg(duration) AS avg_duration BY type status
| eval LAST_VAL=0 
| foreach IN_QUEUE_* [
	| eval CURRENT_VAL=<<FIELD>> 
	| eval AVG_TIME_<<FIELD>>=<<FIELD>>-LAST_VAL 
	| eval LAST_VAL=CURRENT_VAL
]
| table type AVG_TIME_*

[yuanliu]

Can you illustrate your trial code for base search, as well as sample data (anonymize as necessary), then explain how it does not meet your requirements (when combined with johnhuang's solution)?  Without data, it is next to impossible to know how to do base search.

For example, are the fields id, type, status, duration already extracted?  Or are your events contain key-value pair literals separated by colons (:) and your Splunk ingestion extracts nothing?  What is the physical meaning of the field "duration"?  @johnhuang's code assumes that "duration" signifies "the time spent in Queue." Can you confirm or deny?

[Hyperlemon]

Yes that is correct I want the time spent inqueue. All the fields are extracted and can be used directly. Duration is basically the time elapsed since the object got created.

What i want to do is for each object i want to find the duration at which its state was "IN_QUEUE_2" and subtract it with the duration at which it was in state "IN_QUEUE_1", which gives me the time spent in the first queue, get the average of this value and group it by the object type. There will be many objects of each type , each object has a unique id and each object goes through state "IN_QUEUE1" -> "IN_QUEUE_2". 

So end data should look like :

Object type.       Avg inqueue time

type 1                       12

type 2                        20

... 

[yuanliu]

One key point (for outsiders) to digest the requirement is "Duration is basically the time elapsed since the object got created." I further speculate that you want to average across id.  Considering these, this may be what you wanted:

status IN (IN_QUEUE_1, IN_QUEUE_2) ``` add any additional filters ```
| stats max(duration) as enter_queue2 min(duration) as enter_queue1 by id type
| eval duration_in_queue1 = enter_queue2 - enter_queue1
| stats avg(duration_in_queue1) as average_duration_in_queue1 by type