Vicidialer info extraction Help.

[techlabVAIS]

I have an older HP vicidialer 2008-2009 from which im trying to extract specific info from that system. I came across some SQL files and im having a hard time extracting the info into a csv file so i can use it in my new system
Please advice

System OS is Cent OS 5.2

[techlabVAIS]

Hi any ideas?

[Vince-0]

Use mysqldump or any other MySQL backup mechanism to move your asterisk database to your new server.

Just make sure the DB schemas match the Vicidial astguiclient version otherwise you need to do DB schema upgrades.

Vin.

[williamconley]

Hi any ideas?

1) Never reply to your own post. You can drop off the radar (permanently) for those of us who check "unanswered" posts so we can reply to those who have not been helped. phpBB believes your second post was an answer and removed you from the Unanswered list! LOL

2) Vicidial has a perl script for backup. It'll gather EVERYTHING you need to transport your entire Vicidial system to another server. Of course, this will include the DB. Regardless of what installation method was used (goautodial, vicibox, scratch), this backup script usually does a great job of gathering what you need. It's in the /usr/share/astguiclient folder with everything else. Run it with "--help" for instructions/options. The backup zip will be stored in /var/log/astguiclient/archive after you run the script.

3) mysqldump of the entire asterisk database is the simplest method of just grabbing the DB.

Oh, and before I forget:

1) Welcome to the Party!

2) As you are obviously new here, I have some suggestions to help us all help you:

When you post, please post your entire configuration including (but not limited to) your installation method and vicidial version with build.

This IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on EFLO.net, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "from scratch" you must post your operating system and should also post the .iso version from which you installed your original operating system. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600

[techlabVAIS]

Im sorry about the "commenting on my own post" will not do again.

So i followed you guys instructions and created a mysqldump file by using "mysqldump -u root -p asterisk > asteriskbackupmay26.sql

and what i get is not what i was expected. I am really new to this and this is what I might need to get started with the new server but what I ended up getting was commands and stuff I could figure out. I was sort of expecting to get a list of all the phone numbers and contacts that were inputted into this system.
Is this even possible? Can i get all of this information in a SQL file and possibly get them into a CSV file?

[williamconley]

IF your old and new server have the same version of Vicidial installed, this works:

First make a backup of your existing asterisk database (which is the name of the database vicidial uses to store all your data):

Code: Select all

mysqldump -p asterisk > BACKUP_DATE_MM_YY_DD_HHSS.sql

Then you clear the DB to allow replacing it with the new database:

Code: Select all

mysql -p -e "drop database asterisk"

Code: Select all

mysql -p -e "create database asterisk"

Now you have an empty DB awaiting the restoration of the data from the other system.

Execute your prior DB backup into the new server:

Code: Select all

mysql -p asterisk < NEWFILE.sql

You can revert to the original DB by dropping the table again and executing the BACKUP_DATE_MM_YY_DD_HHSS.sql instead.

If they do not have the same version, you'll need to upgrade the DB to the new server's Vicidial level.

Alternately, you can import just the list table, but once again that must match the new server's Vicidial version. Otherwise, you can just download each list in the Vicidial manager's interface for each list (Download This List link at the bottom of each list) and then load those as new leads.

