Convert MySQL query to elasticsearch

(Croos Nilukshan) #1

Help me to convert this MySQL query to elasticsearch (json) query and how to visualize it on kibana.

  sum(length_in_sec) as 'work_time', 
  (max(end_epoch)-min(start_epoch))-sum(length_in_sec) as 'idle_time' 
FROM vicidial_log 
WHERE date(call_date)='2016-01-12' 
group by user

Mysql to elasticsearch Query conversion
(Tanya Bragin) #2

If you provide details on how you've structured data in Elasticsearch, it may be easier to help you. What type of visualization are you looking for... a table? In that case, your SELECT would map to metrics in the Y-axis, Date is just something you'd select using a time picker, and GROUP BY is a Terms Aggregation.

(Croos Nilukshan) #4

Dear tbragin,

Thanks a lot for your response. This is the call_log data collection of the tele-callers of our company. The mapping of my index is following:

"mappings": {
      "vicidial_log": {
        "_timestamp": {
          "enabled": true
        "properties": {         
          "call_date": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss"
          "end_epoch": {
            "type": "integer"
          "lead_id": {
            "type": "integer"
          "length_in_sec": {
            "type": "integer"
          "phone_number": {
            "type": "string",
            "analyzer": "standard"
          "start_epoch": {
            "type": "integer"
          "log_id": {
            "type": "string",
            "analyzer": "standard"
          "user": {
            "type": "string",
            "analyzer": "standard"

I have attached the sample data, and expected visualization hear. Is this understandable? or you want more details, I'm ready to give you.


(Tanya Bragin) #5

The combination of bar chart and data table visualizations should work for you. What have you tried so far?

(Croos Nilukshan) #6

Only the bar chart. The table is just for show my sample data to you.

(Tanya Bragin) #7

I think I understand the reason for your question now - you are not indexing "idle time" and "talk time" directly in ES. You should be able to calculate the "idle time" and "talk time" as scripted fields in Kibana, assuming you can do that based on data from a single document. If your calculation needs to go across document, you might need to use a script as an input to Date Histogram (see example here). Hope this helps!

(Croos Nilukshan) #8

Thanks a lot Bragin,
You got my question. Now I understand the scripted field based on data from a single document and had a idea about Date Histogram. As you mentioned, My case is need to go across document. Now my question is how to get the maximum and minimum value of a field from across document? Would you please explain me a little bit detail.

(Croos Nilukshan) #9

Now I partially complete the bar chart that represent the sum of call length Vs user and call date. How to apply the scripting function for visualize an additional bar parallel with existing bar to represent the Idle time?

Idle time of an employee in particular date is calculate by the following equation,
idle_time=(max(end_epoch) - min(start_epoch)) - sum(length_in_sec)

max(end_epoch), min(start_epoch) are have to calculate for each and every day.

(Tanya Bragin) #10

Try using "grouped" in the Options?

(Croos Nilukshan) #11

ok. I got it. Would you please explain my previous question..

and How to script the aggregation function for Y axis?

(Tanya Bragin) #12

Yeah, there is an example of it here: Display concurrency in data on Kibana

I'm happy to answer a specific question if you can tell me what you already tried based on your reading of that thread?

(Croos Nilukshan) #13

I uploaded the current problem in my task.

And as I mentioned in earlier, the Minimum and Maximum values are have to get from across the field. Then as mentioned in the image, want to display the idle_time like this.

(Croos Nilukshan) #14

I referred and understand the aggregation in elasticsearch also. But still have no idea about the script for the above task in kibana.

(Tanya Bragin) #15

You may need to do something more complicated, such as pass a script to the date aggregation in order to do some calculations across the returned buckets.

Notice in the example I linked to in the thread above, the script actually goes through values returned in the buckets and calculates an array of durations, returning that as a result:

start = doc['recordStart'].value; 
duration = doc['recordDuration'].value; 
l = []; 
for (long i = 0; i < duration; i += 60000) 
	{ l.add(start + i); }; 
return l;

(Croos Nilukshan) #16

Some how I got my first visualization of my task. Still have some issue in scripting. As you mentions, I'll have a deep look on that thread and catch you soon. Anyway thanks for your support.


(Tanya Bragin) #17

Sweet! That looks awesome! :slight_smile:

(system) #18