Get report into email

Questions
,
1

Hi Team,

I am working on the Elasticseearch data transformation to email part. need some help and suggestions to proceed further.

  1. I have http-request which get the details to elastic using GET API request for the data which contains one sum aggregation and 5 bucket aggregation. Here I create the respective data-table in the Kibana to download the data in to csv

  2. May I know how can achieve similarly using n8n. I would like to get transform the data to spreadsheet and send an email every 24 hours

Below is the workflow and sample json request which need to converted into table format to send an email as an attachment

image
image986×172 12.7 KB

JSON Example

{
“took” : 340,
“timed_out” : false,
“_shards” : {
“total” : 1,
“successful” : 1,
“skipped” : 0,
“failed” : 0
},
“hits” : {
“total” : {
“value” : 5,
“relation” : “eq”
},
“max_score” : null,
“hits” : [ ]
},
“aggregations” : {
“timestamp” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : 1696501413397,
“key_as_string” : “2023-10-05T10:23:33.397Z”,
“doc_count” : 1,
“action” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “dilip”,
“doc_count” : 1,
“main-id” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “-”,
“doc_count” : 1,
“main-url” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “-”,
“doc_count” : 1,
“sub-url” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “https://google.com”,
“doc_count” : 1,
“sub-name” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “manager”,
“doc_count” : 1,
“user-name” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “bot”,
“doc_count” : 1,
“main-count” : {
“value” : 3.0
}
}
]
}
}
]
}
}
]
}
}
]
}
}
]
}
}
]
}
},
{
“key” : 1696501411000,
“key_as_string” : “2023-10-05T10:23:31.000Z”,
“doc_count” : 1,
“action” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “king”,
“doc_count” : 1,
“main-id” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “manager-list”,
“doc_count” : 1,
“main-url” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “https://google.in”,
“doc_count” : 1,
“sub-url” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “https://google.ss”,
“doc_count” : 1,
“sub-name” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “-”,
“doc_count” : 1,
“user-name” : {
“doc_count_error_upper_bound” : 0,
“sum_other_doc_count” : 0,
“buckets” : [
{
“key” : “bot”,
“doc_count” : 1,
“sub-count” : {
“value” : 0.0
}
}
]
}
}
]
}
}
]
}
}
]
}
}
]
}
}
]
}
}
]
}
}
}

Here I would like to get in the table format for each of the label name mentioned
Thanks

2

hi @DilipChiru

What table format are you expecting to have? Please, provide an example, as the json is very nested and it is difficult to understand how the table should look.

3

Hi @barn4k ,

Thanks for the reply. Below is the spreadsheet example screenshot

image
image1060×144 46 KB

Once the data we have from the Elasticsearch , we need to transfer them into spreedsheet to send an email every 1hour.

Please help me with this

Thanks

4

the JSON format here is pretty nasty, but you can map elements directly

5

Hi @barn4k ,

Basically, the JSON is dynamic and it is coming from the Elastic Http Request the count of the aggregation will be keep vary depends upon the time. For example. sometimes then agg loop will have more than 10

We can’t load it manually with the code nodes to be add it.

Thanks

6

hi @DilipChiru

try this one (almost entirely dynamic, except the first timestamp field)

7

Hi @barn4k ,

Thanks it is working fine as expected.

Thanks