Thursday, November 3, 2016

Change the Group-Office database collation and character set

Recently we converted the Group-Office database to the collation "utf8mb4_unicode_ci". We did this to support 4 byte unicode characters. Some customers may want to use another collation because of sorting in the database. For example Danish users want to use "utf8mb4_danish_ci". You can choose any collation as long as it starts with "utf8mb4".
You can use this bash script to convert all tables in the database.
Make a backup first!
#!/bin/bash
DB="go61mb4"
COLLATE="utf8mb4_unicode_ci"
USER="root"
PASSWORD="SECRET"

(
  echo 'set foreign_key_checks=0;ALTER DATABASE `'"$DB"'` \
  CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
  mysql -u "$USER" -p"$PASSWORD"  "$DB" -e "SHOW TABLES" --batch --skip-column-names \
  | xargs -I{} echo 'ALTER TABLE `'{}'` \
  CONVERT TO CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
) \
| mysql -u "$USER" -p"$PASSWORD" "$DB"

Change the Group-Office database collation and character set

Recently we converted the Group-Office database to the collation "utf8mb4_unicode_ci". We did this to support 4 byte unicode characters. Some customers may want to use another collation because of sorting in the database. For example Danish users want to use "utf8mb4_danish_ci". You can choose any collation as long as it starts with "utf8mb4".
You can use this bash script to convert all tables in the database.
Make a backup first!
#!/bin/bash
DB="go61mb4"
COLLATE="utf8mb4_unicode_ci"
USER="admin"
PASSWORD="mks14785"

(
  echo 'set foreign_key_checks=0;ALTER DATABASE `'"$DB"'` \
  CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
  mysql -u "$USER" -p"$PASSWORD"  "$DB" -e "SHOW TABLES" --batch --skip-column-names \
  | xargs -I{} echo 'ALTER TABLE `'{}'` \
  CONVERT TO CHARACTER SET utf8mb4 COLLATE '"$COLLATE"';'
) \
| mysql -u "$USER" -p"$PASSWORD" "$DB"

Thursday, April 7, 2016

Simple monitoring

I was searhing for a simple monitoring tool but most tools out there are very complex. We use 3rd party virtual private servers now so hardware monitoring is not our business. We're interested in monitoring two things:
  1. Free disk space
  2. CPU load
So I use two simple scripts that send out an e-mail alert when the diskspace is low or the CPU load it too high.

You can create the two scripts below and edit the MAILTO and TRIGGER variables to suit your needs /usr/local/bin/monitor-load.sh:

#!/bin/bash
TRIGGER=2.00
MAILTO=mail@somedomain.com


host=`hostname -f`
load=`cat /proc/loadavg | awk '{print $2}'`
response=`echo | awk -v T=$TRIGGER -v L=$load 'BEGIN{if ( L > T){ print "greater"}}'`
if [[ $response = "greater" ]]
then
        #capture top command output for the mail body
        body=`top -n 1 -b`

        echo "$body\n."|mail -s "High load on $host - [ $load ]" $MAILTO
fi
/usr/local/bin/monitor-disk-usage.sh:

#!/bin/bash
MAILTO=mail@somedomain.com
TRIGGER=80

HOST=`hostname -f`
CURRENT=$(df / | grep / | awk '{ print $5}' | sed 's/%//g')

if [ "$CURRENT" -gt "$TRIGGER" ] ; then

    mail -s "Disk Space Alert for $HOST" $MAILTO << EOF
The root partition of '$HOST' remaining free space is critically low. Used: $CURRENT%
EOF
fi

Make these two scripts executable:
chmod +x /usr/local/bin/monitor-load.sh 
chmod +x /usr/local/bin/monitor-disk-usage.sh
Now schedule these script in system cron jobs:
/etc/cron.d/monitor:
# Check server load and diskspace
* * * * * root /usr/local/bin/monitor-load.sh
0 7,16 * * * root /usr/local/bin/monitor-disk-usage.sh
This will check the server load every minute and disk space at 7am and 4pm. Finally it's wise to test if mails sent from console arrive. Change the e-mail address in this command and run it:
echo "."|mail -s "Test mail from console" mail@somedomain.com

Wednesday, May 13, 2015

DAVDroid calendar and contact sync

There's a very nice Android synchronization client called DAVDroid.
The beauty of this app is that the setup is extremely simple and sets up contact and calendar sync in one step. Here's a quick run through the setup:

