Kibana Table - Number of Hosts per Average CPU Load - Columns per CPU Load Range (0-50 / 50-80 / 80+ )

[mazoutte]

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.

I created each columns with this logic (here is the example for the > 80 % category) :

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

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

[flash1293 (Joe Reuter)]

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

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:

"ip_count" : {
            "value" : 930
          },
          "unique_count_below_4k" : {
            "value" : 213.0
          },
          "unique_count_above_4k" : {
            "value" : 717.0
          }

[mazoutte]

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

[flash1293 (Joe Reuter)]

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.

[mazoutte]

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

[flash1293 (Joe Reuter)]

Right, real tables won't work. You can get close however using text marks like this: Vega Editor

It won't do layouting based on the contents, but it seems like your values are relatively uniform so it could work out.