[Done 1.3.1] View Inactive Members V2.0

[Myiasis]

This is really a fix, not a mod, but I packaged it as a .mod file.

If you have ever tried to use the admin option of viewing inactive members for ## days you have probably noticed it takes a long long long time. If you have a host who is sensitive to SQL queries that bring the engine to its knees, you might try this solution. I think the option is a nice idea, just the way it was implemented isn't suitable for me -- my host will discontinue your MySQL access if they find you have queries that interfere with the speed of other users (until you get it fixed).

I played around with it for a while and this is what I came up with. This is not 100% like the original ouput, there is no timespan present by the member names, just a list of any of the members who have been inactive for however many days you chose. However, the list of members should be identical to the old system -- it checks all the same log files for activity that the original code did.

I used a temporary table to hold the data and reduce the result set down to just the inactive members. This is a little different from anything I've seen done in the YABB code, but it is a fairly standard way of dealing with large sets of data in the world at large.

I've tested this on both my boards and it appears to be working correctly -- one board has 88 members and 3300 posts, the other 780 members 7500 posts -- and it's fast on both. The more members you have and the more posts you have, the more log entries you are going to have and the harder it has to work to figure out if members have been inactive. The original-code SQL statement just gets overwhelmed with all the possible combinations from having it all in a single select statement.

View Inactive Members Mod

