Who messaged the most?

(Hosein Naseri) #1

I am seeing suspicious activity in my user to user private message chart. Its been 20 times more in 3 successive days. now how can I detect who has messaged others the most on those days?

What cool data explorer queries have you come up with?
Discourse Narrative Bot Beta Feedback
(Daniela) #2

If you are an admin you can see the latest PM with a similar link http://your-site.com/private-posts.rss It’s not accurate but you can see if a particular user send more PM in a day.

Or you can run a query to have a list of users and PM in the last X days (you need to install Data explorer plugin).

Something like this SQL query to get the queue of all the PMs in the system is a good start point to write your own query.

(Felix Freiberger) #3

Make sure you have the Data Explorer plugin installed, navigate to AdminPluginsData Explorer, create a new query and paste in this SQL code:

SELECT user_id, count(*) AS message_count
FROM topics
WHERE archetype = 'private_message' AND subtype = 'user_to_user'
AND age(created_at) < interval '7 days'
GROUP BY user_id
ORDER BY message_count DESC

This will show you all users that have messaged someone in the last 7 days, including the number of messages they have created. (Only new messages count, replies don’t.)

To see the messages, simply click their username and go to MessagesSent :slight_smile:

Provide sortable columns in private messages, similar to latest topics
(Hosein Naseri) #4

I wanted to use that rss link however it only shows me the few recent ones. Is there an option to at least increase the number of message in that rss?

(Daniela) #5

Sincerly I have no idea if this is possible.

The query that @fefrei share for you is certantly a more accurate method and you can change the interval of days in 2 or 3 instead of 7 if you want

(Hosein Naseri) #6

It turns out that the culprit is @discobot . I don’t know why its messages are counted as user to user messages. Because to me a bot isn’t a user.

(Andrew Waugh) #8

Perhaps the user to user stat should ignore user ids less than zero.

(The bot offering to help Hosein just after he wrote that the bot isn’t a user really made me laugh - did it also send you a PM @hnaseri? )

(Hosein Naseri) #9

:smile: Exactly it made me laugh too. No I didn’t get any PM.

thats a great Idea if possible. Would have saved me couple of hours.