Setup the FSG to Log its Sensors

From OpenFSG

This demonstrates how to setup the FSG to log all of it's Temp/Fan/Voltage sensors at a specified interval to a MySQL database. To do this appropriately you need to install ipkg so you can wget to execute php scripts from the shell.

1 Setup
2 Procedure
3 Customizations
4 Appendix
5 Changes since Firmware 4.X

Setup

wget must be installed. Installing ipkg will automatically install wget.
Turn on the MySQL server from the web interface

Procedure

Preparation

Access the phpmyadmin page Here and run the SQL in Appendix A
Using the Web Interface create a Folder called "Logging_Scripts" and give yourself access to it.
Create a new HTTP configuration from the web interface at [1] and enabled PHP on it
In this example, we will use the following for the "Web Front Page" settings:
Name: LoggingScripts
DNS name: "none"
Port: 1600
Folder: Logging_Scripts
Create a directory in /home/Logging_Scripts called "scripts"

Procedure

Copy and paste the script in #Appendix B to a file called sensorutil.php in the scripts directory
Copy and paste the script in #Appendix C to a file called RecordSensors.php in the scripts directory
Copy and paste the script in #Appendix D to a file called DumpSensors.php in the scripts directory
Add the following two lines to the file /etc/init.d/croninit right before the if [ -s /etc/crontab ]; then statement

echo "*/15 * * * * root /opt/bin/wget --delete-after http://127.0.0.1:1600/scripts/RecordSensors.php " >> /etc/crontab
echo "8 */8 * * * root /opt/bin/wget --delete-after http://127.0.0.1:1600/scripts/DumpSensors.php " >> /etc/crontab

Either reboot the FSG or type sh /etc/init.d/croninit stop and then sh /etc/init.d/croninit start

TIP: Incase it doesn't work at first, replace 127.0.0.1 by the internal IP address, e.g. 192.168.1.??

Use

Copy and paste the script in #Appendix E to a file called readsensors.php in the scripts directory
Go Here and copy/Paste this html table into Excel to make some cool graphs.
- Or Download my Excel document from Here
  - This is setup to automatically query your FSG and make the graphs. Look at Sheet2 for the graphs.

Customizations

Sample Frequency

By default the sensors are recorded once every 15 minutes and then collectd values dumped to MySQL every 8 hours on the 08 minute (12:08 am , 8:08 am, 4:08 pm).

To have the sensors recorded more often or less often than every 15 minutes just change the */15 in the first echo line in the /etc/init.d/croninit file

To have the sensors dumped to MySQL more often or less often than every 8 hours just change the */8 in the second echo line in the /etc/init.d/croninit file

Sample Count

By default the sensors are sampled 13 times (the values tend to jump around a lot, this helps to smooth them out). To change this number, edit the $sensorvalues = readsensorsNtimes(13); line in the RecordSensors.php file.

Individual Sensors

By default these scripts record every sensor who's value actually changes. To alter which sensors are recorded, just modify the list of sensors in sensorutil.php and the columns in the readings table.

Appendix

Appendix A - SQL

SQL to create the logging Database and readings Table

CREATE DATABASE `logging` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE logging;
 
-- Table structure for table `readings`

CREATE TABLE `readings` (
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `temp1` char(6) default NULL,
  `temp2` char(6) default NULL,
  `temp3` char(6) default NULL,
  `fan1` char(6) default NULL,
  `in1` char(6) default NULL,
  `in2` char(6) default NULL,
  `in3` char(6) default NULL,
  `in4` char(6) default NULL,
  `in5` char(6) default NULL,
  `in6` char(6) default NULL,
  `in8` char(6) default NULL,
  `pwm1` char(6) default NULL,
  PRIMARY KEY  (`timestamp`)
) TYPE=MyISAM;

-- Create the user
GRANT USAGE ON *.* TO 'sensorslogger'@'%' IDENTIFIED BY 'logger';
GRANT SELECT , INSERT ON `logging`.`readings` TO 'sensorslogger'@'%';

Appendix B

filename: sensorutil.php

Script to read and average the sensors

