# Getting count based on field value

**URL:** https://discuss.elastic.co/t/getting-count-based-on-field-value/34830
**Category:** Elasticsearch
**Created:** [November 17, 2015, 5:19pm UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830 "2015-11-17T17:19:57Z")
**Posts on this page:** 5
**Page:** 1

<div class="post-metadata">

### Author: ![jdepp99](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jdepp99](https://discuss.elastic.co/u/jdepp99)
#### Post date: [November 17, 2015, 5:19pm UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830/1 "2015-11-17T17:19:57Z")

</div>

```
{

```

"query": {  
"filtered": {  
"query": {  
"query\_string": {  
"analyze\_wildcard": true,  
"query": "_"  
}  
},  
"filter": {  
"bool": {  
"must": [  
{  
"query": {  
"match": {  
"PStream": {  
"query": "_",  
"type": "phrase"  
}  
}  
}  
},  
{  
"range": {  
"@timestamp": {  
"gte": 1447777019722,  
"lte": 1447780619722  
}  
}

I am trying to run a query that will return the total message count for the field PStream for any of the possible values. For example there are over 100 different PStream values, and I am setting up an alert system that will trigger an alert when the count = 0 for the last hour for any of the PStream values. Could someone assist or suggest a way to construct this query? Would really appreciate it.

Thanks

---

<div class="post-metadata">

### Author: ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)
#### Post date: [November 18, 2015, 11:35am UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830/2 "2015-11-18T11:35:53Z")

</div>

You could use a boolean query with a must\_not clause which contains the [`missing` query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-missing-query.html) to query for all messages where PStream has a value (i.e. where the value is not missing).

---

<div class="post-metadata">

### Author: ![VincentBiret](https://avatars.discourse-cdn.com/v4/letter/v/54ee81/32.png) [@VincentBiret](https://discuss.elastic.co/u/VincentBiret)
#### Post date: [November 18, 2015, 12:25pm UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830/3 "2015-11-18T12:25:13Z")

</div>

Note : this approach only works if the field PSteam is missing from the document :

"query": {  
"filtered": {  
"query": {  
"query\_string": {  
"analyze\_wildcard": true,

```
"query": "_!exists_:PStream"

```

}  
},

And you can delete the query match parameter

---

<div class="post-metadata">

### Author: ![jdepp99](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jdepp99](https://discuss.elastic.co/u/jdepp99)
#### Post date: [November 18, 2015, 4:08pm UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830/4 "2015-11-18T16:08:59Z")

</div>

Thanks for the responses and suggestion. I apologize, I should have been a little more clearer. I am mainly worried about the counts of that value and not missing values as that field will always be occupied and will serve as the identifier.

```
GET /logstash-*/_search?search_type=count
{
 "query": {
"filtered": {
  "query": {
      "match":{
         "PStream":"864"   
      }
    }
  }
 }
}

```

Returns the following:

```
  "hits": {
    "total": 21657418,
    "max_score": 0,
    "hits": []
  }

```

this is more like what I need. I need to get the counts for all messages with PStream: \* and if count = 0, then that creates an alert. The only thing I don't have correct yet, is defining a time window. Tried to add the following but getting parser error:

```
     {
     "query": {
     "filtered": {
       "query": {
          "match":{
           "PStream":"864"   
          }
      },  

 "aggs": {
   "by_day": {
   "date_histogram": {
      "field": "date",
      "interval": "hour"
       }
     }
  }
 }
}

```

}

Also how do I filter for all PStream values:

```
{
 "query": {
  "filtered": {
    "query": {
        "query_string": {
        "analyze_wildcard": true,
         "query": "_!exists_:PStream"
        }  
    }
  }
}

```

}

---

<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: [July 5, 2017, 11:37pm UTC](https://discuss.elastic.co/t/getting-count-based-on-field-value/34830/5 "2017-07-05T23:37:33Z")

</div>