É sempre um problema saber o que está a acontecer na plataforma, principalmente quando há muitos acessos. A informação dos relatórios do Moodle pouco ajuda, dado que não condensa a informação de forma a poder ser visivel. Fiz umas queries SQL à base de dados, e disponibilizo-as aqui, todas com um output condensado (30 linhas ou menos). Atenção que algumas das queries são pesadas (as mais pesadas, na configuração actual da UAb levam 30 segundos).
Contagens do site de:
- Páginas Vistas
- Mensagens em fóruns
- Caracteres em fóruns
- Utilizadores IP
- Utilizadores Registados
- Módulo / Acção
Periodo:
- último dia (cada hora)
- últimos 7 dias (cada dia)
- últimos 31 dias (cada dia)
- últimos 360 dias (cada mês de 30 dias)
Contagens por Utilizador de:
- Páginas Vistas
- Mensagens em fóruns
- Caracteres em fóruns
Periodo:
- nos últimos 7 dias (histograma 1-9; 10-99; 100-999; ...)
- nos últimos 30 dias (histograma 1-9; 10-99; 100-999; ...)
### PaginasVistasDia
SELECT ANO,MES,DIA,HORA, Count( * ) AS "Paginas Vistas"
FROM (
SELECT hour( FROM_UNIXTIME( time ) ) AS HORA, day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As Ano
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
) AS HORAS
GROUP BY ANO,MES,DIA,HORA
ORDER BY ANO,MES,DIA,HORA
### PaginasVistasSemana
SELECT ANO,MES,DIA, Count( * ) AS "Paginas Vistas"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As Ano
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### PaginasVistasMes
SELECT ANO,MES,DIA, Count( * ) AS "Paginas Vistas"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As Ano
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### PaginasVistasAno
SELECT ANO,MES, Count( * ) AS "Paginas Vistas"
FROM (
SELECT month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As Ano
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
) AS HORAS
GROUP BY ANO,MES
ORDER BY ANO,MES
### MensagensForumDia
SELECT ANO,MES,DIA,HORA, Count( * ) AS "Mensagens em Foruns"
FROM (
SELECT hour( FROM_UNIXTIME( modified ) ) AS HORA, day(FROM_UNIXTIME( modified )) As DIA,
month(FROM_UNIXTIME( modified )) AS MES, year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
) AS HORAS
GROUP BY ANO,MES,DIA,HORA
ORDER BY ANO,MES,DIA,HORA
### MensagensForumSemana
SELECT ANO,MES,DIA, Count( * ) AS "Mensagens em Foruns"
FROM (
SELECT day(FROM_UNIXTIME( modified )) As DIA,
month(FROM_UNIXTIME( modified )) AS MES, year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### MensagensForumMes
SELECT ANO,MES,DIA, Count( * ) AS "Mensagens em Foruns"
FROM (
SELECT day(FROM_UNIXTIME( modified )) As DIA,
month(FROM_UNIXTIME( modified )) AS MES, year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### MensagensForumAno
SELECT ANO,MES, Count( * ) AS "Mensagens em Foruns"
FROM (
SELECT
month(FROM_UNIXTIME( modified )) AS MES, year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
) AS HORAS
GROUP BY ANO,MES
ORDER BY ANO,MES
### CaracteresForumDia
SELECT ANO,MES,DIA,HORA, SUM( Caracteres ) AS "Caracteres em Foruns"
FROM (
SELECT char_length( message ) As Caracteres, hour( FROM_UNIXTIME( modified ) ) AS HORA,
day(FROM_UNIXTIME( modified )) As DIA, month(FROM_UNIXTIME( modified )) AS MES,
year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
) AS HORAS
GROUP BY ANO,MES,DIA,HORA
ORDER BY ANO,MES,DIA,HORA
### CaracteresForumSemana
SELECT ANO,MES,DIA, SUM( Caracteres ) AS "Caracteres em Foruns"
FROM (
SELECT char_length( message ) As Caracteres,
day(FROM_UNIXTIME( modified )) As DIA, month(FROM_UNIXTIME( modified )) AS MES,
year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### CaracteresForumMes
SELECT ANO,MES,DIA, SUM( Caracteres ) AS "Caracteres em Foruns"
FROM (
SELECT char_length( message ) As Caracteres,
day(FROM_UNIXTIME( modified )) As DIA, month(FROM_UNIXTIME( modified )) AS MES,
year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### CaracteresForumAno
SELECT ANO,MES, SUM( Caracteres ) AS "Caracteres em Foruns"
FROM (
SELECT char_length( message ) As Caracteres, month(FROM_UNIXTIME( modified )) AS MES,
year(FROM_UNIXTIME( modified )) As Ano
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
) AS HORAS
GROUP BY ANO,MES
ORDER BY ANO,MES
### UtilizadoresIPDia
SELECT ANO,MES,DIA,HORA, Count( * ) AS "Utilizadores IP"
FROM (
SELECT hour( FROM_UNIXTIME( time ) ) AS HORA, day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, IP
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
GROUP BY ANO,MES,DIA,HORA,IP
) AS HORAS
GROUP BY ANO,MES,DIA,HORA
ORDER BY ANO,MES,DIA,HORA
### UtilizadoresIPSemana
SELECT ANO,MES,DIA, Count( * ) AS "Utilizadores IP"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, IP
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY ANO,MES,DIA,IP
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### UtilizadoresIPMes
SELECT ANO,MES,DIA, Count( * ) AS "Utilizadores IP"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, IP
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
GROUP BY ANO,MES,DIA,IP
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### UtilizadoresIPAno
SELECT ANO,MES, Count( * ) AS "Utilizadores IP"
FROM (
SELECT month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, IP
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
GROUP BY ANO,MES,IP
) AS HORAS
GROUP BY ANO,MES
ORDER BY ANO,MES
### UtilizadoresRegistadosDia
SELECT ANO,MES,DIA,HORA, Count( * ) AS "Utilizadores Registados"
FROM (
SELECT hour( FROM_UNIXTIME( time ) ) AS HORA, day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, userid
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
GROUP BY ANO,MES,DIA,HORA,userid
) AS HORAS
GROUP BY ANO,MES,DIA,HORA
ORDER BY ANO,MES,DIA,HORA
### UtilizadoresRegistadosSemana
SELECT ANO,MES,DIA, Count( * ) AS "Utilizadores Registados"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, userid
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY ANO,MES,DIA,userid
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### UtilizadoresRegistadosMes
SELECT ANO,MES,DIA, Count( * ) AS "Utilizadores Registados"
FROM (
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, userid
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
GROUP BY ANO,MES,DIA,userid
) AS HORAS
GROUP BY ANO,MES,DIA
ORDER BY ANO,MES,DIA
### UtilizadoresRegistadosAno
SELECT ANO,MES, Count( * ) AS "Utilizadores Registados"
FROM (
SELECT month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, userid
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
GROUP BY ANO,MES,userid
) AS HORAS
GROUP BY ANO,MES
ORDER BY ANO,MES
### ModuleActionDia
SELECT day(FROM_UNIXTIME( time )) As DIA,
month(FROM_UNIXTIME( time )) AS MES, year(FROM_UNIXTIME( time )) As ANO, module, Count(*) As Acessos
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 1 ) )
GROUP BY ANO,MES,DIA,module
ORDER BY ANO,MES,DIA,Acessos DESC
### ModuleActionSemana
SELECT module, Count(*) As Acessos
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY module
ORDER BY Acessos DESC
### ModuleActionMes
SELECT module, Count(*) As Acessos
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 31 ) )
GROUP BY module
ORDER BY Acessos DESC
### ModuleActionAno
SELECT module, Count(*) As Acessos
FROM mdl_log
WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
GROUP BY module
ORDER BY Acessos DESC
### PaginasVistasPorUtilizadorSemana
SELECT SUBDATE( CURDATE( ) , 7 ) AS DE, CURDATE() AS A, PaginasVistas AS "Páginas Vistas", Count(*) As "Número de utilizadores"
FROM
(SELECT userid, POW( 10, TRUNCATE( LOG10( Count(*) ) , 0 ) ) AS PaginasVistas
FROM mdl_log WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY userid) AS TABELAAUXILIAR
GROUP BY PaginasVistas
ORDER BY PaginasVistas DESC
### PaginasVistasPorUtilizadorMes
SELECT SUBDATE( CURDATE( ) , 30 ) AS DE, CURDATE() AS A, PaginasVistas AS "Páginas Vistas", Count(*) As "Número de utilizadores"
FROM
(SELECT userid, POW( 10, TRUNCATE( LOG10( Count(*) ) , 0 ) ) AS PaginasVistas
FROM mdl_log WHERE time > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 30 ) )
GROUP BY userid) AS TABELAAUXILIAR
GROUP BY PaginasVistas
ORDER BY PaginasVistas DESC
### MensagensEmForunsPorUtilizadorSemana
SELECT SUBDATE( CURDATE( ) , 7 ) AS DE, CURDATE() AS A, MensagensForum AS "Mensagens em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( Count(*) ) , 0 ) ) AS MensagensForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY MensagensForum
ORDER BY MensagensForum DESC
### MensagensEmForunsPorUtilizadorMes
SELECT SUBDATE( CURDATE( ) , 30 ) AS DE, CURDATE( ) AS A, MensagensForum AS "Mensagens em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( Count(*) ) , 0 ) ) AS MensagensForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 30 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY MensagensForum
ORDER BY MensagensForum DESC
### MensagensEmForunsPorUtilizadorAno
SELECT SUBDATE( CURDATE( ) , 365 ) AS DE, CURDATE( ) AS A, MensagensForum AS "Mensagens em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( Count(*) ) , 0 ) ) AS MensagensForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY MensagensForum
ORDER BY MensagensForum DESC
### CaracteresEmForunsPorUtilizadorSemana
SELECT SUBDATE( CURDATE( ) , 7 ) AS DE, CURDATE() AS A, CaracteresForum AS "Caracteres em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( sum( char_length( message ) ) ) , 0 ) ) AS CaracteresForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 7 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY CaracteresForum
ORDER BY CaracteresForum DESC
### CaracteresEmForunsPorUtilizadorMes
SELECT SUBDATE( CURDATE( ) , 30 ) AS DE, CURDATE() AS A, CaracteresForum AS "Caracteres em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( sum( char_length( message ) ) ) , 0 ) ) AS CaracteresForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 30 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY CaracteresForum
ORDER BY CaracteresForum DESC
### CaracteresEmForunsPorUtilizadorAno
SELECT SUBDATE( CURDATE( ) , 365 ) AS DE, CURDATE() AS A, CaracteresForum AS "Caracteres em Forum", Count( * ) AS "Número de utilizadores"
FROM
(
SELECT POW( 10, TRUNCATE( LOG10( sum( char_length( message ) ) ) , 0 ) ) AS CaracteresForum
FROM mdl_forum_posts
WHERE modified > UNIX_TIMESTAMP( SUBDATE( CURDATE( ) , 365 ) )
GROUP BY userid
) AS TABELAAUXILIAR
GROUP BY CaracteresForum
ORDER BY CaracteresForum DESC