Moderator activity report

Started by Tony Reid, April 15, 2006, 07:05:26 PM

Previous topic - Next topic

Tony Reid





If my moderators are off for more than a week then I want to know. So I thought of a quick way to generate info on my moderator activity.

The report needed to tell me

1) Moderators Name
2) How many modification actions they have done
3) How many days they have been inactive
4) Their Member Group

I dont use a db prefix so here is the SQL to generate the report....


SELECT
  `members`.`memberName` AS `Member Name`,
  COUNT(`members`.`memberName`) AS `Total Mod Actions`,
  TO_DAYS(now()) - TO_DAYS(from_unixtime(`members`.`lastLogin`)) AS `Days Missing`,
  `membergroups`.`groupName` AS `Member Group`
FROM
  `log_actions`
  RIGHT OUTER JOIN `members` ON (`log_actions`.`ID_MEMBER` = `members`.`ID_MEMBER`)
  INNER JOIN `membergroups` ON (`members`.`ID_GROUP` = `membergroups`.`ID_GROUP`)
WHERE
  (`members`.`ID_group` = 1|| `members`.`ID_group` = 102 || `members`.`ID_group` = 2)
GROUP BY
  `members`.`memberName`,
  `membergroups`.`groupName`
ORDER BY
  `membergroups`.`ID_GROUP`


This is the output....


MemberNameTotal Mod ActionsDays MissingMemberGroup
Tony12320Administrator
David432Global Moderator
Julie6788Global Moderator

The ID_GROUP values in the above sql are the different membergroup ID's. If you dont know what your membergroup ID's are then this query will output them for you..

SELECT ID_GROUP,groupName FROM `membergroups`

if you have a prefix use

SELECT ID_GROUP,groupName FROM `{db_prefix}membergroups`

It should give you something like...


GROUP_ID groupName
 
    1 Administrator
    2 Global Moderator
    4 Un-Ranked
    5 Jr. Member
    6 Full Member
    7 Sr. Member
    8 Gold Member
    91 Banned
    102 Site Moderator
 






If you use a db prefix then you will need to modify the {db_prefix} to be whatever you have chosen - although I havent tested it here it is.....


SELECT
  `{db_prefix}members`.`memberName` AS `Member Name`,
  COUNT(`{db_prefix}members`.`memberName`) AS `Total Mod Actions`,
  TO_DAYS(now()) - TO_DAYS(from_unixtime(`{db_prefix}members`.`lastLogin`)) AS `Days Missing`,
  `{db_prefix}membergroups`.`groupName` AS `Member Group`
FROM
  `log_actions`
  RIGHT OUTER JOIN `{db_prefix}members` ON (`{db_prefix}log_actions`.`ID_MEMBER` = `{db_prefix}members`.`ID_MEMBER`)
  INNER JOIN `membergroups` ON (`{db_prefix}members`.`ID_GROUP` = `{db_prefix}membergroups`.`ID_GROUP`)
WHERE
  (`{db_prefix}members`.`ID_group` = 1|| `{db_prefix}members`.`ID_group` = 102 || `{db_prefix}members`.`ID_group` = 2)
GROUP BY
  `{db_prefix}members`.`memberName`,
  `{db_prefix}membergroups`.`groupName`
ORDER BY
  `{db_prefix}membergroups`.`ID_GROUP`



If any mod writers are reading this I think It would be nice to see a pretty version of this in the report section of the admin area :)

*note to moderator  - Not sure if this is of use to anyone - but it might be worth putting in Tips & Tricks
Tony Reid

Tony Reid

Update this today to fix a bug :)

If anyone finds this useful then please let me know.

Tony
Tony Reid

rosoner

Ko je ukrao moj sir?

Surfy



Wow, this would be usefull.. But i have no idea, how to use it, or how i made an entry in my Admin section for it.. My skill is noobie  :'(  -  i hope someone could made a mod  :)

Vinspire^

Which file should we edit and is there any screenshot preview of this mod ?

Anakin_holland

#5
Quote from: Vinspire^ on May 05, 2006, 05:41:27 AM
Which file should we edit and is there any screenshot preview of this mod ?

This isn't a mod, but a query to be executed in PHPMyAdmin directly. Inside PHPMyAdmin click on the database to acces it, then click on the button "SQL" and copy/paste your edited query. Next, click on "Go" and watch the result.

It could become a mod one thay though. Maybe as an extra report inside Admin CP/Generate reports?

Thanks for this one! Might come in handy one day!

Note: If you want to use this query, you have to stop clearing out the Moderation Log! ;) And by default it shows me a value of '1' if no moderation-action have been done?

Vinspire^

Oh ... Query is PHPMyAdmin. Okie. I got it :P

Tony Reid

#7
Ive just made this into a mod ;)


Tony
Tony Reid

Tony Reid

I've still got a few things to do - add a link to the mod report in admin cp and also some other tidying up - but for now - Here is a screenshot of the mod.


I may bring in Karma stats also and make the Names link to profiles.

Then all I have to do is work out how to package it :)

Hopefully this will be finished in a couple of days.

Tony
Tony Reid

Surfy


sweeeet, this is a must have  :-* :-*

Tony Reid

I Now have it showing Karma and its linked to profiles.

Will upload screenshots later.

I have other ideas for improvement too :)

Tony
Tony Reid

Trekkie101

Very nice!

Can't wait to see a packaged mod :)

Aaron

Very cool! I'm looking forward to this one. :)

mindvsmind

is there a file to install it or we have to do it manually

Tony Reid

Quote from: mindvsmind on May 06, 2006, 02:55:15 PM
is there a file to install it or we have to do it manually

It will be installable via package manager :)
Tony Reid

codenaught

Dev Consultant
Former SMF Doc Coordinator

mindvsmind

Quote from: Tony on May 06, 2006, 03:47:31 PM
Quote from: mindvsmind on May 06, 2006, 02:55:15 PM
is there a file to install it or we have to do it manually

It will be installable via package manager :)

when r u releasing this mod ?

Tony Reid

Version 1.0 of this mod is finished - all thats left to do is package it up, its just that I haven't had time to learn how to do packaging today so will do that tomorrow night sometime.

The mod was originally designed as a quick way for me to find out how many days each moderator had been inactive but it has been enhanced to provide:

1) Moderators Name (linked to profile)
2) How many modification actions they have done (Drill down link to view their moderator actions)
3) How many days they have been inactive (since last logged in)
4) Karma points - both good and bad
4) Their Member Group

Overview Screenshot


Full screenshot

Tony Reid

Harzem

Why are you adding karma functionality? Most of the boards don't even use karma, and most of the rest don't bother the karma of the moderators.

Instead, having a link to "moderator specific" actions would be great. I click on a link for a mod and see what he/she has done.

I can help you in coding it also.

Tony Reid

It has a link to moderator spefic actions already which can be accessed by clicking the moderator actions link ;)

Tony Reid

Advertisement: