Usman ur Rehman Ahmed's blog

 

How to add new reports in DNN Site Log?

This is a beginner level post and the purpose is to introduce you how SiteLog is organized and add a new report in Site Log.

First, we'll look at how Site Log works and then introduce a new report. At the core of Site Log there are two tables,

Lists (Table)

Lists table is used throughout DNN for a number of purposes. This table contains types of reports available in SiteLog type drop down. With a default DNN installation 12 reports are avaialble filtered by the field ListName as following,

Site_log_reports_drop_down

SELECT *

    FROM {databaseOwner}{objectQualifier}Lists where ListName = 'Site Log Reports'

The resultant shows that each record is assigned a unique value (identified by Value field) from 1 - 12.

Site_log_reports
SiteLog (Table) 

Thi s table contains data for site log which is used by the stored procedure to render.

GetSiteLog (Stored Procedure/s) 

GetSiteLog referres to a series of stored procedures each affixed by a number denoting which report it belongs to. For example SiteLog5 referrs to the Lists table record identified by Value field equal to 5 which is 'Page Views By Hour'.

How to add a new report in SiteLog

Step 1: To add a new report type, simply add a new record in Lists table providing it a new unique value. Please make sure that value is unqiue. Based over this unique value, the respective report stored procedure is called as we shall see.

Here I'll add a new report as existing report 'Page Views By Day' but that will list views by annonymous users only,

/* insert into the list of reports */
INSERT INTO {databaseOwner}[{objectQualifier}Lists]
           ([ListName]
           ,[Value]
           ,[Text]
           ,[ParentID]
           ,[Level]
           ,[SortOrder]
           ,[DefinitionID]
           ,[Description]
           ,[PortalId]
           ,SystemList)
     VALUES
           ('Site Log Reports'
           ,13
           ,'Anonymous Page Views By Day'
           ,0
           ,0
           ,0
           ,-1
           ,NULL
           ,-1
           ,1)
GO

So now our site log drop down will have a new report listed. However clicking on this report will report an error that GetSiteLog13 doesn't exiss so we need to create one.

Step 2: Create the stored procedure that outputs tabular data required to be rendered. Since we are only tweaking the 'Page Views By Day' data, we can simply use the existing stored proecredure GetSiteLog1 and change it a bit.

Here's how our new SP look like after changing,

/*This SP will return page views by day for annonymous users only.*/

CREATE procedure {databaseOwner}[{objectQualifier}GetSiteLog13]

@PortalID int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select datepart(weekday,DateTime) AS 'WeekDay',
 count(*) AS 'Views',
 count(distinct {objectQualifier}SiteLog.UserHostAddress) AS 'Visitors',
 count(distinct {objectQualifier}SiteLog.UserId) AS 'Users'
from dbo.SiteLog
where PortalId = @PortalID and UserId IS NULL
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
group by datepart(weekday,DateTime)
order by WeekDay

 

Filed under: DNN Dot Net Nuke GetSiteLog Lists objectQualifier site sitelog {DatabaseOwner}
11
To Posterous, Love Metalab