Raspberry PI to PI System (HTML Interface)

Blog Post created by mtippett on Jan 7, 2016

There has been a few posts about inputting data into the PI system from devices such as the Raspberry PI or Arduino using the UFL interface and PI Web API (see here, here and here).  In Carlos’s post he mentions projects using the HTML interface too, though I couldn’t find any details on it so I thought I would share some of my experiences using the HTML interface to bring data into the PI system from a Raspberry PI.




A few months ago I had some free time and wanted to learn more about both python and SQL, so I started playing around with both on my raspberry PI at home, whilst I was waiting for a temperature sensor to arrive (postage to Australia can be slow!) I started by using python to measure some performance statistics about my raspberry PI. I used Python to read the performance statistics and write them to a database in MySQL on the Raspberry PI.  I then used PHP to access the data in MySQL from a webpage, see below:


Fast forward a little while to the new year period and I again had some free time on my hands so I decided to configure a HTML interface to read this data into my PI server, so the data flow now looks like this:



The Raspberry PI is a model B running Raspbian, the HTML interface is version and the PI server (AF and Data Archive) are 2015 R2.




Once MySQL and Apache were installed I used the following Python script to read the performance statistics and write is to the MySQL database, I used crontab to make the following script run every minute.


#! /usr/bin/env python

import subprocess, os, datetime, sys, statvfs
import MySQLdb as mdb

def get_ram():
  "Returns a tuple (total ram, available ram) in megabytes"
  s = subprocess.check_output(["free","-m"])
  lines = s.split('\n')
  return ( int(lines[1].split()[1]), int(lines[2].split()[3]) )
  return 0
def get_process_count():
  "Returns the number of processes"
  s = subprocess.check_output(["ps","-e"])
  return len(s.split('\n'))
  return 0
def get_up_stats():
  "Returns a tuple (uptime, 5 min loading average)"
  s = subprocess.check_output(["uptime"])
  load_split = s.split('load average: ')
  load_five = float(load_split[1].split(',')[1])
  up = load_split[0]
  up_pos = up.rfind(',',0,len(up)-4)
  up = up[:up_pos].split('up ')[1]
  return ( up , load_five)
  return ( '' , 0 )

def get_connections():
  "Returns the number of network connections"
  s = subprocess.check_output(["netstat","-tun"])
  return len([x for x in s.split() if x == 'ESTABLISHED'])
  return 0

def get_temperature():
  "Returns the temperature in DegC"
  s = subprocess.check_output(["/opt/vc/bin/vcgencmd","measure_temp"])
  return float(s.split('=')[1][:-3])
  return 0

def get_ipaddress():
  "Returns the current IP address"
  arg='ip route list'
  data = p.communicate()
  split_data = data[0].split()
  ipaddr = split_data[split_data.index('src')+1]
  return ipaddr

def get_cpu_speed():
  "Returns the current CPU speed"
  f = os.popen('/opt/vc/bin/vcgencmd get_config arm_freq')
  cpu =
  return cpu

def get_current_time():
  "Returns the current time as a string"
  ctime ='%Y-%m-%d %H:%M:%S')
  return ctime

def get_free_MB():
  "Returns the number of available MB on the SD card and % free"
  x = os.statvfs(".")
  freeMbytes = x[statvfs.F_BSIZE] * x[statvfs.F_BAVAIL]
  totalMbytes = x[statvfs.F_BSIZE] * x[statvfs.F_BLOCKS]
  "convert bytes to MB"
  freeMbytes = freeMbytes / (1024*1024)
  totalMbytes = totalMbytes / (1024*1024)
  PCfree = 100 * (float(freeMbytes)/float(totalMbytes))

FreeRam = get_ram()[1]
Processes = get_process_count()
UpTime = get_up_stats()[0]
Connections = get_connections()
Temp = get_temperature()
Time = get_current_time()
FMB = get_free_MB()
FreeMB = FMB[0]
PCFreeMB = FMB[1]

"Insert values into SQL"
  con = mdb.connect('localhost', 'XXXXXX', 'XXXXXX', 'perfmon');
  cur = con.cursor()
  cur.execute("INSERT INTO NalaStats (id,timestamp,freeram,processes,uptime,connections,temp,freeMB,PCfreeMB) VALUES (NULL,%s,%s,%s,%s,%s,%s,%s,%s);", (Time,FreeRam,Processes,UpTime,Connections,Temp,FreeMB,PCFreeMB))
except mdb.Error, e:
  print "Error %d: %s" % (e.args[0],e.args[1])
  if con:


I also set up the following script to run every week to delete data in MySQL which was more than 14 days old so as not to fill up all the space on the SD card.


#! /usr/bin/env python

import datetime, sys
import MySQLdb as mdb

