Splunk Search
Highlighted

How to get stats average with a where clause in the same search?

Explorer

If I run this search:

index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" | stats avg(ELAPSED_TIME)

I get the expected average.
If I run the following search, I get the expected results of events where the ELAPSED_TIME field is less than 1.

index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" ELAPSED_TIME < 1

But if I combine the two searches (looking for an average ELAPSEDTIME for events in which ELAPSEDTIME is less than 1):

index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" ELAPSED_TIME < 1 | stats avg(ELAPSED_TIME)

I get 0 as a result.

What am I missing to achieve the desired results?

Thanks!

0 Karma
Highlighted

Re: How to get stats average with a where clause in the same search?

SplunkTrust
SplunkTrust

Curious if ELAPSED_TIME is always equal to 0 when it is less than 1?

I'm not seeing the same results in my testing. Note my results changed a bit due to the event count changing in between searches:
alt text

0 Karma
Highlighted

Re: How to get stats average with a where clause in the same search?

Explorer

I wish it was that straigthforward, but no, all of the values are between 0 and 1 with the exception of a few (which is why I'm trying to filter to just the ones that are less than 1).

If I do stats count(ELAPSED_TIME) I get the correct number of events. But if I do any other function of stats (avg, sum, min, max), I get a result of 0 every time.

0 Karma
Highlighted

Re: How to get stats average with a where clause in the same search?

SplunkTrust
SplunkTrust

Give this a try

 index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" ELAPSED_TIME < 1 | eval ELAPSED_TIME=tonumber(ELAPSED_TIME)| stats avg(ELAPSED_TIME)

OR

 index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" ELAPSED_TIME < 1 | eval ELAPSED_TIME=ELAPSED_TIME/1.0| stats avg(ELAPSED_TIME)

View solution in original post

Highlighted

Re: How to get stats average with a where clause in the same search?

Explorer

This appears to work:

index=main sourcetype=Metrics MEASUREMENT_POINT_NAME = "Test" ELAPSED_TIME < 1 | eval ELAPSED_TIME=tonumber(ELAPSED_TIME)| stats avg(ELAPSED_TIME)

I'll have to verify the accuracy of it, but it appears to correct. Can you explain why Splunk interpreted this field as non-numeric by default? It should be noted that these events are db inputs from DB Connect2, maybe that has something to do with it?

0 Karma
Highlighted

Re: How to get stats average with a where clause in the same search?

SplunkTrust
SplunkTrust

I've seen weird (non-obvious) results with data from DB Connect, specially for numeric data. I just use to_number wherever required though.

0 Karma