I’ve put together a fairly simple method of displaying a list of recent calls and the number of voicemail messages for my FreePBX installation. It uses the SQL component to query the call records database on the freepbx box and display the results in home assistant. Note, you will probably need to create a HA user in the SQL database on the PBX box and grant rights to the ASTERISKCDRDB database on the PBX box.
YAML for PBX sensors:
Note:
- [Myphone] is my phone number
- The PBX CDR is mysql://[sql userid]:[sql password]@[PBX IP address]/asteriskcdrdb?charset=utf8
sensor:
- platform: sql
db_url: !secret pbx_cdr_url
queries:
- name: CDR_status
query: "select CURRENT_TIMESTAMP AS current_date_time;"
column: 'current_date_time'
- name: CDR_calls
query: "select count(distinct uniqueid) as calls from cdr where did='<<myphone>>' and calldate>DATE_SUB(CURDATE(), INTERVAL 1 DAY);"
column: 'calls'
- name: CDR_voicemails
query: "select count(distinct recordingfile) as voicemails from cdr where did='<<myphone>>' and recordingfile<>'' and calldate>DATE_SUB(CURDATE(), INTERVAL 3 DAY);"
column: 'voicemails'
- name: CDR_t_1
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 0, 1"
column: 'calldate'
- name: CDR_n_1
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 0, 1"
column: 'src'
- name: CDR_t_2
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 1, 1"
column: 'calldate'
- name: CDR_n_2
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 1, 1"
column: 'src'
- name: CDR_t_3
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 2, 1"
column: 'calldate'
- name: CDR_n_3
query: "select distinct calldate, src, cnum from cdr where did='<<myphone>>' order by calldate desc limit 2, 1"
column: 'src'
The Lovelace display is
type: vertical-stack
cards:
# row 0 -------------------------------------------------
- type: 'custom:button-card'
entity: sensor.cdr_status
name: 'PABX Calls'
show_state: true
icon: dummy
style:
- font-size: 25px
- text-align: center
- height: 55px
- padding: 2px
# row 1 -------------------------------------------------
- type: horizontal-stack
cards:
- type: 'custom:button-card'
entity: sensor.cdr_calls
name: Recent Calls
show_state: true
icon: test
state:
- value: 1
operator: '>='
style:
- font-weight: bold
- value: ''
style:
- background-color: rgb(30,30,30)
- color: rgb(0, 0, 0)
- type: 'custom:button-card'
entity: sensor.cdr_voicemails
name: Messages
show_state: true
icon: test
state:
- value: 1
operator: '>='
style:
- font-weight: bold
- background-color: rgb(0,204,0)
- color: rgb(0, 0, 0)
- animation: blink 2s ease infinite
- border-style: solid
- background-color: rgb(255,0,0)
- value: ''
style:
- background-color: rgb(30,30,30)
- color: rgb(0, 0, 0)
# row 0 -------------------------------------------------
- type: glance
columns: 2
show_icon: false
show_name: false
entities:
- sensor.cdr_t_1
- sensor.cdr_n_1
- sensor.cdr_t_2
- sensor.cdr_n_2
- sensor.cdr_t_3
- sensor.cdr_n_3
- sensor.cdr_t_4
- sensor.cdr_n_4
- sensor.cdr_t_5
- sensor.cdr_n_5
- sensor.cdr_t_6
- sensor.cdr_n_6