Page 1 of 1

Track logins in a database instead of a log file

Posted: 2013-05-25 21:26
by shasta59
I had a request to have the logins to the system tracked in a table so that stats can be run on the data easily. They wanted to know frequency, who and other info in order to determine a number of details regarding the site. This can be recorded into a log file but then you have to download the log file and massage the data.

Here is how I did this:

Created a new table in AppGini in my app - called it member_log_ins
Fields were username, ip, date_time. Later added in, due to a request, tablename_first_visited, time_logged_out, auto_logged_out

username: username of person logging in
ip: ip address coming from
date_time: date and time log in happened
tablename_first_visited: this is the table they first click on
time_logged_out: time they logged out if they did
auto_logged_out; a task runs on a timer and checks to see if the time_logged_out field is filled in and if not put a 1 or 0 in auto_logged_out if time_logged_out does not have an entry in it. (This made it easier to check log outs by adding up the 1's and comparing to total access counts)

As this needs to be done automatically the first data is put in there by using the _global.php hook file

Under function login_ok($memberInfo, &$args){

The following code is added: (and this should look familiar as it is partly from the help on the AppGini site to create a log file automatically)

A mysql_connect command to connect to the database to insure it is open

Then the following variable are created:

// the member details we'll be saving into the file
$username=$memberInfo['username'];
$ip=$memberInfo['IP'];
$date=date('m/d/Y');
$time=date('h:i:s a');
$date2=date('Y/m/d');

Followed by a mysql_query("INSERT INTO...) command to put the data into the table

Then to get the table they first click on I put some code into the home.php file to trap the first click. The code first checks to make sure, for the record just created in the members_log_ins, that the field tablename_first_visited is not filled in yet. If it isn't it takes the name of the table clicked on and appends it to that file based upon the record ID.

At log out time the same events happen where it puts in the date/time they logged out. If they left the browser open and the auto log out system had to log them out that was also recorded by putting a 2 into the auto_logged_out field.

Then this data is able to be used to track patterns etc with some more code which calls up this data and displays various charts etc on a page only accessible to those with authorization to view.

I have not put in the entire code and method here as this is just to explain what can also be done with the base code to generate stats and other tracking info and not having to massage the data from a log file.

The next step is going to record what browser they used and if they access the help files for the site. All of this data is then used to make the app/site/ etc more useful and more productive.

Alan

Re: Track logins in a database instead of a log file

Posted: 2013-05-26 05:33
by KSan
Alan, great post as always. Thanks for sharing. I would love to see the code in due course but would fully understand it if you wanted to keep it private. Thanks

Re: Track logins in a database instead of a log file

Posted: 2013-05-26 13:39
by shasta59
Oh there is no desire to keep it private. When I get a chance I will post the code and more details as to where it goes.

As far as the stats part of it goes I use, for this instance, jpgraph to generate some graphs.

http://jpgraph.net/

Code: Select all

function login_ok($memberInfo, &$args){
$username=$memberInfo['username'];
	$ip=$memberInfo['IP'];
	$date=date('m/d/Y');
	$time=date('h:i:s a');
	$date2=date('Y/m/d');
	mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
		mysql_select_db("your_database_name") or die(mysql_error()); 
		
		if ($username <> 'admin'){  //I trap to not record admin access as it is not relevant to the stats
		
		mysql_query( "INSERT INTO members_access_log (username, ip_address, date, time) VALUES ('$username', '$ip', '$date2', '$time')") or die(mysql_error()); 
The above will populate the database when a user logs in. Same method basically as saving to a log file.

When time permits I will add more lines of the other code to track which table is clicked on etc. I will not be covering jpgraph at all as they have their own documentation but I use it to produce charts, both pie and bar.

Alan

Re: Track logins in a database instead of a log file

Posted: 2013-05-27 02:52
by KSan
Alan, thanks much for your sharing. Most appreciated.

Regards,
Kerem

Re: Track logins in a database instead of a log file

Posted: 2014-08-19 06:25
by peebee
Hi All. I need to implement exactly the process that Alan has detailed here. A log of all User login activity to be stored in a table rather than a log file. Luckily for me, Alan had already provided most of the process involved. Thanks!

I've created my login table and the _global hook provided works perfectly to populate the table when the User logs in.

Using the "function login_ok($memberInfo, &$args){" in the _global hook, it was very easy to pass the login variables to the new table fields.

My question is - how/where do I grab the logout details and more importantly, is it possible to relate the logout date/time to the same login session in the table?

That is: Login Username, IP, Login Date/Time PLUS Logout Date/time in the one table entry from the one User session.

Any help would be much appreciated. Thanks.

Re: Track logins in a database instead of a log file

Posted: 2014-08-19 23:31
by peebee
OK, following on from my own post above.....

I figured out that I could pass the logout details to the new table by;

(a) adding a new "logout_ok" hook in the "function logOutMember()" of the generated incCommon.php (around line 232)

Code: Select all

 #########################################################
	function logOutMember(){
		logOutUser();
		// hook: logout_ok
		if(function_exists('logout_ok')){
			$args=array();
				logout_ok(array(
					'username' => $_POST['username'],
					'password' => $_POST['password'],
					'IP' => $_SERVER['REMOTE_ADDR']
					), $args);
			}
		redirect("index.php?signIn=1");
	}
	#########################################################
and then (b) adding a new logout_ok function to the _global.php hook file (just basically duplicating Alan's login_ok function but changing the variables to suit the logout date/time).

This of course though adds a new table row for the logout details, rather than appending it to the existing session login row for that User. Anybody have any suggestions how I might be able to add the logout date/time to the existing User login session log row? Thanks for any suggestions.

Re: Track logins in a database instead of a log file

Posted: 2014-08-20 07:57
by peebee
Feels a bit like I'm talking to myself but just in case anyone else is interested in doing the same, I managed to get it all working so the login data/time and the logout date/time are both recorded in the one row of the table, based on the session ID.

I added a new varchar(255) field to my login_log table (I called it session_ID - it could be a hidden field if so desired).

I added another variable of $session_id=session_id(); to the login_ok function in the _global.php hook (see earlier posts)

I added the $session_id variable to my INSERT INTO mysql_query so it populated the "session_id" field in the login_log table with the Users actual current session ID.

So that's the login details all taken care of - now for the logout:

I added a WHERE condition in the logout_ok hook so it matched the session_id and updated the current session login details with the logout date/time.

The logout_ok hook in the incCommon.php file in my last post killed the session before it could check the session ID in the table. Fortunately, that was easily fixed by moving the hook up one line to before the logOutUser();, like so:

Code: Select all

#########################################################
	function logOutMember(){
		// hook: logout_ok
		if(function_exists('logout_ok')){
			$args=array();
				logout_ok(array(
					'username' => $_POST['username'],
					'password' => $_POST['password'],
					'IP' => $_SERVER['REMOTE_ADDR']
					), $args);
			}
		logOutUser();		
		redirect("index.php?signIn=1");
	}
#########################################################
I then added the logout_ok function to the _global.php hook file and everything works just fine:

Code: Select all

function logout_ok($memberInfo, &$args){
		$logout_date=date("Y-m-d H:i:s A");		
		$session_id_now=session_id();
		sql("update login_log set logged_out=('$logout_date') where session_id=('$session_id_now')", $eo);
	
	return '';
	}

Re: Track logins in a database instead of a log file

Posted: 2014-08-20 15:00
by KSan
Just had a chance to play catchup and wanted to say thanks for your updates. This is great!

Re: Track logins in a database instead of a log file

Posted: 2015-12-07 03:58
by rmloehmann
I upgraded to version 5.42 and now my tracking no longer works. I have 8 database applications all running the same code, and no logins after the 9:50pm update time are being tracked. Anyone have any idea what changed?

Re: Track logins in a database instead of a log file

Posted: 2016-03-30 15:33
by AhmedBR
I am using Global Hook to track logins, inserting the values in a table called LogIns:

Code: Select all

    $username=$memberInfo['username'];
    $ip=$memberInfo['IP'];
    $date=date('Y-m-d');
    $time=date('h:i:s a');
    $group = $memberInfo['groupID'];

    sql("INSERT INTO `LogIns` (`IP`, `Name`, `Date`, `Time`, `Group`, `Client`) VALUES ('$ip','$username', '$date', '$time','$group', null)", $eo);
(not tested on 5.5 yet, but should work since there were no changes to hook files as far as I know)
Hope this helps

Re: Track logins in a database instead of a log file

Posted: 2016-03-31 01:52
by AhmedBR
Made a small change to get a clickable GEO of IP
http://ip-api.com/#95.211.205.151

Code: Select all

    $username=$memberInfo['username'];
    $ip=$memberInfo['IP'];
    $date=date('Y-m-d');
    $time=date('h:i:s a');
    $group = $memberInfo['groupID'];

    sql("INSERT INTO `LogIns` (`IP`, `Name`, `Date`, `Time`, `Group`, `Client`) VALUES (concat('http://ip-api.com/#','$ip'),'$username', '$date', '$time','$group', null)", $eo);

Re: Track logins in a database instead of a log file

Posted: 2016-04-01 01:46
by shasta59
Hey great ideas.

I had just logged in to post how to save the log out details but see that has been done. A different method than I used but it works. There are many ways to get to the same thing.

Next thing I am working on is tracking what a user is doing and logging it into an activity table. Pretty simple actually. All it will record is what records looked at, what records created, changed and what was changed etc. (Have someone who wants to know who did what and when).

Will post it when I get a chance.

I think someone may have posted that on here already. Will have to have a look around.

Alan

Re: Track logins in a database instead of a log file

Posted: 2016-04-01 15:52
by AhmedBR
Thanks to everyone sharing codes in the forum :D

Quick update: Login and Logout code posted here working fine in 5.5 ;)

Re: Track logins in a database instead of a log file

Posted: 2017-10-09 20:56
by Jay Webb
Need help with this. I'm using AppGini 5.62
I have table called; LogIns, with 6 fields; ID, IP, Name, Date, Time, GroupID. and using this code, but getting error in the; mysql("INSERT INTO
I have added code to; __global.php and I can't figure out what is wrong.

( code )
function login_ok($memberInfo, &$args){
$ip=$memberInfo['IP'];
$username=$memberInfo['username'];
$date=date('m/d/Y');
$time=date('h:i:s a');
$group=$memberInfo['groupID'];

mysql("INSERT INTO 'LogIns' ('IP', 'Name', 'Date', 'Time', 'GroupID') VALUES ('$ip', '$username', '$date', '$time', '$group')", $eo);
}
( end code )
( error )
Fatal error: Uncaught Error: Call to undefined function mysql() in /homepages/01/d000000/htdocs/Gini/hooks/__global.php:32 Stack trace: #0 /homepages/01/d000000/htdocs/Gini/incCommon.php(348): login_ok(Array, Array) #1 /homepages/01/d000000/htdocs/Gini/lib.php(81): logInMember() #2 /homepages/01/d000000/htdocs/Gini/index.php(6): include('/homepages/13/d...') #3 {main} thrown in /homepages/01/d000000/htdocs/Gini/hooks/__global.php on line 32
( end error )

Re: Track logins in a database instead of a log file

Posted: 2017-10-09 21:16
by AhmedBR
try your code without the "my" at this line:

Code: Select all

sql("INSERT INTO 'LogIns' ('IP', 'Name', 'Date', 'Time', 'GroupID') VALUES ('$ip', '$username', '$date', '$time', '$group')", $eo);
See if this solves your problem.

Re: Track logins in a database instead of a log file

Posted: 2017-10-12 18:09
by Jay Webb
Thanks for your reply, that didn't work, I get a plain white screen after login but no error. My database is mysql, I tried it a bunch of ways an still can't get it working.

Re: Track logins in a database instead of a log file

Posted: 2017-10-12 23:57
by Jay Webb
Just wanted to post that I fixed my error, it was so obvious, code should have been,

sql("INSERT INTO `db000000`.`LogIns` (`ID`,`IP`,`Name`,`Date`,`Time`,`GroupID`) VALUES ('$id','$ip','$username','$date','$time','$group')", $eo);

had it login to add ID number but didn't have it in the VALUES. All is good...

Re: Track logins in a database instead of a log file

Posted: 2021-01-22 23:06
by aarlauskas
Hi, just wanted to say thanks for all of your work on this. I know its old but hey, this made may day! :D