# Assistance with a query - used phone extensions **URL:** \ **Category:** Elasticsearch\ **Created:** [August 23, 2018, 9:12pm UTC](https://discuss.elastic.co/t/assistance-with-a-query-used-phone-extensions/145807 "2018-08-23T21:12:30Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![bthoon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bthoon/32/34177_2.png) [@bthoon](https://discuss.elastic.co/u/bthoon)\ **Post date:** [August 23, 2018, 9:12pm UTC](https://discuss.elastic.co/t/assistance-with-a-query-used-phone-extensions/145807/1 "2018-08-23T21:12:30Z")
We have an application that will send, every minute, the hook status of several dozen phone extensions. The data looks like this: { "mapping": { "\_doc": { "properties": { "date": { "type": "date", "format": "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||epoch\_millis" }, "extension": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore\_above": 256 } } }, "status": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore\_above": 256 } } }, "tag": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore\_above": 256 } } } } } } This means I've got thousands of rows for each extension, each time stamped, allowing me to go to a particular moment in time and determine if a particular extension was in use at that time. Now, a requirement has come down to identify extensions that are NOT used. The data source will report (status field) ONLINE or OFFLINE with each record. I'd like to come up with a query that will return all extensions that have only EVER been offline. something like (pseudocode, I'm new to ES) count(status:online) = 0 group by extension. I've tried aggs and filters and have found myself stumped. I'd appreciate any help anyone might have! ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [August 24, 2018, 11:32am UTC](https://discuss.elastic.co/t/assistance-with-a-query-used-phone-extensions/145807/2 "2018-08-24T11:32:19Z")
I would use aggregations. Start with a `terms` aggregation to get a bucket for every extension. Next, use the `cardinality` aggregation to get the number of unique statuses per extension. This should be 1 or 2. Now, you can use [the `bucket_selector` pipeline aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html) to select only those extension buckets with 1 unique status. Assuming that those extensions that only have one unique status all have the status OFFLINE, this will return the list of extensions you are looking for. The request would look like this: ```auto { "size": 0, "aggs": { "extension": { "terms": { "field": "extension.keyword", "size": 1000 }, "aggs": { "unique_statuses": { "cardinality": { "field": "status.keyword" } }, "bucket_filter": { "bucket_selector": { "buckets_path": { "unique_statuses": "unique_statuses" }, "script": "params.unique_statuses == 1" } } } } } } ``` The `size` of the `terms` aggregation is `1000` in the request above. As a result, you will only get a list of up to 1000 extensions. If you want to retrieve a larger number of extensions, you can increase this number, but if the number is huge you could consider using [the composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html) instead of the terms agg. Now, if the assumption that extensions that only have one unique status have the status OFFLINE is incorrect (ie. you also have extensions that have a unique status ONLINE), then the approach would be a bit more complex. You could use a [scripted metric aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html) that calculates a number depending on the statuses in a bucket (0 for OFFLINE and 1 for ONLINE). The `bucket_selector` script can then also check for that number to select only those extensions that have the unique status OFFLINE. ```auto { "size": 0, "aggs": { "extension": { "terms": { "field": "extension.keyword", "size": 1000 }, "aggs": { "unique_statuses": { "cardinality": { "field": "status.keyword" } }, "offline_or_online": { "scripted_metric": { "init_script": "params._agg.status = 0", "map_script": "params._agg.status = doc['status.keyword'].value == 'OFFLINE' ? 0 : 1", "combine_script": "return params._agg.status", "reduce_script": "return params._aggs[0]" } }, "bucket_filter": { "bucket_selector": { "buckets_path": { "unique_statuses": "unique_statuses", "offline_or_online": "offline_or_online.value" }, "script": "params.unique_statuses == 1 && params.offline_or_online == 0" } } } } } } ``` ---
**Author:** ![bthoon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bthoon/32/34177_2.png) [@bthoon](https://discuss.elastic.co/u/bthoon)\ **Post date:** [August 24, 2018, 6:27pm UTC](https://discuss.elastic.co/t/assistance-with-a-query-used-phone-extensions/145807/3 "2018-08-24T18:27:06Z")
EXCELLENT solution. This is wonderful, and really gives me (as an ES novice) confidence that I've selected the right platform. Thank you so much for your quick and very comprehensive solution! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 21, 2018, 6:27pm UTC](https://discuss.elastic.co/t/assistance-with-a-query-used-phone-extensions/145807/4 "2018-09-21T18:27:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.