Rapporto Dashboard - Accessi Sospetti

[SaraDev (Sara Devlaeminck)]

Questa è una versione SQL del report della dashboard per accessi sospetti.

Questo report della dashboard fornisce dettagli sui nuovi accessi da parte di utenti che differiscono sospettosamente dagli accessi precedenti, in un intervallo di date specificato. Identificando tentativi di accesso insoliti, gli amministratori possono adottare misure proattive per proteggere gli account utente e il forum da potenziali minacce alla sicurezza.

-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

SELECT 
    u.id user_id, 
    u.username, 
    t.client_ip, 
    t.user_agent, 
    t.created_at login_time
FROM user_auth_token_logs t
JOIN users u ON u.id = t.user_id
WHERE t.action = 'suspicious'
    AND t.created_at >= :start_date
    AND t.created_at <= :end_date
ORDER BY t.created_at DESC

Spiegazione della query SQL

La query SQL opera recuperando un elenco di tentativi di accesso contrassegnati come sospetti entro un intervallo di date specificato. Ecco una ripartizione di come funziona la query:

• Definizione dei parametri: la query inizia definendo due parametri, :start_date e :end_date, per specificare il periodo di tempo per il report. Entrambi i parametri di data accettano il formato AAAA-MM-GG.
• Selezione dei dati: la query seleziona campi specifici dalla tabella user_auth_token_logs, che registra i dettagli sulle attività di autenticazione dell’utente, e dalla tabella users, che contiene informazioni sugli utenti stessi. I campi selezionati includono:
  • u.id (user_id): l’identificatore univoco dell’utente.
  • u.username: il nome utente dell’utente.
  • t.client_ip: l’indirizzo IP da cui è stato effettuato il tentativo di accesso.
  • t.user_agent: la stringa user agent del browser o del dispositivo utilizzato per il tentativo di accesso.
  • t.created_at (login_time): il timestamp in cui si è verificato il tentativo di accesso.
• Operazione di join: la query unisce la tabella user_auth_token_logs (t) con la tabella users (u) sul loro campo comune user_id per correlare ogni tentativo di accesso con le informazioni utente corrispondenti.
• Filtraggio: filtra i record per includere solo i tentativi di accesso contrassegnati come suspicious nel campo action della tabella user_auth_token_logs. Inoltre, filtra i record per includere solo quelli avvenuti nell’intervallo di date specificato (:start_date a :end_date).
• Ordinamento: infine, la query ordina i risultati in ordine decrescente in base al timestamp created_at, assicurando che i tentativi di accesso sospetti più recenti siano elencati per primi.

Esempio di risultati

user	username	client_ip	user_agent	login_time
user_id_example_1	username_example	2001:0db8:85a3:0000:0000:8a2e:0370:7334	Mozilla/5.0 (iPhone; CPU iPhone OS 17_2_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.2 Mobile/15E148 Safari/604.1	2024-01-09T08:18:57.535Z
…	…	…	…	…