Frequently Asked Question
Useful FreePBX MariaDB Queries
Last Updated about an hour ago
Useful FreePBX MariaDB Queries
List extensions and associated CID
SELECT
u.extension,
u.name,
u.outboundcid AS cid
FROM users u
LEFT JOIN devices d ON d.id = u.extension
ORDER BY u.extension;
List inbound routes with destination details
SELECT
extension AS did,
destination,
description
FROM asterisk.incoming
ORDER BY extension;
Find extensions with no devices attached
SELECT
u.extension,
u.name
FROM users u
LEFT JOIN devices d ON d.id = u.extension
WHERE d.id IS NULL;
Count calls per extension (last 30 days)
SELECT
u.extension,
COUNT(c.cid) AS call_count
FROM cdr c
JOIN users u ON c.dstexten = u.extension
WHERE c.starttime >= NOW() - INTERVAL 30 DAY
GROUP BY u.extension
ORDER BY call_count DESC;
List trunks with status and type
SELECT
id,
name,
trunktype,
status,
host,
port
FROM trunks
ORDER BY name;
Find unused SIP trunks
SELECT
t.name,
t.host,
t.port
FROM trunks t
LEFT JOIN peers p ON t.id = p.trunkid
WHERE p.trunkid IS NULL
ORDER BY t.name;
Check database connection status
SELECT
COUNT(*) AS active_connections
FROM information_schema.processlist
WHERE COMMAND = 'Query'
AND HOST LIKE CONCAT('%:%', SUBSTRING_INDEX(USER(), '@', -1), '%');
List all configured dial patterns
SELECT
dialplanpattern,
description
FROM dialplan
WHERE dialplanpattern IS NOT NULL
ORDER BY dialplanpattern;
Useful FreePBX MariaDB Queries
List extensions and associated CID
SELECT
u.extension,
u.name,
u.outboundcid AS cid
FROM users u
LEFT JOIN devices d ON d.id = u.extension
ORDER BY u.extension
List inbound routes with destination details
SELECT
extension AS did,
destination,
description
FROM asterisk.incoming
ORDER BY extension
Find extensions with no devices attached
SELECT
u.extension,
u.name
FROM users u
LEFT JOIN devices d ON d.id = u.extension
WHERE d.id IS NULL
Count calls per extension (last 30 days)
SELECT
u.extension,
COUNT(c.cid) AS call_count
FROM cdr c
JOIN users u ON c.dstexten = u.extension
WHERE c.starttime >= NOW() - INTERVAL 30 DAY
GROUP BY u.extension
ORDER BY call_count DESC
List trunks with status and type
SELECT
id,
name,
trunktype,
status,
host,
port
FROM trunks
ORDER BY name
Find unused SIP trunks
SELECT
t.name,
t.host,
t.port
FROM trunks t
LEFT JOIN peers p ON t.id = p.trunkid
WHERE p.trunkid IS NULL
ORDER BY t.name
Check database connection status
SELECT
COUNT(*) AS active_connections
FROM information_schema.processlist
WHERE COMMAND = Query
AND HOST LIKE CONCAT(%:%, SUBSTRING_INDEX(USER(), @, -1), %)
List all configured dial patterns
SELECT
dialplanpattern,
description
FROM dialplan
WHERE dialplanpattern IS NOT NULL
ORDER BY dialplanpattern
List inbound routes and link in outbound cid from extensions
SELECT
i.extension AS did,
i.description AS route_name,
SUBSTRING_INDEX(i.destination, ',', 1) AS dest_type,
SUBSTRING_INDEX(SUBSTRING_INDEX(i.destination,',',2),',',-1) AS dest_target,
u.outboundcid AS outbound_cid
FROM asterisk.incoming i
LEFT JOIN asterisk.users u
ON u.extension = SUBSTRING_INDEX(SUBSTRING_INDEX(i.destination,',',2),',',-1)
ORDER BY i.extension;
