none
how to find out list of user who not logged in a website for 15 days

    Question

  • plz

    masters

    how to find out. if need table structure  plz let me know...plz master help...


    thanks and regard love4csharp

    Saturday, September 28, 2013 7:28 AM

Answers

  • Hi,

    If you have a custom web application with login form then you need the following. It is very simple:

    create table dbo.Users
    (
    UserName varchar(128) not null primary key
    , Password varchar(255) not null
    , LastLoginDate datetime null
    )

    In your login check password stored procedure you should do the following:

    create procedure dbo.proc_Users_CheckPassword
    (
    	@UserName varchar(128)
    	, @Password varchar(255)
    	, @LoginSuccessful bit = null output
    )
    as
    begin
    	
    	if exists (
    		select
    			u.UserName
    		from dbo.Users u
    		where u.UserName = @UserName
    			p.Password = @Password
    	)
    	begin
    		update u
    		set
    			LastLoginDate = getdate()
    		from dbo.Users u
    		where u.UserName =  @UserName;
    
    		set @LoginSuccessful = 1;
    	end
    	else
    		set @LoginSuccessful = 0;
    	end
    
    	return 0;
    end

    I strongly advise you do do some kind of password hashing either on application or database level so even administrators could not see or get users passwords.

    Getting users that did not login to site in 15 days is easy:

    select
    *
    from dbo.Users u
    where u.LastLoginDate < dateadd(day, -15, getdate())


    HTH, Regards, Dean Savović

    Monday, September 30, 2013 9:40 AM

All replies

  • What do you mean by "USER"?
    Do you mean IP address or user name?!? the links which VadiveluB wrote is analyse you site trafic and have nothing to do with User Name but with IP (same IP can used by several different users). if you want to analyse something like user name then you should tell us What type of Authentication you are using on your site (Forms Authentication or Windows Authentication) as the way to analyse is different. Windows Authentication can be analyse by checking the windows/domain login logs (with the same links above) while to analyse Forms Authentication you need to develop a log system...

    For log system you need to chose what you want to log and build the tables according to your architecture. we need more information to help with this.


    [Personal Site] [Blog] [Facebook]signature

    Saturday, September 28, 2013 8:01 AM
  • If i understand correctly the sql should look like,

    declare @users table (uid int, logindate datetime, loginout char(2))
    insert into @users values(1,getdate() - 30,'IN')
    insert into @users values(2,getdate() - 14,'IN')
    select * from @users 
    where datediff(d,logindate,getdate())>=15 and loginout = 'IN'


    Regards, RSingh


    Saturday, September 28, 2013 6:13 PM
  • Hi,

    If you have a custom web application with login form then you need the following. It is very simple:

    create table dbo.Users
    (
    UserName varchar(128) not null primary key
    , Password varchar(255) not null
    , LastLoginDate datetime null
    )

    In your login check password stored procedure you should do the following:

    create procedure dbo.proc_Users_CheckPassword
    (
    	@UserName varchar(128)
    	, @Password varchar(255)
    	, @LoginSuccessful bit = null output
    )
    as
    begin
    	
    	if exists (
    		select
    			u.UserName
    		from dbo.Users u
    		where u.UserName = @UserName
    			p.Password = @Password
    	)
    	begin
    		update u
    		set
    			LastLoginDate = getdate()
    		from dbo.Users u
    		where u.UserName =  @UserName;
    
    		set @LoginSuccessful = 1;
    	end
    	else
    		set @LoginSuccessful = 0;
    	end
    
    	return 0;
    end

    I strongly advise you do do some kind of password hashing either on application or database level so even administrators could not see or get users passwords.

    Getting users that did not login to site in 15 days is easy:

    select
    *
    from dbo.Users u
    where u.LastLoginDate < dateadd(day, -15, getdate())


    HTH, Regards, Dean Savović

    Monday, September 30, 2013 9:40 AM