Not Normalized Rotating Header Image

TSQL CTE For DNN SiteLog Statistics

Nov 26th, 2012
by Todd.
No comments yet

DotNetNuke is an open source content management system / application development framework that leverages ASP.NET / Microsoft.NET. The DNN Database schema contains the SiteLog table where all page hits are logged. Every visit to the DNN site results in a row being inserted into the SiteLog table. Depending on how busy the site is this table can get quite large.

SiteLogTableProperties

The image above shows a sample SiteLog table with nearly 2 million rows and over 1 GB in size. In contrast the entire DNN database including the SiteLog table is about 1.4 GB.

Joining the data in the SiteLog table with the Tabs table or better yet the vw_Tabs view allows for the analysis of hit statistics per tab/page. Note that query performance could be greatly improved if appropriate indexes on the SiteLog table existed; particularly on the DateTime and TabId columns. However, since this is a write-heavy table, performance of the site could be affected negatively if additional indexes are added to the SiteLog table. (Remember that every insert to the table would need to update any indexes as well which could be a performance hit depending on how busy the site is.)

Since the SiteLog table is written to for every site hit directly querying the SiteLog table, especially during times where the site is busy, can degrade performance that will manifest in blocking of the query.  Use caution when querying the SiteLog table on a production system. With that background and associated cautions out of the way – let us look at a tsql script that queries the DNN SiteLog using a CTE. (MSDN CTE Documentation)

Three parameters need to be set at the beginning of the script: @BeginDate, @EndDate, and @TabId.  The script starts by creating a temporary table, #HitCount, containing each TabId, along with Hit Count, and Unique Hit Count from the SiteLog table where the DateTime column values are GTE @BeginDate and LT @EndDate.  The CTE that follows can then join on the temporary table instead of the live SiteLog table improving performance.  The CTE utilizes a third parameter, @TabId, to determine the starting point for recursion.

?Download TSQL_CTEForDNNSiteLogStatistics_sql.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

 
USE [DotNetNuke]
GO
 
DECLARE @BeginDate DATETIME ;
DECLARE @EndDate DATETIME ;
DECLARE @TopTabId INT ;
 
SET @BeginDate = '01-Oct-2012' ; -- Change this to the desired Begin Date.
SET @EndDate   = '01-Nov-2012' ; -- Change this to the desired End Date.
SET @TopTabId  = 54 ; -- Change this to the desired starting / top Tab Id.
 
SELECT  [sitelog].[TabId] ,
        COUNT([sitelog].[SiteLogId]) AS [HitCount] ,
        COUNT(DISTINCT [sitelog].[UserHostAddress]) AS [UniqueHitCount]
