{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"description": "Landing Dashboard and call overview information",
"editable": false,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 2618282053234688,
"links": [],
"panels": [
{
"fieldConfig": {
"defaults": {},
"overrides": []
},
"gridPos": {
"h": 2,
"w": 24,
"x": 0,
"y": 0
},
"id": 12,
"options": {
"code": {
"language": "plaintext",
"showLineNumbers": false,
"showMiniMap": false
},
"content": "# 3CX call overview",
"mode": "markdown"
},
"pluginVersion": "12.4.0-20160616057",
"title": "",
"type": "text"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": 0
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 0,
"y": 2
},
"id": 1,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT\r\n COUNT(DISTINCT t.main_call_history_id) AS total_calls\r\nFROM\r\n `3cx_call_data.public_cdroutput` AS t\r\nWHERE $__timeFilter(t.cdr_started_at)\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Total number of calls",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"total_calls": "Total calls"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"noValue": "0",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": 0
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 3,
"y": 2
},
"id": 6,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH cdrs AS (\r\n\tSELECT\r\n\t\t--c.main_call_history_id,\r\n\t\t--c.cdr_id,\r\n\t\tc.cdr_answered_at,\r\n\t\tROW_NUMBER() OVER (PARTITION BY c.main_call_history_id ORDER BY c.cdr_id DESC) AS row_num\t\t-- 1 = last cdr\r\n\tFROM `3cx_call_data.public_cdroutput` AS c\r\n WHERE $__timeFilter(c.cdr_started_at)\r\n)\r\nSELECT count(*) AS answered_calls\r\nFROM cdrs AS c\r\nWHERE c.row_num = 1 -- 1 = last cdr\r\nAND c.cdr_answered_at IS NOT NULL\t\t-- last cdr is answered\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Answered Calls",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"answered_calls": "Calls answered at the end"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": 0
}
]
},
"unit": "percent"
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 6,
"y": 2
},
"id": 2,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"text": {
"percentSize": 1
},
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH cdrs AS (\r\n\tSELECT\r\n\t\tc.cdr_answered_at,\r\n\t\tROW_NUMBER() OVER (PARTITION BY c.main_call_history_id ORDER BY c.cdr_id DESC) AS row_num\t\t-- 1 = last cdr\r\n\tFROM `3cx_call_data.public_cdroutput` AS c\r\n WHERE $__timeFilter(c.cdr_started_at)\r\n)\r\nSELECT\r\n\t(COUNTIF(c.cdr_answered_at IS NOT NULL) * 100.0) / COUNT(*) AS percent_answered\r\nFROM cdrs AS c\r\nWHERE c.row_num = 1 -- 1 = last cdr\r\nHAVING COUNT(*) > 0\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "% Answered Calls",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"percent_answered": "Calls answered at the end"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "red",
"value": 0
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 9,
"y": 2
},
"id": 5,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH cdrs AS (\r\n\tSELECT\r\n\t\t--c.main_call_history_id,\r\n\t\t--c.cdr_id,\r\n\t\tc.cdr_answered_at,\r\n\t\tROW_NUMBER() OVER (PARTITION BY c.main_call_history_id ORDER BY c.cdr_id DESC) AS row_num\t\t-- 1 = last cdr\r\n\tFROM `3cx_call_data.public_cdroutput` AS c\r\n WHERE $__timeFilter(c.cdr_started_at)\r\n)\r\nSELECT count(*) AS unanswered_calls\r\nFROM cdrs AS c\r\nWHERE c.row_num = 1 -- 1 = last cdr\r\nAND c.cdr_answered_at IS NULL\t\t\t\t-- last cdr is not answererd\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Not Answered",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"unanswered_calls": "Calls not answered at the end"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": 0
}
]
},
"unit": "s"
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 12,
"y": 2
},
"id": 3,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n SUM(TIMESTAMP_DIFF(cdr_ended_at, cdr_answered_at, SECOND)) AS total_talking_time\r\nFROM `3cx_call_data.public_cdroutput`\r\nWHERE cdr_answered_at IS NOT NULL\r\nAND $__timeFilter(cdr_started_at)\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Total Talking Duration",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"total_talking_time": "Total talking duration"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": 0
}
]
},
"unit": "s"
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 15,
"y": 2
},
"id": 7,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n SUM(TIMESTAMP_DIFF(t.cdr_ended_at, t.cdr_answered_at, SECOND)) / COALESCE(COUNT(DISTINCT t.main_call_history_id), 1) AS avg_talking_duration\r\nFROM `3cx_call_data.public_cdroutput` AS t\r\nWHERE $__timeFilter(t.cdr_started_at)\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Average Talking Duration",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"avg_talking_duration": "Average talking duration"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": 0
}
]
},
"unit": "s"
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 18,
"y": 2
},
"id": 13,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH calls AS (\r\n SELECT\r\n t.main_call_history_id,\r\n TIMESTAMP_DIFF(max(t.cdr_ended_at), min(t.cdr_started_at), SECOND) AS duration\r\n FROM `3cx_call_data.public_cdroutput` AS t\r\n WHERE $__timeFilter(t.cdr_started_at)\r\n GROUP BY t.main_call_history_id\r\n)\r\nSELECT\r\n avg(c.duration) AS avg_call_duration\r\nFROM calls AS c\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Average Call Duration",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"avg_call_duration": "Average call duration"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "blue",
"value": 0
}
]
},
"unit": "s"
},
"overrides": []
},
"gridPos": {
"h": 4,
"w": 3,
"x": 21,
"y": 2
},
"id": 8,
"options": {
"colorMode": "value",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"percentChangeColorMode": "standard",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showPercentChange": false,
"textMode": "auto",
"wideLayout": true
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH calls AS (\r\n SELECT\r\n t.main_call_history_id,\r\n TIMESTAMP_DIFF(max(t.cdr_ended_at), min(t.cdr_started_at), SECOND) AS duration\r\n FROM `3cx_call_data.public_cdroutput` AS t\r\n WHERE $__timeFilter(t.cdr_started_at)\r\n GROUP BY t.main_call_history_id\r\n)\r\nSELECT\r\n max(c.duration) AS longest_call_duration\r\nFROM calls AS c\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Longest Call Duration",
"transformations": [
{
"id": "organize",
"options": {
"excludeByName": {},
"includeByName": {},
"indexByName": {},
"renameByName": {
"longest_call_duration": "Longest call duration"
}
}
}
],
"type": "stat"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green",
"value": 0
},
{
"color": "yellow",
"value": 25
},
{
"color": "orange",
"value": 50
},
{
"color": "red",
"value": 75
}
]
},
"unit": "percent"
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 3,
"x": 0,
"y": 6
},
"id": 9,
"options": {
"minVizHeight": 75,
"minVizWidth": 75,
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true,
"sizing": "auto"
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH all_calls AS (\r\n SELECT\r\n COALESCE(COUNT(DISTINCT t.main_call_history_id), 0) AS count\r\n FROM `3cx_call_data.public_cdroutput` AS t\r\n WHERE $__timeFilter(t.cdr_started_at)\r\n AND t.destination_entity_type = 'extension'\r\n),\r\nmissed_calls AS (\r\n SELECT\r\n COALESCE(COUNT(DISTINCT t.main_call_history_id), 1) AS count\r\n FROM `3cx_call_data.public_cdroutput` AS t\r\n WHERE t.cdr_answered_at IS NULL\r\n AND $__timeFilter(t.cdr_started_at)\r\n AND t.destination_entity_type = 'extension'\r\n)\r\nSELECT\r\n missed_calls.count * 100.0 / all_calls.count AS missed_call_rate\r\nFROM all_calls, missed_calls\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "% Extension Missed Calls",
"type": "gauge"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"mappings": [
{
"options": {
"cancelled": {
"index": 3,
"text": "Canceled"
},
"continued_in": {
"index": 2,
"text": "Continued further"
},
"deflected": {
"index": 4,
"text": "Deflected"
},
"dst_participant_terminated": {
"index": 0,
"text": "Ended by destination participant"
},
"redirected": {
"index": 5,
"text": "Redirected"
},
"rejected": {
"index": 6,
"text": "Rejected"
},
"src_participant_terminated": {
"index": 1,
"text": "Ended by source participant"
}
},
"type": "value"
}
]
},
"overrides": [
{
"__systemRef": "hideSeriesFrom",
"matcher": {
"id": "byNames",
"options": {
"mode": "exclude",
"names": [
"count",
"Canceled",
"Rejected"
],
"prefix": "All except:",
"readOnly": true
}
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": false,
"tooltip": true,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 9,
"w": 6,
"x": 3,
"y": 6
},
"id": 11,
"options": {
"legend": {
"displayMode": "table",
"placement": "right",
"showLegend": true
},
"pieType": "pie",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": true
},
"sort": "desc",
"tooltip": {
"hideZeros": false,
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "WITH cdrs AS (\r\n\tSELECT\r\n\t\tc.termination_reason,\r\n\t\tROW_NUMBER() OVER (PARTITION BY c.main_call_history_id ORDER BY c.cdr_id DESC) AS row_num\t\t-- 1 = last cdr\r\n\tFROM `3cx_call_data.public_cdroutput` AS c\r\n WHERE $__timeFilter(c.cdr_started_at)\r\n)\r\nSELECT\r\n c.termination_reason,\r\n count(*) AS count\r\nFROM cdrs AS c\r\nWHERE c.row_num = 1 -- 1 = last cdr\r\nAND c.termination_reason IS NOT NULL\r\nGROUP BY termination_reason\r\nORDER BY count DESC\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Final Termination Reasons",
"type": "piechart"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"description": "Time series Graph",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": 0
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "num_calls"
},
"properties": [
{
"id": "displayName",
"value": "Number of calls"
}
]
}
]
},
"gridPos": {
"h": 9,
"w": 15,
"x": 9,
"y": 6
},
"id": 4,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"hideZeros": false,
"mode": "single",
"sort": "none"
},
"xTickLabelRotation": 0,
"xTickLabelSpacing": 200
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n TIMESTAMP_TRUNC(cdr_started_at, HOUR) AS time_bucket, \r\n COUNT(DISTINCT t.main_call_history_id) AS num_calls\r\nFROM `3cx_call_data.public_cdroutput` AS t\r\nWHERE $__timeFilter(t.cdr_started_at)\r\nGROUP BY time_bucket\r\nORDER BY time_bucket\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Amount of Calls over Time",
"type": "barchart"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"description": "Top 10 most dialed numbers",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "line+area"
}
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green",
"value": 0
},
{
"color": "yellow",
"value": 60
},
{
"color": "orange",
"value": 70
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "destination_dn_number"
},
"properties": [
{
"id": "displayName",
"value": "Dialed number"
}
]
},
{
"matcher": {
"id": "byName",
"options": "call_count"
},
"properties": [
{
"id": "displayName",
"value": "Number of calls"
}
]
}
]
},
"gridPos": {
"h": 14,
"w": 8,
"x": 0,
"y": 15
},
"id": 10,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"hideZeros": false,
"mode": "single",
"sort": "none"
},
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n CASE WHEN c.destination_participant_phone_number IS NULL OR c.destination_participant_phone_number = '' THEN c.destination_dn_number ELSE c.destination_participant_phone_number END AS destination_dn_number, \r\n COUNT(DISTINCT c.main_call_history_id) AS call_count\r\nFROM `3cx_call_data.public_cdroutput` AS c\r\nWHERE $__timeFilter(c.cdr_started_at)\r\nAND c.destination_dn_number NOT IN ('EndCall', 'QCB')\r\nGROUP BY destination_dn_number\r\nORDER BY call_count DESC\r\nLIMIT 10\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Top 10 Most Dialled Numbers",
"type": "barchart"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"description": "Top 10 answered extensions",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "line+area"
}
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green",
"value": 0
},
{
"color": "yellow",
"value": 60
},
{
"color": "orange",
"value": 70
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "destination_dn_number"
},
"properties": [
{
"id": "displayName",
"value": "Extension number"
}
]
},
{
"matcher": {
"id": "byName",
"options": "call_count"
},
"properties": [
{
"id": "displayName",
"value": "Number of answered calls"
}
]
}
]
},
"gridPos": {
"h": 14,
"w": 8,
"x": 8,
"y": 15
},
"id": 14,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"hideZeros": false,
"mode": "single",
"sort": "none"
},
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n destination_dn_number, \r\n COUNT(*) AS call_count\r\nFROM `3cx_call_data.public_cdroutput`\r\nWHERE $__timeFilter(cdr_started_at)\r\nAND destination_entity_type = 'extension'\r\nAND cdr_started_at IS NOT NULL\r\nGROUP BY destination_dn_number\r\nORDER BY call_count DESC\r\nLIMIT 10\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Top 10 Answered Extensions",
"type": "barchart"
},
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"description": "Top 10 extensions with missed calls",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"axisBorderShow": false,
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 80,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 1,
"scaleDistribution": {
"type": "linear"
},
"thresholdsStyle": {
"mode": "line+area"
}
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green",
"value": 0
},
{
"color": "yellow",
"value": 60
},
{
"color": "orange",
"value": 70
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "destination_dn_number"
},
"properties": [
{
"id": "displayName",
"value": "Extension number"
}
]
},
{
"matcher": {
"id": "byName",
"options": "call_count"
},
"properties": [
{
"id": "displayName",
"value": "Number of missed calls"
}
]
}
]
},
"gridPos": {
"h": 14,
"w": 8,
"x": 16,
"y": 15
},
"id": 15,
"options": {
"barRadius": 0,
"barWidth": 0.97,
"fullHighlight": false,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
"tooltip": {
"hideZeros": false,
"mode": "single",
"sort": "none"
},
"xTickLabelRotation": 0,
"xTickLabelSpacing": 0
},
"pluginVersion": "12.4.0-20160616057",
"targets": [
{
"datasource": {
"type": "grafana-bigquery-datasource",
"uid": "fejbhs11adslcd"
},
"editorMode": "code",
"format": 1,
"location": "",
"project": "grafana3cx",
"rawQuery": true,
"rawSql": "SELECT \r\n destination_dn_number, \r\n COUNT(*) AS call_count\r\nFROM `3cx_call_data.public_cdroutput`\r\nWHERE $__timeFilter(cdr_started_at)\r\nAND destination_entity_type = 'extension'\r\nAND cdr_answered_at IS NULL\r\nGROUP BY destination_dn_number\r\nORDER BY call_count DESC\r\nLIMIT 10\r\n;",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"title": "Top 10 Extensions with Missed Calls",
"type": "barchart"
}
],
"preload": false,
"refresh": "30m",
"schemaVersion": 42,
"tags": [
"3CX"
],
"templating": {
"list": []
},
"time": {
"from": "now-30d",
"to": "now"
},
"timepicker": {},
"timezone": "browser",
"title": "01. Overview (BigQuery)",
"uid": "36bd6045-f60b-4f51-9933-f8f79d1cec21",
"version": 1,
"weekStart": ""
}