1. Install the DAVDroid app and open it.
2. Select URL and user name:
3. Fill in your details:
4. Select the auto detected your address books and calendars you'd like to sync:

Now it's done and you can view the new calendars and address books in the calendar and contacts app!



Connect your website to Group-Office

Often it's desired to connect a website to Group-Office. For example you might want to have a sign up form that adds a contact to Group-Office.
Group-Office has a powerful JSON API to do this. Here's an example script that add's a contact using cURL. With cURL you can do HTTP requests in your own PHP script that you can run on your own CMS like Wordpress or Drupal for example.



<?php
//URL to Group-Office. You must use SSL because we use basic auth!
$groupoffice_url = "https://localhost/groupoffice-6.1/www/";

//Group-Office username and password. You should create a restricted user that 
//can only add contacts to this addressbook.
$username = 'website';
$password = 'secret';

//The contact properties to POST
$post = array(
  'addressbook_id' => 1, //required
  'company_id' => 0,
  'first_name' => 'Curl',
  'last_name' => 'Tester',
  'initials' => '',
  'title' => '',
  'suffix' => '',
  'sex' => 'M', // or 'F"
  'birthday' => '', //local format
   'email' => '',
  'email2' => '',
  'email3' => '',
  'department' => '',
  'function' => '',
  'home_phone' => '',
  'work_phone' => '',
  'fax' => '',
  'work_fax' => '',
  'cellular' => '',
  'cellular2' => '',
  'homepage' => '',
  'country' => 'NL', //2 character ISO code 
  'state' => '',
  'city' => '',
  'zip' => '',
  'address' => '',
  'address_no' => '',
  'comment' => ''
);

$process = curl_init($groupoffice_url . '?r=addressbook/contact/submit');
curl_setopt($process, CURLOPT_USERPWD, $username . ":" . $password);
curl_setopt($process, CURLOPT_POST, 1);
curl_setopt($process, CURLOPT_POSTFIELDS, $post);
curl_setopt($process, CURLOPT_RETURNTRANSFER, TRUE);

$return = curl_exec($process);
curl_close($process);

//JSON decode the response
$json = json_decode($return, true);


//Handle success or error here.
if ($json['success']) {
 echo "Contact saved!\n";
} else {
 echo "Failed to save contact: " . $json['feedback'] . "\n";
}

Tuesday, October 21, 2014

Anti spam plugin

When hosting e-mail, you'll have to deal with spam unfortunately. We use Amavis with spamassassin and ClamAV antivirus. This setup works great fo 95% of the time but there are always some spam messages that pass the spamfilter. For this you can train spamassassin to recognize the spam better.
I came across a nice dovecot plugin that is very simple to implement. Users can classify messages as spam simply by moving the messages into the spam folder. Taking messages out of the spam folder tells spamassassin that this message was in fact not spam.

Here's a short how to on the installation. I assume you have dovecot+spamasssin running already:

$ apt-get install dovecot-antispam

Add the "antispam" plugin to /etc/dovecot/conf.d/20-imap.conf (your plugins list may be different):

mail_plugins = $mail_plugins quota imap_quota antispam

Add the following to /etc/dovecot/conf.d/90-plugin.conf:

plugin {
  antispam_backend = pipe
  antispam_debug_target = syslog
  antispam_verbose_debug = 1
  antispam_signature= X-Spam-Status
  antispam_signature_missing= move
  antispam_mail_sendmail_args= --username=%u
  antispam_mail_spam  = --spam
  antispam_mail_notspam  = --ham
  antispam_mail_sendmail = /usr/bin/sa-learn-pipe.sh
  antispam_spam = SPAM;Spam
  antispam_unsure = Virus
  antispam_trash = Trash;trash
}

Create /usr/bin/sa-learn-pipe.sh:

#!/bin/bash
echo /usr/bin/sa-learn $* /tmp/sendmail-msg-$$.txt
echo "$$-start ($*)" >> /tmp/sa-learn-pipe.log

#echo $* > /tmp/sendmail-parms.txt
cat<&0 >> /tmp/sendmail-msg-$$.txt

/usr/bin/sa-learn $* /tmp/sendmail-msg-$$.txt

rm -f /tmp/sendmail-msg-$$.txt

#echo "$$-end" >> /tmp/sa-learn-pipe.log

exit 0