There are many ways to do it, but that depends on the versions of each server and your experience level. We charge $50 for a backup and $100 for a restore and $100 for a DB upgrade (if needed to match the receiving server's Vicidial Version).

[paolo]

Hi,

I hope this helps. I usually follow this steps when moving one server to a another.

Moving of Dialer Instructions

1 Zip current version of database on the server to be moved (mysqldump --quick asterisk | gzip > asterisk.gz)
(make sure that your in /usr/src/)

2. move the database back-up to the target database location (scp /usr/src/asterisk.gz root@target database IP:/usr/src/database_name.gz)

3. drop the database before extracting the backup

4. create the database name to be used on the new server

5. extract the database

6. upgrade the sql version (reference: http://wiki.vicidial.org/index.php/SVN:howto)
Note: since we are only upgrading the database cat the upgrade in cd /usr/src/astguiclient/trunk/extras/
and follow the SQL upgrade instruction

7. On the database use the database to be ugrade e.g. use v##asterisk

8. run /mysql -f --database=oasterisk < /usr/src/astguiclient/trunk/extras/upgrade_2.4.sql up to the last version - the upgrade needs to be done gradually: upgrading the sql version from base version to the next version till we upgrade it to the latest version.
to get a list of SQL versions do an ls on /usr/src/astguiclient/trunk/extras/

9. On the new server run /usr/share/astguiclient/ADMIN_update_server_ip.pl
confirmation message should be SERVER IP ADDRESS CHANGE FOR VICIDIAL FINISHED!

10. reboot the server

11. Move recordings from /var/lib/asterisk

12. Copy conf files and dial plans from old server to new

13. transfer call recordings

[techlabVAIS]

I looked up on how to check my vicidial version but no luck.
I was able to get my asterisk version by typing asterisk r-"connected to asterisk 1.4.21 currently running on localhost"

I did a : mysqldump -u root -pmypass --all-databases > alldatabases.sql command and I got about 750mb of data just in case I needed it.

But this is still not what I need, i guess i havent made myself clear since I am so inexperienced with this but let me start all over again.

I have this old server from where i need to extract the phone numbers and contacts into a CSV file or some sort of table to be able to transfer the info to an ALREADY running server (do not know the version of vicidial)






not sure if this is helpful but this is the first few line of my
mysqldump -u root -pmypass --all-databases > alldatabases.sql
command.



-- MySQL dump 10.11
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.0.45

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `asterisk`
--

[techlabVAIS]

This older system does not have a GUI so it makes it even harder for me to access files.
But I have my ubuntu machine connected to the same network so I'm able to browse all the files and i happen to encounter folder where i found what I was looking for but just not nearly enough numbers. I found roughly 15,000 contacts but I am looking for around 1-2 million contacts.
The files were found on Localhost.local>Var>WWW>html>Vicidial
The files "vicidial_temp_file.xls and Vicidial_temp_file.csv" have around 15 thousand contacts, this exactly what i need either a XLS, or CSV file.

I've looked everywhere else for these type of files but nothing.

I did a look up for XLS and CSV but only the ones I had already found came up.

Thanks again for all your help.

[williamconley]

1) look up instructions for "mysqldump" which can put the information you want into a csv file for you. It could also dump to a tab delimited file (which will load nicely into excel and often has less problems than csv).

2) you CAN dump just the "vicidial_list" table, then load that table into a temporary database in your new server with phpMyAdmin and then use phpMyAdmin to dump only what you want back out in whatever format you desire.

To dump just that one table, you just add that table name (after a "space") after the database name during the mysqldump:

Code: Select all

mysqldump -p asterisk vicidial_list > BACKUP_DATE_MM_YY_DD_HHSS_vicidial_list.sql

Then you create a new empty database in phpMyAdmin and import that file into it. You now have visual access to the data (including phone numbers) and can play with the export options at will.

You could also just get the phone_number field from mysql with no other information with this:

Code: Select all

mysql -p asterisk -e "select phone_number from vicidial_list" > DATE_MM_YY_DD_HHSS_vicidial_list_phone_number.sql

[boybawang]

mysqldump -p asterisk > asteriskdb.sql

[williamconley]

mysqldump -p asterisk > asteriskdb.sql

Dumps to sql format, not csv format. He was askin for csv!

[pikalla]

Good morning all
thank you very much for this assistance
I use ViciBox_v9.x86_64-9.0.3
version 2.14-613c
BUILD: 200922-0948
the installation method is expert mode cluster
I took 2 Dedicated servers online
I would like to know how to introduce a file extraction API like for example IQUALIF thank you