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 the maximum value from a timechart tabl...

- 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

avisram

Path Finder

04-12-2016
06:55 PM

Hi folks,

I am trying to obtain the maximum value from any cell in a table generated by a timechart search. For example, in the attached image the search string is:

```
index=_internal | timechart count by sourcetype
```

The time span automatically used is `1 day`

. Based on this I want to receive the single value of `70434`

which occurs under the `splunkd`

column on `4/12/16`

.

I can get the maximum value for each sourcetype with the following search:

```
index=_internal | timechart count by sourcetype | stats max(*) as *
```

However this doesn't get me the single value I want.

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 the maximum value from a timechart table?

sideview

SplunkTrust

04-12-2016
06:59 PM

1) You want to use `untable`

to turn the chart/timechart style result set into a "stats style" result set,

then you can find the maximum value along with both the time value and the relevant value of the split-by field.

Using your index=_internal example it would look like

```
index=_internal | timechart count by sourcetype | untable _time sourcetype count | sort - count | head 1
```

2) the other way to do it is to just use bin and stats instead of timechart in the first place, like so:

```
index=_internal | bin _time span="1h" | stats count by _time sourcetype | sort - count | head 1
```

but then you have to do the binning by hand with the bin command.

Highlighted

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

MuS

SplunkTrust

04-12-2016
07:01 PM

Hi avisram,

based on your example you can run this search:

```
index=_internal
| timechart count by sourcetype
| stats max(*) AS *
| transpose
| stats max(*) AS *
| rename column AS sourcetype "row 1" AS count
```

and the result will look like this:

Hope this helps ...

cheers, MuS

Highlighted
##

Thanks MuS and sideview for your responses. Both of these methods give me the desired result. Is one more efficient than the other?

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

Re: How to get the maximum value from a timechart table?

avisram

Path Finder

04-12-2016
07:08 PM

Highlighted
##

Run the options with your real life events ( and your search ) and you will see in the job inspector which one will win 😉 I reckon it's @sideview option 2

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

Re: How to get the maximum value from a timechart table?

MuS

SplunkTrust

04-12-2016
07:09 PM

Highlighted
##

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

Re: How to get the maximum value from a timechart table?

sideview

SplunkTrust

04-12-2016
07:16 PM

My money is on @sideview option 2! I love the 2-dimensional `max(*) as *`

craziness though.

Highlighted
##

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

Re: How to get the maximum value from a timechart table?

avisram

Path Finder

04-12-2016
07:31 PM

Actually MuS' was faster - 1.76 to 1.894 over 274,899 events.

Highlighted
##

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

Re: How to get the maximum value from a timechart table?

sideview

SplunkTrust

04-13-2016
12:17 AM

`index=_internal | head 100000 | bin _time span="1h" | stats count by _time sourcetype | sort - count | head 1`

is quite a lot faster than the other options. I'm glad they all work for you though. 😃

Highlighted
##

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

Re: How to get the maximum value from a timechart table?

avisram

Path Finder

04-12-2016
07:41 PM

As I only need the value I modified MuS' search to the following:

index=_internal | timechart count by sourcetype| stats max(*) as * | transpose | stats max("row 1") as value

Thanks to both of you for your responses!

Highlighted
##

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

Re: How to get the maximum value from a timechart table?

Roopaul

Explorer

12-23-2016
04:13 PM

Hi, I have a similar situation. But I need min,max and avg for each sourcetype. Using your query only one calculation can be done. Is there a way to include all 3 in a single table.

index=* | extract pairdelim="," kvdelim=":" | table _time,* xml,* json

| stats max(* ) AS *

| transpose

| rename column AS sourcetype "row 1" AS Max

[*xml and *json because the fields are dynamic]