# Visualize complicated elastic query

**URL:** <https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522>\
**Category:** Kibana\
**Created:** [September 22, 2017, 5:45pm UTC](https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522 "2017-09-22T17:45:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Martin\_Lipovsky](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/martin_lipovsky/32/22433_2.png) [@Martin\_Lipovsky](https://discuss.elastic.co/u/Martin_Lipovsky)\
**Post date:** [September 22, 2017, 5:45pm UTC](https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522/1 "2017-09-22T17:45:42Z")

</div>

Hi, i'm new in kibana/elastic. Can you help me visualize this elastic query? I want use visualize-data-metric to show 'total\_sum'

thanks

```
GET /era-mdm/_search
{
  "size": 0,
  "aggs": {
    "hws": {
      "terms": {
        "field": "Hw.FingerPrint.keyword"
      },
      "aggs": {
        "last_hws": {
          "terms": {
            "field": "@timestamp",
            "order": {
              "_key": "desc"
            },
            "size": 1
          },
          "aggs": {
            "total_devices": {
              "sum": {
                "field": "Era.MDMCore.TotalDeviceCount"
              }
            }
          }
        },
        "sum_last_hws": {
          "sum_bucket": {
            "buckets_path": "last_hws>total_devices"
          }
        }
      }
    },
    "total_sum": {
      "sum_bucket": {
        "buckets_path": "hws>sum_last_hws"
      }
    }
  } 
}
```

---

<div class="post-metadata">

**Author:** ![tylersmalley](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tylersmalley/32/8833_2.png) [@tylersmalley](https://discuss.elastic.co/u/tylersmalley)\
**Post date:** [September 25, 2017, 3:52am UTC](https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522/2 "2017-09-25T03:52:27Z")

</div>

Could you describe the data and the visualization you're looking to achieve? It's a little easier to visualize.

---

<div class="post-metadata">

**Author:** ![Martin\_Lipovsky](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/martin_lipovsky/32/22433_2.png) [@Martin\_Lipovsky](https://discuss.elastic.co/u/Martin_Lipovsky)\
**Post date:** [September 25, 2017, 4:22am UTC](https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522/3 "2017-09-25T04:22:39Z")

</div>

It's a little bit like "discuss-101523". With the difference that I'd like the resulting number ("total\_sum": {"value": 15}) to be displayed in the dashboard. - Resulting number is actual sum of total devices (actual = latest doc per hw.fingerprint)

```
DELETE discuss-101522

PUT discuss-101522
{
  "settings": {},
  "mappings": {
    "doc": {
      "properties": {
        "Era.MDMCore.TotalDeviceCount": {
          "type": "integer"
        },
        "Hw.FingerPrint": {
          "type": "keyword"
        },
        "@timestamp": {
          "type": "date"
        }
      }
    }
  }
}

POST discuss-101522/doc
{
  "Era.MDMCore.TotalDeviceCount": 2,
  "Hw.FingerPrint": "01007921-1E96-FE8B-DC11-6DA2E2643EDE",
  "@timestamp": "2017-09-18T13:08:20.429Z"
}

POST discuss-101522/doc
{
  "Era.MDMCore.TotalDeviceCount": 5,
  "Hw.FingerPrint": "0100E08F-84D3-10D5-CAAE-FE0FE45CF0D4",
  "@timestamp": "2017-09-19T08:31:51.203Z"
}

POST discuss-101522/doc
{
  "Era.MDMCore.TotalDeviceCount": 11,
  "Hw.FingerPrint": "010035F9-FF3E-3DB5-D706-C51560F85CD8",
  "@timestamp": "2017-09-19T13:29:37.847Z"
}

POST discuss-101522/doc
{
  "Era.MDMCore.TotalDeviceCount": 7,
  "Hw.FingerPrint": "010035F9-FF3E-3DB5-D706-C51560F85CD8",
  "@timestamp": "2017-09-19T18:17:27.514Z"
}

POST discuss-101522/doc
{
  "Era.MDMCore.TotalDeviceCount": 8,
  "Hw.FingerPrint": "010035F9-FF3E-3DB5-D706-C51560F85CD8",
  "@timestamp": "2017-09-22T08:50:27.876Z"
}

GET /discuss-101522/_search
{
  "size": 0,
  "aggs": {
    "hws": {
      "terms": {
        "field": "Hw.FingerPrint"
      },
      "aggs": {
        "last_hws": {
          "terms": {
            "field": "@timestamp",
            "order": {
              "_term": "desc"
            },
            "size": 1
          },
          "aggs": {
            "total_devices": {
              "sum": {
                "field": "Era.MDMCore.TotalDeviceCount"
              }
            }
          }
        },
        "sum_last_hws": {
          "sum_bucket": {
            "buckets_path": "last_hws>total_devices"
          }
        }
      }
    },
    "total_sum": {
      "sum_bucket": {
        "buckets_path": "hws>sum_last_hws"
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 23, 2017, 4:37am UTC](https://discuss.elastic.co/t/visualize-complicated-elastic-query/101522/4 "2017-10-23T04:37:09Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.