Calls and Messages display from FreePBX

Share your Projects!
1

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.
HA_PBX

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
3 Likes
2

@steveintas, nice work!

BTW, could you post some instructions to create the HA user in the SQL, please?

Thanks!

3

Not sure if I can remember the details. I use webmin to manage by pbx box and you can use the sql module in that to examine tables,users, permissions etc and create new ones and grant permissions. This is a lot easier than trusting my rusty knowledge of command line instructions. The sql userid has to be name@ipaddress to work from the ha box.
I would suggest use something like webmin or get one of the sql workspace programs, try and then debug issues.
Sorry its not very specific, i kind of hacked my way through it at the time, testing it as i went to find issues. From memory you may have to specifically reinstate the freepbx permissions after adding another sql user. That may just have been me messing something up or may not.