<?
$sensorlocation = "/proc/sys/dev/sensors/w83782d-i2c-0-28/";
$sensors=array("fan1","pwm1","temp1","temp2","temp3", "in1","in2","in3","in4","in5","in6","in8"); 
$firstnumbersensors=array("pwm1");


function readsensors(&$sensorvalues)
{
  global $integersensors, $firstnumbersensors, $sensors, $sensorlocation;
  
  foreach($sensors as $sensor)
  {
    $value = file_get_contents("$sensorlocation$sensor");

    $pieces = explode(" ", $value);
    
    //For some of the sensors, the first value is the one we want 
    if(in_array($sensor, $firstnumbersensors))
      $value = $pieces[0];
    else
      $value = $pieces[count($pieces)-1];
      
    $value = floatval($value);

    //Sanity checks
    if($sensor == "fan1")
    {
       if($value < 11000 && $value > 1)
            array_push($sensorvalues[$sensor], $value);
     } 
     else
         array_push($sensorvalues[$sensor], $value);
  }
}

function readsensorsNtimes($repetitions)
{
  $sensorvalues = array();
  $sensortotals = array();
  
  global $sensors, $integersensors;

  //Create the sample collection arrays
  foreach($sensors as $sensor)
    $sensorvalues[$sensor] = array();
  
  //Collect all the samples (sensors values only change once every 1.5 seconds)
  for($i=1; $i<$repetitions; $i++)
  {
    readsensors($sensorvalues);
    sleep(2);
  }
  
  readsensors($sensorvalues);
  
  //Find the mean of each sample 
  foreach($sensors as $sensor)
  {
    $samples = count($sensorvalues[$sensor]);
    //if there are less than 2 samples, drop it
    if($samples < 2)
      $sensorvalues[$sensor] = NULL;
    else
    {
      $total = 0;
      foreach($sensorvalues[$sensor] as $value)
        $total += $value;     
      $sensorvalues[$sensor] = $total / $samples;
    }
  }
 
  //Convert each float into a 6 char string
  foreach($sensors as $sensor)
  {
    $value = (int)($sensorvalues[$sensor]);
    
    if($value < 10)
      $value = sprintf("%01.4F", $sensorvalues[$sensor]);
    else if($value < 100)
      $value = sprintf("%01.3F", $sensorvalues[$sensor]);
    else if($value < 1000)
      $value = sprintf("%01.2F", $sensorvalues[$sensor]);
    else
      $value = sprintf("%01.1F", $sensorvalues[$sensor]);  
         
    $sensorvalues[$sensor] = substr(strval($value), 0, 6);
  }    
  
  return $sensorvalues;
}

?>

Appendix C

filename: RecordSensors.php

Script to read and record the sensors

<?
require_once("sensorutil.php");

$sensorvalues = readsensorsNtimes(13);
$values="";
  
foreach($sensors as $sensor)
{
  if($sensorvalues[$sensor] == NULL)
    $values .=  'NULL , ';
  else
  {
    $value = is_string($sensorvalues[$sensor]) ? "'" . $sensorvalues[$sensor] . "'" : $sensorvalues[$sensor];
    $values .=  $value  . ',';
  } 
}
$mydate = date('Y-m-d H:i:s'); 

$query = "INSERT INTO readings (" . join(',', $sensors) . ",timestamp) VALUES ($values '$mydate');";

//open file for append 
$filename = "sensor_readings.sql"; 
if($myFile = fopen($filename, "a")) 
{ 
  fputs($myFile, $query); 
 
  //close the file 
  fclose($myFile); 
} 
 
?>

Appendix D

filename: DumpSensors.php

Script to Dump sensor values to the MySQL server

<?
$filename ="sensor_readings.sql";

$username="sensorslogger";
$password="logger";
$database="logging";

mysql_connect("localhost",$username,$password);
mysql_select_db($database);

//open file for append 
$file_contents = file_get_contents($filename);
if($file_contents)
{
  $queries = explode(";",$file_contents);
   
  print(count($queries));
  
  $result = true;

  foreach($queries as $query)
    $result = $result && mysql_query($query);
  
  //Delete the file we just ran if all the rows were inserted
  if($result)
    unlink($filename); 
}

