Visualize complicated elastic query


(Martin Lipovsk√Ĺ) #1

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"
      }
    }
  } 
}

(Tyler Smalley) #2

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


(Martin Lipovsk√Ĺ) #3

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"
      }
    }
  }
}

(system) #4

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