I found 2 topics on this topic with no clear solution. I eventually got it working.
I basically used the curl to output ES aggregate query to file and then used file input to read and convert to csv. But same can be done to send aggregate data to ES as a new index.
input {
pipe {
codec => "json"
command => "./ord_summary_curl.sh"
}
}
filter {
split { field => "[aggregations][by-date][buckets]" }
split { field => "[aggregations][by-date][buckets][by-brand][buckets]" }
split { field => "[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets]" }
ruby { code => "
event.set('tot-Amt', event.get('[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][tot-Sales][value]') + event.get('[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][tot-demand][value]'))
"
}
}
output {
stdout {codec => rubydebug}
csv {
fields => ["[aggregations][by-date][buckets][key_as_string]","[aggregations][by-date][buckets][by-brand][buckets][key]","[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][key]","[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][tot-ord-qty][value]","[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][tot-Sales][value]","[aggregations][by-date][buckets][by-brand][buckets][by-status][buckets][tot-demand][value]","tot-Amt"]
path => "ord-summary.csv"
}
}
ES Query - curl command
curl -XGET "http://localhost:3602/perfectorder/OrderItem/_search?" -H 'Content-Type: application/json' -d'
{
"query": {
"bool": {
"must": [
{
"match_all": {}
},
{
"bool": {
"must": [
{
"range": {
"OrderItem.OrderDate": {
"gte": "now-6d/d",
"lt": "now-5d/d",
"time_zone": "PST8PDT"
}
}
},
{
"term": {
"_type": "OrderItem"
}
},
{
"terms": {
"OrderItem.Brand.keyword": [
"COMP-A",
"COMP-B"
]
}
}
]
}
}
],
"must_not": []
}
},
"size": 0,
"_source": {
"excludes": []
},
"aggs": {
"by-date": {
"date_histogram": {
"field": "OrderItem.OrderDate",
"interval": "1d",
"time_zone": "America/Los_Angeles",
"min_doc_count": 1
},
"aggs": {
"by-brand": {
"terms": {
"field": "OrderItem.Brand.keyword",
"size": 5,
"order": {
"by-eid1": "desc"
}
},
"aggs": {
"by-eid1": {
"cardinality": {
"field": "OrderItem.EID.keyword"
}
},
"by-status": {
"terms": {
"field": "OrderItem.FulfillmentStatus.keyword",
"size": 50,
"order": {
"by-eid2": "desc"
}
},
"aggs": {
"by-eid2": {
"cardinality": {
"field": "OrderItem.EID.keyword"
}
},
"tot-ord-qty": {
"sum": {
"field": "OrderItem.Quantity"
}
},
"qty-bo": {
"sum": {
"field": "OrderItem.BackOrderQuantity"
}
},
"qty-cancel": {
"sum": {
"field": "OrderItem.CancelledQuantity"
}
},
"qty-del": {
"sum": {
"field": "OrderItem.DeliveredQuantity"
}
},
"qty-rel": {
"sum": {
"field": "OrderItem.ReleasedQuantity"
}
},
"qty-ship": {
"sum": {
"field": "OrderItem.ShippedQuantity"
}
},
"tot-demand": {
"sum": {
"field": "OrderItem.DemandSalesTotal"
}
},
"tot-Sales": {
"sum": {
"field": "OrderItem.ShippedSalesTotal"
}
}
}
}
}
}
}
}
}
}'