Make this script executable
$ chmod +x /usr/bin/sa-learn-pipe.sh

Restart dovecot
$ service dovecot restart

Now move a message into the spam folder and watch /var/log/syslog. It should have something like this:
Oct 21 15:16:25 debian imap: antispam: mail copy: src spam: 0, dst spam: 1, src unsure: 0
Oct 21 15:16:25 debian imap: antispam: running mailtrain backend program /usr/bin/sa-learn-pipe.sh
Oct 21 15:16:25 debian imap: antispam: running mailtrain backend program /usr/bin/sa-learn-pipe.sh
Oct 21 15:16:25 debian imap: antispam: running mailtrain backend program parameter 1 --username=admin@intermesh.dev
Oct 21 15:16:25 debian imap: antispam: running mailtrain backend program parameter 2 --spam


After this test you should disable debugging in /etc/dovecot/conf.d/90-plugin.conf

Tuesday, August 12, 2014

Configuring a fresh Ubuntu for Group-Office development

Unfortunately my Asus Zenbook Prime's SSD drive failed so I had to install a fresh system for Group-Office development (twice...). I thought it would be useful to post my steps right after a fresh Ubuntu 14.04 installation:
  1. Install java for netbeans
     $ sudo add-apt-repository ppa:webupd8team/java
     $ sudo apt-get update
     $ sudo apt-get install oracle-java7-installer
    
  2. Install Netbeans from site.
     $ sh ~/Downloads/netbeans-8.0-linux.sh
    
  3. Install Group-Office
     $ sudo echo -e "\n## Group-Office repository\ndeb \
    http://repos.groupoffice.eu/ fivezero main" | sudo tee -a /etc/apt/sources.list
    $ gpg --keyserver hkp://keyserver.ubuntu.com:11371 --recv-keys 01F1AE44 $ gpg --export --armor 01F1AE44 | sudo apt-key add - $ sudo apt-get update $ sudo apt-get install groupoffice-com
  4. Install subversion
     $ sudo apt-get install subversion
    
  5. Own the /var/www directory
     $ sudo chown -R mschering:mschering /var/www
    
  6. Checkout groupoffice repository
     $ svn co svn+ssh://mscheering@svn.code.sf.net/p/group-office/code/branches/groupoffice-6.0
    
  7. Launch Netbeans and open the Group-Office project and start coding.
  8. Install Firebug add-on in Firefox
  9. Install Dark look and feel plugin for Netbeans. I prefer a dark screen when staring at it for 8 hours a day ;)
  10. Install phpmyadmin
    $ sudo apt-get install phpmyadmin
    
    DANGER Set auto root login in /etc/phpmyadmin/config.php
        $cfg['Servers'][$i]['auth_type']    = 'config';
        $cfg['Servers'][$i]['user']         = 'root';  
        $cfg['Servers'][$i]['password']         = '';
    
    Enable:
        $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
    
  11. Disable local sending in SSH to get rid of warnings that locale is not found on remote server. Cange /etc/ssh/ssh_config:
    #    SendEnv LANG LC_*
    
  12. Install shutter for taking screenshots
    $ sudo apt-get install shutter
    

Some extra stuff for fun

  1. Install Variety for automatic wallpapers
     $ sudo add-apt-repository ppa:peterlevi/ppa
     $ sudo apt-get update
     $ sudo apt-get install variety
    
  2. Install Weather indicator
     $ sudo add-apt-repository ppa:atareao/atareao
     $ sudo apt-get update
     $ sudo apt-get install my-weather-indicator
    
  3. Mount WebDAV
     $ sudo apt-get install davfs2
    
    To enable webdav for non root users:
     $ sudo dpkg-reconfigure davfs2
    
     $ sudo vi /etc/fstab
    
    Add the line:
     https://intermesh.group-office.com/webdav/ /home/mschering/Group-Office davfs rw,user,noauto 0 0
    
    Add your user to the davfs2 group
     $ sudo adduser mschering davfs2
    
    Refresh your group membership without logging out:
     $ exec su -l mschering
    
    Create secrets file because it fails without:
     $ touch .davfs2/secrets
     $ chmod 600 .davfs2/secrets
    
    Create local dir:
     $ mkdir ~/Group-Office
     $ mount ~/Group-Office
    
  4. Install Skype I downloaded and installed Skype from their website which works fine except for the indicator icon. I had to install this package:
     $ sudo apt-get install sni-qt:i386