XF 2.2 List of all users with at least one warning point

[daimpa]

Hello, where can I find the list of all users of the forum with at least one warning point?
I want to reset them all to 0 warnig points, but I don't understand how to do it.

 

[Ozzy47]

In the ACP, https://YOURSITE.com/admin.php?warnings/

 

[daimpa]

In the ACP, https://YOURSITE.com/admin.php?warnings/

This list all the warnings that can be assigned, not all the users with at least one warning point. What I'm looking for, is the list of users that have, at this moment, at least one warning point applied, so I can put them all to 0 warning points.

 

[Ozzy47]

Wooops.

That data is not exposed to the UI so you would have to query the database directly.

 

[daimpa]

Wooops.

That data is not exposed to the UI so you would have to query the database directly.

Thanks for your reply. Do anyone of the staff could help me with this? I'm not sure which could be the query.
Also, I think it would be really important to add such feature. So strange it's missing.

 

[djbaxter]

This sets all expired warning points to zero.

XF 2.1 - SQL help to delete warning points prior to a specified date

On a vBulletin to XF conversion (where some of the vB tables may have been corrupted), a residual issue is that there are old unexpired vBulletin warning points left over. As a result, if some members get a 1-point warning via XF, it puts them over the threshold and temporarily bans them. I...

xenforo.com

This will reset all user's warning points to be derived from the XF warning system, counting non-expired warnings. Note: if there are warnings past their expiry time but not explicitly expired they will be included so XF's warning expiry system works properly.

update xf_user
set warning_points = COALESCE(
   (select sum(xf_warning.points) from xf_warning where xf_warning.user_id = xf_user.user_id and is_expired = 0)
, 0);

Added: Just noticed a possible type in the above SQL. Should is_expired = 0 actually be is_expired = 1? Check with @Xon

 

[Kruzya]

SELECT user_id, username
FROM xf_user
WHERE warning_points > 0

This query list you all members with any warning points. Queries in post above help you reset him to zero.