Tabular graph display group by Month-Year format-Elastic search

Grafana Elasticsearch
1

Hi,

Looking to display data count group by month-year(Ex: Apr- 19) in table visual, will be of two columns MonthYear and count of data for that month using elastic search. Please suggest if anyone have done this.

Thanks,

2

Hi,
Are you perhaps want to do something similar or close to this?
image

Thanks
pak

3

@pakblangkon85 Thanks for taking a look. I am looking for something like this, which is done using MySql date fomrat function and Group by date.
image .

4

Perhaps since the month column value in my sample is slightly different with what you hope, it’s became confusing.

IMO, basically what you need to do is to add another field / column to keep the “month-year” value based on the date record, just like what you define on that query, “Jan-19”, “Feb-19”, etc.
And then you can use this field in the ElasticSearch / Lucene query in the “group by terms” option.

pak

1 Like
5

Thanks @pakblangkon85. Is there any way it can be done through script for elastic search in grafana?

6

Hi @amey1809.

Unfortunately, I haven’t found the answer to your question, and even I don’t know if this thing is possible in the first place, seeing the format of the query panel that Grafana gave us.
I have searched both in the documentation and the community but there is no extensive explanation as how to do a complex Lucene query to achieve this result.
Still waiting for an answer myself regarding similar case for table panel, but nobody has answered yet.

I am very sorry I can’t help you that much.

pak

2 Likes
7

@pakblangkon85 Is it possible, if you could tell how you did the table format for month and count you replied intially to this thread?

8

Sure.
Here is the setup on the query :

image
image1216Ă—460 16.1 KB

On the group by column, change the date histogram to terms and use the specific field that you want.

If you need to show only 2019 data, perhaps you can add filter in the query box, something like this : creationdate:[“2019-01-01” TO *]

As for the panel customization, I am sure you can explore it by yourself, and find a proper set that suitable to your needs.

I guess that’s all.
Just let me know if you still have any difficulties with this.

-pak-

1 Like
9

use date_histogram aggregation with Interval “1M” AND format:“yyyy-MM” or whatever you want a format. The code will look like:

“aggs”: {
“dateAggs”: {
“date_histogram”: {
“field”: “DATE_FIELD”,
“interval”: “1M”,
“format”: “yyyy-MM”
}

          }
10

Hi Aleem,
where can i add format? I can’t find any way to add this formatting.

Thanks!