IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'ky_NETFrameworkLicenseCount'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_NETFrameworkLicenseCount]
  END
GO

CREATE PROCEDURE
       [dbo].[ky_NETFrameworkLicenseCount]
      (@Database      NVARCHAR(20),
       @Result        INT                OUTPUT)
WITH ENCRYPTION
AS
/*********************************************************************************************
 *
 * Stored Procedure Name: [dbo].[ky_NETFetchCurrentLogonUsers]
 * Description: This procedure is used to Get the instance count running on server,
 *              the count will be exclude the ADMIN login
 *
 * Created By: Arun.V
 * Modification History:
 * ????-??-??   Arun V.           Created
 * 2017-07-20   Pino Carafa       Modified it to prevent duplicates for same hostname/handler
 *
 *********************************************************************************************/
BEGIN

SET NOCOUNT ON

DECLARE @RESULTTBL TABLE
       ([HostName]              NCHAR(128),
        [ProgramName]           NCHAR(128),
        [HandlerCode]           VARCHAR(10),
        [HandlerName]           VARCHAR(30),
        [Status]                NVARCHAR(60),
        [login_time]            DATETIME,
        [hostprocess]           NCHAR(10),
        [nt_domain]             NCHAR(128),
        [nt_username]           NCHAR(128))

INSERT
  INTO @RESULTTBL
      ([HostName],
       [ProgramName],
       [HandlerCode],
       [HandlerName],
       [Status],
       [login_time],
       [hostprocess],
       [nt_domain],
       [nt_username])
SELECT [P].[hostname]                                 AS [HostName],
       [P].[program_name]                             AS [ProgramName],
       RTRIM(ISNULL(HAN.[CODE], ''))                  AS [HandlerCode],
       RTRIM(ISNULL(HAN.[Name], ''))                  AS [HandlerName],
       [DB].[state_desc]                              AS [Status],
       CONVERT(NVARCHAR(10),[P].[login_time],108)     AS [LoginTime],
       [P].[hostprocess]                              AS [HostProcessID],
       CASE WHEN RTRIM([P].[nt_domain]) <> N''
                 THEN [P].[nt_domain]
            ELSE [P].[hostprocess] END,
       CASE WHEN RTRIM([P].[nt_username]) <> N''
                 THEN [P].[nt_username]
            ELSE [P].[hostprocess] END  
  FROM SYS.[databases] [DB]
       INNER JOIN [master].[dbo].[SYSPROCESSES] [P]
               ON [P].[dbid] = DB.[database_id]
              AND [P].[program_name] LIKE 'KHDesktop%'
              AND [P].[program_name] NOT LIKE '%OutlookAddin%'
              AND [P].[program_name] NOT LIKE '%OfficeAddin%'
              AND CASE WHEN CHARINDEX(CHAR(0),
                                      CONVERT(VARCHAR(MAX),
                                              [P].[context_info])) > 0
                            THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                   [P].[context_info]),
                                           1,
                                           CHARINDEX(CHAR(0),
                                                     CONVERT(VARCHAR(MAX),
                                                     [P].[context_info])) - 1)
                       ELSE CONVERT(VARCHAR(MAX),
                                    [P].[context_info]) END NOT IN ('', 'ADM')
       LEFT OUTER JOIN [Handlers] HAN
                    ON HAN.[CODE] = CASE WHEN CHARINDEX(CHAR(0),
                                                        CONVERT(VARCHAR(MAX),
                                                                [P].[context_info])) > 0
                                              THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                                     [P].[context_info]),
                                                             1,
                                                             CHARINDEX(CHAR(0),
                                                                       CONVERT(VARCHAR(MAX),
                                                                               [P].[context_info])) - 1)
                                         ELSE CONVERT(VARCHAR(MAX),
                                                      [P].[context_info]) END
 WHERE [DB].[name] = @Database

  SELECT @Result = COUNT(1)
    FROM (  SELECT [RES].[HostName],
                   [RES].[HandlerCode],
                   [RES].[HandlerName],
                   [RES].[Status],
                   [RES].[nt_domain],
                   [RES].[nt_username]
              FROM @RESULTTBL [RES]
          GROUP BY [RES].[HostName],
                   [RES].[HandlerCode],
                   [RES].[HandlerName],
                   [RES].[Status],
                   [RES].[nt_domain],
                   [RES].[nt_username]) [ORGRES]

SET NOCOUNT OFF

END

GO