# Dashboard Report - System
**URL:** https://meta.discourse.org/t/dashboard-report-system/293546
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [January 30, 2024, 10:39pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546 "2024-01-30T22:39:29Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [January 30, 2024, 10:39pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546/1 "2024-01-30T22:39:29Z")
This is an SQL version of the Dashboard Report for System.
This dashboard report provides a daily count of the number of personal messages sent automatically by the system.
```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
SELECT
DATE(created_at) AS day,
COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
AND subtype = 'system_message'
AND created_at BETWEEN :start_date AND :end_date
AND deleted_at IS NULL
AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day
```
### SQL Query Explanation
The query works by extracting data from the `topics` table – specifically, those that qualify as private messages to users with a subtype of `system_message` within a given time frame. Let’s break it down:
- **Date Parameters** :
- The query accepts two parameters, `:start_date` and `:end_date`, which define the date range for the report. Both date parameters accept the date format of `YYYY-MM-DD`.
- **SELECT** : The query selects two fields:
- `DATE(created_at) AS day`: This extracts the date part of the `created_at` timestamp, effectively grouping records by the day they were created.
- `COUNT(*) AS notifications_count`: This counts the total number of system-generated PMs for each day.
- **FROM** : Specifies the `topics` table as the data source, which contains records of all topics, including private messages.
- **WHERE** : Contains multiple filters to narrow down the dataset:
- `archetype = 'private_message'`: Only includes entries that are private messages.
- `subtype = 'system_message'`: Further narrows down the selection to only system-generated messages.
- `created_at BETWEEN :start_date AND :end_date`: Filters the PMs to those created within the range specified by the parameters.
- `deleted_at IS NULL`: Excludes messages that have been deleted.
- `user_id > 0`: Ensures that messages are associated with real user accounts rather than the system or anonymous accounts.
- **GROUP BY** : Groups results based on the day they were created.
- **ORDER BY** : Orders the final result set by the day in ascending order, ensuring a chronological sequence of daily counts.
### Example Results
| day | notifications\_count |
| --- | --- |
| 2024-01-01 | 5 |
| 2024-01-02 | 7 |
| 2024-01-03 | 11 |
| 2024-01-04 | 14 |
| 2024-01-05 | 8 |
---
### Author:  [@Jumanji](https://meta.discourse.org/u/Jumanji)
#### Post date: [March 12, 2024, 11:31pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546/2 "2024-03-12T23:31:31Z")
Can this be tied to a specific PM, like the welcome PM?
I’d like to know how many of those go out daily.
Thoughts?
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [March 14, 2024, 5:56pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546/3 "2024-03-14T17:56:32Z")
> [@Jumanji](#):
>
> Can this be tied to a specific PM, like the welcome PM?
Yes, the best way to add this in would be to add a section to the `WHERE` statement in the query, filtering by topic `title`.
For example:
```sql
WHERE archetype = 'private_message'
AND subtype = 'system_message'
AND created_at BETWEEN :start_date AND :end_date
AND title = 'Greetings!'
```
Would find all of the `Greetings!` messages.
Note that some of the system messages do not include a real user, so removing the line `AND user_id > 0` may be necessary with this type of query.
You may also want to remove `AND deleted_at IS NULL` to still count welcome messages that users might delete.
You could use regex to match topics with a similar title.
To match a topic by title using a regular expression (regex) in PostgreSQL, you can use the `~` operator, which matches a regular expression against a string. The query structure would look like this:
```sql
SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'
```
Replace `'YourRegexPatternHere'` with the actual regex pattern you want to match against the `title` field.
For example, if you’re looking for topics with titles that contain the word “Welcome” (case-insensitive), you could use:
```sql
SELECT *
FROM topics
WHERE title ~* 'Welcome'
```
The `~*` operator is used for case-insensitive matching.
---
### Author:  [@Jumanji](https://meta.discourse.org/u/Jumanji)
#### Post date: [March 20, 2024, 12:56am UTC](https://meta.discourse.org/t/dashboard-report-system/293546/4 "2024-03-20T00:56:00Z")
Oh this is awesome. Thanks so much! I’ll give this a try!