Categories
English Language

Cardio and oxygen saturation monitoring at home

Personal intro

Before I start describing what I did I have to talk a little bit about some private stuff.

I have a stepdaughter, named Anouschka, who was born severely disabled, who is now 17 years old and had here 43th surgery last June. After here last surgery she developed the need for supplimental oxygen at night. She had a polysomnography (a one night sleep study) shortly after.

The study showed that her need for  supplimental oxygen at night was “in the grey arrea”. This means that it is neither a definite “yes” nor “no”.
And her doctor said that of course data gathered at home would give a much clearer picture.

This triggered me, being a long time IT guy, to see what I could do.

IMPORTANT NOTE: All the time we tuned the level of oxygen given our stepdaughter was continously monitored using the RAD-8 monitor as well as
her personal fysician from a renowned Dutch hospital. Of course there were all kinds of critical alarm settings continously in place.

The start

A couple of days later we got a Philips Everflo oxygen concentrator and a Masimo RAD-8 saturation measurement device. The RAD-8 turned out to have a serial port on the back panel. When configuring the RAD-8 to use the serial port in the “ASCII 2” setting (if I remember correctly) would spit out one line of information every 2 seconds at 9600 Baud 8-n-1 like this:

00/00/00 00:00:03 SN=0000051252 SPO2=---% BPM=--- PI=--.--% SPCO=--.-% SPMET=--.-% DESAT=-- PIDELTA=+-- ALARM=0000 EXC=000000\r\n

This was something I could work with.

I created a MySQL table to store the data received and used a Python program to capture the serial output and send it to the MySQL database.

