**I have below data stored in an elasticsearch index
{
"prospector" => {
"type" => "log"
},
"message" => "11/1/2018 10:05:00,fabrice,eldo,7,100",
"@timestamp" => 2019-04-17T05:44:47.133Z,
"SITE" => "GRENOBLE",
"source" => "/home/msk/strgrept/CAD_REPORTS/USAGE_REPORTS/2018/try/PA_lic_usage4.csv",
"LIC_TOTAL" => 100,
"@version" => "1",
"fields" => {
"document_type" => "usage-type"
},
"GROUP" => "TRD",
"division" => "TRD_GNB",
"DATE" => "11/1/2018 10:05:00",
"LIC_USAGE" => 7,
"offset" => 933,
"tags" => [
[0] "beats_input_codec_plain_applied"
],
"USER" => "fabrice",
"LIC_FEATURE_NAME" => "eldo",
"beat" => {
"version" => "6.4.2",
"name" => "dlhl2117",
"hostname" => "dlhl2117" },
"host" => { "name" => "dlhl2117" },
"TIME_STAMP" => "2018-11-01T04:35:00.000Z",
"input" => { "type" => "log" } }
{
"prospector" => {
"type" => "log" },
"message" => "11/1/2018 10:05:00,narwal,eldo,2,100",
"@timestamp" => 2019-04-17T05:44:47.132Z,
"SITE" => "GREATER NOIDA",
"source" => "/home/msk/strgrept/CAD_REPORTS/USAGE_REPORTS/2018/try/PA_lic_usage4.csv",
"LIC_TOTAL" => 100,
"@version" => "1",
"fields" => {
"document_type" => "usage-type" },
"GROUP" => "ADG",
"division" => "ADG_MICRO",
"DATE" => "11/1/2018 10:05:00",
"LIC_USAGE" => 2,
"offset" => 603,
"tags" => [
[0] "beats_input_codec_plain_applied" ],
"USER" => "narwal",
"LIC_FEATURE_NAME" => "eldo",
"beat" => {
"version" => "6.4.2",
"name" => "dlhl2117",
"hostname" => "dlhl2117" },
"host" => { "name" => "dlhl2117" },
"TIME_STAMP" => "2018-11-01T04:35:00.000Z",
"input" => { "type" => "log" } }
{
"prospector" => {
"type" => "log"
},
"message" => "11/1/2018 10:00:00,paul,eldo,1,100",
"@timestamp" => 2019-04-17T05:44:47.132Z,
"SITE" => "GRENOBLE",
"source" => "/home/msk/strgrept/CAD_REPORTS/USAGE_REPORTS/2018/try/PA_lic_usage4.csv",
"LIC_TOTAL" => 100,
"@version" => "1",
"fields" => {
"document_type" => "usage-type"
},
"GROUP" => "TRD",
"division" => "TRD_MEMS",
"DATE" => "11/1/2018 10:00:00",
"LIC_USAGE" => 1,
"offset" => 489,
"tags" => [
[0] "beats_input_codec_plain_applied"
],
"USER" => "paul",
"LIC_FEATURE_NAME" => "eldo",
"beat" => {
"version" => "6.4.2",
"name" => "dlhl2117",
"hostname" => "dlhl2117"
},
"host" => {
"name" => "dlhl2117"
},
"TIME_STAMP" => "2018-11-01T04:30:00.000Z",
"input" => {
"type" => "log"
}
}
{
"prospector" => {
"type" => "log"
},
"message" => "11/1/2018 10:00:00,shamsi,eldo,1,100",
"@timestamp" => 2019-04-17T05:44:47.132Z,
"SITE" => "GREATER NOIDA",
"source" => "/home/msk/strgrept/CAD_REPORTS/USAGE_REPORTS/2018/try/PA_lic_usage4.csv",
"LIC_TOTAL" => 100,
"@version" => "1",
"fields" => {
"document_type" => "usage-type"
},
"GROUP" => "TRD",
"division" => "TRD_MEMS",
"DATE" => "11/1/2018 10:00:00",
"LIC_USAGE" => 1,
"offset" => 265,
"tags" => [
[0] "beats_input_codec_plain_applied"
],
"USER" => "shamsi",
"LIC_FEATURE_NAME" => "eldo",
"beat" => {
"version" => "6.4.2",
"name" => "dlhl2117",
"hostname" => "dlhl2117"
},
"host" => {
"name" => "dlhl2117"
},
"TIME_STAMP" => "2018-11-01T04:30:00.000Z",
"input" => {
"type" => "log"
}
}
I have to execute the below query on this index itself, the output of which is to be stored in an new field based on matching division field in the same index.
{
"aggs": {
"hr": {
"date_histogram": {
"field": "TIME_STAMP",
"interval": "5m",
"format": "dd-MM-yyyy hh:mm:ss" },
"aggs": { "site": { "terms": { "field": "SITE.keyword" }, "aggs": { "group": { "terms": { "field": "GROUP" }, "aggs": { "division": { "terms": {"field": "DIVISION" }, "aggs": { SUM_LICU_BY_DIVISION": { "sum": { "field": "LIC_USAGE" } } } } } } } } } } } }
For example, field "SUM_LICU_BY_DIVISION" of given DIVISION( in output of query) should be stored in corresponding(matched) DIVISION in data as new field for the same TIME_STAMP & same SITE.
The output expected is :
|Time|Site|Group|Division|User|Lic Feature|Lic Usage|Lic Total|SUM_LICU_BY_DIVISON|
|11/1/2018 10:00|GREATER NOIDA|ADG|ADG_MICRO|rajeshb|eldo|2|100|5|
|11/1/2018 10:00|GREATER NOIDA|ADG|ADG_MICRO|narwal|eldo|3|100|5|
|11/1/2018 10:00|GREATER NOIDA|ADG|ADG_RF|das|eldo|4|100|4|
|11/1/2018 10:00|GREATER NOIDA|TRD|TRD_LAB|ashu|eldo|3|100|9|
|11/1/2018 10:00|GREATER NOIDA|TRD|TRD_LAB|vivek|eldo|6|100|9|
|11/1/2018 10:00|GREATER NOIDA|TRD|TRD_MEMS|bally|eldo|1|100|2|
|11/1/2018 10:00|GREATER NOIDA|TRD|TRD_MEMS|shamsi|eldo|1|100|2|
|11/1/2018 10:00|GRENOBLE|ADG|ADG_MICRO|pcm|eldo|2|100|2|
|11/1/2018 10:00|GRENOBLE|ADG|ADG_GNB|JPM|eldo|3|100|3|
|11/1/2018 10:00|GRENOBLE|ADG|ADG_RF|olivier|eldo|4|100|4|
|11/1/2018 10:00|GRENOBLE|TRD|TRD_GNB|fabrice|eldo|3|100|3|
|11/1/2018 10:00|GRENOBLE|TRD|TRD_LAB|arnaud|eldo|6|100|6|
|11/1/2018 10:00|GRENOBLE|TRD|TRD_MEMS|paul|eldo|1|100|2|
|11/1/2018 10:00|GRENOBLE|TRD|TRD_MEMS|sylvain|eldo|1|100|2|
|11/1/2018 10:05|GREATER NOIDA|ADG|ADG_MICRO|rajeshb|eldo|1|100|3|
|11/1/2018 10:05|GREATER NOIDA|ADG|ADG_MICRO|narwal|eldo|2|100|3|
|11/1/2018 10:05|GREATER NOIDA|ADG|ADG_RF|das|eldo|3|100|3|
|11/1/2018 10:05|GREATER NOIDA|TRD|TRD_LAB|ashu|eldo|4|100|10|
|11/1/2018 10:05|GREATER NOIDA|TRD|TRD_LAB|vivek|eldo|6|100|10|
|11/1/2018 10:05|GREATER NOIDA|TRD|TRD_MEMS|bally|eldo|2|100|3|
|11/1/2018 10:05|GREATER NOIDA|TRD|TRD_MEMS|shamsi|eldo|1|100|3|
|11/1/2018 10:05|GRENOBLE|ADG|ADG_MICRO|pcm|eldo|5|100|5|
|11/1/2018 10:05|GRENOBLE|ADG|ADG_GNB|JPM|eldo|2|100|2|
|11/1/2018 10:05|GRENOBLE|ADG|ADG_RF|olivier|eldo|3|100|3|
|11/1/2018 10:05|GRENOBLE|TRD|TRD_GNB|fabrice|eldo|7|100|7|
|11/1/2018 10:05|GRENOBLE|TRD|TRD_LAB|arnaud|eldo|5|100|5|
|11/1/2018 10:05|GRENOBLE|TRD|TRD_MEMS|paul|eldo|1|100|2|
|11/1/2018 10:05|GRENOBLE|TRD|TRD_MEMS|sylvain|eldo|1|100|2|