Basic Elastic Search Query

Grafana Elasticsearch
1

Hi Team,

Here are my elastic search get results. Can u help me to plot a time series graph with lucene query on grafana? I have the data source configured.

{“took”:3,“timed_out”:false,"_shards":{“total”:5,“successful”:5,“failed”:0},“hits”:{“total”:4,“max_score”:0.2876821,“hits”:[{"_index":“region_summary”,"_type":“region_cpu_summary”,"_id":“1”,"_score":0.2876821,"_source":{“cpu_available”: 20, “timestamp”: “2017-06-21T02:55:17.824675”, “region_name”: “EGL_C_01”, “cpu_used”: 12}},{"_index":“region_summary”,"_type":“region_cpu_summary”,"_id":“3”,"_score":0.2876821,"_source":{“cpu_used”: 21, “timestamp”: “2017-06-21T18:27:26.611000”, “cpu_available”: 99, “region_name”: “EGL_C_01”}},{"_index":“region_summary”,"_type":“region_cpu_summary”,"_id":“2”,"_score":0.18232156,"_source":{“region_name”: “EGL_C_01”, “cpu_available”: 20, “timestamp”: “2017-06-21T02:57:55.390432”, “cpu_used”: 12}},{"_index":“region_summary”,"_type":“region_cpu_summary”,"_id":“4”,"_score":0.18232156,"_source":{“region_name”: “EGL_C_01”, “timestamp”: “2017-06-22T11:50:05.658329”, “cpu_used”: 21, “cpu_available”: 99}}]}}

I want the cpu available as a time series graph

2

Where are you getting stuck?

Here is an example Elasticsearch dashboard on the Grafana demo site:

http://play.grafana.org/dashboard/db/elasticsearch-metrics?orgId=1

Have a look at the query for the first panel and see if you can figure it out. The Query field is for the lucene query and then you choose which field and aggregation you want (sum, average etc.) and then you need a Group By Date Histogram:

http://play.grafana.org/dashboard/db/elasticsearch-metrics?panelId=1&fullscreen&edit&orgId=1

3

Hi ,

Thanks I am good and did some basic graphs with elastic search and grafana. I need some help in writing some advanced queries …

I have a Elastic Search document with fileds

_index:a
_type:b
_id:blahblah
timestamp:
{
Total:1000
Used:919
}

I am trying to write an elasit search query in grafana to subtract Total field from used field.

This sort of json for same Index/type is pumped everyday into the elastic search

Can some one help?

4

This was a bit tricky but this seems to work using the script field. _value in this case is the @number field and I am adding the @value field to it.

5

Thanks @daniellee … U saved my day !

1 Like
6

Hi Team,
I think I have the same issue here!

in kibana, I can create script field, then I can use it in aggs…

How to do it in grafana (also how to perform mathematical operations on two fields and create new field?):
in Kibana field is:

 "script_fields" : {
    "Delay": {
      "script": {
        "inline": "!doc['End_Time'].empty ? ((doc['End_Time'].value - doc['timestamp'].value )/1000) : ''"
      }
    }
  }

this creates a new field called Delay that can be used in ranges,aggregations…

Elsticsearch: 5.4.1
Grafana: 4.3.2
Ubuntu 16.04.2 LTS

7

@michaelaeino not sure if that will work? Are you getting an error?

8

@daniellee I’m not sure how or where to add this… is it defined as templated? or directly in the graph query!
& what the correct syntax?
I’m really new with Grafana :blush:

9

can i add variable & inject this script inside it ?

I have tried to do that by several syntaxes… with no luck

I got this error:
Template variables could not be initialized: a.metricFindQuery(...) is undefined

10

I was thinking you would use the script field like in this answer: Basic Elastic Search Query

11

I can write a new name instead of “@number” , & the script i added only the equation

doc['End_Time'].value - doc['timestamp'].value
but nothing happened!
is that possible here to type my own field name so it is get created ? also to calculate it’s value from other fields ?

12

This is why aliases exist I believe. Here is how i do it …

image.jpg2446×382 77.8 KB

Here is my script - _value-doc[‘h_1.memory_used_mb’].value

as you see i am having my own field on graph as alias - free_memory which is a subtracted value of document field h_1.memory_mb and h_1.memory_used_mb

Hope this helps

1 Like
13

Hi @coolshiva123,
yes i think this will work… but my problem is that my field is timestamp not number… i can’t select any of them here… anybwork around here ?

14

Are u trying to do arithmetic operations on time stamp?

15

I can do it with kibana script fields…

Is there a work around… should i convert the timestamp to epoch time ?

16

Interesting…epoch is one option . i am thinking on this line too …but not having any answers at the moment

17

Yes the epoch time did the trick… but in cost of having two more fields :frowning:
for grafana it should be allowed as it is a normal operation for elastic.

2 Likes
18

May be @michaelaeino you can raise a GIT issue - feature request ! Also please post your field examples on elastic search and query that you wrote on grafana …it will help !

19

Created feeature request #8899
https://github.com/grafana/grafana/issues/8899

1 Like
20

@daniellee in this example, instead of doc[‘@value’].value function can I use sum instead?

I have an elastic seach index with multiple types having same fields, i want to sum them up all