Joomla! CLI Script to collect user stats such as active sessions and also to calculate churn. Includes install script to create tables.

userstats.php

<?php
/**
 * @package     Joomla.CLI
 * @subpackage  cli_userstats
 */


// Make sure we're being called from the command line, not a web interface
if (array_key_exists('REQUEST_METHOD', $_SERVER)) die();

/**
 * CLI Bootstrap
 *
 * Run the framework bootstrap with a couple of mods based on the script's needs
 */

// We are a valid entry point.
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

// Load system defines
if (file_exists(dirname(dirname(__FILE__)) . '/defines.php'))
{
	require_once dirname(dirname(__FILE__)) . '/defines.php';
}

if (!defined('_JDEFINES'))
{
	define('JPATH_BASE', dirname(dirname(__FILE__)));
	require_once JPATH_BASE . '/includes/defines.php';
}

// Get the framework.
require_once JPATH_LIBRARIES . '/import.legacy.php';

// Bootstrap the CMS libraries.
require_once JPATH_LIBRARIES . '/cms.php';

// Import the configuration.
require_once JPATH_CONFIGURATION . '/configuration.php';

// System configuration.
$config = new JConfig;

// Configure error reporting to maximum for CLI output.
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Load Library language
$lang = JFactory::getLanguage();


/**
 * A command line cron job to run the userstats collection.
 *
 * @package     Joomla.CLI
 * @subpackage  cli_userstats
 */
class UserstatsCli extends JApplicationCli
{
	/**
	 * Entry point for CLI script
	 *
	 * @return  void
	 */
	public function doExecute()
	{
		$db = JFactory::getDbo();

		$db->setQuery("set time_zone = '+00:00'");
		$db->execute();

		try {
			if ($this->input->get('install'))
			{
				$this->install($db);
			}

			if ($this->input->get('sessions'))
			{
				$this->sessions($db);
			}

			if ($this->input->get('churn'))
			{
				$this->churn($db);
			}
		} catch (Exception $e)
		{
			$this->out('Error processing request: ' . $e->getMessage());
		}
	}

	protected function install($db)
	{
		$db->setQuery(<<<INSTALL
CREATE TABLE IF NOT EXISTS `#__userstats_sessions` (
  `measurement_date` datetime NOT NULL,
  `active_sessions` int(11) DEFAULT NULL,
  `guest_sessions` int(11) DEFAULT NULL,
  PRIMARY KEY (`measurement_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSTALL
);
		$db->execute();
		$this->out('Sessions Table Installed');

		$db->setQuery(<<<INSTALL
CREATE TABLE IF NOT EXISTS `#__userstats_logins` (
  `measurement_date` date NOT NULL,
  `last_2days` int(11) DEFAULT NULL,
  `last_7days` int(11) DEFAULT NULL,
  `last_30days` int(11) DEFAULT NULL,
  `last_45days` int(11) DEFAULT NULL,
  `last_60days` int(11) DEFAULT NULL,
  PRIMARY KEY (`measurement_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSTALL
);
		$db->execute();
		$this->out('Logins Table Installed');
	}

	protected function sessions($db)
	{
		$session = JFactory::getSession();
		$query = $db->getQuery(true)
			->delete($db->quoteName('#__session'))
			->where($db->quoteName('time') . ' < ' . $db->quote((int) (time() - $session->getExpire())));
		$db->setQuery($query);
		$db->execute();
		$db->setQuery('insert into #__userstats_sessions SELECT NOW(), count(*), guests from #__session s, (SELECT count(*) as guests FROM #__session where guest = 1) guests ');
		$db->execute();
		$this->out("Sessions stored.");
	}

	protected function churn($db)
	{
		$db->setQuery(<<<QUERY
REPLACE into #__userstats_logins
select now(), last_2days, last_7days, last_30days, last_45days, last_60days
FROM
	(select count(*) last_2days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 2) day2,
	(select count(*) last_7days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 7) day7,
	(select count(*) last_30days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 30) day30,
	(select count(*) last_45days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 45) day45,
	(select count(*) last_60days from #__users where DATEDIFF(NOW(), lastvisitdate) <= 60) day60
QUERY
);
		$db->execute();
		$this->out("Churn values stored.");
	}
}

// Instantiate the application object, passing the class name to JCli::getInstance
// and use chaining to execute the application.
JApplicationCli::getInstance('UserstatsCli')->execute();