FTP Daily CDR to remote Server

QCFourie

Joined
May 25, 2010
Messages
16
Likes
0
Points
0
#1
Hi Everyone

Hope this script can be helpful to someone. Basically it dumps the daily CDR records to a file, archive’s it with ZIP and then ftp’s it to an ftp server using NCFTP.

First install NCFTP client by following instructions in http://www.ncftp.com/download/

Then, create folder /tmp/backup and assign the appropriate write rights to the folder for your user.

Create the script below in the /usr/sbin folder, let's call the script ftpDailyBackup.sh

Don’t forget to change the details applicable to your environment.

### Script Start

### System Setup ###
BACKUP=/tmp/backup
NOW=$(date +"%Y-%m-%d")

### MySQL Setup ###
MUSER="user"
MPASS="password"

### FTP server Setup ###
FTPU="user"
FTPP="password"
FTPS="192.168.0.1"

### Other stuff ###
EMAILID="me@work.com"

### Start MySQL Backup ###
FILE=$BACKUP/qcs-$NOW.txt

mysqldump -u $MUSER -p$MPASS --fields-enclosed-by=\" --fields-terminated-by=',' --tab=$BACKUP asteriskcdrdb cdr --where="calldate > '$NOW 00:00:00'"

mv $BACKUP/cdr.txt $FILE

### Archive File
zip -r9 $FILE.zip $FILE

### Dump backup using FTP ###
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
lcd $BACKUP
mput $FILE.zip
quit
EOF

### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
rm -f $FILE.zip
rm -f $FILE
rm -f $BACKUP/cdr.sql
rm -f $BACKUP/cdr.txt
else
T=/tmp/backup.fail
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T
rm -f $T
fi

### Script End

Finally schedule a cron job to execute after hours, lets make it 23:00 at night.
# crontab –e

Ener the line:
59 22 * * * sh /usr/sbin/ftpDailyBackup.sh



Regards
Quentin

quentin@qcsolutions.co.za
 

danardf

Joined
Dec 3, 2007
Messages
8,069
Likes
10
Points
88
#2
Hi.

Indeed, this script should be useful. :)

Good work.
 

garrywadams

Joined
Jul 1, 2010
Messages
37
Likes
0
Points
0
#3
When I first ran through your instructions I installed NCFTPd (the FTP server, not the FTP client). You might update your post to indicate that you need the FTP client, not the server. I was confused at first until I realized that what I installed was not an FTP client at all. :)

The NCFTP website has great instructions on instaling the NCFTPd server, but is lacking in the client install instructions. I have modified the script to run from where I extracted the binaries. Does anyone know what I need to do to add the NCFTP client binaries so they don't have to be run from the temp directory where they were extracted?

Great script, by the way. This is just what I was looking for. I may need something that can do more of an hourly dump instead of daily, but this should do for now.

Thanks!
 

garrywadams

Joined
Jul 1, 2010
Messages
37
Likes
0
Points
0
#4
Ok, I got the NCFTP client installed so I don't have to run it from the /tmp directory anymore.

Still, it would be nice to modify the frequency of the script to run several times during the day. We will be using it for call record integration with our CRM. The data will only be helpful if it is current. Definitely a step in the right direction!
 

QCFourie

Joined
May 25, 2010
Messages
16
Likes
0
Points
0
#5
Glad it could be of some use.

To increase the frequency, to let’s say hourly, I would just change the T-SQL MYSql statement to retrieve the last hours data based on the current date and time and then the cronjob to execute hourly.

Thanks again
Quentin

www.qcsolutions.co.za
 

garrywadams

Joined
Jul 1, 2010
Messages
37
Likes
0
Points
0
#6
I'm not very good with SQL, sorry... I don't see a T-SQL statemet in that code anywhere. I'm not exactly sure what I'm looking for. Could you be a little more specific?

Also, I have changed a few other items to suit my environment a little better. For example, our FTP server sits on the same LAN so bandwidth isn't really an issue. I am skipping the archive section so that I don't have to extract the zip file to view the CDR. I am also saving it in csv format so I can open it directly in Excel on the other end. Other than that, its pretty much un-modified.

My code (with sensitive info changed, of course):

### Script Start

### System Setup ###
BACKUP=<BACKUP DIR>
NOW=$(date +"%Y-%m-%d")

### MySQL Setup ###
MUSER="<SQL USER>"
MPASS="<SQL PASS>"

### FTP server Setup ###
FTPU="<FTP USER>"
FTPP="<FTP PASS>"
FTPS="<FTP SERVER>"

### Other stuff ###
EMAILID="<EMAIL ADDRESS>"

### Start MySQL Backup ###
FILE=$BACKUP/<PBX NAME>-$NOW.csv

mysqldump -u $MUSER -p$MPASS --fields-enclosed-by=\" --fields-terminated-by=',' --tab=$BACKUP asteriskcdrdb cdr --where="calldate > '$NOW 00:00:00'"

mv $BACKUP/cdr.txt $FILE

### Archive File
# zip -r9 $FILE.zip $FILE

### Dump backup using FTP ###
ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
lcd $BACKUP
mput $FILE
quit
EOF

### Find out if ftp backup failed or not ###
if [ "$?" == "0" ]; then
# rm -f $FILE.zip
rm -f $FILE
rm -f $BACKUP/cdr.sql
# rm -f $BACKUP/cdr.txt
else
T=/tmp/backup.fail
echo "Date: $(date)">$T
echo "Hostname: $(hostname)" >>$T
echo "Backup failed" >>$T
mail -s "BACKUP FAILED" "$EMAILID" <$T
rm -f $T
fi

### Script End
 

garrywadams

Joined
Jul 1, 2010
Messages
37
Likes
0
Points
0
#7
On second thought, I think I will change the frequency of the cron job and leave the SQL dump portion alone. This means that every hour (or however often I schedule the cron job) I will get an updated csv file with the entire day's CDR. I will overwrite the copy on the FTP server so that when I open it, it will be the full day's CDR up to the most recent hour.

That will mean fewer files to prune out when the FTP directory starts getting huge, and it will mean that if I miss an hour (because a server is rebooting or offline or something) it won't matter. I won't have a blank spot in my CDR because next time it runs it will update with the full day's CDR.

In short, the code as it exists above will work perfectly. Thanks again!
 

QCFourie

Joined
May 25, 2010
Messages
16
Likes
0
Points
0
#8
Very good idea!

Thanks for the feedback.

Quentin
 

Members online

No members online now.

Latest posts

Forum statistics

Threads
30,902
Messages
130,887
Members
17,565
Latest member
omarmenichetti
Top