Deleting a large number of DIDs from FreePBX


#1

Hello,

We’ve had a bit of a tidy up and I need to delete about 400 DID numbers from FreePBX as we are dropping the numbers with our supplier as they are no longer used.

I am comfortable using SQL so would there be any repercussions from dropping these records directly from the asterisk.incoming table and then simply issuing fwconsole reload afterwards?

400 numbers is a rather lot to do by hand! I thought of using the Bulk handler although I don’t think that would help me!

Thanks


(Lorne Gaetz) #2

I’m 95% sure deleting from asterisk.incoming will be fine. Prob best to start by deleting a single route to test the water.


(Jared Busch) #3

image


(Lorne Gaetz) #4

… on a Friday afternoon.


(TheJames) #5

I ALWAYS recommend using freepbx internals. This makes sure all the hooks process as well

<?php
include '/etc/freepbx.conf';

$f = FreePBX::Create();

$items = [
    [
        'did' => '1234',
        'cid' => '1234',
    ],
];

foreach($items as $item){
    $f->Core->delDID($item['did'], $item['cid']);
}


(Lorne Gaetz) #6

Very nice. Ask for dynamite, get a scalpel.


#7

Thanks :slight_smile:

Amazing!! So, just list all my did’s to drop in that array and then run the php file?


#8

interesting - is there documentation on these somewhere?


(Itzik) #9

James can probably link the exact docs, but take a look at the developer corner home it has a ton of information.

https://wiki.freepbx.org/display/FOP/Developer+Corner+Home


(Jared Busch) #10

Just the source code. No one has ever fully documented this to my knowledge. Would be great to be shown wrong.


(TheJames) #11

I spent a good portion of my time documenting development while at sangoma and it’s all in the wiki

I also do examples and some docs on my on my github which I usually tweet out.


(Daniel Friedman) #12

Hello @Fraser,

Based on @jfinstrom tip (thank you very much for that), here is a basic PHP script to delete all the DIDs from the inbound routes. If you need to filter them, then you will need to edit the MySQL query. If you have different database settings, change them as well. I tried this script on one of my backup machines and it deleted hundreds of dids in a second. Don’t forget to reload the web page to test if it works.

<?php
include '/etc/freepbx.conf';

//Prepare database connection
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'asterisk';

//Connect to the database
$conn = new mysqli($servername, $username, $password, $dbname);

//Check the connection
if ($conn ->connector_error) {
  die("Connection failed: " . $conn->connector_error);
}

//Create Array function
function ResultToArray($result) {
  $rows = array();
  while ($row = $result->fetch_assoc()) {
    $rows[] = $row; 
  }
  return $rows;
}

//Query the DIDs
//$query = "select cidnum,extension from incoming where extension like '0%'";
$query = "select cidnum,extension from incoming";
$result = $conn->query($query);
$rows = ResultToArray($result);
$result->free();

//Prepare the DIDs deletion
$f = FreePBX::Create();

foreach($rows as $item){
  $f->Core->delDID($item['extension'], $item['cidnum']);
  echo "The DID " . $item['extension'] . " with CID " . $item['cidnum'] . " has been deleted\r\n";
}

echo "\r\nDone!\r\n";  

Thank you,

Daniel Friedman
Trixton LTD.


(Jared Busch) #13

@danielf there are built in methods to handle queries to the MySQL database. Do not code in DB connections.

// Load FreePBX bootstrap environment
require_once('/etc/freepbx.conf');

// Initialize a database connection
global $db;

// This pulls every number in contact maanger that is part of the group specified by $contact_manager_group
$sql = "SELECT cen.number, cge.displayname, cen.type, cen.E164, 0 AS 'sortorder' FROM contactmanager_group_entries AS cge LEFT JOIN contactmanager_entry_numbers AS cen ON cen.entryid = cge.id WHERE cge.groupid = (SELECT cg.id FROM contactmanager_groups AS cg WHERE cg.name = '$contact_manager_group') ORDER BY cge.displayname, cen.number;";

// Execute the SQL statement
$res = $db->prepare($sql);
$res->execute();
// Check that something is returned
if (DB::IsError($res)) {
    // Potentially clean this up so that it outputs pretty if not valid                
    error_log( "There was an error attempting to query contactmanager<br>($sql)<br>\n" . $res->getMessage() . "\n<br>\n");
} else {
    $contacts = $res->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($contacts as $i => $contact){
etc...........

Full example on my github: https://github.com/sorvani/freepbx-helper-scripts/blob/master/ContactManager_to_Fanvil_AddressBook/cm_to_fv_ab.php


#14

Sorry for the late reply.

Thanks for all the responses, I’m yet to do this task but I’ll definitely go down @jfinstrom’s route so thank you for that.

Thanks to @danielf and @sorvani however I don’t want to delete all my DIDs, only a selection :smirk:

Fraser


(Daniel Friedman) #15

Hello @Fraser,

You did not notice that you can change the SQL query of the DID selection in my script:

$query = "select cidnum,extension from incoming where extension like '0%'";

You do not have to delete all your DIDs if you do not need to. Although, sometimes it is quicker to delete all the DIDs from the database and then add the necessary DIDs through the bulk handler module.

Thank you,

Daniel Friedman
Trixton LTD.