How to combine two searches from same index

[fredv44]

Hi,
From these logs (unique index):

2022-03-16 16:43:43.279 traceId="1234" svc="Service1" url="/customer/{customerGuid}" duration=132
2022-03-16 16:43:43.281 traceId="5678" svc="Service3" url="/customer/{customerGuid}" duration=219
2022-03-16 16:43:43.284 traceId="1234" svc="Service2" url="/user/{userGuid}" duration=320
2022-03-16 16:43:44.010 traceId="1234" svc="Service2" url="/shop/{userGuid}" duration=1023
2022-03-16 16:43:44.299 traceId="1234" svc="Service3" url="/shop/{userGuid}" duration=822
2022-03-16 16:43:44.579 traceId="5678" svc="Service2" url="/info/{userGuid}" duration=340
2022-03-16 16:43:44.928 traceId="9012" svc="Service1" url="/user/{userGuid}" duration=543

how to extract the following information?

• target only traceIds which trigger at least one operation to 'Service2'
• for each traceId, get first (txStart) and last (txEnd) event timestamps (including all logs for this traceId, not only those of Service2)
• build stats around 'Service2'

Given the example above, I would like to get the following report:

traceId	txStartTs	txEndTs	nbCallsService2	avgDurationService2
1234	2022-03-16 16:43:43.279	2022-03-16 16:43:44.299	2	671.5
5678	2022-03-16 16:43:43.281	2022-03-16 16:43:44.579	1	340

Is it possible achieve this in one query? I tried to append, join searches but it does not go anywhere 😞

Ideally, I need something like like (in broken terms):

index=idx
| stats earliest(_time), latest(_time) by traceId
| join traceId [ search index=idx svc="Service2" | stats count avg(duration) by traceId ]

[fredv44 · Accepted solution]

Thanks to @ITWhisperer and @BahadirS , it looks like the solution would be:

index=...
| stats min(_time) as txStartTs max(_time) as txEndTs avg(eval(if(svc="Service2",duration,null))) as avgDuration count(eval(svc="Service2")) as nbCallsService2 by traceId
| where nbCallsService2>0

It works well with only one search, running stats on the bigger set of events, and building stats on Service2 by filtering out other services through: eval(if(svc="Service2",field,null)).

[ITWhisperer]

| makeresults
| eval _raw="2022-03-16 16:43:43.279 traceId=\"1234\" svc=\"Service1\" url=\"/customer/{customerGuid}\" duration=132
2022-03-16 16:43:43.281 traceId=\"5678\" svc=\"Service3\" url=\"/customer/{customerGuid}\" duration=219
2022-03-16 16:43:43.284 traceId=\"1234\" svc=\"Service2\" url=\"/user/{userGuid}\" duration=320
2022-03-16 16:43:44.010 traceId=\"1234\" svc=\"Service2\" url=\"/shop/{userGuid}\" duration=1023
2022-03-16 16:43:44.299 traceId=\"1234\" svc=\"Service3\" url=\"/shop/{userGuid}\" duration=822
2022-03-16 16:43:44.579 traceId=\"5678\" svc=\"Service2\" url=\"/info/{userGuid}\" duration=340
2022-03-16 16:43:44.928 traceId=\"9012\" svc=\"Service1\" url=\"/user/{userGuid}\" duration=543"
| multikv noheader=t
| table _raw
| eval _time=strptime(_raw,"%F %T.%3N")
| extract
| fields - _raw
| stats min(_time) as txStartTs max(_time) as txEndTs values(svc) as svcs avg(duration) as avgDuration count(eval(svc="Service2")) as nbCallsService2 by traceId
| where nbCallsService2>0
| fieldformat txStartTs=strftime(txStartTs,"%F %T.%3N")
| fieldformat txEndTs=strftime(txEndTs,"%F %T.%3N")

[fredv44]

Thanks for the suggestion.

Your query doesn't return what I am looking for though: average calculation is only for lines matching svc="Service2".

Hence report should return 671.5 for traceId=1234, and not 574.25 as your query returns, which is the average of all events for this traceId, regardless of the service type.

[BahadirS]

@fredv44 

Why dont you filter out other services before calculating?

Also you can edit stats row like this for specific results.

avg(eval(if(svc="Service2",duration,null)) as avgDuration