How to unregister and register users in realtime from MySQL database?

Right now I have inserted user details in MySQL database in asterisk SIP server. I am able to make calls from client application.

  1. If I want to unregister the user , is the only way is to delete the entire record from database?
  2. If I want to re-register them, then the only way is to insert the entire record into database again?

Following is the create statement of the table that has user details. These columns might give you an idea, whether I can register and unregister with a value in it.

CREATE TABLE `ast_sipfriends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `callerid` varchar(80) DEFAULT NULL,
  `defaultuser` varchar(80) NOT NULL,
  `regexten` varchar(80) NOT NULL,
  `secret` varchar(80) DEFAULT NULL,
  `mailbox` varchar(50) DEFAULT NULL,
  `accountcode` varchar(20) DEFAULT NULL,
  `context` varchar(80) DEFAULT NULL,
  `amaflags` varchar(7) DEFAULT NULL,
  `callgroup` varchar(10) DEFAULT NULL,
  `canreinvite` char(3) DEFAULT 'yes',
  `defaultip` varchar(15) DEFAULT NULL,
  `dtmfmode` varchar(7) DEFAULT NULL,
  `fromuser` varchar(80) DEFAULT NULL,
  `fromdomain` varchar(80) DEFAULT NULL,
  `fullcontact` varchar(80) DEFAULT NULL,
  `host` varchar(31) NOT NULL,
  `insecure` varchar(4) DEFAULT NULL,
  `language` char(2) DEFAULT NULL,
  `md5secret` varchar(80) DEFAULT NULL,
  `nat` varchar(5) NOT NULL DEFAULT 'no',
  `deny` varchar(95) DEFAULT NULL,
  `permit` varchar(95) DEFAULT NULL,
  `pickupgroup` varchar(10) DEFAULT NULL,
  `port` varchar(5) NOT NULL,
  `qualify` char(3) DEFAULT NULL,
  `restrictcid` char(1) DEFAULT NULL,
  `rtptimeout` char(3) DEFAULT NULL,
  `rtpholdtimeout` char(3) DEFAULT NULL,
  `type` varchar(6) NOT NULL DEFAULT 'friend',
  `disallow` varchar(100) DEFAULT 'all',
  `allow` varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
  `musiconhold` varchar(100) DEFAULT NULL,
  `regseconds` int(11) NOT NULL DEFAULT '0',
  `ipaddr` varchar(15) NOT NULL,
  `cancallforward` char(3) DEFAULT 'yes',
  `lastms` int(11) NOT NULL,
  `useragent` char(255) DEFAULT NULL,
  `regserver` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=899 DEFAULT CHARSET=latin1 |

Thanks.

I don’t really know what You mean. You just showed us that You created an empty table. If You put into proper client details, You have to use softphone and register Your account with credentials You put into table and click ‘register’ in it. If You want to unregister, You click unregister, just it;-).

For device 3100…

sip prune realtime peer 3100

sip show peer 3100 load

Sivan,

I presume you are asking whether there is a column in your table that holds a value which can be changed to stop Asterisk from accepting registrations for this user, and you only need to flip this variable back to re-enable him/her?

Probably simply changing the password will do what you want.

Remember to kick the running Asterisk to re-read the table, or in other words flush the cache, as mkozusnik already hinted upon. Otherwise it may not even consider going to the database again.

Hoppie

you can add one more column “regstatus” as true/false or 1/0. And then you can just fetch only that rows from tables , that have regstatus=1 or regstatus!=0 . By default the value of regstatus should be one(1) , replace it with 0 for user you don’t want now but needs in future.

If you don’t want that user permanently . then delete it.