dbo.GetLoginFailureErrorReport.StoredProcedure.sql
3.95 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetLoginFailureErrorReport] Script Date: 02/06/2018 10:49:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MAGIC SOFTWARE PVT LTD
-- Create date: 17 Jan, 2011
-- Description: Stored Procedure to get LoginFailureErrorReport
-- =============================================
CREATE PROCEDURE [dbo].[GetLoginFailureErrorReport]
-- Add the parameters for the stored procedure here
-- FromDate & ToDate are mandatory
@sFromDate VARCHAR(20),
@sToDate VARCHAR(20),
@sAccoutNumber CHAR(16)='',
@sFailureCause TINYINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dtFromDate DATETIME
DECLARE @dtToDate DATETIME
-- convert the datatype of fromdate & todate parameter to datetime
SELECT @dtFromDate = CONVERT(DATETIME,@sFromDate)
SELECT @dtToDate = DATEADD(ms,-3,DATEADD(DAY,1,CONVERT(DATETIME,@sToDate)))
SELECT UserLoginLog.AccountNumber as AccountNumber,
(CASE WHEN ActiveEdition.Title IS NULL
THEN ISNULL(UserLoginLog.Edition,'')+' (Unknown)'
ELSE ActiveEdition.Title
END) AS EditionTitle,
UserLoginLog.ReferalUrl as ReferalUrl,
UserLoginLog.HttpReferer as HttpReferer,
LoginFailureCause.Description as FailureCause,
CONVERT(VARCHAR,UserLoginLog.LogDate,101) as LogDate
FROM UserLoginLog
LEFT JOIN (SELECT Edition.Title, Edition.Id FROM Edition WHERE Edition.IsActive=1) ActiveEdition
ON UserLoginLog.Edition = cast(ActiveEdition.Id AS NVARCHAR)
INNER JOIN LoginFailureCause
ON UserLoginLog.FailureId=LoginFailureCause.Id
WHERE UserLoginLog.LogDate BETWEEN @dtFromDate AND @dtToDate
AND UserLoginLog.FailureId = (CASE WHEN @sFailureCause > 0
THEN @sFailureCause
ELSE UserLoginLog.FailureId
END)
AND UserLoginLog.AccountNumber = (CASE WHEN LEN(@sAccoutNumber) > 0
THEN @sAccoutNumber
ELSE UserLoginLog.AccountNumber
END)
END
GO