# Help Required with query

**URL:** <https://discuss.elastic.co/t/help-required-with-query/163980>\
**Category:** Elasticsearch\
**Created:** [January 12, 2019, 4:51am UTC](https://discuss.elastic.co/t/help-required-with-query/163980 "2019-01-12T04:51:51Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ramachandra\_Bhaskar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramachandra_bhaskar/32/39722_2.png) [@Ramachandra\_Bhaskar](https://discuss.elastic.co/u/Ramachandra_Bhaskar)\
**Post date:** [January 12, 2019, 4:51am UTC](https://discuss.elastic.co/t/help-required-with-query/163980/1 "2019-01-12T04:51:51Z")

</div>

We have our logs in Elasticsearch which are normalized. Here is what we store in nginx logs.

```auto
“_source”: {
“remote”: “influx-dev-cluster01”,
“host”: “10.88.14.126”,
“ident”: “-”,
“user”: “user”,
“method”: “POST”,
“path”: “/query?db=&q”,
“code”: “200”,
“size”: “236”,
“referer”: “-”,
“agent”: “DBClient”,
“upstream_latency”: “0.002”,
“latency”: “0.000”,
“hostname”: “ssd02”,
“component”: ““influxdb””,
“@timestamp”: “2019 Jan 11 12:40:59”,
“tag”: “nginx”
}

```

I want to visualize availability. Definition of availability is

Availability % = ( Successful Requests \* 100 ) / ( Success Requests + Failed Requests )

Where Successful requests.= requests with response code \< 500 and  
Failed Requests = requests with response code \>= 500

Can some one help me or guide with writing query to calculate the approach. We want to visualize this with grafana

---

<div class="post-metadata">

**Author:** ![Ramachandra\_Bhaskar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramachandra_bhaskar/32/39722_2.png) [@Ramachandra\_Bhaskar](https://discuss.elastic.co/u/Ramachandra_Bhaskar)\
**Post date:** [January 13, 2019, 3:04pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/2 "2019-01-13T15:04:29Z")

</div>

Any help here greatly appreciated

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [January 13, 2019, 3:09pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/3 "2019-01-13T15:09:21Z")

</div>

I would recommend you have a look at [pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html), as I believe that may be the best fit. Not sure if or how these can be used with Grafana though.

---

<div class="post-metadata">

**Author:** ![Ramachandra\_Bhaskar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramachandra_bhaskar/32/39722_2.png) [@Ramachandra\_Bhaskar](https://discuss.elastic.co/u/Ramachandra_Bhaskar)\
**Post date:** [January 13, 2019, 3:28pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/4 "2019-01-13T15:28:13Z")

</div>

Thanks a lot Christian . I will give a try

---

<div class="post-metadata">

**Author:** ![Ramachandra\_Bhaskar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ramachandra_bhaskar/32/39722_2.png) [@Ramachandra\_Bhaskar](https://discuss.elastic.co/u/Ramachandra_Bhaskar)\
**Post date:** [January 13, 2019, 8:20pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/5 "2019-01-13T20:20:44Z")

</div>

I have given a try.  
GET /myindex\*/\_search  
{  
"size": 0,  
"aggs": {  
"events": {  
"date\_histogram": {  
"field": "@timestamp",  
"interval": "hour",  
"format": "yyyy-MM-dd-HH:mm",  
"min\_doc\_count": 0  
},  
"aggs": {  
"total\_hits": {  
"range": {  
"field": "code",  
"keyed": true,  
"ranges": [  
{  
"key": "success",  
"from": 0,  
"to": 500  
},  
{  
"key": "failures",  
"from": 500,  
"to": 1000  
}  
]  
}  
}  
}  
}  
}  
}

I am not able to compute the availabiliity formula as mentioned above. tried pipeline aggregations still not able to get my head around. Can you suggest how to calculate the %

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [January 13, 2019, 9:52pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/6 "2019-01-13T21:52:35Z")

</div>

[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html)

---

<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:** [February 10, 2019, 9:52pm UTC](https://discuss.elastic.co/t/help-required-with-query/163980/7 "2019-02-10T21:52:45Z")

</div>

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