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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...