For a more detailed SITE LOG, where or how can I add more stored procedures for reports and extend (adding a few columns to existing ones) ?

I'd need a few more reports. And, adding columns to existing sprocs is enough? Does change happen automatically? (yes, it does)

Also, I want to show IP address in any  report whenever possible. Reason for this one is sometimes COUNTRY shows up blank. But, if I show IP I could manually get that data analyzed in another program. BTW, IP and Country, I'd like to have both fields unavailable to user in REGISTRATION page. So they cannot edit.

If someone have already done any of what I am asking or some interesting reports, please show me.

Thanks in advance.

Thanks to you Leupold I can understand now. I could match entry in List table to stored procedures.

Stored procedures have this name [dbo].[GetSiteLogX] where X is number from 1-9 and 12.

However, I have seen all but two stored procedures. They are

Site Log Reports    11    User Registrations By Country
Site Log Reports    10    User Registrations By Date

Could you, or someone else tell me the sproc name for these two reports?

Also, I added an entry to List Table, but I am not sure how entry - sproc thing is resolved. Entry in List is not the sproc name.

Thanks.

(BTW, I use your module extensively in my site. I have thousands of tabs. I created links Categories and present them in UDTs. This because is not efficient, from our perspective, to put all those tabs in DNNMenu. - UDT is a great module, I have to learn to use the other one, XML)

Roger that on 10 and 11. About matching List name and sproc name, I'll just go and try naming sproc like "GetSiteLogXX" being XX a number greater than or equal to 13.

Time to give now. DNN "make me so hony" when I find it so easy to work with.

I needed to show Country Code and Country Name  report Detailed Site Log. Here is what I did.

1 Create table GeoIPCountry and add data from MaxMind free CSV available here

/****** Object:  Table [dbo].[geoIPCountry]    Script Date: 08/25/2007 03:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[geoIPCountry](
    [startIp] [varchar](20) NULL,
    [endIp] [varchar](20) NULL,
    [startIpNum] [bigint] NULL,
    [countryCode] [char](2) NULL,
    [country] [nvarchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

After importing data table looks like this:

startIp              endIp                startIpNum           countryCode country
-------------------- -------------------- -------------------- ----------- -------------
4.17.135.32          4.17.135.63          68257568             CA          Canada
4.17.135.64          4.17.142.255         68257600             US          United States
4.17.143.0           4.17.143.15          68259584             CA          Canada
4.17.143.16          4.18.32.71           68259600             US          United States
4.18.32.72           4.18.32.79           68296776             MX          Mexico

2 Create three functions dbo.CCODETOCOUNTRY, dbo.IPTOCCODE, dbo.IPTOLOCATION

From (or adapted) original code by Chrissy LeMaire (clemaire@gmail.com)

#1 CCODE TO COUNTRY
/****** Object:  UserDefinedFunction [dbo].[CCodeToCountry]    Script Date: 08/25/2007 03:28:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CCodeToCountry]  (@strCCode varchar(2)) 
RETURNS nvarchar(42)
AS 
BEGIN
DECLARE @Country nvarchar(42)
         
        SET @Country = (select distinct country from GeoIPCountry where countryCode = @strCCode)
        IF LEN(@Country) = 0 SET @Country = @strCCode

RETURN @Country
END

#2 IP TO COUNTRY CODE
/****** Object:  UserDefinedFunction [dbo].[IPtoCCode]    Script Date: 08/25/2007 03:30:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* PRIMARY FUNCTIONS / STORED PROCEDURES */
CREATE FUNCTION [dbo].[IPtoCCode]  (@strIP varchar(15)) 
RETURNS nvarchar(255)
AS 
BEGIN
DECLARE @intIPNum bigint
DECLARE @strLocation nvarchar(255)
DECLARE @strCountry nvarchar(55)
        IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- if there 3 periods     
        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint)  + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))

        SET @strLocation = (SELECT top 1 city + '.' + region + '.' + country from
        geoIPCityLocation WHERE locid = (SELECT TOP 1 locid FROM GeoIPCityBlocks
        WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC) AND city IS NOT NULL)

        IF PARSENAME(@strLocation,1) not in ('US','CA')
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + dbo.CCodeToCountry(PARSENAME(@strLocation,1))
        ELSE
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + PARSENAME(@strLocation,2)  + ' ' + PARSENAME(@strLocation,1)
       
        IF @strLocation IS NULL
        BEGIN
            SET @strCountry = (SELECT TOP 1 countryCode FROM GeoIPCountry WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC)
            If @strCountry is NULL
            SET @strLocation = ' '
            ELSE
            SET @strLocation = @strCountry
        END
       
RETURN @strLocation
END

#3 IP TO LOCATION
/****** Object:  UserDefinedFunction [dbo].[IPtoLocation]    Script Date: 08/25/2007 03:32:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* PRIMARY FUNCTIONS / STORED PROCEDURES */
CREATE FUNCTION [dbo].[IPtoLocation]  (@strIP varchar(15)) 
RETURNS nvarchar(255)
AS 
BEGIN
DECLARE @intIPNum bigint
DECLARE @strLocation nvarchar(255)
DECLARE @strCountry nvarchar(55)
        IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- if there 3 periods     
        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint)  + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))

        SET @strLocation = (SELECT top 1 city + '.' + region + '.' + country from
        geoIPCityLocation WHERE locid = (SELECT TOP 1 locid FROM GeoIPCityBlocks
        WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC) AND city IS NOT NULL)

        IF PARSENAME(@strLocation,1) not in ('US','CA')
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + dbo.CCodeToCountry(PARSENAME(@strLocation,1))
        ELSE
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + PARSENAME(@strLocation,2)  + ' ' + PARSENAME(@strLocation,1)
       
        IF @strLocation IS NULL
        BEGIN
            SET @strCountry = (SELECT TOP 1 country FROM GeoIPCountry WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC)
            If @strCountry is NULL
            SET @strLocation = 'Unknown or Unassigned'
            ELSE
            SET @strLocation = @strCountry
        END
       
RETURN @strLocation
END

3 Alter procedure for Detailed Site Log Report, that is stored procedure dbo.GetSiteLog2. New fields in bold

/****** Object:  StoredProcedure [dbo].[GetSiteLog2]    Script Date: 08/25/2007 03:07:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetSiteLog2]
(
    @PortalId int,
    @PortalAlias nvarchar(50),
    @StartDate datetime,
    @EndDate datetime
)
AS

SELECT
    SiteLog.DateTime,
    Name = CASE
        WHEN SiteLog.UserId IS NULL THEN NULL
        ELSE Users.FirstName + ' ' + Users.LastName
    end,
    Referrer = CASE
        WHEN SiteLog.Referrer LIKE '%' + @PortalAlias + '%' THEN NULL
        ELSE SiteLog.Referrer
    end,
    UserAgent = dbo.BrowserName(userAgent),
    SiteLog.UserHostAddress,
    Location = dbo.IPToCCode(SiteLog.UserHostAddress),
    Country = dbo.IPToLocation(SiteLog.UserHostAddress),
    Tabs.TabName
FROM SiteLog
    LEFT OUTER JOIN Users ON SiteLog.UserId = Users.UserId
    LEFT OUTER JOIN Tabs ON SiteLog.TabId = Tabs.TabId
WHERE
    SiteLog.PortalId = @PortalId
    AND SiteLog.DateTime BETWEEN @StartDate and @EndDate
ORDER BY
    SiteLog.DateTime desc

That's it. Report should look like this now.