Count number of diffrent hours in a day from docs

Admin 1
Viewed 77

I have time-stamp field (yyyy-mm-dd T hh:mm:ss). I want to count how many different hours are present in given day per device id. How to do that in kibana ?

this is my actual file.

https://drive.google.com/open?id=1JRZj8myVu1UHJ3jxZzzb8LSKKMicY0Qi For example.

 Doc1=
 {
   name = "heart_beat"
   date= "2019-06-05 T 12:10:00"
   "device_id" = "abc"
 }
 Doc2=
 {
    name="heart_beat"
    date="2019-06-05 T 09:10:00"
    "device_id" = "xyz"
  }
  Doc3=
  {
    name="heart_beat"
    date="2019-06-05 T 12:15:00"
    "device_id" = "pqr"
  }
  Doc4=
  {
     name="heart_beat"
     date="2019-06-05 T 07:10:00"
     "device_id" = "def"
  }
  Doc5=
 {
    name="heart_beat"
    date="2019-06-07 T 09:10:00"
    "device_id" = "xyz"
  }
  Doc6=
  {
    name="heart_beat"
    date="2019-06-07 T 12:15:00"
    "device_id" = "pqr"
  }

answer should be

  1. "2019-06-05" device-"abc" hours-1

  2. "2019-06-05" device-"pqr" hours-1

  3. "2019-06-05" device-"xyz" hours-1

  4. "2019-06-05" device-"def" hours-1

  5. "2019-06-07" device-"xyz" hours-1

  6. "2019-06-07" device-"pqr" hours-1
kibana elasticsearch json java
1 Answers

I am using scripting to get distinct hours. Query has 2 aggregations 1. terms aggregations - which gives all distinct hours and count of documents in them 2. cardinality aggregation -- gives count of distinct hours

you can keep either or both as per your requirement

Mapping:

PUT testindex4/_mapping
{
  "properties": {
    "name":{
      "type":"text"
    }, 
    "date":
    {
      "type":"date",
      "format":"YYYY-MM-DD'T'HH:mm:ss"
    }
  }
}

Query:

GET testindex4/_search
{
  "size":0,
  "aggs": {
    "byHours": {    ---> if you need doc count in distinct hours
      "terms": {
        "script": {
          "source": "doc[params.date_field].value.hourOfDay;",
          "params": {
            "date_field": "date"
          }
        }
      }
    },
    "distinct_hours": {      ---> count of distinct hours
      "cardinality": {
        "script": {
          "lang": "painless",
          "source": "doc[params.date_field].value.hourOfDay;",
          "params": {
            "date_field": "date"
          }
        }
      }
    }
  }
}

Full Data

    "hits" : [
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ZjyHc2sB36IvcvW_wPNu",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T12:10:00"
        }
      },
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ZzyIc2sB36IvcvW_DPMn",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T12:15:00"
        }
      },
      {
        "_index" : "testindex4",
        "_type" : "_doc",
        "_id" : "ajyPc2sB36IvcvW_0fOc",
        "_score" : 1.0,
        "_source" : {
          "name" : "heart_beat",
          "date" : "2019-06-05T01:15:00"
        }
      }
    ]

Result:

"aggregations" : {
    "distinct_hours" : {
      "value" : 2
    },
    "byHours" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "12",
          "doc_count" : 2
        },
        {
          "key" : "1",
          "doc_count" : 1
        }
      ]
    }
  }

Edit: For getting records per device Mapping

PUT testindex4/_mapping
{
  "properties": {
    "device": {
      "properties": {
        "make": {
           "type": "text"
        },
        "model":{
           "type": "keyword"
        }
      }
    },
    "date": {
      "type": "date",
      "format": "yyyy-MM-dd HH:mm:ss"
    }
  }
}

Query

GET testindex4/_search
{
  "size": 0,
  "aggs": {
    "byDevice": {
      "terms": {
        "field": "device.model"
      },
      "aggs": {
        "byDate": {
          "terms": {
            "script": {
              "source": "doc[params.date_field].value.toString('yyyy-MM-dd')",
              "params": {
                "date_field": "date"
              }
            }
          },
          "aggs": {
            "byHours": {
              "terms": {
                "script": {
                  "source": "doc[params.date_field].value.getHour();",
                  "params": {
                    "date_field": "date"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Result

  "aggregations" : {
    "byDevice" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "VFD 720",
          "doc_count" : 3,
          "byDate" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "2019-06-06",
                "doc_count" : 2,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    },
                    {
                      "key" : "15",
                      "doc_count" : 1
                    }
                  ]
                }
              },
              {
                "key" : "2019-06-05",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "key" : "VFD 721",
          "doc_count" : 2,
          "byDate" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "2019-06-05",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              },
              {
                "key" : "2019-06-06",
                "doc_count" : 1,
                "byHours" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "12",
                      "doc_count" : 1
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
user_92793540
Related