Relatório de Acesso no Moodle por Cidades e País com Comando SQL

Esse poste tem por objetivo criar um relatório que mapeie o acesso ao Moodle por cidade ou país. Faz um rastreamento de acesso por endereço dos usuários. Pois, identifica de qual cidade ou país os usuários acessam o Moodle com maior frequência.

Esse relatório será montado em forma de comando SQL. As informações serão extraídas das seguintes tabelas:

• mdl_user - Tabela de usuário. Dessa tabela será extraída o endereço  (cidade e  país);

• mdl_log – Tabela de log. Dessa tabela será extraída a quantidade de acesso por endereço do usuário (cidade e  país);

Comando SQL que extrai relatório por cidade:
SELECT u.city, COUNT(DISTINCT u.id) AS quant_user, COUNT(l.id) AS quant_acesso,COUNT(l.id)/COUNT(DISTINCT u.id) AS media_acesso  FROM mdl_user u INNER JOIN mdl_log l ON u.id=l.userid GROUP BY u.city  ORDER BY COUNT(l.id)/COUNT(DISTINCT u.id) DESC
   
A consulta retorna quatro colunas:
u.city – Nome da cidade dos usuários cadastrado na tabela mdl_user
quant_user – Quantidade de usuários que moram na cidade
quant_acesso – Quantidade de acesso ao Moodle feito por todos os moradores da cidade
media_acesso  - Média de acesso por cidade. É computado pela quantidade de acesso divididos por número de moradores. É o acesso proporcional por morador, ou seja, por usuário. 

O resultado é organizado por média de acesso. Ou seja cidade que ficar no topo da lista é a cidade cujo usuários são mais ativos, ou seja, acessam o Moodle com maior frequência.
   
Para extrair o mesmo relatório por país, basta substituir a  coluna u.city para  u.country. O restante do comando fica o mesmo.

Comando SQL que extrai relatório por país:
SELECT u.country, COUNT(DISTINCT u.id) AS quant_user, COUNT(l.id) AS quant_acesso,COUNT(l.id)/COUNT(DISTINCT u.id) AS media_acesso  FROM mdl_user u INNER JOIN mdl_log l ON u.id=l.userid GROUP BY u.country ORDER BY COUNT(l.id)/COUNT(DISTINCT u.id) DESC
   
Nesse momento pode ainda não estar satisfeito e questionar:
-Por que não mapear a cidade e o país por IP de acesso?
    Essa pergunta é pertinente. Mais a ideia desse relatório é mapear quais cidades cujos usuários são mais ativos  no que tange ao acesso e não de qual local o acesso é feito. Por outro lado, rastrear local de acesso pelo IP não garante a confiabilidade dos dados uma vez que o IP pode ser forjado através de proxy anônimo. Bem esse papo é assunto para um outro poste.

    Execute o comando SQL diretamente na base de dados ou numa linguagem de programação e conheca o perfil de acesso do seu aluno por região, ou seja, cidade ou país. Se você fizer isso, certamente a equipe pedagógica vai gostar muito.