How can I take count by status after a split?


(Dj Vidov) #1

Hello,

I want to create a report to show how many apps are in running status, how many apps are closed, etc
My logs are event based. When an app has a new status it send to elk a message with the new status.

Until now I was able to do this, as kibana report looks like in image. And What I want now is to count how many apps are in status 102, 79, etc T
There is a way to do this?


Generated query in Kibana is:

{
  "title": "New Visualization",
  "type": "table",
  "params": {
    "perPage": 10,
    "showPartialRows": false,
    "showMeticsAtAllLevels": true
  },
  "aggs": [
    {
      "id": "1",
      "type": "max",
      "schema": "metric",
      "params": {
        "field": "date"
      }
    },
    {
      "id": "5",
      "type": "terms",
      "schema": "bucket",
      "params": {
        "field": "app_id",
        "size": 0,
        "order": "desc",
        "orderBy": "1"
      }
    },
    {
      "id": "6",
      "type": "terms",
      "schema": "bucket",
      "params": {
        "field": "new_status_id",
        "size": 1,
        "order": "desc",
        "orderBy": "_term"
      }
    }
  ],
  "listeners": {}
}

Thank you.


(Thomas Neirynck) #2

hi @djvidov,

you can add a 2nd metric in the "Metrics" section. Choose "Count". This will add another column to your table that gives a count for all [app_id, new_status_id] tuples.

Thanks,


(Dj Vidov) #3

Hi Thomas,
Thank you for your answer. I already try that and it works as you say.
But this is not what I want. I want a count only for new_status_id. Because, in the end, I want to have: currently we have 75 apps in status 102 and 50 apps in status 79.
And the reason why I added max metric by id with split rows by app_id ordered descending orderBy 1 is because I want to take the last status from my logs.
Thank you! :slight_smile:


(Thomas Neirynck) #4

Not sure if you can do that. Any metric will apply to whatever buckets you have in that row. So if you have one row per app_id, you won't be able to get an aggregate count in an extra column that applies to all app_ids.

You'd be able to do this with Pipeline aggregations in ES, https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html, but Kibana doesn't have a UI for this yet.

You could you do a metric "Unique Count" of app_id, for new_status_id terms. But then you don't limit it to the "latest" status. But if you filter on date or some other field-value to get the latest app_id status, you could do it that way.


(system) #5