Creating data table using group by like clause in Kibana?

Elastic Stack Kibana
1

I'm having a dataset fed from my database into an index which I created using logstash. I'm trying to create a table data visualization through Kibana for the data set available.

I've got to create a table data in Kibana which should look more a like this.

Sketch.png754×138 49.4 KB

In the above image I could simply get the date and the month seperately. The next column (num of Txn) is an aggregation from a groupby which contains the total hits of a particular userid, a string value. The other columns, (Gross Transaction Val) is a sum of a field (chargeamount) which is a string value and the other columns are response code counts for a particular userid in db.

I tried creating the table using metrics and buckets, but couldn't get it done completely. These are some snapshots of what I tried:

Sketch2.png1855×676 151 KB

The sub buckets:

Sketch3.png415×773 70.7 KB

These are the issues:

  1. I'm having a date and month fields separately. But is it possible to append both of them together in Kibana?
  2. How can I get a
    count metric being applied to a String value? Is there a way
    that I could convert it to int through Kibana?
  3. Is groupby clause in MySQL equal to aggregation buckets in
    Kibana?

Where am I going wrong? Any help could be appreciated.

2

Unfortunately, the data table visualization isn't going to get you the same table you have in your example.

In the data table visualization, think of the rows as buckets, and the columns as metrics calculated on the buckets. So the closest I think you could get to recreating your desired table is to select a date histogram bucket aggregation with a daily interval. Then, you can add metrics, like count, sum chargeamount, etc. Unfortunately you won't be able to have separate group by metrics as columns, because those are counts of separate buckets. You could then add a second bucket aggregation and split rows by response code, but then you'd have different rows when what you really want is separate columns.

1 Like