topic Getting average response after joining 2 sourcetypes in Splunk Search

Getting average response after joining 2 sourcetypes

dminev1 — Wed, 30 Sep 2020 04:39:15 GMT

Hello Splunk Community,

I am trying to create dashboard with the following query but the query returns no results. I am using the query to:
extract the batch size and duration (they are in different source types. duration is in sourcetype = "AAA"and batch size is in sourcetype = "BBB") than extract the batch size =1, find the duration for that same request by joint both events using transaction ID and search for batch size = 1. But when I add the following line:

| timechart span=5m eval(round(avg(duration),0)) as AVG_Response_Time(ms) | search "AVG_Response_Time(ms)" > 10

the query return noting.

index =ose_index source="ent-splunk-pyx" (svc=/service/detokenize AND "duration") OR ("Batch Detokenization Operation, batch size:")
| rex field=raw "Batch Detokenization Operation, batch size: (?\d+)"
| rex field=_raw "txid=(?([a-z0-9.-]+))"
| eval duration = round(duration/1000, 3)
| stats max(duration) as Duration values(Batch_Size) as Batch_Size by ID
| search Duration=* Batch_Size=1
| timechart span=5m eval(round(avg(duration),0)) as AVG_Response_Time(ms) | search "AVG_Response_Time(ms)" > 10

If I remove the last line and use "| stats avg(Duration) " I am getting the avg result but this is not the way I want it. I must use "| timechart span=5m eval(round(avg(duration),0)) as AVG_Response_Time(ms) | search "AVG_Response_Time(ms)" > 10" because it is a part of a template that the company is using and I can't change this part.

Re: Getting average response after joining 2 sourcetypes

ttilstra — Wed, 30 Sep 2020 04:35:03 GMT

Try rewriting your last line as follows:
| eval(round(avg(duration),0)) as AVG_Response_Time(ms) | timechart span=5m | search "AVG_Response_Time(ms)" > 10,try rewriting your line as follows:

| eval(round(avg(duration),0)) as AVG_Response_Time(ms) | timechart span=5m | search "AVG_Response_Time(ms)" > 10

Re: Getting average response after joining 2 sourcetypes

richgalloway — Tue, 17 Mar 2020 16:57:30 GMT

Try breaking the timechart command apart.

| timechart span=5m avg(Duration) as ART
| eval ART=round(ART,0)
| where ART > 10
| rename ART as "AVG_Response_Time(ms)"

Re: Getting average response after joining 2 sourcetypes

dminev1 — Tue, 17 Mar 2020 17:04:19 GMT

I did try your suggestion and broke timechart. Still no result for average

Re: Getting average response after joining 2 sourcetypes

richgalloway — Tue, 17 Mar 2020 17:46:34 GMT

I missed it the first time. "duration" should be "Duration". Updated my answer.

Re: Getting average response after joining 2 sourcetypes

dminev1 — Wed, 30 Sep 2020 04:39:22 GMT

I noticed this and made the change. Still no result

This is what I have now:

index =ose_index source="ent-splunk-pyx" (svc=/service/detokenize AND "duration") OR ("Batch Detokenization Operation, batch size:")
| rex field=raw "Batch Detokenization Operation, batch size: (?\d+)"
| rex field=_raw "txid=(?([a-z0-9.-]+))"
| eval duration = round(duration/1000, 3)
| stats max(duration) as Duration values(Batch_Size) as Batch_Size by ID
| search Duration=* Batch_Size=1
| timechart span=5m avg(Duration) as ART
| eval ART=round(ART,0)
| where ART > 10
| rename ART as "AVG_Response_Time(ms)"

Re: Getting average response after joining 2 sourcetypes

woodcock — Wed, 18 Mar 2020 02:07:44 GMT

Try this:

index =ose_index  source="*ent-splunk-pyx*" (svc=/service/detokenize AND "duration") OR ("Batch Detokenization Operation, batch size:")
| rex field=_raw "Batch Detokenization Operation, batch size: (?\d+)" 
| rex field=_raw "txid=(?([a-z0-9_\.-]+))"
| eventstats values(Batch_Size) AS Batch_Size BY ID
| search duration="*" AND Batch_Size="1"
| timechart span=5m eval(round(avg(duration/1000),0)) as AVG_Response_Time(ms) 
| search "AVG_Response_Time(ms)" > 10

Re: Getting average response after joining 2 sourcetypes

dminev1 — Wed, 18 Mar 2020 12:28:51 GMT

When I run the query I am getting the following error:

Error in 'timechart' command: The eval expression has no fields: 'round(avg(duration/1000),0)'.