Relatorios personalizados desenvolvidos pela comunidade
Introdução
Os modelos de relatórios disponibilizados aqui foram desenvolvidos pela comunidade. Todos são livres para contribuir. Se você tiver dúvidas de trabalho que acha que seriam úteis para os membros da comunidade, compartilhe-as aqui.
Plugins de relatório
Há dois plugins de relatório comumente utilizados no Moodle para criar e exibir relatórios SQL:
Você pode instalar e usar qualquer um desses plugins de relatório em seu site Moodle. Se seu foro é mais no envio de relatórios por e-mail, então verifique primeiro o” Ad-hoc database queries”. Se você deseja compartilhar relatórios com professores e estudantes em cursos, então veja o plugin “Configurable Reports”.
Algumas notas técnicas para usuários de relatórios
Estrutura de tabelas e schema
Se você está procurando alguma ajuda para entender as tabelas e estrutura do banco de dados, você pode encontrar nesta página um esquema de banco de dados atualizado, graças ao Marcus Green.
Nomes das tabelas e prefix_
Você verá que muitas das consultas abaixo usam "prefix_" para iniciar nomes de tabelas, por exemplo “prefix_user”. Este “prefix_” é usado antes dos nomes das tabelas quando você está usando um dos dois plugins, relatórios “Ad-hoc” ou “Configurable Reports”. Esses dois plugins substituem “prefix_” pelo prefixo real definido em seu arquivo de configuração do Moodle “config.php”. Isso ocorre porque, embora o prefixo padrão da tabela seja “mdl_”, ao instalar o Moodle, você pode definir qualquer prefixo que desejar, portanto, é personalizável.
As consultas escritas para serem usadas com qualquer um desses dois plugins devem sempre ter “prefix_” para iniciar todos os nomes de tabelas em vez do prefixo real.
Quando você usa ferramentas como PhpMyAdmin, Workbench, etc., você deve usar os nomes das tabelas reais como estão no banco de dados, incluindo o prefixo real definido para seu próprio site.
Portanto:
- Se uma consulta abaixo tiver "prefix_" em seus nomes de tabela e você quiser usar essa consulta em uma ferramenta como phpMyAdmin, será necessário substituí-los pelo prefixo real das tabelas do seu site;
- Se uma consulta abaixo tiver "mdl_" ou algum outro prefixo em seus nomes de tabela e você estiver usando isso em qualquer um dos dois plugins do Moodle, será necessário substituir esses prefixos explícitos por "prefix_".
Você receberá um erro "Sem prefixo explícito" em qualquer um desses plugins se usar código com o prefixo da tabela real nele.
Sobre %%FILTER_ e variáveis similares
Você verá várias consultas com variáveis que começam e terminam com dois “%%” sinais de porcentagem, por exemplo “%%WWWROOT%%” e vários deles que começam com “%%FILTER_”. Essas variáveis são específicas para um ou ambos os plugins do Moodle. Eles não são código SQL padrão e não funcionarão em código feito em outras ferramentas fora desses dois plugins.
Além disso, embora esses dois plugins compartilhem parte da variável, alguns funcionam apenas em um ou outro plugin. Por exemplo, “%%WWWROOT%%” funciona em ambos; as variáveis “%%C%%”, “%%S%%” e “%%Q%%” só funcionam no plugin de consultas de banco de dados Ad-hoc; e aqueles que iniciam “%%FILTER_” funcionam apenas no plugin Relatórios Configuráveis (Configurable Reports). Você receberá um erro de sintaxe se usar uma variável que o plugin não suporta.
Encontre mais detalhes na seção sobre Variáveis no final da página.
Sobre formatação de datas
As datas e horas no Moodle são armazenadas internamente no banco de dados como o timestamp Unix Epoch, que é o número de segundos desde 1º de janeiro de 1970 às 00:00 UTC, que no banco de dados será representado como 0.
Para convertê-los em formatos "legíveis por humanos" que usamos todos os dias, você precisará fazer isso em sua consulta com código. Existem vários exemplos nos relatórios nesta página: uma pesquisa nesta página por "FROM_UNIXTIME" mostrará como usar e converter as datas. Observe que quando os horários são convertidos em data padrão e os horários dos carimbos de data/hora estão no formato UTC e você pode querer convertê-los em um fuso horário local.
As funções “FROM_UNIXTIME” e “DATE_FORMAT” que são comuns nos relatórios abaixo são funções específicas do MySQL, que você pode usar em sites MySQL e MariaDB.
Para outra versão do SQL, você precisará editar essas consultas para que funcionem corretamente. Para PostgreSQL, consulte a página de manual em funções de formatação de tipo de dados. Há vários exemplos específicos do PostgreSQL entre as consultas abaixo.
Existem muitos formatos de data e hora usados em vários países, mas todos podem ser formatados com base no valor do registro de data e hora original. Para obter uma amostra em MySQL, você pode usar a seguinte consulta para mostrar a data de instalação do seu site Moodle em vários formatos:
SELECT
l.timecreated AS "Timestamp",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%d/%m/%Y') AS "Formato padrão no mundo",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m/%d/%Y') AS "Formato EUA",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%Y-%m-%d') AS "Formato padrão ISO",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%Y-%m-%d %H:%i') AS "Formato padrão ISO com tempo em 24 horas"
FROM
prefix_logstore_standard_log l
WHERE
l.id = 1
Veja a documentação do MySQL para todas as opções possíveis de formatação de data.
Nota: para timestamps que são 0, isso às vezes representa "Nunca" ou "Sem data" para alguns campos no Moodle. Por exemplo, se o primeiro login de um usuário for 0, isso significa que ele nunca fez login, não que seu primeiro login tenha sido em 1º de janeiro de 1970 (já que o Moodle ainda não existia). Se você deseja distinguir essas datas em sua saída, pode usar uma condicional para testar o 0, por exemplo:
IF(
u.firstaccess = 0,
"Nunca",
DATE_FORMAT(FROM_UNIXTIME(u.firstaccess), '%d-%m-%Y %H:%i')
) AS "Primeiro acesso",
Isso mostrará "Nunca" quando o "timestamp" for igual a "0" ou a data formatada corretamente, permitindo que você classifique a coluna facilmente.
Relatórios
Relatórios Administrativos
Acessos únicos ao Moodle
Este relatório considera acesso únicos como qualquer ação de “visualização” de usuários distintos (contagem de "username"). Limitado ao período de 30 dias.
Observação: O Moodle registra a ação de "loggedin", mas esse parâmetro pode gerar inconsistências no relatório de acessos únicos por dia, uma vez que dependendo da configuração do Moodle o usuário pode permanecer autenticado por mais de um dia (no caso do Moodle App o padrão é 12 semanas) e por isso foi considerado a ação de visualização e não "login".
PostgreSQL
SELECT TO_CHAR(TO_TIMESTAMP(L.TIMECREATED),'DD-MM-YYYY') AS "Data",
COUNT (DISTINCT U.USERNAME) AS "Acessos"
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
LEFT JOIN MDL_USER AS U ON L.USERID = U.ID
WHERE L.ACTION = 'viewed'
AND TO_TIMESTAMP(L.TIMECREATED) >= NOW() - interval '30 days'
GROUP BY "Data"
Acessos únicos ao Moodle com dados de campo de perfil do usuário
Este relatório considera acesso únicos como qualquer ação de “visualização” de usuários distintos (contagem de "username"). Limitado ao período de 30 dias.
No exemplo abaixo foi adicionado uma "subquery" para adicionar um campo de perfil personalizado de nome "Pólo". Você pode alterar para qualquer valor de "shortname" (nome curto do campo).
PostgreSQL
SELECT TO_CHAR(TO_TIMESTAMP(L.TIMECREATED), 'DD-MM-YYYY') AS "Data",
(SELECT D1.DATA
FROM PREFIX_USER_INFO_DATA D1
JOIN PREFIX_USER_INFO_FIELD F1 ON D1.FIELDID = F1.ID
AND F1.SHORTNAME = 'polo'
WHERE D1.USERID = U.ID ) AS "Pólo",
COUNT (DISTINCT U.USERNAME) AS "Acessos"
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
LEFT JOIN PREFIX_USER AS U ON L.USERID = U.ID
WHERE L.ACTION = 'viewed'
AND TO_TIMESTAMP(L.TIMECREATED) >= NOW() - interval '30 days'
GROUP BY "Data", "Pólo"
Dica: A pesquisa por texto é sempre mais lenta que a pesquisa por "chave primária", logo, recomendo trocar o "shortname" pelo "id" da tabela "mdl_user_info_field". No exemplo acima, a variável F1.SHORTNAME = 'polo' uma vez otimizada seria 'F1.ID = '1'. O número "1" corresponde a chave primária do campo de perfil "Pólo", para saber qual chave utilizar você deve checar a tabela "mdl_user_info_field".
Erros de acesso, motivo: usuário inexistente
O Moodle registra em seu log as tentativas de acesso malsucedidas e este relatório lista, dentro do intervalo de 30 dias, os acessos não concluídos devido a inexistência de cadastro ou erro de preenchimento dos dados de login.
PostgreSQL
SELECT JSON_EXTRACT_PATH_TEXT(OTHER :: JSON,'username') AS USERNAME,
TO_CHAR(TO_TIMESTAMP(L.TIMECREATED),'DD/MM/YYYY HH:MM') AS DATA
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
WHERE L.ACTION = 'failed'
AND L.TARGET = 'user_login'
AND L.OTHER like '%reason":1%'
AND TO_TIMESTAMP(L.TIMECREATED) >= NOW() - interval '30 days'
ORDER BY TIMECREATED DESC
Erros de acesso, motivo: senha inválida
Este relatório lista os usuários válidos, que nunca acessaram a plataforma devido a senha inválida. No início de semestre ou cadastro recém realizado, para ações proativas, o suporte poderá agir entrando em contato com o usuário.
PostgreSQL
SELECT DISTINCT U.USERNAME AS "Usuário", CONCAT (U.FIRSTNAME,' ',U.LASTNAME) AS "Nome completo",
CONCAT('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',L.USERID,'">Acessar Perfil</a>') AS "Perfil",
L.TARGET,
L.ACTION
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
JOIN PREFIX_USER AS U ON U.ID = L.USERID
WHERE L.ACTION LIKE 'failed'
AND L.TARGET LIKE 'user_login'
AND TO_TIMESTAMP(L.TIMECREATED) >= NOW() - interval '7 days'
AND U.LASTACCESS = 0
Usuários criados: Contagem por autor do cadastro
PostgreSQL
SELECT CONCAT(U2.FIRSTNAME, ' ', U2.LASTNAME) AS "Autor dos cadastros",
COUNT(DISTINCT U.USERNAME) AS "Usuários criados"
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
JOIN PREFIX_USER AS U ON U.ID = L.RELATEDUSERID
JOIN PREFIX_USER AS U2 ON U2.ID = L.USERID
WHERE L.ACTION = 'created'
AND L.TARGET = 'user'
GROUP BY "Autor dos cadastros"
ORDER BY "Usuários criados" DESC
Usuários criados: Últimos 30 dias
PostgreSQL
SELECT U.USERNAME,CONCAT(U.FIRSTNAME,' ',U.LASTNAME) AS "Usuário criado",
CONCAT(U2.FIRSTNAME,' ',U2.LASTNAME) AS "Autor do cadastro",
L.ORIGIN,
TO_CHAR(TO_TIMESTAMP(L.TIMECREATED),'DD/MM/YYYY HH:MM') AS "Data de criação"
FROM PREFIX_LOGSTORE_STANDARD_LOG AS L
JOIN PREFIX_USER AS U ON U.ID = L.RELATEDUSERID
JOIN PREFIX_USER AS U2 ON U2.ID = L.USERID
WHERE L.ACTION = 'created'
AND L.TARGET = 'user'
AND TO_TIMESTAMP(L.TIMECREATED) >= NOW() - interval '30 days'
ORDER BY L.TIMECREATED DESC
