topic Re: How to I combine data from 2 seperate events . in Splunk Search

How to combine data from 2 separate events?

Hyperlemon — Wed, 02 Nov 2022 02:43:03 GMT

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

Re: How to I combine data from 2 seperate events .

johnhuang — Tue, 01 Nov 2022 18:26:05 GMT

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

Re: How to I combine data from 2 seperate events .

Hyperlemon — Wed, 02 Nov 2022 06:38:25 GMT

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

Re: How to I combine data from 2 seperate events .

yuanliu — Wed, 02 Nov 2022 07:51:55 GMT

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?

Re: How to I combine data from 2 seperate events .

Hyperlemon — Wed, 02 Nov 2022 17:48:45 GMT

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

...

Re: How to I combine data from 2 seperate events .

johnhuang — Wed, 02 Nov 2022 18:28:04 GMT

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:

Re: How to I combine data from 2 seperate events .

yuanliu — Thu, 03 Nov 2022 05:01:34 GMT

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