Can I show multiple aggregations in a single datatable in Kibana?

Elastic Stack Kibana
1

Hello. I am very new to creating Visualizations and Dashboards with Kibana, so if my approach is way off, please help orient me.

I am recording usage statistics for my website, and I would like to create a datagrid of information about users from the log data.

My data looks basically like such:

      {
        "_index" : "logstash",
        "_type" : "_doc",
        "_id" : "jZBv_WwBHANqO5FAj8e5",
        "_score" : 1.0,
        "_source" : {
          "username" : "joe.shmoe",
          "company" : "ACME",
          "tab" : "f11008b7-50b7-4d49-8c37-be565f2aa27c",
          "@timestamp" : "2019-09-04T18:00:42.577Z",
          "companyId" : 30,
          "userId" : 12632,
          "destinationPath" : "/",
          "sourcePath" : "/login"
        }
      },
      {
        "_index" : "logstash",
        "_type" : "_doc",
        "_id" : "jZBv_WwBHANqO5FAj9j6",
        "_score" : 1.0,
        "_source" : {
          "username" : "joe.shmoe",
          "company" : "ACME",
          "tab" : "f11008b7-50b7-4d49-8c37-be565f2aa27c",
          "@timestamp" : "2019-09-04T18:00:42.577Z",
          "companyId" : 30,
          "userId" : 12632,
          "destinationPath" : "/matrix",
          "sourcePath" : "/"
        }
      },
      {
        "_index" : "logstash",
        "_type" : "_doc",
        "_id" : "jZBv_WwBHANqO5FAfh7e",
        "_score" : 1.0,
        "_source" : {
          "username" : "joe.shmoe",
          "company" : "ACME",
          "tab" : "f11008b7-50b7-4d49-8c37-be565f2aa27c",
          "@timestamp" : "2019-09-04T18:00:42.577Z",
          "companyId" : 30,
          "userId" : 12632,
          "destinationPath" : "/matrix/3",
          "sourcePath" : "/matrix"
        }
      }

And then imagine that is for one user, and then I have a similar sets for other users.

Is it possible to produce a Visualization that can show something like this for each user, all in the same grid:

Company Name | User Name | Pages Visited | Login Count

(where pages visited could be the count of log entries for a company, and Login Count could be the number of times the sourcePath was "/login" and the destinationPath was "/")

Or is this something I should be using a different tool to generate?

Thanks much!

2

I have found a visualization type called TSVB, and it almost accomplishes the requirements, but it only seems to want to allow me columns if they can be derived from numerical results.

So I am able to show "Pages Visited" and "Login Count", but I can't tell it to just produce the "User Name". Also, I would like to be able to have it show a column for MAX(@timestamp) where sourcePath = "/login" and destinationPath = "/" -- aka 'Last Login'.

As previously, any help is appreciated!

3

Well, based on everything I've found so far, it doesn't seem like Kibana is designed for the type of report I'm trying to generate. And in fact, the very usage of the term report seems to fall outside of the scope of Kibana. It seems more like real time metrics of specific smaller buckets of data (as opposed to reporting the number of page views, logins, at time on site for 900+ clients in a grid).

Also, the documentation seems a little terse... trying to understand how the TSVB Markdown works fromthe standard documenation seems impossible. Even copying the suggested "The following variables can be used in the Markdown" and pasting them into the markdown textbox -- they don't render. Even based on the screenshots provided here there must be something wrong with my data? The other TSVB options at least show something. But that is all an aside, as I don't think the the Markdown was going to work for me (unless it was, via the #each _all keywords).

4

Thanks for the feedback- I think you're running into a common problem here, which I agree that the documentation is not oriented for solving.

Conceptually, Kibana's built-in tools let you visualize simple aggregation queries against Elasticsearch. Few of the tools let you modify individual documents. The aggregated structure is nested, and then flattened to render into a table like you are seeing. There are not built-in options in Kibana to let you do post-processing of these tables. However, there are other ways of getting the data.

  • SQL is supported in the Canvas dashboard tool- if you can express your query using Elasticsearch SQL this might be the easiest option
  • You might be able to run a data frame transform which can create new documents that are pre-aggregated in ways you want, and then do transformations against those
  • You could use a Vega visualization in Kibana which lets you write a custom Elasticsearch query and post-process the results in your browser
  • There are third-party plugins available that can give you alternative table-like interfaces for Kibana which might be what you are expecting
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.