Tip - Getting Most Active Users Count to Work For You

Reply

NikW Posted: Thu, Jan 18 2007 10:05 AM

rated by 0 users

Several of our users have reported that our Most Active Users box shows innacurate results, with the majority of users being new members with (0) next to their name.Having spent some time on the CS site looking for help, I found a pointer to this stored procdure: cs_system_UpdateMostActiveUsers

From looking at the code, it turns out that Most Active Users is calculated based on the most active users in the last 24 hours, not over a period of time, as our users expected.

By amending this line of the stored procedure, you can adjust what time frame these statistics are calculated over. This can be useful for less active sites, or where you want the statistic to represent your best users over a period of time:

 TotalPosts = ISNULL( (SELECT count(PostID) FROM cs_Posts, cs_Sections WHERE cs_Sections.ApplicationType = 0 and cs_Sections.SecitonID = cs_Posts.SectionID and cs_Sections.SettingsID = @SettingsID AND UserID = U.cs_UserID AND PostDate > DateAdd(day, -1, GetDate())),0)

This last section (DateAdd(day, -1, GetDate())),0)), the -1 marks the period of time to go back in calculating the statistics. I have changed our configuiration to -60, but you could equally change this to a longer period of time, although with a large number of posts this might lead to performance problems.

Reply

Torsten Kirmse replied on Thu, Jul 26 2007 7:59 AM

rated by 0 users

Hi NikW,
Thanks for searching the pointer and the stored procedure. I have checked the procedure, too.
I don´t understand why there is a "SELECT TOP 100". The Most Active Users displays only the Top 10. That’s why a "SELECT TOP 10" should be enough for performance reasons.
If anyone is interested in displaying the "All Time Top10 Users" you can use the following code, which has no timeframe.
ALTER   procedure [dbo].[cs_system_UpdateMostActiveUsers]
(
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
SET NOCOUNT ON
 
DELETE FROM cs_statistics_User WHERE SettingsID = @SettingsID
 
INSERT INTO cs_statistics_User
SELECT TOP 10
U.UserID,
U.TotalPosts,
@SettingsID
FROM
cs_vw_Users_FullUser U
WHERE
U.IsAnonymous = 0 AND
U.IsIgnored = 0   AND
U.UserAccountStatus = 1 and U.SettingsID = @SettingsID
ORDER BY
TotalPosts DESC
 
SET NOCOUNT OFF
END
 
Because there is no time dependence there is no calculation. It´s a really simple Select from the View. There can’t be a performance issue.
 
Regards
Torsten

Reply

Daniel Rae replied on Thu, Feb 28 2008 4:33 AM

rated by 0 users

To dig this one back up...

I've got the SDK and have found the above in cs_Site_Statistics.PRC. If I make changes to it, How do I get it into my live site?

Do I recompile the SiteStatistics.cs file in visual basic and then the updated cs_Site_Statisctics will update?

Can anyone give me a simple step by step guide?

Reply

Alex Crome replied on Fri, Feb 29 2008 7:03 PM

rated by 0 users

You don't need the SDK for this.  You need to connect direct to your database (using a tool such as Enterprise Manager, or SQL Management Studio Express) , open up the cs_Site_Statistics sproc, modify it as appropiate and then execute the script to update the sproc on the live database.

For SQL Management Studio Express (download from http://msdn2.microsoft.com/en-gb/express/bb410792.aspx if need be)

1. Open the app up and connect to your database (normally domain yourdomain.com and username and password specified in your DB's conenctions tring)
2. In the Tree menu on the left hand side, goto YOUR DATABASE > Programibility > Stored Procedures > dbo.cs_Site_Statistics.  Right click on that and select modify.
3. Make a backup of the current script so you can restore the original if something goes wrong (i.e. copy the script that is displayed to you)
4. Make the modifications to the script specified above.
5. Execute the stored procedure (Icon of a Red Exclimation Mark)
6. You're Done.

Reply

Daniel Rae replied on Tue, Aug 26 2008 4:12 AM

rated by 0 users

Could this be adjusted to show... the top posters this month. It'd need to check the current month against the server time and check posts that had been made in that particular month. This would then, in theory, update at the 1st of each month and reset everyone back to 0.

Cheers!