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!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...