How to build correlationId (transactionId) search?

[LealP]

Hi,

Below is an example of my use case:

timestamp	messageId	correlationId	region	category	trace
17/05/2023 00:00	1	correlationA	UCAN	orders	START
17/05/2023 00:01	2	correlationA	UCAN	orders	FLOW
17/05/2023 00:02	3	correlationA	UCAN	orders	FLOW
17/05/2023 00:03	4	correlationA	UCAN	orders	FLOW
17/05/2023 00:04	5	correlationA	UCAN	orders	FLOW
17/05/2023 00:05	6	correlationA	UCAN	orders	FLOW
17/05/2023 00:06	7	correlationA	UCAN	orders	FLOW
17/05/2023 00:07	8	correlationA	UCAN	orders	END
17/05/2023 00:10	9	correlationB	EMEA	accounts	START
17/05/2023 00:11	10	correlationB	EMEA	accounts	FLOW
17/05/2023 00:12	11	correlationB	EMEA	accounts	FLOW
17/05/2023 00:13	12	correlationB	EMEA	accounts	FLOW
17/05/2023 00:14	13	correlationB	EMEA	accounts	FLOW
17/05/2023 00:15	14	correlationB	EMEA	accounts	FLOW
17/05/2023 00:16	15	correlationB	EMEA	accounts	EXCEPTION

And the expected output:

timestamp	correlationId	region	category	status	#records	duration
17/05/2023 00:10	correlationA	UCAN	order	SUCCESS	6	00:07:00
17/05/2023 00:16	correlationB	EMEA	accounts	ERROR	5	00:06:00

- the goal is to have one row per correlationId 

- the column STATUS should be calculated with the following rule: if there is a row with trace=END it means that the flow ran successfully and the status should be "SUCCESS" and if there is a row with trace=EXCEPTION it means that an error occurred and the status should be "ERROR"

- the number of records should consider only the rows with trace=FLOW

- the duration should be the time elapsed from the row with trace=START until the row trace=END or trace=EXCEPTION

Can you help on building the query?

[ITWhisperer]

Try something like this

| stats earliest(_time) as start latest(_time) as end last(trace) as status values(region) as region values(category) as category count as records by ocrrelationId
| eval timestamp = end
| eval duration = end-start
| fieldformat duration = tostring(duration, "duration")