Splunk Search

Getting average response after joining 2 sourcetypes

dminev1
Explorer

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.

Tags (2)
0 Karma

woodcock
Esteemed Legend

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
0 Karma

dminev1
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)"
---
If this reply helps you, an upvote would be appreciated.
0 Karma

dminev1
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

dminev1
Explorer

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

0 Karma

ttilstra
New Member

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

0 Karma