Report Builder, checking log ins in a month?

5 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: August 1, 2011   (RSS)

By (Deleted User) - July 10, 2011

Is the Report Builder able to generate reports that would capture the number of all users (as a whole) have logged into the system over the last month, and it so, does anyone have any examples they could point at for this?

This would be not be referring to the total number of times they have logged in. The intention would be to grab just the number of users that are using the system within the last month, and not the usage frequency. Something like scanning the database and pulling back a count of how many people have a last log-in within the last 30 days or so.

Re: [robin] Report Builder, checking log ins in a month?

By (Deleted User) - July 24, 2011

Hey Robin...that is putting me on track. What I would be looking for is something to hook onto that will not only display the users who have logged in over the last 30 days, but also a flat total number of users that have logged in over the last 30 days.

Maybe I need to do this is two separate reports? I was thinking of listing the total number of user log-ins by month, but I really only need to be able to do the last 30 days. Any advice?

Re: [cKopyar] Report Builder, checking log ins in a month?

By robin - July 25, 2011

Hi,

A seperate report is a good idea if you want to pull any other kind of results.

CMS Builder only stores the last login date though, so the data is there to see how many individual users logged in the last month, but not how many times each. With that in mind - you just need to modify your query a little to pull a total:

SELECT count(*) as totalUsers
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE lastLoginDate > DATE_SUB(NOW(),INTERVAL 30 DAY)

Hipe that helps,
RObin
Robin
Programmer
interactivetools.com

Re: [cKopyar] Report Builder, checking log ins in a month?

By gkornbluth - August 1, 2011

Hi cKopyar,

I don't know if this recipe from my CMSB Cookbook http://www.thecmsbcookbook.com will be of any help but I thought I'd pass it on.

Hope it helps,

Jerry Kornbluth

CREATING A LOGIN LOG TO SEE WHICH MEMBERS SIGN IN AND WHEN

NOTE: Records will only be added to the "Login Log" section for actual membership logins, not by logins to the CMSB interface.

1) Create a Multi Record section called "Login Log".

2) Modify it and remove all the fields except "num" and "createdDate" (note that you'll need to "Enable System Field Editing" under the "Advanced Commands..." dropdown to remove some of the fields.)

3) Add a List Field called "Who". Modify your "Who" field as follows:

List Options: Get options from database (advanced)

Section Tablename: accounts
Use this field for option values: num
Use this field for option labels: username


4) Now, make some more changes to your Login Log section, this time at the top of the page:

In the General tab, change "ListPage Fields" to "createdDate, who".
Under the Viewer Urls tab, delete all the existing "Filename Fields" entries.
Under the Searching tab, set "Search Fields" to "who, createdDate".
Finally, under the Sorting tab, set "Order By" to "createdDate DESC". Now click Save Details.

5) Now open up cmsAdmin/plugins/websiteMembership/websiteMembership.php in a text editor and find "redirect on success". Add the code below before "redirect on success":

// CUSTOM CODE! add record to login_log
global $TABLE_PREFIX;
mysql_query(mysql_escapef("INSERT INTO {$TABLE_PREFIX}login_log SET createdDate = NOW(), who = ?", $CURRENT_USER['num']))
or die("Mysql error adding login_log record: ". htmlspecialchars(mysql_error()) . "\n");

After you've created this log, you can create a list viewer that your client can access and restrict access to admins only.

Here's a simple example that you can style to match your site design.

At the top of your listing page:
NOTE: Don't forget to change the $dirsToCheck = array('/PATH_TO_YOUR_SERVER/','','../','../../','../../../'); to match your server path. (you can find this in the admin>code generator code for any section.

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/PATH_TO_YOUR_SERVER/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records
list($login_logRecords, $login_logMetaData) = getRecords(array(
'tableName' => 'login_log',
));
if (!$CURRENT_USER['isAdmin']) { websiteLogin_redirectToLogin(); }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Login Log</title>
</head>
<body>

<?php foreach ($login_logRecords as $record): ?>
<?php echo date("F jS Y, g:i a ", strtotime($record['createdDate'])) ?>

<?php echo $record['who:label'];?>
<hr/>
<?php endforeach ?>
</body>
</html>
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php