Splunk Search

How to combine two searches from same index

fredv44
Explorer

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 ]

 

 

 

 

Labels (2)
0 Karma
1 Solution

fredv44
Explorer

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

View solution in original post

0 Karma

fredv44
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Explorer

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.

0 Karma

BahadirS
Path Finder

@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 

 

 

 

Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...