Trying to build a table visualization with multiple aggregations

I want to build a table of alerts from and IDS where the rows are keyed on a term (alert name) then in the first column, the second column I want the alert category then I want then number of unique src IP addresses and the second column the number of dest IP addresses.

Ideally when you click on the src ip address count you drill down to a another visualisation with the IP addresses as key followed but count of alerts for that IP and so on down to the individual alert.

My first attempt at the top level table is below:

This is a long way from what I want and I am clearly on the wrong track.

Specifically I just want the count of unique terms in each column. That I can use to drill down to a lower level visualisation.

Can someone point me to some detailed documentation on building complex table visualisations. Everything I have found by searching is very simplistic. As are all the training videos. I would much prefer a written tutorial which describes how one is supposed to use the table building tool to build non trivial table.

I know how to construct a queries to do the aggregations necessary and could build the tables in a program but I would really not have to do that!

It is far from intuitive!

Hey, sorry for the late reply. This should be possible:

  • Configure a data table visualization with the following aggregations:
    • Bucket split rows terms on alert name
    • Bucket split rows terms on alert category (or alternatively, if the name implies the category, a metric "top hits" on the alert category with size 1 is also possible)
    • Metric "cardinality" on the src ip address field
    • Metric "cardinality" on the dest ip address field
  • This should give you your first view
  • For the drilled down view, create a second table visualization:
    • Bucket split rows on dest ip address field
    • Metric count (etc.)
  • Put the first overview table on a dashboard and save it
  • Put the second table on a separate dashboard and save it as well
  • Go back to the first dashboard, and configure a "dashboard to dashboard" drilldown from the context menu. Select the second dashboard as a target
  • Save and go into "view" mode
  • Now on the first dashboard, if you hover over a src ip address and press the (+) button, you will get a context menu entry for your drilldown - clicking it will take you to the second dashboard with the filter for this IP set:
    Screenshot 2021-03-31 at 11.25.43

Let me know whether this helps!

Also, not sure which version of the Elastic stack you are using, but if you are on 7.11 or 7.12 you could do this with the "Lens" datatable view as well. There's no big difference right now, but we are working on more sophisticated table features there.