Ho creato quel rapporto la scorsa settimana per la prima volta. È stato molto veloce e facile da fare con l’esploratore di dati. Ecco le query che uso (merito a @SaraDev che le ha scritte):
Reporting DSA
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01
WITH flag_data AS (
SELECT
r.id AS flag_id,
p.id AS post_id,
p.topic_id,
p.raw AS flagged_item_text,
p.user_id AS post_author_id,
fu.username AS flagged_by_username,
r.created_at AS flagged_date,
r.type AS flag_type,
r.reviewable_by_moderator AS flag_source,
r.payload AS flag_reason,
r.status AS review_status,
rs.reviewed_by_id,
rs.reviewed_at,
rs.score AS review_score,
ru.username AS reviewed_by_username,
p.deleted_at AS post_deleted_at,
u.silenced_till AS user_silenced_till,
u.suspended_till AS user_suspended_till,
p.hidden_at AS post_hidden_at,
pa.post_action_type_id
FROM
reviewables r
LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
LEFT JOIN users fu ON r.created_by_id = fu.id
LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
LEFT JOIN users u ON p.user_id = u.id
LEFT JOIN post_actions pa ON pa.post_id = p.id
WHERE
r.created_at BETWEEN :start_date AND :end_date
AND r.status = 1 -- Includi solo i flag per cui è stata presa una decisione e si è concordato
),
flag_types AS (
SELECT
3 AS post_action_type_id, 'Off-topic' AS flag_type_name
UNION ALL
SELECT
4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
UNION ALL
SELECT
6 AS post_action_type_id, 'Notify_user' AS flag_type_name
UNION ALL
SELECT
7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
UNION ALL
SELECT
8 AS post_action_type_id, 'Spam' AS flag_type_name
UNION ALL
SELECT
10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
SELECT
CASE
WHEN ft.flag_type_name IS NOT NULL THEN ft.flag_type_name
WHEN fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) THEN 'something_else'
ELSE fd.flag_type
END AS flag_type,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
WHERE
fd.reviewed_at IS NOT NULL
AND (
ft.flag_type_name IS NOT NULL -- Includi i tipi di flag mappati
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Includi tipi di flag specifici per risultati NULL
)
GROUP BY
CASE
WHEN ft.flag_type_name IS NOT NULL THEN ft.flag_type_name
WHEN fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) THEN 'something_else'
ELSE fd.flag_type
END
)
SELECT
CASE
WHEN ft.flag_type_name IS NOT NULL THEN ft.flag_type_name
WHEN fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) THEN 'something_else'
ELSE fd.flag_type
END AS Type,
COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
COUNT(*) AS Total,
COALESCE(mta.median_time_minutes, 0) AS "Tempo mediano per agire (minuti)",
COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "Utente silenziato",
COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "Utente sospeso",
COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post eliminato",
COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post nascosto"
FROM
flag_data fd
LEFT JOIN flag_types ft
ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta
ON CASE
WHEN ft.flag_type_name IS NOT NULL THEN ft.flag_type_name
WHEN fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) THEN 'something_else'
ELSE fd.flag_type
END = mta.flag_type
WHERE
ft.flag_type_name IS NOT NULL -- Includi tipi di flag mappati
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Includi tipi di flag specifici per risultati NULL
GROUP BY
CASE
WHEN ft.flag_type_name IS NOT NULL THEN ft.flag_type_name
WHEN fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) THEN 'something_else'
ELSE fd.flag_type
END,
mta.median_time_minutes
ORDER BY
Total DESC
Azioni di Moderazione Intraprese
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :action_type
SELECT
uh.acting_user_id,
uh.action AS action_type,
CASE uh.action
WHEN 1 THEN 'delete_user'
WHEN 10 THEN 'suspend_user'
WHEN 11 THEN 'unsuspend_user'
WHEN 17 THEN 'delete_post'
WHEN 18 THEN 'delete_topic'
WHEN 25 THEN 'reviewed_post'
WHEN 30 THEN 'silence_user'
WHEN 31 THEN 'unsilence_user'
WHEN 39 THEN 'deactivate_user'
WHEN 41 THEN 'lock_trust_level'
WHEN 42 THEN 'unlock_trust_level'
WHEN 47 THEN 'notified_about_get_a_room'
WHEN 49 THEN 'post_locked'
WHEN 50 THEN 'post_unlocked'
WHEN 56 THEN 'post_approved'
WHEN 60 THEN 'removed_silence_user'
WHEN 61 THEN 'removed_suspend_user'
WHEN 62 THEN 'removed_unsilence_user'
WHEN 63 THEN 'removed_unsuspend_user'
WHEN 64 THEN 'post_rejected'
WHEN 69 THEN 'approve_user'
WHEN 95 THEN 'post_staff_note_create'
WHEN 96 THEN 'post_staff_note_destroy'
ELSE 'unknown_action'
END AS action_name,
uh.target_user_id AS user_id,
uh.subject AS "Soggetto", -- Soggetto dell'azione
uh.created_at AS "Quando", -- Timestamp dell'azione
uh.details AS "Dettagli", -- Dettagli aggiuntivi sull'azione
uh.context AS "Contesto", -- Contesto dell'azione
uh.previous_value,
uh.new_value,
u.suspended_till,
u.silenced_till,
uh.topic_id AS topic_id,
uh.post_id AS post_id,
uh.category_id AS category_id,
uh.custom_type
FROM
user_histories uh
LEFT JOIN
users u ON uh.target_user_id = u.id
WHERE
uh.created_at BETWEEN :start_date AND :end_date
AND uh.action IN (
1, -- delete_user
10, -- suspend_user
11, -- unsuspend_user
17, -- delete_post
18, -- delete_topic
25, -- reviewed_post
30, -- silence_user
31, -- unsilence_user
39, -- deactivate_user
41, -- lock_trust_level
42, -- unlock_trust_level
47, -- notified_about_get_a_room
49, -- post_locked
50, -- post_unlocked
56, -- post_approved
60, -- removed_silence_user
61, -- removed_suspend_user
62, -- removed_unsilence_user
63, -- removed_unsuspend_user
64, -- post_rejected
69, -- approve_user
95, -- post_staff_note_create
96 -- post_staff_note_destroy
)
AND (:action_type IS NULL OR uh.action = :action_type)
ORDER BY
uh.created_at DESC
E per gli avvisi emessi: https://meta.discourse.org/admin/reports/moderator_warning_private_messages?end_date=2024-12-31&mode=table&start_date=2024-01-01