SELECT Last 5 Days User Login Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT Last 5 Days User Login Report

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-28 : 01:54:37

Friends,
I have to select last 5 days login data from UserLog Table, Based on the LogMessage, Please find below example for clarity

Table : UserLog
Sample Data :
LogId | UserID | IP | DateTime | LogMessage
1 | 1012 | 102.34.23.xx | 2013-10-22 08:42:00 | User ID 1012 (Soft Token)[] - Primary authentication successful from RDS
2 | 1012 | 102.34.23.xx | 2013-10-22 08:43:00 | User ID 1012 (Soft Token)[] - Network Connect: Session started from RDS Location
3 | 1012 | 102.34.23.xx | 2013-10-22 08:45:00 | User ID 1012 (Soft Token)[] - Network Session Initiated: Success Session from RDS Location
4 | 1015 | 102.xx.203.xx | 2013-10-22 09:42:00 | User ID 1015 (Soft Token)[] - Primary authentication successful from RDS
5 | 1015 | 102.xx.203.xx | 2013-10-22 09:43:00 | User ID 1015 (Soft Token)[] - Network Connect: Session started from RDS Location
6 | 1015 | 102.xx.203.xx | 2013-10-22 09:45:00 | User ID 1015 (Soft Token)[] - Network Session Initiated: Success Session from RDS Location
7 | 1012 | 102.34.23.xx | 2013-10-22 09:52:00 | User ID 1012 (Soft Token)[] - Primary authentication successful from RDS
8 | 1012 | 102.34.23.xx | 2013-10-22 09:53:00 | User ID 1012 (Soft Token)[] - Network Connect: Session started from RDS Location
9 | 1012 | 102.34.23.xx | 2013-10-22 09:55:00 | User ID 1012 (Soft Token)[] - Network Session Initiated: Success Session from RDS Location
10 | 1012 | 102.34.23.xx | 2013-10-23 11:52:00 | User ID 1012 (Soft Token)[] - Primary authentication successful from RDS
11 | 1012 | 102.34.25.xx | 2013-10-23 11:54:00 | User ID 1012 (Soft Token)[] - Network Connect: Session started from RDS Location
12 | 1012 | 102.34.25.xx | 2013-10-23 11:55:00 | User ID 1012 (Soft Token)[] - Network Session Initiated: Success Session from RDS Location

ExpectedResult:

I would like to select the user loged data by UserID for last 5 days, I will pass the UserID as a paramter, Time taken should be calculated based on LogMessage ( Time Between "Primary authentication successful" message and "Network Connect: Session started " Message), If multiple login for the same day We have to take the most recent one for the day.

Input : @UserID = 1012
UserID | Date | IP Address | TimeTaken (Min)
1012 | 2013-10-22 | 102.34.23.xx | 1
1012 | 2013-10-23 | 102.34.25.xx | 2

Please let me know if you want more details to get my expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 07:13:57

[code]
SELECT UserID,DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0) AS [Date],IPAddress,DATEDIFF(minute,MAX(CASE WHEN LogMessage LIKE 'Primary authentication successful%' THEN [DateTime] END),MAX(CASE WHEN LogMessage LIKE 'Network Connect: Session started %' THEN [DateTime] END)) AS TimeTaken
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserID,DATEDIFF(dd,0,[DateTime]),LogMessage ORDER BY [DateTime] DESC) AS Seq,*
FROM UserLog
WHERE UserID = @UserID
AND [DateTime] >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-5)
)t
WHERE Seq=1
GROUP BY UserID,DATEADD(dd,DATEDIFF(dd,0,[DateTime]),0),IPAddress
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dhinasql
Posting Yak Master

195 Posts

Posted - 2013-10-28 : 08:26:03

Hey Thanks Lot, Works charm...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 09:06:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources