깡민쓰~IT Story

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

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 
 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  
 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