This is the layout of the MySQL table:

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tijd` datetime DEFAULT NULL,
  `data` varchar(150) DEFAULT NULL,
  `spo2` int(11) DEFAULT NULL,
  `bpm` int(11) DEFAULT NULL,
  `session` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tijd3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `id` (`id`),
  KEY `session_index` (`session`),
  KEY `tijd_index` (`tijd`),
  KEY `spo2_index` (`spo2`)
)

And the Python program I wrote looked like this:

#!/usr/bin/python
print "Loading libraries"
import serial, time,  os
import logging
import logging.handlers
import MySQLdb
import datetime

version="1.0"

def log(msg):
  print msg
  my_logger.debug(os.path.basename(__file__)+":"+msg)

print "Initialising logging"
my_logger = logging.getLogger('MyLogger')
my_logger.setLevel(logging.DEBUG)
handler = logging.handlers.SysLogHandler(address = '/dev/log')
my_logger.addHandler(handler)

log("Log-RAD-8 version "+version)
log("Copyright 2014 J. Baten")
log("")
log("Logging serial data from Masimo RAD-8 serial port")
log("Note: Works only with RAD-8 serial port set to 'ASC1'")
log("")
#my_logger.debug('this is debug')
#my_logger.critical('this is critical')

log("Initialising database connection")
#setting up database connection
try:
    conn = MySQLdb.connect(host= "10.1.1.1", user="lograd8",  passwd="lograd8",  db="lograd8")
except MySQLdb.Error, e:
    log( "ERROR %d IN CONNECTION: %s" % (e.args[0], e.args[1]))
#except MySQLdb.Error:
    #log("ERROR IN CONNECTION")
    exit(2)
x = conn.cursor()
#x.execute("SELECT *  FROM anooog1")
#x.execute (" INSERT INTO anooog1 VALUES ('%s','%s') ", (188,90))
#row = x.fetchall()

now = datetime.datetime.now()
#print now
log("Program started at "+str(now))
#today8am = now.replace(hour=8, minute=0, second=0, microsecond=0)
today = datetime.datetime.today()
#print today
one_day = datetime.timedelta(days=1)
tomorrow = today + one_day
#print 'Tomorrow :', tomorrow
endtime= tomorrow.replace(hour=8, minute=0, second=0, microsecond=0)
log("Logging should stop at "+ str(endtime))

#initialization and open the port
#possible timeout values:
#    1. None: wait forever, block call
#    2. 0: non-blocking mode, return immediately
#    3. x, x is bigger than 0, float allowed, timeout block call
log( "Initializing port")
ser = serial.Serial()
#ser.port = "/dev/ttyUSB0"
ser.port = "/dev/ttyS0"
#ser.port = "/dev/ttyS2"
ser.baudrate = 9600
ser.bytesize = serial.EIGHTBITS #number of bits per bytes
ser.parity = serial.PARITY_NONE #set parity check: no parity
ser.stopbits = serial.STOPBITS_ONE #number of stop bits
#ser.timeout = None          #block read
ser.timeout = 1            #non-block read
#ser.timeout = 2              #timeout block read
ser.xonxoff = False     #disable software flow control
ser.rtscts = False     #disable hardware (RTS/CTS) flow control
ser.dsrdtr = False       #disable hardware (DSR/DTR) flow control
ser.writeTimeout = 2     #timeout for write
try:
    log( "Trying to open serial port")
    ser.open()
except Exception, e:
    log( "error open serial port: " + str(e))
    exit(1)
if ser.isOpen():
    log( "Port succesfully opened")
    try:
        log( "Flushing port data")
        #flush input buffer, discarding all its contents
        ser.flushInput()
        #flush output buffer, aborting current output
        ser.flushOutput()
        #and discard all that is in buffer
        #write data
        #ser.write("AT+CSQ")
        #time.sleep(0.5)  #give the serial port sometime to receive the data
        while True:
            response = ser.readline()
            #response = "TESTDATA 09/01/14 04:01:41 SN=0000051252 SPO2=092% BPM=086 PI=01.03% SPCO=--.-% SPMET=--.-% DESAT=-- PIDELTA=+-- ALARM=0000 EXC=000800"
            print response
            response = MySQLdb.escape_string(response)
            print(response)
            if (len(response)>5):
                try:
                    x.execute ("INSERT INTO data (tijd,data) VALUES (now(),%s) ", response)
                    conn.commit()
                except MySQLdb.Error, e:
                    log( "ERROR %d IN CONNECTION: %s" % (e.args[0], e.args[1]))
                    log( "Last query was: "+ x._last_executed )
            # Is it time to stop yet?
            #endtime= now+ datetime.timedelta(seconds=5)
            now = datetime.datetime.now()
            if (now > endtime):
                break
        ser.close()
    except Exception, e1:
        log( "error communicating...: " + str(e1))
else:
    log( "cannot open serial port ")
conn.close()

As you can see I added some logging and even made it possible to run for a specific length of time. In the end I did not need that because as soon as the RAD-8 was turned of the serial port would of course be silent hence there would be no data.

 The data

After the first night I had some records. Actually I had approximately 36.000 records. But there where some pittfalls. The timestamp of the RAD-8 was not the actual timestamp. So I had to add a timestamp field to the MySQL table. And it would be nice if I could easily select the data for one night. Unfortunately that means from somewhere around 20:00 until 8:00 the next day!

I made life easier for me by adding a column called “session” that would take the timestamp and subtract 12 hours from it. This way I could very easily make selections for one specific date.

I also wanted to have the SPO2 (O2 saturation) and the BPM (heart Beats Per Minute) in a separate column.

These three things are acomplished by the following SQL commands;

update data set session=DATE_SUB(tijd, INTERVAL 12 HOUR) where spo2 is null;
update data set spo2=substring(data,38,3) where spo2 is null;
update data set bpm=substring(data,47,3) where bpm is null;

(I know, I could make a trigger to do this automagically, but I just never got round to it)

Analysing the data

After some two weeks of capturing data I had 700.000 records. Now capturing and storing data is one thing, but without analysing it, what’s the point?

My first analyses was a simple SQL query to get some hourly averages, minimum and maximum values, etc.


mysql> select
  date_format(tijd,'%d/%m/%Y %H') as date_hour,
  count(*) as measurements,
  avg(spo2) as avg_spo2,
  std(spo2) as std_spo2,
  max(spo2) as max_spo2,
  min(spo2) as min_spo2,
  avg(bpm) as gem_bpm,
  std(bpm) as std_bpm,
  max(bpm) as max_bpm,
  min(bpm) as min_bpm
from data
  group by date_format(session,'%d/%m/%Y %H');

+---------------+--------------+----------+----------+----------+----------+----------+---------+---------+---------+
| date_hour     | measurements | avg_spo2 | std_spo2 | max_spo2 | min_spo2 | gem_bpm  | std_bpm | max_bpm | min_bpm |
+---------------+--------------+----------+----------+----------+----------+----------+---------+---------+---------+
| 02/09/2014 15 |          117 |  16.3248 |  35.9541 |       96 |        0 |  12.1795 | 26.8250 |      73 |       0 |
| 02/09/2014 20 |          279 |  93.2652 |  20.8964 |      100 |        0 |  77.1470 | 23.0635 |      97 |       0 |
| 02/09/2014 21 |         3601 |  99.3913 |   0.5947 |      100 |       98 |  79.8578 | 13.9956 |     111 |      58 |
| 02/09/2014 22 |         3600 |  98.3753 |   0.6046 |      100 |       97 |  69.2242 |  6.0704 |      91 |      59 |
| 02/09/2014 23 |         3601 |  98.1086 |   0.5884 |      100 |       96 |  65.5959 |  3.8593 |      89 |      56 |
| 03/09/2014 00 |            2 |  98.0000 |   0.0000 |       98 |       98 |  66.0000 |  0.0000 |      66 |      66 |
| 03/09/2014 20 |          343 |  94.2915 |  18.0130 |      100 |        0 |  94.8513 | 19.8918 |     107 |       0 |
| 03/09/2014 21 |         3600 |  97.4208 |   1.1367 |      100 |       95 |  91.4211 |  9.7283 |     113 |      71 |
| 03/09/2014 22 |         3601 |  96.2869 |   0.6961 |       98 |       89 |  77.9431 |  5.7278 |      96 |      62 |
| 03/09/2014 23 |         3600 |  96.4931 |   0.9354 |      100 |       95 |  69.1972 |  8.0643 |      97 |      56 |
| 04/09/2014 00 |         3601 |  97.5301 |   0.7060 |       99 |       94 |  74.3510 |  7.4690 |      93 |      60 |
| 04/09/2014 01 |         3600 |  98.1533 |   0.6415 |      100 |       95 |  77.2231 |  7.2076 |     108 |      60 |
| 04/09/2014 02 |         3601 |  97.8970 |   0.8599 |      100 |       94 |  69.5146 |  6.6820 |      91 |      58 |
| 04/09/2014 03 |         3601 |  97.5260 |   0.9520 |      100 |       95 |  79.5568 |  6.2660 |      98 |      64 |
| 04/09/2014 04 |         3600 |  98.6144 |   0.7270 |      100 |       96 |  72.4103 |  6.7762 |     100 |      60 |
| 04/09/2014 05 |         3601 |  98.0297 |   0.8958 |      100 |       96 |  73.6409 |  6.6742 |      96 |      58 |
| 04/09/2014 06 |         3600 |  98.5117 |   1.0214 |      100 |       95 |  75.9700 |  8.9882 |     111 |      59 |
| 04/09/2014 07 |           51 | 100.0000 |   0.0000 |      100 |      100 |  84.2941 |  3.0888 |      91 |      81 |
+---------------+--------------+----------+----------+----------+----------+----------+---------+---------+---------+

I wanted to graph my data, quick and dirty. So I configured my public_html folder (did I mention that I do all of my work on a Linux workstation?) and started building something in PHP.

Graphing the data using PHP

Now, finding a PHP graphic library turned out to be not so easy as I thought. I had used JpGraph before, but Google and also Google Trends showed me it was not the leader of the pack anymore. There were some promising alternatives though.  At first I started with JQuery and the Google Charts library. It’s from Google so it cant be bad, right? Wrong! It turned out to be a rather complicated chore involving reformatting dates and times to individual arrays and what not. At some point a post on a forum pointed me to the briljant Dygraphs library. And all it needed was some headers and CVS data. So I rebuild my AJAX scripts to generate CVS data and that was it!

At first I created a PHP “get-sat2.php” script to graph individual days. The PHP involved looks like this:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Saturatie weergave</title>
  <link rel="stylesheet" href="jquery/jquery-ui.min.css">
  <link rel="stylesheet" href="get-sat.css">
  <script src="jquery/external/jquery/jquery.js"></script>
  <script src="jquery/jquery-ui.min.js"></script>
  <script type="text/javascript" src="dygraph-combined.js"></script>

  <script>
  $(function() {
    $( "#datepicker" ).datepicker({
        showButtonPanel: true
    });
  });

  $(function() {
    $( "input[type=submit], a, button" )
      .button()
      .click(function( event ) {
        event.preventDefault();
        console.log("submit event");
        drawChart();
      });
  });
  </script>

  <script>
  $(function() {
    $( "#datepicker" ).datepicker({dateFormat : 'yy-mm-dd'});
  });
  </script>

  <script type="text/javascript">

      function drawChart() {
         <!-- console.log("In drawChart"); -->
         var startdate  = "";
         if ($("#datepicker").hasClass('hasDatepicker'))
         {
           startdate  = $("#datepicker").datepicker('getDate',{dateFormat : 'yy-mm-dd'});
         }
         if (startdate != null ) {
           <!-- console.log(startdate); -->
           var day = startdate.getDate();
           var month = startdate.getMonth()+1;
           var year = startdate.getFullYear();
           startdate= year+"-"+month+"-"+day;
           console.log(startdate);

           g2 = new Dygraph(
             document.getElementById("chart_div"),
             "get-data2.php?startdate=" + startdate ,  // path to CSV file
             { // rollPeriod: 7,
              legend: 'always',
              animatedZooms: true,
              showRoller: true
              //errorBars: true,
              //valueRange: [40,100]}          // options
             }
           );
         }
      }
  </script>

  <script>
  if ($("#datepicker").hasClass('hasDatepicker')) {
    startdate  = $("#datepicker").datepicker('getDate');
    console.log(startdate);
}
  </script>
</head>
<body>

<p>Datum: <input type="text" id="datepicker"><input type="submit" value="Toon grafiek"></p>

<div id="wrap">
<h3>Stats</h3>
</div> -->
<div class="box" id="chart_div" style="width:95%">
<h3>Graph</h3>
</div>
</div><!--end wrap-->
</body>
</html>

Basically it draws a date selectorbox with calendar and after selecting a date, it gets input from the ajax php script “get-data2.php”.

The script looks like this:

<?php
    ini_set("error_reporting",E_ALL);
    syslog(LOG_DEBUG,"Start get-data.php");

    # get startdate parameter
    $startdate = $_GET['startdate'];
    syslog(LOG_DEBUG,$startdate);
    if (strlen($startdate) > 5 ) {

      # set db connection parameters
      $dbhost="10.1.1.1";
      #$dbhost="127.0.0.1";
      $dblogin="lograd8";
      $dbpwd="secret";
      $dbname="lograd8";

      $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
      mysql_select_db($dbname);    

      # // CSV or path to a CSV file.
      #    "Date,Temperature\n" +
      #    "2008-05-07,75\n" +
      #    "2008-05-08,70\n" +
      #    "2008-05-09,80\n"

      $rows  = "Tijd,SPO2,BPM\n";

      #$startdate = $_POST['startdate'];
      $former=0;

      $query = "SELECT tijd,spo2,bpm from data where date(session)='$startdate' order by session  ";
      syslog(LOG_DEBUG,"$query");
      $result = mysql_query($query);
      while($r = mysql_fetch_assoc($result)){
        #syslog(LOG_DEBUG,print_r($r,TRUE));
        $tijd=$r['tijd'];
        $tijd=str_replace("-","/",$tijd);
        $spo2=$r['spo2'];
        $bpm=$r['bpm'];
        #syslog(LOG_DEBUG,print_r($tijd,TRUE));
        if ( $spo2 > 0 ) {
          if ( $spo2 != $former ) {
            $rows = $rows.$tijd.",".$spo2.",".$bpm."\n";
            }
            $former=$spo2;
           }
      }
      syslog(LOG_DEBUG,print_r($rows,TRUE));
      #echo $cols . '"rows":[',$google_JSON_row ."]}";
      # output van json string to caller.
      echo $rows;

      # storage of json string for debugging purposes
      #$fh=fopen("/tmp/get-data.cvs","w");
      #fwrite($fh,$rows);
      #fclose($fh);

      mysql_close($db);
    }
    syslog(LOG_DEBUG,"End get-data.php\n\n");
?>

Both these PHP scripts result in the visualisation of the logged data like in this next picture:

The first picture shows you the data for september 11th 2014.

get-sat2

The second picture show you that DyGraph allows you to zoom in to the data.
You can play around with DyGraph settings like “rollPeriod” to tune the image to your liking.

get-sat2-zoom

In practice this graph turned out to be of little use. We played around with it for some time but to no avail.
We wanted to see the difference between giving oxygen and not giving oxygen.
Selecting a date that she got oxygen and compare it to a date where she did not get additional oxygen proved to be too difficult.

Graphing all data using PHP

At some point it occured to me to graph all data as shown in the first table of this article. This turned out to be a good idea.

I went to the code editor (again) and made a variantion of the earlier shown PHP script. This time with two seperated graphs.
One for the oxygen level and one for the heartbeat:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Saturatie weergave</title>
  <link rel="stylesheet" href="jquery/jquery-ui.min.css">
  <link rel="stylesheet" href="get-sat.css">
  <script type="text/javascript" src="dygraph-combined.js"></script>

</head>
<body>

<div class="box" id="spo2_div" style="width:95%">
<h3>SPO2</h3>
</div>

<div class="box" id="bpm_div" style="width:95%">
<h3>SPO2</h3>
</div>
   <script>
           <!-- console.log(startdate); -->

            g = new Dygraph(
             document.getElementById("spo2_div"),
             "get-data3spo.php",  // path to CSV file
             { // rollPeriod: 7,
              legend: 'always',
              animatedZooms: true,
              showRoller: true
              //errorBars: true,
              //valueRange: [40,100]}          // options
             }
           );

           g = new Dygraph(
            document.getElementById("bpm_div"),
             "get-data3bpm.php",  // path to CSV file
             { // rollPeriod: 7,
              legend: 'always',
              animatedZooms: true,
              showRoller: true
              //errorBars: true,
              //valueRange: [40,100]}          // options
             }
           );
  </script>
</body>
</html>

The script that produces the oxygen information looks like this:

<?php
  ini_set("error_reporting",E_ALL);
  syslog(LOG_DEBUG,"Start get-data.php");

  # set db connection parameters
  $dbhost="10.1.1.1";
  #$dbhost="127.0.0.1";
  $dblogin="lograd8";
  $dbpwd="secret";
  $dbname="lograd8";

  $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
  mysql_select_db($dbname);    

  $rows  = "DatumTijd,gem_SPO2,spreiding_SPO2,max_SPO2,min_SPO2\n";

  #$startdate = $_POST['startdate'];
  $former=0;

  $query = "select date_format(tijd,'%Y/%m/%d %H:00') as datum,  avg(spo2) as gem_spo2, std(spo2) as spreiding_spo2,  max(spo2) as max_spo2,  min(spo2) as min_spo2   from data group by date_format(session,'%d/%m/%Y %H');";
  # +---------------+----------+----------------+----------+----------+
  # | datum_uur     | gem_spo2 | spreiding_spo2 | max_spo2 | min_spo2 |
  # +---------------+----------+----------+----------------+----------+

  syslog(LOG_DEBUG,"$query");
  $result = mysql_query($query);
  while($r = mysql_fetch_array($result)){
    #syslog(LOG_DEBUG,print_r($r,TRUE));
    $rij="";
    # vervangen min=0 door min=avg bij spo2 en bpm
    if ($r[4]==0) $r[4]=$r[1];
    for ($i=0;$i<=4;$i++) {
      $rij = $rij.$r[$i].",";
    }
    $rij=substr($rij,0,strlen($rij)-1);
    $rows = $rows.$rij."\n";
  }
  #syslog(LOG_DEBUG,print_r($rows,TRUE));
  #syslog(LOG_DEBUG,print_r($rows,TRUE));
  #echo $cols . '"rows":[',$google_JSON_row ."]}";
  # output off json string to caller.
  echo $rows;

  # saving of cvs string for debugging purposes
  #$fh=fopen("/tmp/get-data.cvs","w");
  #fwrite($fh,$rows);
  #fclose($fh);

  mysql_close($db);

  syslog(LOG_DEBUG,"Einde get-data.php\n\n");
?>

And, of course, the script to get the BPM info is very similar:

<?php
  ini_set("error_reporting",E_ALL);
  syslog(LOG_DEBUG,"Start get-data.php");

  # set db connection parameters
  $dbhost="10.1.1.1";
  #$dbhost="127.0.0.1";
  $dblogin="lograd8";
  $dbpwd="secret";
  $dbname="lograd8";

  $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
  mysql_select_db($dbname);    

  $rows  = "DatumTijd,gem_BPM,spreiding_BPM,max_BPM,min_BPM\n";

  $former=0;

  $query = "select date_format(tijd,'%Y/%m/%d %H:00') as datum,  avg(bpm) as gem_bpm, std(bpm) as spreiding_bpm,   max(bpm) as max_bpm,   min(bpm) as min_bpm     from data group by date_format(session,'%d/%m/%Y %H');";
  # +---------------+----------+---------------+---------+---------+
  # | datum_uur     | gem_bpm  | spreiding_bpm | max_bpm | min_bpm |
  # +---------------+----------+---------------+---------+---------+

  syslog(LOG_DEBUG,"$query");
  $result = mysql_query($query);
  while($r = mysql_fetch_array($result)){
    #syslog(LOG_DEBUG,print_r($r,TRUE));
    #syslog(LOG_DEBUG,print_r($tijd,TRUE));
    $rij="";
    # replace min=0 by min=avg for spo2 and bpm
    if ($r[4]==0) $r[4]=$r[1];
    for ($i=0;$i<=4;$i++) {
      $rij = $rij.$r[$i].",";
    }
    $rij=substr($rij,0,strlen($rij)-1);
    $rows = $rows.$rij."\n";
  }
  #syslog(LOG_DEBUG,print_r($rows,TRUE));
  #syslog(LOG_DEBUG,print_r($rows,TRUE));

  #echo $cols . '"rows":[',$google_JSON_row ."]}";
  # output off json string to caller.
  echo $rows;

  # saving of cvs string for debugging purposes
  #$fh=fopen("/tmp/get-data2.cvs","w");
  #fwrite($fh,$rows);
  #fclose($fh);

  mysql_close($db);

  syslog(LOG_DEBUG,"Einde get-data.php\n\n");
?>

The visual result of these scripts is as shown below:

get-sat3

When we zoom in on the period of the 11th until the 13th of september we see that the hourly minimum SPO2 level keeps decreasing:

get-spo3-zoom

This was also a weekend where we did not give additional oxygen. We knew that the decreased oxygen level was something that was centrally managed (no airway blockage or lung problems). This proved to us, and more importantly to her physisian, that not giving oxygen at night would probably result in deterioration of her physical well being and subsequently the study at home ended.

Categories
Geen categorie

Upgrading Zabbix server from Ubuntu 12.04 to 14.04 (using a MySQL database)

So, I like Zabbix. Sue me 🙂

But upgrading is a whole different ballgame. Zabbix on Ubuntu 12.04 is version 1.8.1 and Zabbix on Ubuntu 14.04 is version 2.2.2. And Zabbix only does an automatic database upgrade from version 2.0 onwards. So, basically, you are on your own and Google does not seem to help either.

So, let me tell you what I did and maybe it works for you too. If not, I am sorry.

In the end I had to checkout the Zabbix sources to find a database patchfile.

So I did:

svn co svn://svn.zabbix.com/trunk

And I found the following directory:  trunk/upgrades/dbpatches/2.0

And it has a “upgrade” script. Don’t bother, it does not work as is. But there is also a “patch” subdirectory with a shitload of sql scripts. They turn out to be just the ticket. So I listed them al in a file, put a mysql command in from of them and, for safety, a “read” between every line and run the script.

So, my logging output in /var/log/zabbix-server/zabbix_server.log changed from:

6033:20140701:133019.785 WEB monitoring: YES
6033:20140701:133019.785 VMware monitoring: YES
6033:20140701:133019.785 Jabber notifications: YES
6033:20140701:133019.785 Ez Texting notifications: YES
6033:20140701:133019.785 ODBC: YES
6033:20140701:133019.785 SSH2 support: YES
6033:20140701:133019.785 IPv6 support: YES
6033:20140701:133019.785 ******************************
6033:20140701:133019.785 using configuration file: /etc/zabbix/zabbix_server.conf
6033:20140701:133019.789 Cannot upgrade database: the database must correspond to  version 2.0 or later. Exiting ...

to:

9446:20140701:151423.638 Starting Zabbix Server. Zabbix 2.2.2 (revision 42525).
9446:20140701:151423.638 ****** Enabled features ******
9446:20140701:151423.638 SNMP monitoring: YES
9446:20140701:151423.638 IPMI monitoring: YES
9446:20140701:151423.638 WEB monitoring: YES
9446:20140701:151423.638 VMware monitoring: YES
9446:20140701:151423.638 Jabber notifications: YES
9446:20140701:151423.638 Ez Texting notifications: YES
9446:20140701:151423.638 ODBC: YES
9446:20140701:151423.638 SSH2 support: YES
9446:20140701:151423.638 IPv6 support: YES
9446:20140701:151423.638 ******************************
9446:20140701:151423.638 using configuration file: /etc/zabbix/zabbix_server.conf
9446:20140701:151423.797 current database version (mandatory/optional):  02010000/02010000
9446:20140701:151423.797 required mandatory version: 02020000
9446:20140701:151423.797 starting automatic database upgrade
9446:20140701:151423.831 completed 0% of database upgrade
9446:20140701:151423.863 completed 1% of database upgrade
9446:20140701:151423.955 completed 2% of database upgrade
9446:20140701:151424.055 completed 3% of database upgrade
9446:20140701:151424.371 completed 4% of database upgrade
9446:20140701:151424.916 completed 5% of database upgrade
9446:20140701:151425.232 completed 6% of database upgrade
9446:20140701:151425.701 completed 7% of database upgrade

etc.
So, before you begin, please make a backup of your database first!

The script I used in the end was this one:

mysql -h mysqlhost -p -D zabbix -pmysqlpassword < acknowledges.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < actions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < alerts.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < applications.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < auditlog_details.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < auditlog.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < autoreg_host.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < conditions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < config.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < dchecks.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < dhosts.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < drules.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < dservices.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < escalations.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < events.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < expressions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < functions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < globalmacro.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < globalvars.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < graph_discovery.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < graphs_items.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < graphs.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < graph_theme.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < groups.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < help_items.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_log.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_str.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_str_sync.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_sync.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_text.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_uint.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < history_uint_sync.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < host_inventory.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hostmacro.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hosts_groups.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hosts_profiles_ext.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hosts_profiles.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hosts.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < hosts_templates.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < housekeeper.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < httpstepitem.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < httpstep.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < httptestitem.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < httptest.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < icon_mapping.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < icon_map.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < ids.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < images.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < interface.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < item_discovery.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < items_applications.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < items.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < maintenances_groups.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < maintenances_hosts.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < maintenances.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < maintenances_windows.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < mappings.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < media.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < media_type.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < node_cksum.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < nodes.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opcommand_grp.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opcommand_hst.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opconditions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < operations.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opgroup.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opmediatypes.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opmessage_grp.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opmessage.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < opmessage_usr.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < optemplate.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < profiles.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < proxy_autoreg_host.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < proxy_dhistory.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < proxy_history.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < regexps.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < rights.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < screens_items.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < screens.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < scripts.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < service_alarms.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < services_links.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < services.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < services_times.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sessions.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < slideshows.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < slides.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmap_element_url.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmaps_elements.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmaps_links.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmaps_link_triggers.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmaps.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < sysmap_url.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < timeperiods.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < trends.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < trends_uint.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < trigger_depends.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < trigger_discovery.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < triggers.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < user_history.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < users_groups.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < users.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < usrgrp.sql
read dummy
mysql -h mysqlhost -p -D zabbix -pmysqlpassword < valuemaps.sql

After the update you are (almost) ready to go. When you get the url to /zabbix working in Apache he hits you with a configurator with lot of questions because he wants a /etc/zabbix/zabbix.conf.php file. Simply answer the questions and install the config file and you are good to go.

Enjoy!

Categories
Boeken English LibrePlan

LibrePlan, the missing manual becomes available!

 

Hello people,

It has taken a lot of work, but now the result is there.  “LibrePlan, the missing manual” has become available on lulu.com. And not only that, there now is also a nice manual with training exercises that I use when I give a training at a customer site.

IMG-20140227-WA0002

Now that all the writing is done, it is time to start another kind of writing. Next thing is working towards a new release! I hope to tell more about the features that will be in there soon.

For now, I am just happy with the book. I hope you are too.

Kind regards.

Jeroen Baten

Categories
English LibrePlan

LibrePlan number 2 in the list of great Project Management applications!

Hi people,

Recently Robin Muilwijk wrote a story on opensource.com titled “Top 5 open source project management tools in 2014“. And guess what, LibrePlan is ranked at second position!

Robin writes: “This is another full featured tool supporting resource allocation, Gantt charts, financials, and more. These features coupled with a web based application make LibrePlan a great collaboration platform.”

Sofar, nothing new 🙂

Then he continues: “LibrePlan ranks in my personal top 3 favorite open source project management tools because of its modern design and balanced user interface, as well as, good and complete documentation, built in reporting, and professional support.”

To us regular users of LibrePlan this is probably no news, but it is always good to have someone write such nice works about our project.

Behind the scenes we are still working very hard on this project and when there is more news we will sure let you all know about it asap.

Greets, Jeroen.

Categories
English LibrePlan

LibrePlan, behind the scenes

It’s been a while since I wrote a blogpost about LibrePlan, but apart from that things have been very busy. There is some behind-the-scenes stuff going on that I hope I can share with you all somewhere in the future. Now I would like to write about the things that have happened since my last post.

It is a lot. I spoke about LibrePlan at the NLUUG conference, at the SPIder foundation meeting, both located in the Netherlands. I just uploaded my presentation to SlideShare so you can all find it here.

I talked with companies (email or conferencecall) in several countries (US, Spain, France,  Netherlands) who are considering to implement LibrePlan and even gave a couple of demo’s using Skype and GotoMeeting.

I talked to a couple of companies who wanted to have some extra functionality build into LibrePlan and we shared some great ideas.

So, simply put, there is a lot going in behind the scenes.