INTO    [#HitCount]
FROM    [dbo].[sitelog] (NOLOCK)
WHERE   [sitelog].[DateTime] >= @BeginDate AND [sitelog].[DateTime] < @EndDate
GROUP BY [sitelog].[TabId]
ORDER BY [sitelog].[TabId] ASC ;
 
WITH    MenuTabs ( [TabId], [TabName], [ParentId], [MenuLevel], [Level], [Title], [TabPath], [HasChildren], [IsVisible], [HitCount], [UniqueHitCount] )
          AS ( SELECT   [VT1].[TabId] ,
                        [VT1].[TabName] ,
                        [VT1].[ParentId] ,
                        0 AS [MenuLevel] ,
                        [VT1].[Level] ,
                        [VT1].[Title] ,
                        [VT1].[TabPath] ,
                        [VT1].[HasChildren] ,
                        [VT1].[IsVisible] ,
                        [H1].[HitCount] ,
                        [H1].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT1]
                        INNER JOIN [#HitCount] [H1] ON [VT1].[TabId] = [H1].[TabId]
               WHERE    [VT1].[TabId] = @TopTabId
                        AND [VT1].[IsVisible] = 1
               UNION ALL
               SELECT   [VT2].[TabId] ,
                        [VT2].[TabName] ,
                        [VT2].[ParentId] ,
                        [M].[MenuLevel] + 1 ,
                        [VT2].[Level] ,
                        [VT2].[Title] ,
                        [VT2].[TabPath] ,
                        [VT2].[HasChildren] ,
                        [VT2].[IsVisible] ,
                        [H2].[HitCount] ,
                        [H2].[UniqueHitCount]
               FROM     [dbo].[vw_Tabs] [VT2]
                        INNER JOIN [#HitCount] [H2] ON [VT2].[TabId] = [H2].[TabId]
                        INNER JOIN MenuTabs [M] ON [VT2].[ParentID] = [M].[TabId]
               WHERE    [VT2].[IsVisible] = 1
             )
    SELECT  [TabId] ,
            [TabName] ,
            [ParentId] ,
            [Level] ,
            [Title] ,
            [TabPath] ,
            [HasChildren] ,
            [IsVisible] ,
            [HitCount] ,
            [UniqueHitCount]
    FROM    [MenuTabs]
    ORDER BY [TABPATH] ;
 
DROP TABLE [#HitCount] ;

If repeated queries of the data are desired; a better idea may be to offload / transform the data from the SiteLog table periodically into another table where additional indexes can be added. A follow-up post will explore options along these lines.

* Note that my experiences with DNN and the corresponding data structures are related to version 5.x.  Version 6.x is in mainstream release and version 7.0 is in beta at the time of this writing.

“DNN is a leading Web Content Management Platform (or CMS) for Microsoft, powering over 700,000 production web sites worldwide. The flexible DNN open source CMS platform also functions as a web application development framework. Depending on your role within your organization, DNN provides powerful benefits to support your Web initiatives.” http://www.dotnetnuke.com/

Posted in: DotNetNuke, SQLServer.
Tagged: ·

Account Lockout Notification – Asp.Net Membership Provider

Nov 16th, 2012
by Todd.
No comments yet

A while back I was working with an Asp.Net Application that leveraged the Asp.Net Membership Provider as the basis for its security.  The Asp.Net Membership provider has a security mechanism that locks out accounts after a specified / configurable amount of failed logins within a period of time.

While this is good for security – it can be confusing for support / helpdesk staff that take a call from an end user who is having trouble logging into the application – mainly because they are unaware that there is an account lockout situation involved.

In order to give the support / helpdesk staff direction an “after update” trigger on the aspnet_Membership table was written that emails the helpdesk any time an account becomes locked out.  This way when the user calls in they know that the solution starts with unlocking the account.

Here is the code for the trigger.

?Download trg_ASPMembershipAfterUpdate_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

CREATE TRIGGER [dbo].[trg_aspnet_MembershipAfterUpdate] ON [dbo].[aspnet_Membership]
    AFTER UPDATE
AS
  DECLARE @OldIsLockedOut    BIT ;
  DECLARE @NewIsLockedOut    BIT ;
  DECLARE @EmailRecipients   NVARCHAR(MAX) ;
  DECLARE @EmailCCRecipients NVARCHAR(MAX) ;
  DECLARE @EmailSubject      VARCHAR(100) ;
  DECLARE @EmailContent      VARCHAR(100) ;
  DECLARE @EmailFormat       VARCHAR(20) ;
  DECLARE @EmailImportance   VARCHAR(6) ;
  DECLARE @UserName          NVARCHAR(256) ;
 
  IF UPDATE(IsLockedOut)
    BEGIN
      SET @EmailRecipients   = '' ; -- Email Address Intentionally Removed 
      SET @EmailCCRecipients = '' ; -- Email Address Intentionally Removed 
      SET @EmailFormat       = 'HTML' ;
      SET @EmailImportance   = 'HIGH' ;
 
      SELECT @NewIsLockedOut =  i.IsLockedOut,
             @OldIsLockedOut =  d.IsLockedOut,
             @UserName       =  au.UserName
      FROM   inserted i
      INNER JOIN deleted d on i.UserId = d.UserId
      INNER JOIN dbo.aspnet_Users au on i.UserId = au.UserId ;
 
      IF (@NewIsLockedOut) = 1 AND (@OldIsLockedOut) = 0
        BEGIN
          SELECT @EmailSubject = @@servername + ' - Portal Application Account (' + @UserName + ') Lock Out' ;
          SELECT @EmailContent = 'Portal Application User ' + @username + ' has been locked out.' ;
          EXECUTE msdb.dbo.sp_send_dbmail @recipients      = @EmailRecipients,
                                          @copy_recipients = @EmailCCRecipients,
                                          @subject         = @EmailSubject,
                                          @body            = @EmailContent,
           	                          @body_format     = @EmailFormat,
                                          @importance      = @EmailImportance ;
        END
    END
RETURN

Posted in: ASP.NET, SQLServer.
Tagged: · ·

Linked Server Connectivity / Availability

Nov 9th, 2012
by Todd.
No comments yet

A query that attempts to access data from a linked server intermittently fails. The Linked Server is configured properly and the query that utilizes the Linked Server has been tested and verified previously.

Try / Catch Logic can be used to trap the failure – but wouldn’t it be great if there was a way to be able to test the Linked Server in order to know that it was available before attempting to connect to it?

There Is !  Best of all, unlike other methods I have seen it does not require the use of xp_cmdshell.

Introduced in SQL 2005, sp_testlinkedserver tests the connection to a linked server.  Although the documentation shows that the return code values are 0 (success) or 1 (failure), at this time a failure will always be catastrophic and a return code will not be set.

According to this Microsoft Connect Item, the only update planned for this issue is to fix the documentation to note the behavior.  Ideally the return values would work as indicated – so feel free to go to the Microsoft Connect Site and make your opinion known if you agree.

The sample code below uses sp_testlinkedserver and should work with both the current implementation of the procedure and in the case that the procedure is updated to return the values as currently documented.

?Download LinkedServerTest_SQL.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

BEGIN TRY
      DECLARE @LSIsAvail BIT ;
      EXECUTE @LSIsAvail = sp_testlinkedserver @servername = N'MyLinkedServer' ;
END TRY
BEGIN CATCH
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
END CATCH
 
IF @LSIsAvail = 0 -- Successful/Linked Server Okay
   BEGIN
         BEGIN TRY
               -- Select Data from Linked Server
               SELECT *
               FROM   OPENQUERY(MyLinkedServer, 'SELECT [EmployeeId]
                                                 ,      [FirstName]
                                                 ,      [LastName]
                                                 FROM   [dbo].[Employee]
                                                 ;') ;
         END TRY
         BEGIN CATCH
               -- Logic to handle Linked Server query error.
               PRINT 'Linked Server Query Error' ;
               RETURN ;
         END CATCH ;
   END
      ELSE
   BEGIN
      -- Logic to handle Linked Server being unavailable.
      PRINT 'Linked Server is Unavailable' ;
      RETURN ;
   END

Posted in: SQLServer.
Tagged: