# Kibana Table - Number of Hosts per Average CPU Load - Columns per CPU Load Range (0-50 / 50-80 / 80+ ) **URL:** \ **Category:** Kibana\ **Created:** [March 31, 2022, 12:00pm UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189 "2022-03-31T12:00:19Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![mazoutte](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mazoutte/32/120188_2.png) [@mazoutte](https://discuss.elastic.co/u/mazoutte)\ **Post date:** [March 31, 2022, 12:00pm UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/1 "2022-03-31T12:00:19Z")
Hello everybody, ELK Stack : 7.17.1 I have metricbeat agents installed on a bunched of Windows Domain Controllers, which are spread accross multiple AD Domains/forest. (50+) I have a field with the FDQN AD domain populated in each metricbeat documents, the field name is _dnsdomain_. (text + keyword) I want to create a table that shows the number of hosts depending on their average CPU Load (system.cpu.total.norm.pct) , then agreggate by dnsdomain. What I tried does not work, and I don't know if the standard Kibana table can achieve that. ![2022-03-31 13_26_25-Window](https://us1.discourse-cdn.com/elastic/original/3X/9/0/90c6705ccd7e7cc79baf614e1a87b02214234f70.png) I created each columns with this logic (here is the example for the \> 80 % category) : ```auto Metrics > Average Bucket Bucket : Filters : system.cpu.total.norm.pct >=0.8 Aggregation : Unique Count of host.name.keyword. Buckets : Split rows by Terms : dnsdomain.keyword ``` ![2022-03-31 13_48_51-Window](https://us1.discourse-cdn.com/elastic/original/3X/1/c/1caf91d73c96cb99cfe44b6fb37439d74374ea1d.png) The mistake here, it's not doing the Average on the CPU Load and then display it by category/columns. It's just displaying if 1 event matched the CPU filter during the Timespan selected on the upper right corner, then unique count the hostnames attached to the matched events. We can see the **AD Domain B** for example, the sum of all hosts by category is 3, whereas I have only 2 hosts on this domain. With what I want, 1 host should appear on only 1 category/column. I'm close to my goal but not in the right path. If you have any recommendation, that would be great ! Luc ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [March 31, 2022, 1:28pm UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/2 "2022-03-31T13:28:43Z")
I couldn't find a way to do it with the regular table, but you should to be able to get there using vega and a query like this (using logs sample data): ```auto GET kibana_sample_data_logs/_search { "size": 0, "aggs": { "per_country": { "terms": { "field": "geo.dest" }, "aggs": { "unique_count_below_4k": { "sum_bucket": { "buckets_path": "per_ip>below_4k" } }, "unique_count_above_4k": { "sum_bucket": { "buckets_path": "per_ip>above_4k" } }, "ip_count": { "cardinality": { "field": "ip" } }, "per_ip": { "terms": { "field": "ip", "size": 1000 }, "aggs": { "avg_bytes": { "avg": { "field": "bytes" } }, "below_4k": { "bucket_script": { "buckets_path": { "bytes": "avg_bytes" }, "script": "params.bytes < 4000 ? 1 : 0" } }, "above_4k": { "bucket_script": { "buckets_path": { "bytes": "avg_bytes" }, "script": "params.bytes >= 4000 ? 1 : 0" } } } } } } } } ``` In the response, for each `geo.dest`, you will have objects like this: ```auto "ip_count" : { "value" : 930 }, "unique_count_below_4k" : { "value" : 213.0 }, "unique_count_above_4k" : { "value" : 717.0 } ``` ---
**Author:** ![mazoutte](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mazoutte/32/120188_2.png) [@mazoutte](https://discuss.elastic.co/u/mazoutte)\ **Post date:** [March 31, 2022, 2:15pm UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/3 "2022-03-31T14:15:05Z")
Hi Joe, Thank you very much for the input. I was wondering if it was possible without Vega, I didn't take time to look around and learn Vega yet ... so it's a good opportunity to learn Vega now 😉 Does it seem possible with a Lens Formula maybe ? I will give it a try with the example you provided me. Have a great Day Luc ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [March 31, 2022, 2:19pm UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/4 "2022-03-31T14:19:56Z")
Unfortunately I don't think it's possible to do with Lens formula because it requires the "collapse" step - fetch the average for all the hosts, then throw away the ones you don't need. This is not something Lens formula is doing today. Filters can never do this, because it's a filter on the bucket level, not the document level. ---
**Author:** ![mazoutte](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mazoutte/32/120188_2.png) [@mazoutte](https://discuss.elastic.co/u/mazoutte)\ **Post date:** [April 4, 2022, 9:49am UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/5 "2022-04-04T09:49:30Z")
Hi Joe, So I got your search working with my data, unfortunately I'm not able to present it via a Table with Vega. Drawing Tables in Vega is not possible (I just started learning Vega actually). I need to find another way to present the datas I'm looking for. Thank you again for the input 😉 Have a good Day, Luc ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [April 5, 2022, 8:53am UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/6 "2022-04-05T08:53:27Z")
Right, real tables won't work. You can get close however using text marks like this: [Vega Editor](https://vega.github.io/editor/#/url/vega-lite/N4IgJAzgxgFgpgWwIYgFwhgF0wBwqgegIDc4BzJAOjIEtMYBXAI0poHsDp5kTykBaADZ04JAKyUAVhDYA7EABoQAEySYUqUAwBOgtCrVICUJNohSZ8gL5KA7jWX00YgAwul8GmSzO3SwUgAnnDaaADaoMjaANb62nBQmIogcLJQbMo0smRooIG5IABmNHCCyvoA8tpeWcmYgThw+rJsCFlIejYgAB4FxaXl6ADCgcKyyiEQdQ1N6GzambIdIF3pgvMFSGRk8RSYsyAIcEjySv1l+gAS8xBwOGy2IStWNpGmsej73UlKqemLOU0IHyQPOgxAVRqpxA9UazVa7U6Sl6oJKF2GoyyEzM0zhcwWiJWSi+SSBWx2fH2+iOJ2SYKuNzuDyeLysAF0lOlZMVAaAkN0aFMgTsHGhMNoGHBiTQoNEAEIncFwb6pJKsoA) It won't do layouting based on the contents, but it seems like your values are relatively uniform so it could work out. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 3, 2022, 8:53am UTC](https://discuss.elastic.co/t/kibana-table-number-of-hosts-per-average-cpu-load-columns-per-cpu-load-range-0-50-50-80-80/301189/7 "2022-05-03T08:53:54Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.