"Number of days to store data"
TimeRange = 14

"Delete historical data from SQL"
  con = mdb.connect('localhost', 'XXXXXX', 'XXXXXX', 'perfmon');
  cur = con.cursor()
  cur.execute("DELETE FROM NalaStats WHERE TIMESTAMPDIFF(DAY,timestamp,NOW()) > %s;", TimeRange)
except mdb.Error, e:
  print "Error %d: %s" % (e.args[0],e.args[1])
  if con:


The code for the webpage is below, it grabs the data from MySQL as well as doing some aggregate calculations (though I don’t read them into PI)


  <h1>Nala Performance Monitor</h1>
  // Connection information
  $server ="localhost";
  $username = "XXXXXX";
  $password = "XXXXXX";
  $dbname = "perfmon";

  // Create connection and select database
  try {
  $DBH = new PDO("mysql:host=$server;dbname=$dbname",$username,$password);
  } catch(Exception $e){
  echo $e->getMessage();

  // Prepare to query MySQL

  // Get most recent values from MySQL
  $STH_Rec = $DBH->query("SELECT * FROM NalaStats ORDER BY id DESC LIMIT 1");
  // Get averages from MySQL
  $STH_1hrAvg = $DBH->query("SELECT AVG(temp) FROM (SELECT temp FROM NalaStats ORDER BY id DESC LIMIT 60) AS temperature");
  $STH_24hrAvg = $DBH->query("SELECT AVG(temp) FROM (SELECT temp FROM NalaStats ORDER BY id DESC LIMIT 1440) AS temperature");

  // Set the fetch mode

  // Fetch data
  $Rec = $STH_Rec->fetch();
  $Temp1hrAvg = $STH_1hrAvg->fetch();
  $Temp24hrAvg = $STH_24hrAvg->fetch();

  // Print values
  print "<P>Measurement #: <B>" . $Rec['id'] . "</B></P>";
  print "<P>Timestamp: <B>" . $Rec['timestamp'] . "</B></P>";
  print "<P>Free RAM: <B>" . $Rec['freeram'] . "</B> MB</P>";
  print "<P>Free disk space: <B>" . $Rec['freeMB'] . "</B> MB</P>";
  print "<P>Free disk space: <B>" . round($Rec['PCfreeMB'],1) . "</B> %</P>";
  print "<P>Processes: <B>" . $Rec['processes'] . "</B></P>";
  print "<P>System Uptime: <B>" . $Rec['uptime'] . "</B></P>";
  print "<P>Connections: <B>" . $Rec['connections'] . "</B></P>";
  print "<P>Temperature: <B>" . $Rec['temp'] . "</B> DegC</P>";
  print "<P>Temperature 1 hour average: <B>" . round($Temp1hrAvg['AVG(temp)'],1) . "</B> DegC</P>";
  print "<P>Temperature 24 hour average: <B>" . round($Temp24hrAvg['AVG(temp)'],1) . "</B> DegC</P>";

  // Cleanup
  $DBH = null;




Here is an AF element template I created for my Raspberry PI performance monitor, it is configured so that the tags can be automatically created, to roll out an element from this template the user just needs to enter the data in the ‘Tag Configuration’ Attributes and press ‘Create or Update Data Reference’ and all tags will be created.

The current tag values, note that 226693 measurements at 1 measurement/minute is around 157 days.  The longest up-time I have had so far is around 100 days, showing that the platform is fairly stable (being in my living room the biggest threat it has are me doing the vacuuming and my cat's curiosity)

An interesting thing a found was that it is possible to determine when I had the air conditioning on by looking at the CPU temperature of the raspberry PI, you can see in the plot below that the temperature drops markedly (more than 3 degrees in about 15 minutes) when the air conditioning is turned on.


Another interesting observation is the behaviour of the free disk space on the Raspberry PI, data is inserted into a SQL table every minute and then old data is deleted once a week, you can see this pattern reflected in the free space available.


Final Thoughts

  1. It was interesting to see how I could accurately determine whether the air conditioning was on based on a measurement of the core temperature rather than measuring the air temperature directly.  It goes to show how much information you can get from data if it is available.  I think my next steps may be to use analyses and event frames to see what else I can gleam from this.
  2. The timestamp of my measurements is consistently 2 seconds past the minute though it is scheduled to run on the minute.  This probably reflects a combination of the relatively inefficient way I have implemented this and the limitations of the Raspberry PI.
  3. Configuring the HTML interface was really easy as I had complete control of both the interface configuration and data source (web page) having this degree of control is relatively uncommon in my experience, but helped a lot in this case.  The reason the values on the webpage are in bold (first figure above) is that I used the bold HTML markers to find the values with the HTML interface.