Skip navigation
All Places > All Things PI - Ask, Discuss, Connect > Blog > 2016 > January
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.

 

Overview

 

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 2.3.0.71 and the PI server (AF and Data Archive) are 2015 R2.

 

Implementation

 

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"
  try:
  s = subprocess.check_output(["free","-m"])
  lines = s.split('\n')
  return ( int(lines[1].split()[1]), int(lines[2].split()[3]) )
  except:
  return 0
def get_process_count():
  "Returns the number of processes"
  try:
  s = subprocess.check_output(["ps","-e"])
  return len(s.split('\n'))
  except:
  return 0
def get_up_stats():
  "Returns a tuple (uptime, 5 min loading average)"
  try:
  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)
  except:
  return ( '' , 0 )


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


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


def get_ipaddress():
  "Returns the current IP address"
  arg='ip route list'
  p=subprocess.Popen(arg,shell=True,stdout=subprocess.PIPE)
  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 = f.read()
  return cpu


def get_current_time():
  "Returns the current time as a string"
  ctime = datetime.datetime.now().strftime('%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))
  return(freeMbytes,PCfree)




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"
try:
  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))
  con.commit()
except mdb.Error, e:
  print "Error %d: %s" % (e.args[0],e.args[1])
  sys.exit(1)
finally:
  if con:
  con.close()

 

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"
try:
  con = mdb.connect('localhost', 'XXXXXX', 'XXXXXX', 'perfmon');
  cur = con.cursor()
  cur.execute("DELETE FROM NalaStats WHERE TIMESTAMPDIFF(DAY,timestamp,NOW()) > %s;", TimeRange)
  con.commit()
except mdb.Error, e:
  print "Error %d: %s" % (e.args[0],e.args[1])
  sys.exit(1)
finally:
  if con:
  con.close()

 

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)

 

<html>
  <body>
  <h1>Nala Performance Monitor</h1>
  <?php
  // 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
  $STH_Rec->setFetchMode(PDO::FETCH_ASSOC);
  $STH_1hrAvg->setFetchMode(PDO::FETCH_ASSOC);
  $STH_24hrAvg->setFetchMode(PDO::FETCH_ASSOC);


  // 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;
  ?>
  </body>
</html>

 

Results

 

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.

There is no way to send a negative number directly to a digital PI tag. Or more accurately, sending a negative integer to a digital state tag will result in a system digital state being written to the tag. For more details on why that happens, see this KB article.

 

Scott Robertson recently introduced me to a clever work-around for this issue. Instead of sending the negative number directly to the digital state tag you can instead do the following:

  1. Store the value in an integer PI Tag. We will call this PI Tag JANET.
  2. Reference JANET in an AF attribute. We will call the AF attribute CHRISSY.
  3. Use an enumeration set for CHRISSY that translates the integer into a string, and then you have two options:
    1. Write the string from CHRISSY back to a second digital PI Tag using a PI Analysis Service Expression. We will call the second PI Tag JACK.
    2. Use CHRISSY directly and save yourself a PI Tag.

 

If you do not have PI AF Server at your site, then you have one option:

  1. Store the integer value in JANET like before.
  2. Create JACK as a digital Performance Equation (PE) tag that uses a long, nested IF THEN ELSE statement to perform the translation from the integer to the digital state.

 

If you are new to enumeration sets, then these videos will help you get started:

Filter Blog

By date: By tag: