Diesen Bericht habe ich erst letzte Woche zum ersten Mal erstellt. Es war mit dem Data Explorer sehr schnell und einfach zu erledigen. Hier sind die Abfragen, die ich verwende (Dank an @SaraDev, die sie geschrieben hat):
DSA-Berichterstattung
-- [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 -- Nur Flags einschließen, denen zugestimmt und Maßnahmen ergriffen wurden
),
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 -- Abgebildete Flag-Typen einschließen
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Spezifische Flag-Typen für NULL-Ergebnisse einschließen
)
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 "Median time to act (minutes)",
COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"
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 -- Abgebildete Flag-Typen einschließen
OR fd.flag_type IN (
'ReviewableAkismetPost',
'ReviewableUser',
'ReviewableFlaggedPost',
'ReviewableChatMessage',
'ReviewablePost',
'ReviewableQueuedPost'
) -- Spezifische Flag-Typen für NULL-Ergebnisse einschließen
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
Moderationsmaßnahmen
-- [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 "Subject", -- Betreff der Aktion
uh.created_at AS "When", -- Zeitstempel der Aktion
uh.details AS "Details", -- Zusätzliche Details zur Aktion
uh.context AS "Context", -- Kontext der Aktion
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
Und für ausgestellte Warnungen: https://meta.discourse.org/admin/reports/moderator_warning_private_messages?end_date=2024-12-31&mode=table&start_date=2024-01-01