topic How to limit results in a bar chart while still sorting by one of the values of column-split field? in Splunk Search

How to limit results in a bar chart while still sorting by one of the values of column-split field?

nickrally2009 — Tue, 17 Mar 2020 19:44:19 GMT

I have this search, where I am charting usage over id field (which is on x-axis) split by two columns - two values of Day field.

source=foo resource=foobar
earliest=-1d@d latest=now
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| rex max_match=0 "(?:'id': )(?P<id>[^,]+)|(?:'usage': )(?P<usage>[^,]+)" 
| chart latest(usage) over id by Day 
| where Yesterday!=Today | table id Yesterday Today | sort Today

Is there a way to limit the result to a certain number?
I tried | sort 5 Today .

| sort Today breaks when I add a limit - as if it stops sorting by Today and starts sorting by id.
When I use sort 5 Now it results in sorting by id while I actually want to sort by usage in Today column. Is that possible?

Thank you.

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

to4kawa — Tue, 17 Mar 2020 19:57:07 GMT

source=foo resource=foobar
earliest=-1d@d latest=now
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| rex max_match=0 "(?:'id': )(?P<id>[^,]+)|(?:'usage': )(?P<usage>[^,]+)" 
| chart latest(usage) over id by Day 
| streamstats count(eval(Yesterday!=Today)) as session
| where Yesterday!=Today AND session < 6
| table id Yesterday Today

see https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Chart

limit
- Syntax: limit=
- Description: Only valid when a column-split is specified. Use the limit option to specify the number of results that should appear in the output. When you set limit=N the top N values are retained, based on the sum of each series. If limit=0, all results are returned.

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

nickrally2009 — Tue, 17 Mar 2020 20:37:55 GMT

@to4kawa, thanks, but I am afraid limit is not working. I saw a few posts on this forum that limit does not necessarily work with chart command, and also, per documentation, "The limit and agg options are ignored if an explicit where-clause is provided", and I have a where clause in my search. Also, it does not look like head works with my search either. I am looking for a way to sort by values of a column-split field, and limit the data points to a specific number.

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

to4kawa — Tue, 17 Mar 2020 20:55:48 GMT

If you need sort, try before streamstats

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

nickrally2009 — Tue, 17 Mar 2020 21:17:00 GMT

@to4kawa, thanks, I tried the search you suggested, using streamstats count as session but unfortunately it resulted in only one data point: Yesterday/Today group for only one id, and based on the value of id it is obvious that it is sorted by id, not a value in column-split field.

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

to4kawa — Tue, 17 Mar 2020 21:24:41 GMT

Have you try sort before streamstats?

Re: How to limit results in a bar chart while still sorting by one of the values of column-split field?

nickrally2009 — Wed, 18 Mar 2020 00:26:59 GMT

yes, I did