We believe that tracking when users log into a computer is very good information. We can easily look up where students log in, track computer usage, aid with helpdesk tasks, etc. Our primary focus was to show that computer labs and carts are being used, and justify the 1:1 initiative we set forth.
This is was not a difficult task to setup but could be useful to others, so here it is!
First we need a good way to get data from the clients. With Linux, we could do it a multitude of ways, with windows, it can be a bit limited. We chose to do our data submission with a simple URL get. Simple, easy, fast, reliable. With some reading, it wasn’t too difficult to setup with Windows.
First… Server recording
We need a simple script to take variables and insert them into mysql with the needed information. Really all we need is the computer name and username. We can use the server time and get the client’s IP from the server so that we know it is consistent. Here is the server script:
<?php
// Written by Kirk Schnable for Marengo Community High School
// July 20th, 2011
/****************** SAMPLE SYNTAX ********************/
/* http://server/login.php?u=USERNAME&h=COMPUTERNAME */
/*****************************************************/
//# MySQL Connection
$link = mysql_connect("MYSQLSERVER", "DB", "PASSWORD") or die(mysql_error());
//# Gather Information
$user = mysql_real_escape_string($_GET['u']);
$hostname = mysql_real_escape_string($_GET['h']);
$ip = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
$date = date("Y-m-d H:i:s");
//# Query Database
$result = mysql_query("INSERT INTO `iplookup`.`login_records` (`LogonTime` ,`IP`, `Hostname` ,`User`)VALUES ('$date', '$ip', '$hostname', '$user');");
if(!$result){ /* do something on failure of mysql query */ }
?>
MySQL Database Structure
Using the server side script we can see who the IP was of the user submitting the record, time stamp that event, see who the user is, and what the hostname of the computer is.
Linux Logon Agent
The Linux logon agent could be written many different ways using several programs; curl, wget, lynx, etc. Simply put it needs to pull two variables and curl that URL… This needs to be put in the users (and/or skel’s) startup so that it runs at logon This could be done in a single command like:
curl -s "http://SERVER/logon-agent/login.php?u=$USER&h=$HOSTNAME"
Windows Logon Agent
Unfortunately windows does not have awesome tools like wget, curl, lynx, etc so it is a bit more in-depth to get it working. However it is easier to deploy using group policy on the server, you can deploy this script pretty easily. Put this script in the User Logon scripts:
' DECLARE ENVIRONMENT VARIABLES
dim URL
' GET SYSTEM VARIABLES
Set wshShell = CreateObject( "WScript.Shell" )
username = wshShell.ExpandEnvironmentStrings( "%USERNAME%" )
computer = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
' SET URL TO CONTACT
URL = "http://SERVER/logon-agent/login.php?u=" & username & "&h=" & computer
' ECHO VARIABLES FOR TESTING
'WScript.Echo URL
'WScript.Echo username
'WScript.Echo computer
on error resume next
Set objXML = CreateObject("MSXML2.ServerXMLHTTP")
if err then
msgbox("Error: " & err.description)
wscript.quit 1
end if
' Call the remote machine the request
objXML.open "GET", URL, False
objXML.send()
' return the response
'msgbox objXML.responSetext
' clean up
Set objXML = Nothing
Searching For Data!
This is a simple search web tool we created to locate user/computer/IP history.
<head>
<title>Realtime User Login Lookup</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<div style="text-align: center;">
<span id="header">Computer Login Database</span>
<form method="post" action="">
<span>Lookup IP: </span><input id="ip" type="text" name="ip" value="<?php echo($_GET['ip']); ?>" />
<br/>
<span>Lookup Host: </span><input id="hostname" type="text" name="hostname" value="<?php echo($_GET['host']); ?>" />
<br/>
<span>Lookup User: </span><input id="user" type="text" name="user" value="<?php echo($_GET['user']); ?>" />
<br/>
<span>Records: </span><input id="records" type="text" name="records" value="10000" />
<br/>
<input type="submit" value="Go" />
</form>
</div>
<?php if($_POST){
$link = mysql_connect("SERVER", "USER", "PASSWORD") or die(mysql_error());
$user = mysql_real_escape_string($_POST['user']);
$hostname = mysql_real_escape_string($_POST['hostname']);
$ip = mysql_real_escape_string($_POST['ip']);
$records = mysql_real_escape_string(intval($_POST['records']));
$standarddate = 'l, F jS, Y \a\t g:ia';
if($ip){ // Lookup IP
echo("<hr/>");
echo('<div style="text-align: center;">');
$rdate = mysql_query("SELECT DISTINCT CAST(`LogonTime` AS DATE) AS dateonly FROM `iplookup`.`login_records` WHERE `IP`='$ip'") or die(mysql_error());
echo("<p><span>This computer has been logged into on " . mysql_num_rows($rdate) . " different days.</span></p>");
$r = mysql_query("SELECT * FROM `iplookup`.`login_records` WHERE `IP`='$ip' ORDER BY `LogonTime` DESC LIMIT $records") or die(mysql_error());
if(mysql_num_rows($r) == 0){ echo("<p><span>No Results for IP <b>$ip</b></span></p>"); }
elseif(mysql_num_rows($r) == $records){ echo("<p><span>Showing the $records most recent logins on <b>$ip</b></span></p>"); }
else{ echo("<p><span>Showing all " . mysql_num_rows($r) . " previously recorded logins on <b>$ip</b></span></p>"); }
while($row = mysql_fetch_assoc($r)){
$date = $row['LogonTime']; $date = strtotime($date); $date = date($standarddate, $date); $username = $row['User']; $host = $row['Hostname'];
echo("<span><p><b>$username</b> @$host on $date</span></p>");
}
echo('</div>');
}
if($user){ // Lookup Username
echo("<hr/>");
echo('<div style="text-align: center;">');
$rdate = mysql_query("SELECT DISTINCT CAST(`LogonTime` AS DATE) AS dateonly FROM `iplookup`.`login_records` WHERE `User`='$user'") or die(mysql_error());
echo("<p><span>This user has logged in on " . mysql_num_rows($rdate) . " different days.</span></p>");
$r = mysql_query("SELECT * FROM `iplookup`.`login_records` WHERE `User`='$user' ORDER BY `LogonTime` DESC LIMIT $records") or die(mysql_error());
if(mysql_num_rows($r) == 0){ echo("<p><span>No Results for User <b>$user</b></span></p>"); }
elseif(mysql_num_rows($r) == $records){ echo("<p><span>Showing the $records most recent IPs of <b>$user</b></span></p>"); }
else{ echo("<p><span>Showing all " . mysql_num_rows($r) . " previously recorded IPs of <b>$user</b></span></p>"); }
while($row = mysql_fetch_assoc($r)){
$date = $row['LogonTime']; $date = strtotime($date); $date = date($standarddate, $date); $ip = $row['IP']; $host = $row['Hostname'];
echo("<span><p><b>$ip</b> ($host) on $date</span></p>");
}
echo('</div>');
}
if($hostname){ // Lookup Hostname
echo("<hr/>");
echo('<div style="text-align: center;">');
$rdate = mysql_query("SELECT DISTINCT CAST(`LogonTime` AS DATE) AS dateonly FROM `iplookup`.`login_records` WHERE `Hostname` LIKE '%$hostname%'") or die(mysql_error());
echo("<p><span>This computer has been logged into on " . mysql_num_rows($rdate) . " different days.</span></p>");
$r = mysql_query("SELECT * FROM `iplookup`.`login_records` WHERE `Hostname` LIKE '%$hostname%' ORDER BY `LogonTime` DESC LIMIT $records") or die(mysql_error());
if(mysql_num_rows($r) == 0){ echo("<p><span>No Results for Hostname <b>$hostname</b></span></p>"); }
elseif(mysql_num_rows($r) == $records){ echo("<p><span>Showing the $records most recent logins on <b>$hostname</b></span></p>"); }
else{ echo("<p><span>Showing all " . mysql_num_rows($r) . " previously recorded logins on <b>$hostname</b></span></p>"); }
while($row = mysql_fetch_assoc($r)){
$date = $row['LogonTime']; $date = strtotime($date); $date = date($standarddate, $date); $ip = $row['IP']; $username = $row['User'];
echo("<span><p><b>$username</b> ($ip) on $date</span></p>");
}
echo('</div>');
}
if(!$hostname && !$user && !$ip){ // Show "all" records.
echo("<hr/>");
echo('<div style="text-align: center;">');
$r = mysql_query("SELECT * FROM `iplookup`.`login_records` ORDER BY `LogonTime` DESC LIMIT $records") or die(mysql_error());
if(mysql_num_rows($r) == 0){ echo("<p><span>No Results.</span></p>"); }
elseif(mysql_num_rows($r) == $records){ echo("<p><span>Showing the $records most recent logins.</span></p>"); }
else{ echo("<p><span>Showing all " . mysql_num_rows($r) . " previously recorded logins.</span></p>"); }
while($row = mysql_fetch_assoc($r)){
$date = $row['LogonTime']; $date = strtotime($date); $date = date($standarddate, $date); $ip = $row['IP']; $username = $row['User']; $hostnames = $row['Hostname'];
echo("<span><p><b>$username</b> ($ip) ($hostnames) on $date</span></p>");
}
echo('</div>');
}
}
?>
Its complimentary style.css:
BODY{ background-color: maroon; }
SPAN{ color: white; }
SPAN#header{ font-weight: bold; }
INPUT#ip{ width: 175px; }
INPUT#hostname{ width: 150px; }
INPUT#user{ width: 150px; }
INPUT#records{ width: 50px; }
Custom Reports…
Once you have this information you should be able to run reports however you see fit. We have created a highly custom report for our use which is specific to our school that shows computer usage. That code is not ready to be posted at this time however here is a glimpse of what could be done. Please note that ltcart1,2204, and 2506 have been physically relocated to other rooms but because database entries exists, they show up as 0.
With this report we can see that with 30 calendar days, there have been 22 instructional days. This shows that these rooms are using computers almost every day!
Recent Comments