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?
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 ]
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)).
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)).
| 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")
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.
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