我需要一个Elasticsearch查询来显示Grafana中的数据

Question

假设我有文档a、b、c、d，其中包含字段site_name、device_name、Interface_name和utilization。我需要为每个接口名称和每个站点名称显示设备名称的最大利用率。

以下是示例数据：

**Site  Device  Interface Name           Utilization**
TYO    tyo-gb1  TenGigabitEthernet1      33,23,699
TYO    tyo-gb1  TenGigabitEthernet1      38,92,992
TYO    tyo-gb2  TenGigabitEthernet2      98,824
TYO    tyo-gb2  TenGigabitEthernet2      49,187
SYD    syd-gb1   GigabitEthernet1        52,800
SYD    syd-gb1   GigabitEthernet1        71,572
STLD   stld-gb1  GigabitEthernet1        1,62,886
STLD   stld-gb1  GigabitEthernet1        40,977

我需要这样显示：

**Site    Device  Interface Name           Utilization**
TYO    tyo-gb1  TenGigabitEthernet1      38,92,992
TYO    tyo-gb2  TenGigabitEthernet2      98,824
SYD    syd-gb1   GigabitEthernet1        71,572
STLD   stld-gb1  GigabitEthernet1        1,62,886

提前感谢！

Answer 1

您可以使用此查询

{
"size": 0,
"_source": false,
"stored_fields": "_none_",
"aggregations": {
"groupby": {
"composite": {
"size": 1000,
"sources": [
{
"Site": {
"terms": {
"field": "Site",
"missing_bucket": true,
"order": "asc"
}
}
},
{
"Device": {
"terms": {
"field": "Device",
"missing_bucket": true,
"order": "asc"
}
}
},
{
"Interface Name": {
"terms": {
"field": "Interface Name",
"missing_bucket": true,
"order": "asc"
}
}
}
]
},
"aggregations": {
"Utilization Sum": {
"sum": {
"field": "Utilization"
}
}
}
}
}
}

Answer 2

数据摄取

POST test_nagendra/_doc
{
"site_name": "TYO",
"device_name": "tyo-gb1",
"interface_name": "TenGigabitEthernet1",
"utilization": 3323699
}

POST test_nagendra/_doc
{
"site_name": "TYO",
"device_name": "tyo-gb1",
"interface_name": "TenGigabitEthernet1",
"utilization": 3892992
}

POST test_nagendra/_doc
{
"site_name": "TYO",
"device_name": "tyo-gb2",
"interface_name": "TenGigabitEthernet2",
"utilization": 98824
}

POST test_nagendra/_doc
{
"site_name": "TYO",
"device_name": "tyo-gb2",
"interface_name": "TenGigabitEthernet2",
"utilization": 49187
}

POST test_nagendra/_doc
{
"site_name": "SYD",
"device_name": "syd-gb1",
"interface_name": "GigabitEthernet1",
"utilization": 52800
}

POST test_nagendra/_doc
{
"site_name": "SYD",
"device_name": "syd-gb1",
"interface_name": "GigabitEthernet1",
"utilization": 71572
}

POST test_nagendra/_doc
{
"site_name": "STLD",
"device_name": "stld-gb1",
"interface_name": "GigabitEthernet1",
"utilization": 162886
}

POST test_nagendra/_doc
{
"site_name": "STLD",
"device_name": "stld-gb1",
"interface_name": "GigabitEthernet1",
"utilization": 40977
}

查询

POST test_nagendra/_search
{
"size": 0,
"aggs": {
"sites": {
"terms": {
"field": "site_name.keyword",
"size": 10
},
"aggs": {
"devices": {
"terms": {
"field": "device_name.keyword",
"size": 10
},
"aggs": {
"interfaces": {
"terms": {
"field": "interface_name.keyword",
"size": 10
},
"aggs": {
"max_utilization": {
"max": {
"field": "utilization"
}
}
}
}
}
}
}
}
}
}

响应

{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 8,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"sites" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "TYO",
"doc_count" : 4,
"devices" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "tyo-gb1",
"doc_count" : 2,
"interfaces" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "TenGigabitEthernet1",
"doc_count" : 2,
"max_utilization" : {
"value" : 3892992.0
}
}
]
}
},
{
"key" : "tyo-gb2",
"doc_count" : 2,
"interfaces" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "TenGigabitEthernet2",
"doc_count" : 2,
"max_utilization" : {
"value" : 98824.0
}
}
]
}
}
]
}
},
{
"key" : "STLD",
"doc_count" : 2,
"devices" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "stld-gb1",
"doc_count" : 2,
"interfaces" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "GigabitEthernet1",
"doc_count" : 2,
"max_utilization" : {
"value" : 162886.0
}
}
]
}
}
]
}
},
{
"key" : "SYD",
"doc_count" : 2,
"devices" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "syd-gb1",
"doc_count" : 2,
"interfaces" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "GigabitEthernet1",
"doc_count" : 2,
"max_utilization" : {
"value" : 71572.0
}
}
]
}
}
]
}
}
]
}
}
}