Select IP Address for Last Login in DotNetNuke
Feb
18
Written by:
2/18/2010
I thought I’d share a little gem that I had drummed up some time back to help you with a little bit of built in audit information from your DotNetNuke® website. Many sites that want to present their visitors with a notification of when and where they last logged into the web application. This can be useful, even if I believe few to no people actually pay attention to it past the first couple of times they logged in. That being said, it’s very easy to implement in DotNetNuke®, and you don’t even need to build a module to take advantage of it!
First, you’d want to add a stored procedure to your DNN site that queries the database for the user login information that’s currently logged in. Here is an example snippet:
1: CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetLastIpNumberForUser]
2: @UserId INT,
3: @PortalId INT
4: AS
5: BEGIN
6: SET NOCOUNT ON;
7:
8: DECLARE @Xml XML, @LogDate DATETIME;
9: SELECT TOP 1 @Xml = CAST(el.[LogProperties] AS XML), @LogDate = el.[LogCreateDate]
10: FROM [EventLog] el
11: WHERE (el.[LogUserId] = @UserId AND el.[LogPortalID] = @PortalId)
12: AND el.[LogTypeKey] = 'LOGIN_SUCCESS'
13: ORDER BY el.[LogCreateDate] DESC
14:
15: SELECT @Xml.value('(/LogProperties/LogProperty/PropertyValue)[1]', 'NVARCHAR(50)'), @LogDate
16: END
As you can see, you might want to slightly alter the preceding snippet to suit your needs. Also, if you run the code outside of the SQL Module in DNN, you would need to replace {databaseOwner} and {objectQualifier"} with the appropriate values.
Now, all you have to do is pass the UserId and PortalId into the stored procedure, and format the result. There are multiple ways to use this via your own DNN module, or even by using the core Reports Module. Andrew Nurse has a great blog outlining passing parameters to the DNN Reports Module.