|
USE LCSCDR
declare @SipAddress nvarchar(200),
@StartDate datetime,
@EndDate datetime,
@CurrPage int
select @SipAddress = 'test001@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
|