Forum archive 2000-2006

Nandor Sieben - mysql query to find active users

Nandor Sieben - mysql query to find active users

by Arnold Pizer -
Number of replies: 0
inactiveTopicmysql query to find active users topic started 4/20/2005; 2:48:12 PM
last post 7/3/2005; 2:58:34 PM
userNandor Sieben - mysql query to find active users  blueArrow
4/20/2005; 2:48:12 PM (reads: 1555, responses: 4)
I am using the sql_single layout. Is there the a way to do an sql query in the mysql command line program to get a list of students currently loged in? Is this information kept in the database?

Nandor

<| Post or View Comments |>


userMichael Gage - Re: mysql query to find active users  blueArrow
4/20/2005; 4:13:50 PM (reads: 1843, responses: 0)
if you have direct access to the mysql database you can, with a little effort.

In the webwork database in the table labeled

courseName_key

there are entries labeled timestamp

The timestamp is in the standard unix notation of seconds since a certain date. You could grab those entries that have timestamps within the last 10 minutes and consider those the active users.

It would also be possible to add a module to WeBWorK 2 which pulled this data out of the database and presented it to an instructor on a webpage. The module would be fairly easy to write since all of the tools are in place -- it's just a matter of finding someone with the time to get it done.

-- Mike

<| Post or View Comments |>


userJohn Jones - Re: mysql query to find active users  blueArrow
4/20/2005; 6:17:32 PM (reads: 1893, responses: 0)
It is already part of the class list page. My guess Nandor wants to gather the information from the command line, or a script for logging.

John

<| Post or View Comments |>


userNandor Sieben - Re: mysql query to find active users  blueArrow
4/22/2005; 3:08:36 PM (reads: 1913, responses: 0)
Here is a solution. Thanks for the help. Nandor

#!/usr/bin/perl

# Prints a list of active webwork users. Assumes sql_single layout. # Author: Nandor Sieben

$password = 'mysqlrootpassword';

sub mysql { my ($sql) = @_; # print "$sql"; qx[mysql -s -u root -p$password webwork << EOF $sql EOF ]; }

@tables= mysql(q[show tables like '%key';]);

$ctime=time;

foreach $table (@tables) { chop $table; @rows=mysql(qq[select user_id, timestamp from $table]); foreach $row (@rows) { if ( $row =~ /(S+)s+(S+)/) { $name=$1; $stamp=$2; $age =($ctime - $stamp) / 60; if ($age < 10) { $table =~ s/_key//; print "$table\t $name\t $age\n"; } } } }

<| Post or View Comments |>


userNandor Sieben - Re: mysql query to find active users  blueArrow
7/3/2005; 2:58:34 PM (reads: 1693, responses: 0)
The script is available by CVS from the nau library. Nandor

<| Post or View Comments |>