Project:LHS Graphs and Visualizations

LHS Graphs and Visualizations
Members	Elliot
QR code

Overview

I'd like to supplement the Cacti graphs that we have for LHS bandwidth and power with metrics that provide insight to the growth of our community and organisation over time.

Initially I'd like to chart the following:

• Number of members
• Wiki activity

Later I'd like to investigate:

• Website visitors and/or page impressions
• Mailing list activity
• Space occupancy

Phase 1

To chart the initial metrics various bits of data will need to be exposed in a Cacti friendly way. I need help in getting access to these data sources so that I can write the various data input methods.

Number of members

This data is stored in an Sqlite database on Turing. See the Schema. It can be queried like so:

 SELECT COUNT(id)
 FROM users
 WHERE subscribed = true;

And for pending users:

 SELECT COUNT(id)
 FROM users
 WHERE subscribed = false;

To import historic data into rrdtool we can look at the date of members first payments - I probably won't bother with this:

 SELECT t1.timestamp, COUNT(t1.id) 
 FROM transactions t1
 LEFT JOIN transactions AS t2 ON t1.user_id = t2.user_id
 AND t1.timestamp > t2.timestamp
 WHERE t2.timestamp IS NULL
 GROUP BY t1.timestamp;

Note: So it seems that Cacti runs on babbage, therefore we actually require a PHP script on Turing to expose the member numbers and then a script on babbage to make the HTTP request.

This PHP script should generate the following output for cacti: subscribed:136 pending:2

 <?
 header("Content-Type: text/plain");
 
 $db = new fDatabase('sqlite', dirname(__FILE__) . '/../../var/database.db');
 
 $subscribers = $db->translatedQuery( 'SELECT COUNT(id) FROM users WHERE subscribed=1' )->fetchRow();
 $pending = $db->translatedQuery( 'SELECT COUNT(id) FROM users WHERE subscribed=1' )->fetchRow();
 
 ?>subscribers:<?php echo $subscribers['count(*)'] ?> pending:<?php echo $pending['count(*)'] ?>

Perl to fetch member numbers for cacti:

 #!/usr/bin/perl
 
 use strict;
 use LWP::Simple;
 
 eval {
   my $file = get("http://london.hackspace.org.uk/member_stats.php");
   print $file;
   1;
 } or do {
   print "NaN";
 }

Wiki statistics

• We can get this using the MediaWiki API:

 http://wiki.hackspace.org.uk/w/api.php?action=query&meta=siteinfo&siprop=statistics&format=xml

It returns:

 <?xml version="1.0"?>
 <api>
   <query>
     <statistics
       pages="759"
       articles="215"
       views="229656"
       edits="7368"
       images="186"
       users="166"
       activeusers="22"
       admins="61"
       jobs="13"
     />
   </query>
 </api>

Perl to generate the following output for cacti: pages:759 articles:215 views:229656 edits:7368 images:186 users:166 activeusers:22 admins:61 jobs:13

 #!/usr/bin/perl
 
 use strict;
 use LWP::Simple;
 use XML::Simple;
 
 eval {
   my $file = get("http://wiki.hackspace.org.uk/w/api.php?action=query&meta=siteinfo&siprop=statistics&format=xml");
   my $xs1 = XML::Simple->new();
   my $doc = $xs1->XMLin($file);
   my $first = 1;
 
   foreach my $key (keys (%{$doc->{query}->{statistics}})){
     if ($first eq 1) {
       $first = 0;
     } else {
       print " ";
     }
     print $key . ":" . $doc->{query}->{statistics}->{$key};
   }
   1;
 } or do {
   print "NaN";
 }

Phase 2

Mailing list activity

• There is no API for Google Groups. Perhaps poll the RSS feed and count unrecognized message ids or look at the date?

 https://groups.google.com/group/london-hack-space/feed/rss_v2_0_msgs.xml?num=50

• We can also calculate the size of the list (members) from the mailing list download

Website visitors and/or page impressions

The main site and the Wiki use Google Analytics and this has an API. This documented method looks promising:

 https://www.google.com/analytics/feeds/data?metrics=ga%3Avisits%2Cga%3Apageviews&start-date=2010-11-29&end-date=2010-12-13&max-results=50

Phase 3

Space occupancy

• We could use a directional IR occupancy counter. I think that we already have something like this in the LHS stores.