Custom visualization in kibana

Elastic Stack Kibana
1

Help me to visualize it on kibana.
This is the call_log data collection of the tele-callers of our company. The user is tele-callers. 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"
          }, 
          "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.

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)

sample sql query:

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

651×515 42.7 KB

2

If you write the work_time and the idle_time to your elasticsearch documents this would be very simple. Alternatively you can create these fields with "scripted fields".

3

I think, you didn't get my point. Please see the hint and help me to script the function.