USE LCSCDR declare @SipAddress nvarchar(200), @StartDate datetime, -- 검색 날자 시작 변수 @EndDate datetime, -- 검색 날자 끝 변수 @CurrPage int -- 페이지처리 변수 select @SipAddress = 'test@mani4u.com', @CurrPage = 1, @StartDate = cast('2015-01-01' as datetime), @EndDate = cast('2015-05-30' as datetime) select @SipAddress, @CurrPage, @StartDate, @EndDate ; WITH ConnectionLog AS ( SELECT ROW_NUMBER() OVER(ORDER BY reg.RegisterTime desc) AS rownum , DATEADD(minute, DATEDIFF(minute, sysutcdatetime(), sysdatetime()), reg.SessionIdTime) AS SessionIdTimeS , reg.SessionIdSeq , usr.UserUri AS SipAddress , DATEADD(minute, DATEDIFF(minute, sysutcdatetime(), sysdatetime()), reg.RegisterTime) AS LoginTime , DATEADD(minute, DATEDIFF(minute, sysutcdatetime(), sysdatetime()), reg.DeRegisterTime) AS LogoutTime , reg.IpAddress ,CASE WHEN reg.[DeRegisterTypeId] = 1 THEN N'정상종료' WHEN reg.[DeRegisterTypeId] = 2 THEN N'접속만료' WHEN reg.[DeRegisterTypeId] = 3 THEN N'비정상종료' WHEN reg.[DeRegisterTypeId] = 4 THEN N'사용자속성변경' WHEN reg.[DeRegisterTypeId] = 5 THEN N'POOL변경' WHEN reg.[DeRegisterTypeId] = 6 THEN N'Client버전문제' ELSE N'알수없음' END AS LogoutReason ,CASE WHEN reg.[RegistrarId] = 1 THEN N'Lync 2013 Client' WHEN reg.[RegistrarId] = 1003 THEN N'Lync 2013 Client' WHEN reg.[RegistrarId] = 1002 THEN N'MSPL' WHEN reg.[RegistrarId] = 2002 THEN N'MSPL' WHEN reg.[RegistrarId] = 2003 THEN N'MSPL' ELSE N'ETC' END AS ClientVer , REPLACE(ser.ServerFQDN,'.adatum.com','') AS ServerFQDN , REPLACE(poo.PoolFQDN,'.adatum.com','') AS PoolFQDN , reg.EdgeServerId , CASE WHEN reg.IsInternal = 1 THEN N'내부' ELSE N'외부' END AS IsInternal , reg.IsUserServiceAvailable , reg.IsPrimaryRegistrar , reg.IsPrimaryRegistarCentral , reg.ResponseCode , reg.DiagnosticId , reg.DeviceId , reg.EndpointId , reg.EndpointEra , (SELECT top 1 [Version] FROM [LcsCDR].[dbo].[ClientVersions] where reg.ClientVersionId=[VersionId]) as VersionName FROM Registration reg INNER JOIN Users usr ON reg.UserId = usr.UserId INNER JOIN Pools poo ON reg.PoolId = poo.PoolId INNER JOIN Servers ser ON reg.RegistrarId = ser.ServerId INNER JOIN ( SELECT [VersionId] FROM [LcsCDR].[dbo].[ClientVersions] where 1=1 and (charindex('Android', Version) > 0 or charindex('iPhone', Version) > 0 or charindex('iPad', Version) > 0) ) VER ON VER.[VersionId] = reg.ClientVersionId WHERE (1 = 1) --AND (usr.UserUri = @SipAddress) AND dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), reg.[RegisterTime]) BETWEEN DATEADD(day,0,CONVERT(VARCHAR(10), @StartDate,23)) AND DATEADD(day,1,CONVERT(VARCHAR(10), @EndDate,23)) ) SELECT * FROM ConnectionLog --WHERE rownum BETWEEN (@CurrPage-1) * 50 + 1 AND @CurrPage * 50
|