I don't know the details of how temporary tables are handled in MySQL so I can't say exactly how efficient this system is. In general temporary tables are created to manipulate subsets of data from larger sets and are optimized for speed -- usually being created in RAM rather than on the disk (assuming you don't have too much data). In memory or not, this method still comes out considerably more efficient than the original SQL statement.

If you have a large amount of members you could even experiment with putting an index on the temporary table for the deletes it does when it finds matches in the log tables. You'd probably have to have quiet a few suspect members to make this worth your while though -- particularly if the temp table is in memory. If you try this, or find you need it, I recommend you add the index AFTER you have filled the temp table (but before the deletes obviously).

[Myiasis]

Had been playing with my larger board on a local machine... When I try my larger board on my host it takes it a few seconds to display the list. It was timing out before on my host, so it's an improvemnt, but it's not really instant!

[Jeff Lewis]

I'll look it over sometime this weekend.  Assuming all is well, mind if we use the code?

[Myiasis]

I'll look it over sometime this weekend.  Assuming all is well, mind if we use the code?

I don't mind at all if you like it.

[iamdamnsam]

Cool, now that it actually works without halting the site, maybe this could be implemented

http://www.yabb.info/community/index.php?board=141;action=display;threadid=8051

[Myiasis]

I agree, that would be kind of cool to see. This method of viewing the inactive members I used is definitely faster than the old method, but I don't think it is something you'd want to use on a constant basis. The nature of this inactive status really doesn't lend itself to speed at the moment. It's a fairly work intensive process at the moment to search all of the log files for any recent activity.

A better solution to this and what you were asking in that other post is to design an active flag into the board. I've never seen the vBulletin code, but I would bet they have done something along these lines -- something specifically coded to make tracking of that easy. It wouldn't be a hard thing to do and I doubt the impact of doing it would be any more noticeable than all the log files it writes to anyway. Maybe a little more of an impact than the log files since updates are slower than inserts generally, but that's "technically" slower and I doubt anybody would experience a visual difference. It really just needs a "last_visit" date field in the member that gets updated anytime the user does something. That would make this function as fast as any of the other data fetching function and would allow mods like you mentioned.

Begin Edit
I was thinking about the mod suggestion in the other post and maybe I'll give it a whirl later. Some of the things I mentioned here I think can be worked with easily enough. Instead of adding a new field to the member I think recycling the 'lastlogin' field would probably do the trick -- that field doesn't really get used much anyway and it won't hurt anything for the code to update it on a login. Depending on how accurate you wanted to track the last visit, it could be made to update the last visit time only once per day, saving the time of updating it on ever click. That would only give you accuracy of a span of days but maybe that's enough. It could always be changed to write on each click giving you accuracy to the second.
End Edit

[J-Dawg]

I must say this mod does sound good.

[Webby]

I must say this mod does sound good.

No, it IS good  

Would it be possible to add something to it like :

inactive members with 0 (zero) posts ?!

[Myiasis]

I've redone this a little bit. I haven't had the time to add any of the suggestions you guys mentioned, but you'll find this version is a lot more efficient than my original post.

First though, if you want to try this out, you'll have to uninstall version 1.0 of the mod. This really isn't an enhancement to the first one but a rewrite of the way it works. This is still a very simple mod with very little to be changed. In fact, there is less code in this one than there was in the first attempt.

Version 1.0 works, but the larger your board, with more members and more log entries, the slower it gets. That's just the nature of trying to determine a last visit by rummaging around in the logs (my version 1.0 and the version that comes with YaBBSE both look at the logs). There may also be some issues with using the log files: They get trimmed on occaision if I remember right so if you have your log length in days less than then number of days of inactivity your a looking for, you might get some false hits. Lets say I visited 12 days ago and you have your logs set to trim after 7 days -- I'd no longer be in the logs and finding inactivity for me could be problematic.

Version 2.0 -

This version tracks visits by any of the registered members. It's similar to the click log, but only for members of the board. Each time somebody who is logged in takes any action at all on your board, it updates their visit time. This is probably somewhat similar to some of the "What user is doing" threads I've seen, but I haven't looked at them to see how they are going about it. I'm sure this is much more basic than the other mods -- there is no attempt here at keeping track of what the user is doing, just that they have done something on the board.

There is a field in the members table that doesn't get used much: lastlogin. Most of the members on my two boards don't have any value in this field at all. It apparently only gets updated when a member specifically logs in using the login screen. For most of my members, they have never logged in -- registration signs them in automatically and they checked the "Stay Logged In".  This field is used in the same way on the login as I'm using it so even if a user does come along and log in it won't affect anything when it updates this field.

With a field keeping track of the last visit for members, it become easy to determine how long they have been inactive. There's no need to poke through the log tables anymore. If you have installed that index package that somebody thoughtfully created for us you'll find that this field is even indexed already. Finding a list of members is as easy as selecting based on this indexed field -- it's fast regardless of how many members you have or how big your log files are. Although if you DO have a lot of members and they are inactive or don't have a value in the lastlogin in field (from never having explicitly logged in) there can be a bit of data to transfer, but that can't be helped.

If when viewing inactive members you see a bunch of "Unknown" those are the members that don't have any timestamp at all in the lastlogin field. If you want, you could update the lastlogin for every member to give it a starting point. If you want to set some starting point for all your users: Once the mod is installed visit your board (so that you create a timestamp for your account) then go to the database and do: Select lastlogin from members where membername='yourloginname'; Note the really big number it gives you. Then write an update statement to update that field in every member using that number: update members set lastlogin=bignumberhere where lastlogin is null;

I have left the original mod file available in case you want to try uninstalling it with BoardMod and no longer have it.

The new version can be found here: ViewInactiveMembersVer2.zip

[groan]

hi! great idea!

only problem i have at the moment is...er...

how do i install it?

i cant find any other .mod files anywhere in my se install.

sorry, im a mod virgin.

[Myiasis]

hi! great idea!

only problem i have at the moment is...er...

how do i install it?

i cant find any other .mod files anywhere in my se install.

sorry, im a mod virgin.

Need to download a copy of BoardModSE. There is a sticky topic in this area that has a link to the file. From there it has instructions on how to use it. You take the .mod file from the zip I created and run it with BoardModSE and it modifies the files for you. Or you can just read the .mod file and make the changes by hand. Very few changes for this mod -- by hand probably wouldn't take more than a few minutes.

[groan]

excellent, thanks, youve been very helpful.

[Spaceman-Spiff]

I must say this mod does sound good.

No, it IS good  

Would it be possible to add something to it like :

inactive members with 0 (zero) posts ?!

will be good with even more options
like: http://www.yabb.info/community/index.php?board=141;action=display;threadid=8964

[Myiasis]

will be good with even more options
like: http://www.yabb.info/community/index.php?board=141;action=display;threadid=8964

I agree, it would be nice with all those options. My original intention was just to make the function usable though. I don't personally have a great need for all of those options and probably won't have the time to add that stuff to it.

[Wiziwig]

Nice ideal, can you make it compatable with 1.4.1 ?