Convert MySQL query to elasticsearch

Elastic Stack Kibana
1

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

SELECT 
  user, 
  call_date, 
  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
1 Like
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.

1 Like
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.

regds,
croos.

qqq.JPG723×501 50.6 KB

5

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

6

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

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!

1 Like
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.

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?

hint:
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.

1364×618 36.4 KB

10

Try using "grouped" in the Options?

Screen Shot 2016-02-09 at 9.23.15 AM.png2808×1422 68 KB

1 Like
11

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

and How to script the aggregation function for Y axis?

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?

1 Like
13

Hi,
I uploaded the current problem in my task.

new.png1366×768 60 KB

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.

14

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

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;
1 Like
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.

Regards,
Croos.

v3.JPG1365×624 49.8 KB

1 Like
17

Sweet! That looks awesome! :slight_smile:

1 Like