Hello there,
I was looking into the online users block and wanted to see if anyone can help me to find out the number of concurrent users for a given time period. This would show me how many users are logged in during a 15 minute time period.
For example, using the mdl_log and other tables, show me the number of users that were in the system on X day between 8:00 AM to 8:15 AM and then 8:16 AM to 8:30 AM, 8:31 AM to 8:45 AM, and so on.
I know I have to use the distinct to have a more accurate number of users.
What is the sql statement to do that? I don't care about their roles so it makes it easier.
Thanks
Felix "Flex"
SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
Number of replies: 17
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
anybody? still struggling to write the query =(
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Hubert Chathi -
You can take the timestamp, and round it down to the nearest 15 minutes using some arithmetic (a timestamp is just a number of seconds). Then do a group-by on the result.
In reply to Hubert Chathi
Re: SQL statement to find out number of concurrent users for a given time period
by Frank Ralf -
You should have a look at Moodle's core functions for retrieving information from the database, perhaps you find something useful: Development:Developer_FAQ#How_do_I_insert.2Fretrieve_records_in_the_database.2C_without_creating_my_own_database_connections.3F
hth
Frank
hth
Frank
In reply to Hubert Chathi
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
Could you give me an example using the online courses block query as a base/ starting point?
thanks
thanks
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by sam marshall -
This probably works:
SELECT time/(15*60), COUNT(DISTINCT userid) FROM mdl_log
GROUP BY time/(15*60)
Add a condition on course= if you want to restrict to a particular course. Add time conditions if you want to restrict to a particular day or whatever. Once you have the results, multiply the 'time' value by 15*60 to get back to a 'normal' time display. (You could do this via nested query in the database if it's easier, otherwise just do it in PHP.)
Note that on a large system this query will take approximately forever, especially if you don't add any of those conditions mentioned. You should generally try to avoid doing this type of query (...and most queries that touch the log table) in response to a user request, i.e. make sure it's only run occasionally as a report or manually by admin only or whatever.
--sam
SELECT time/(15*60), COUNT(DISTINCT userid) FROM mdl_log
GROUP BY time/(15*60)
Add a condition on course= if you want to restrict to a particular course. Add time conditions if you want to restrict to a particular day or whatever. Once you have the results, multiply the 'time' value by 15*60 to get back to a 'normal' time display. (You could do this via nested query in the database if it's easier, otherwise just do it in PHP.)
Note that on a large system this query will take approximately forever, especially if you don't add any of those conditions mentioned. You should generally try to avoid doing this type of query (...and most queries that touch the log table) in response to a user request, i.e. make sure it's only run occasionally as a report or manually by admin only or whatever.
--sam
In reply to sam marshall
Re: SQL statement to find out number of concurrent users for a given time period
by Deleted user -
you can restrict the query to only get data from the last 4 weeks, this will give you a rough good estimate.
However, I would properly modify the query to get the hourly statistics for each day rather then just an average over all days, the query above really doesn't give you a whole lot of information except some 'numbers' as found on many kids/php websites.
Ries
However, I would properly modify the query to get the hourly statistics for each day rather then just an average over all days, the query above really doesn't give you a whole lot of information except some 'numbers' as found on many kids/php websites.
Ries
In reply to sam marshall
Re: SQL statement to find out number of concurrent users for a given time period
by Deleted user -
you can restrict the query to only get data from the last 4 weeks, this will give you a rough good estimate.
However, I would properly modify the query to get the hourly statistics for each day rather then just an average over all days, the query above really doesn't give you a whole lot of information except some 'numbers' as found on many kids/php websites.
Ries
However, I would properly modify the query to get the hourly statistics for each day rather then just an average over all days, the query above really doesn't give you a whole lot of information except some 'numbers' as found on many kids/php websites.
Ries
In reply to sam marshall
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
actually this query ran very fast, less than 1 sec so I'm not worried about that =)
Still having problems though ..
Let's simplify it so I can get the basics.. because are PST, that could be an issue too with the time field in the mdl_log so we could get a more accurate "date / time"
Let's use this as an example:
-------
Show me / count the number of distinct users who where in the system on Tuesday, October 6, 2009 from 9:00:00 AM PST to 9:15:00 AM PST for any given course
--------
once I see this, I can loop through the table and adjust accordingly.
The main goal is to find the peak periods of activity a bit more precisely and create a text file of this data into a graphical application.
day of week| date | time | users
thanks
Still having problems though ..
Let's simplify it so I can get the basics.. because are PST, that could be an issue too with the time field in the mdl_log so we could get a more accurate "date / time"
Let's use this as an example:
-------
Show me / count the number of distinct users who where in the system on Tuesday, October 6, 2009 from 9:00:00 AM PST to 9:15:00 AM PST for any given course
--------
once I see this, I can loop through the table and adjust accordingly.
The main goal is to find the peak periods of activity a bit more precisely and create a text file of this data into a graphical application.
day of week| date | time | users
thanks
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Hubert Chathi -
Times in Moodle are stored as UNIX timestamps. There are various PHP functions for converting to and from timestamps (e.g. strftime, strptime, mktime, and getdate).
In reply to Hubert Chathi
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
Thank you and I do realize that it's stored as bigint as unix timestamp
I just need someone to show me how to write the one query above and I can do the rest after I study it.
I just need someone to show me how to write the one query above and I can do the rest after I study it.
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Barry Oosthuizen -
Maybe this is what you're looking for:
SELECT (
FROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period) AS concurrent_users_report
If you want to test the query in MySQL without writing PHP you can do it like this:
SELECT (FROM_UNIXTIME(
FROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period) AS concurrent_users_report
SELECT (
period * ( 15 *60 )
) AS time, online_usersFROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period
If you want to test the query in MySQL without writing PHP you can do it like this:
SELECT (FROM_UNIXTIME(
period * ( 15 *60 ))
) AS time, online_usersFROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period
In reply to Barry Oosthuizen
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
Barry,
How would you modify it to put it into a delimited file? Something like this:
SELECT (
FROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period) AS concurrent_users_report
INTO OUTFILE '/mydirectory/report.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
How would you modify it to put it into a delimited file? Something like this:
SELECT (
period * ( 15 *60 )
) AS time, online_usersFROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period
INTO OUTFILE '/mydirectory/report.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Barry Oosthuizen -
Check if you have permission to access your mysql server directly (the file will be generated directly on your server)
If that's not the issue, try this one (not tested):
SELECT (period * ( 15 *60 )) AS time, online_users
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM (SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log GROUP BY period) AS concurrent_users_report
Actually, I would just use PHPmyadmin (which has lot's of download formats), Tim's new admin report (with CSV download) or use the new flexitable API (with CSV, XLS, ODS downloads) where you can stick any sql query in and get a sortable downloadable table.
If that's not the issue, try this one (not tested):
SELECT (period * ( 15 *60 )) AS time, online_users
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM (SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log GROUP BY period) AS concurrent_users_report
Actually, I would just use PHPmyadmin (which has lot's of download formats), Tim's new admin report (with CSV download) or use the new flexitable API (with CSV, XLS, ODS downloads) where you can stick any sql query in and get a sortable downloadable table.
In reply to Barry Oosthuizen
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
Barry,
How could you format the time to have day of week, date, time on their own separate columns? I almost got it but not day of week ..
something like
Tuesday | November 10, 2009 | 11:40 | 10
Thanks again
How could you format the time to have day of week, date, time on their own separate columns? I almost got it but not day of week ..
something like
Tuesday | November 10, 2009 | 11:40 | 10
Thanks again
In reply to Flex Liong
Re: SQL statement to find out number of concurrent users for a given time period
by Barry Oosthuizen -
SELECT (
DAY , DATE_FORMAT( FROM_UNIXTIME( period * ( 15 *60 ) ) , '%M %d, %Y' ) AS Date, TIME( FROM_UNIXTIME( period * ( 15 *60 ) ) ) AS Time, online_users
FROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period) AS help_me_get_this_right
FROM_UNIXTIME( period * ( 15 *60 ) )
) AS Date, DAYNAME( FROM_UNIXTIME( period * ( 15 *60 ) ) ) AS DAY , DATE_FORMAT( FROM_UNIXTIME( period * ( 15 *60 ) ) , '%M %d, %Y' ) AS Date, TIME( FROM_UNIXTIME( period * ( 15 *60 ) ) ) AS Time, online_users
FROM (
SELECT ROUND( time / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_log
GROUP BY period
In reply to Barry Oosthuizen
Re: SQL statement to find out number of concurrent users for a given time period
by Flex Liong -
you da man .. thank you very much
In reply to Barry Oosthuizen
Re: SQL statement to find out number of concurrent users for a given time period
by Derick Turner -
For the versions that have moved away from the old mdl_log table and use the mdl_logstore_standard_log table instead you can use this SQL -
SELECT (period * ( 15 *60 )) AS time, online_users
FROM (SELECT ROUND( timecreated / ( 15 *60 ) ) AS period, COUNT( DISTINCT userid ) AS online_users
FROM mdl_logstore_standard_log GROUP BY period) AS concurrent_users_report