mysql_close();

?>

Appendix E

filename: readsensors.php

Script to read the sensors from MySQL and output an HTML table

<?

$count = 300;
if(array_key_exists('count', $_GET))
  $count = intval($_GET['count']); 

$sensors=array("timestamp","fan1","pwm1","temp1","temp2","temp3", "in1","in2","in3","in4","in5","in6","in8");
$query = "SELECT * FROM readings ORDER BY timestamp DESC LIMIT $count";

$username="sensorslogger";
$password="logger";
$database="logging";

mysql_connect("localhost",$username,$password);
mysql_select_db($database);
$result = mysql_query($query);
print("<table><tr>");
foreach($sensors as $column)
  print("<th>$column</th>");
print("</tr>");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  print("<tr>");
  foreach($sensors as $column)
    print("<td>" . $row[$column] . "</td>");
  print("</tr>");  
}
mysql_close();

?>

Changes since Firmware 4.X

Appendix A - SQL

SQL to create the logging Database and readings Table

CREATE DATABASE `logging` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE logging;
 
-- Table structure for table `readings`

CREATE TABLE `readings` (
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `temp1_input` char(6) default NULL,
  `temp2_input` char(6) default NULL,
  `temp3_input` char(6) default NULL,
  `fan1_input` char(6) default NULL,
  `in1_input` char(6) default NULL,
  `in2_input` char(6) default NULL,
  `in3_input` char(6) default NULL,
  `in4_input` char(6) default NULL,
  `in5_input` char(6) default NULL,
  `in6_input` char(6) default NULL,
  `in8_input` char(6) default NULL,
  `pwm1` char(6) default NULL,
  PRIMARY KEY  (`timestamp`)
) TYPE=MyISAM;

-- Create the user
GRANT USAGE ON *.* TO 'sensorslogger'@'%' IDENTIFIED BY 'logger';
GRANT SELECT , INSERT ON `logging`.`readings` TO 'sensorslogger'@'%';

Appendix B

filename: sensorutil.php

Script to read and average the sensors

<?
$sensorlocation = "/proc/sys/dev/sensors/w83782d-i2c-0-28/";
$sensors=array("fan1_input","pwm1","temp1_input","temp2_input","temp3_input", "in1_input","in2_input","in3_input","in4_input","in5_input","in6_input","in8_input");
$firstnumbersensors=array("pwm1");
...

Appendix E

filename: readsensors.php

Script to read the sensors from MySQL and output an HTML table

<?
$count = 300;
if(array_key_exists('count', $_GET))
  $count = intval($_GET['count']); 

$sensors=array("fan1_input","pwm1","temp1_input","temp2_input","temp3_input", "in1_input","in2_input","in3_input","in4_input","in5_input","in6_input","in8_input"); 
$query = "SELECT * FROM readings ORDER BY timestamp DESC LIMIT $count";

$username="sensorslogger";
$password="logger";
$database="logging";

mysql_connect("localhost",$username,$password);
mysql_select_db($database);
$result = mysql_query($query);
print("<html><head><title>FSG-3 Recorded Sensors</title></head>");
print("<body >");
print("<table style='font-family: Verdana; font-size: 11px;' border=1><tr>");
print("<th>Fecha/Hora</th>");
foreach($sensors as $column)
  print("<th>$column</th>");
print("</tr>");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  print("<tr>");
  print("<td>" . $row['timestamp'] . "</td>");
  foreach($sensors as $column)
    print("<td>" . $row[$column] . "</td>");
  print("</tr>");  
}
print("</body></html>");
mysql_close();
?>

Retrieved from "http://www.openfsg.com/index.php/Setup_the_FSG_to_Log_its_Sensors"

Category: HowTos

Setup the FSG to Log its Sensors

From OpenFSG

Contents

Setup

Procedure

Preparation

Procedure

Use

Customizations

Sample Frequency

Sample Count

Individual Sensors

Appendix

Appendix A - SQL

Appendix B

Appendix C

Appendix D

Appendix E

Changes since Firmware 4.X

Appendix A - SQL

Appendix B

Appendix E

Views

Personal tools

Navigation

Search

Toolbox