Limit series in Log Analytics histogram

[Deleted]

I have a Log Analytics query that produces a histogram based on some data being ingested, by ending the query with a 'render timechart' command. This results in a graph with over 12 series plotted across the x-axis (time axis). Since this is a histogram with the backing table resembling something like...

Computer             Time              Value

Machine a             09:00              5

Machine a             10:00              7

Machine a             11:00              10

Machine b             09:00              8

Machine b             11:00              10

Machine c              10:00              14

...

Machine z               09:00             12

Is there a best way to limit the series in the histogram to only a certain number of machines (not rows, since as seen above there could be multiple rows per machine)? In other words, how would I limit the timechart to only show series for the top 2 Machines based on their values. If I simply use 'top 5 by Value', or 'top 5 by Computer', it will only take the top x rows...not the top x Machines and all their respective rows.

If this is not possible, when pinning these results to an Azure Portal dashboard, is there anyway to exclude the 'OTHERS' aggregation that's automatically created?

Thanks for any help anyone can provide here.

[#2]

What about using the distinct operator to filter prior to showing the  'top 5 by Value', or 'top 5 by Computer'

https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/distinct-operator

[#3]

Hi

If I understand the question correctly I think if you put:

| summarize arg_max(Value, *) by Computer

before 

| render timechar

I think you might achieve the desired effect.

arg_max() - https://docs.loganalytics.io/docs/Language-Reference/Aggregation-functions/arg_max()

Let me know if it works.

[#4]

Thanks for your response Orion.

I couldn't get distinct to work because it didn't have the effect of filtering that I wanted. I did get one solution to work. I basically performed an initial search to find the top 5 machines by average value, then inner joined that with the time-series search I originally used. That way, only the time series for the top 5 machines (based on their overall average) were shown.

[#5]

Thanks Stanislav!

Your response actually showed what I was missing in my solution, which was a way to determine the top 5 machines before displaying a histogram. The solution I used, seen in the response above, basically does this first, and inner joins that with the time-series search.

[#6 · Accepted solution]

If this is the thing you wanted to achieve:

https://cloudadministrator.wordpress.com/2018/03/22/top-10-charts-in-azure-log-analytics-and-applica...

I would have pointed you earlier but I didn't understand exactly the request I guess. Reply yes if the logic in that blog post matches what you've wanted to achieve. That way I can mark this reply as answer for future people to see it. 

[#7]

Yes, this is exactly it! Thanks, and apologies for not being clearer in my question.

[#8]

No problem. Glad that you've solved the problem.

[#9]

Thanks Stan.  I would just make one refinement to the query, using Top 10 in the first statement.

Also in your blog, you can always point to a query in action in our demo environment.  Here is the link to the query below.

let TopComputers = Perf

| where ObjectName == 'Processor' and CounterName == '% Processor Time' and InstanceName == '_Total'

| summarize AggregatedValue = avg(CounterValue) by Computer

| top 10 by AggregatedValue desc

| project Computer;

Perf

| where ObjectName == 'Processor' and CounterName == '% Processor Time' and InstanceName == '_Total' and Computer in (TopComputers)

| summarize AggregatedValue = avg(CounterValue) by Computer, bin(TimeGenerated, 1h) | render timechart