# Dashboard Report - Suspicious Logins

**URL:** <https://meta.discourse.org/t/dashboard-report-suspicious-logins/294773>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [February 9, 2024, 12:15am UTC](https://meta.discourse.org/t/dashboard-report-suspicious-logins/294773 "2024-02-09T00:15:29Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [February 9, 2024, 12:15am UTC](https://meta.discourse.org/t/dashboard-report-suspicious-logins/294773/1 "2024-02-09T00:15:29Z")

</div>

This is an SQL version of the Dashboard Report for Suspicious Logins.

This dashboard report provides details of new logins from users that differ suspiciously from previous logins, over a specified date range. By identifying unusual login attempts, admins can take proactive measures to secure user accounts and the forum against potential security threats.

```sql
-- [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

```

### SQL Query Explanation

The SQL query operates by retrieving a list of login attempts that have been flagged as suspicious within a specified date range. Here’s a breakdown of how the query functions:

- **Parameter Definition** : The query starts by defining two parameters, `:start_date` and `:end_date`, to specify the time period for the report. Both date parameters accept the format of `YYYY-MM-DD`.
- **Data Selection** : The query selects specific fields from the `user_auth_token_logs` table, which logs details about user authentication activities, and the `users` table, which contains information about the users themselves. The fields selected include:
  - `u.id` (user\_id): The unique identifier of the user.
  - `u.username`: The username of the user.
  - `t.client_ip`: The IP address from which the login attempt was made.
  - `t.user_agent`: The user agent string of the browser or device used for the login attempt.
  - `t.created_at` (login\_time): The timestamp when the login attempt occurred.

- **Join Operation** : The query joins the `user_auth_token_logs` table (`t`) with the `users` table (`u`) on their common `user_id` field to correlate each login attempt with the corresponding user information.
- **Filtering** : It filters the records to include only those login attempts that are marked as `suspicious` in the `action` field of the `user_auth_token_logs` table. Additionally, it filters the records to include only those that occurred within the specified date range (`:start_date` to `:end_date`).
- **Ordering** : Finally, the query orders the results in descending order by the `created_at` timestamp, ensuring that the most recent suspicious login attempts are listed first.

### Example Results

| 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 |
| … | … | … | … | … |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-suspicious-logins/294773)._