Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to get stats average with a where clause in th...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

justinrowan

Explorer

04-13-2016
02:48 PM

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 ELAPSED*TIME for events in which ELAPSED*TIME 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!

1 Solution

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

jkat54

SplunkTrust

04-13-2016
09:16 PM

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

justinrowan

Explorer

04-14-2016
06:53 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

somesoni2

SplunkTrust

04-14-2016
07:29 AM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

justinrowan

Explorer

04-14-2016
07:46 AM

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?

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

somesoni2

SplunkTrust

04-14-2016
08:44 AM