/****** Version 2023.8.0 ******/

/****** Stop all Windows Services related to JTB ******/
/****** EXISTINGDATABASE is an existing database ******/
/****** Search and replace EXISTINGDATABASE with the name of the existing database ******/
/****** After it is done, run the Service Configurator, run Clear and then Sampling All Records. ******/
/****** Start all Windows Services related to JTB ******/

USE [EXISTINGDATABASE]  
GO 
DROP TABLE [dbo].[UserDetailedLogTemp]
GO

SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
PRINT N'Creating UserDetailedLogTemp.'; 
CREATE TABLE [dbo].[UserDetailedLogTemp](
	[ServerName] [int] NULL,
	[UserName] [int] NULL,
	[UserHost] [int] NULL,
	[AppName] [int] NULL,
	[DateField] [datetime] NULL,
	[LingerValue] [int] NULL,
	[Licenses] [int] NULL,
	[ID] [int] NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp]
(
	[DateField] ASC
)
INCLUDE ( 	[ServerName],
	[UserName],
	[UserHost],
	[AppName],
	[LingerValue],
	[Licenses],
	[ProjectID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp]
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp]
(
	[ServerName] ASC,
	[AppName] ASC,
	[DateField] ASC,
	[Licenses] ASC,
	[LingerValue] ASC,
	[UserHost] ASC,
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp_UserName] ON [dbo].[UserDetailedLogTemp] ([UserName]) INCLUDE ([ServerName],[UserHost],[AppName])
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblTempOldNewDate](
	[Oldestdate] [datetime] NULL,
	[Newestdate] [datetime] NULL
) ON [PRIMARY]
GO

PRINT N'Creating SamplingInterval.'; 
CREATE TABLE [dbo].[SamplingInterval](
	[SamplingInterval] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempAppName(AppName nvarchar(100),Description nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempServerName(ServerName nvarchar(100),Description nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempFeatureGroups(AppGroupName nvarchar(100),AppName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempServerGroups(ServerGroupName nvarchar(100),ServerName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempFeatureServer(AppName nvarchar(100),ServerName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempDetailedLog(AppName nvarchar(100),ServerName nvarchar(100),UserName nvarchar(100),HostName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempUserName(UserName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempHostName(HostDesc nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempGroupName(UserName nvarchar(100),GroupId int,UserDescription nvarchar(100),GroupName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE tblTempHostGroupName(HostName nvarchar(100),HostGroupId int,HostDescription nvarchar(100),HostGroupName nvarchar(100))ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserNameHostGroup.'; 
CREATE TABLE [dbo].[tblUserNameHostGroup](
	[HostName] [int] NOT NULL,
	[HostGroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameHostGroup]
(
	[HostName] ASC,
	[HostGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserNameGroup.'; 
CREATE TABLE [dbo].[tblUserNameGroup](
	[UserName] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[FromDate] [datetime] NULL,
	[ToDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [unique] ON [dbo].[tblUserNameGroup]
(
	[UserName] ASC,
	[GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserDetails.';
CREATE TABLE [dbo].[tblUserDetails](
[UserName] [nvarchar](100) NOT NULL,
	[UserDescription1] [nvarchar](255) NULL,
	[UserDescription2] [nvarchar](255) NULL,
	[UserDescription3] [nvarchar](255) NULL,
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	 CONSTRAINT [PK_tblUserDetails_1] PRIMARY KEY CLUSTERED 
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_tblUserDetails] UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT N'Creating tblUserDetailedLogPrev.';
CREATE TABLE [dbo].[tblUserDetailedLogPrev](
	[ServerName] [nvarchar](100) NULL,
	[UserName] [nvarchar](100) NULL,
	[UserHost] [nvarchar](100) NULL,
	[AppName] [nvarchar](100) NULL,
	[DateField] [datetime] NULL,
	[LingerDate] [datetime] NULL,
	[CheckedOutDate] [datetime] NULL,
	[LicHandle] [int] NULL,
	[Licenses] [int] NULL,
	[CheckedOutDateOrg] [datetime] NULL,
	[LicenseSystem] [nvarchar](100) NULL,
	[ProjectID] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dt] ON [dbo].[tblUserDetailedLogPrev]
(
	[CheckedOutDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblUserDetailedLogprev_Datefield] ON [dbo].[tblUserDetailedLogprev] ([DateField] ASC) INCLUDE( [CheckedOutDate]) WITH(SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON[PRIMARY]
GO
CREATE NONCLUSTERED INDEX _IX_tblUserDetailedLog_AppName_DateField ON [dbo].[tblUserDetailedLog]([AppName], [DateField]) INCLUDE([LicHandle])
GO

BEGIN TRY  
    CREATE TABLE tblWildcard (ItemType nvarchar(10) NOT NULL,Wildcard nvarchar(100) NOT NULL,ItemGroup nvarchar(100) NOT NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE SamplingInterval (SamplingInterval int NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostDetails ADD HostIPv4 nvarchar(15)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostDetails ADD HostIPv6 nvarchar(39)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE tblProjects (ProjectID int IDENTITY(1,1) NOT NULL CONSTRAINT PKeyMyId PRIMARY KEY,Project nvarchar(50) NOT NULL,IsActive smallint NOT NULL)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblProjects ADD  CONSTRAINT DF_tblProjects_IsActive  DEFAULT ((1)) FOR IsActive
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE UNIQUE INDEX unique1 ON tblProjects (Project)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE TABLE tblUserCompleteDetailedLog (ServerName nvarchar(100), UserName nvarchar(100), UserHost nvarchar(100), AppName nvarchar(100), DateField datetime, LingerDate datetime, CheckedOutDate datetime, LicHandle INTEGER, Licenses INTEGER)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX defindex ON tblUserCompleteDetailedLog (DateField, ServerName, UserName, UserHost, AppName, CheckedOutDate,LicHandle)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.log(DateTime)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblUserDetailedLog ADD ProjectID int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.tblUserDetailedLog(CheckedOutDate)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblUserDetailedLogPrev ADD ProjectID int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE INDEX dt ON dbo.tblUserDetailedLogPrev(CheckedOutDate)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD DisplayAsUser bit DEFAULT 0
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_NoLog  DEFAULT ((0)) FOR NoLog
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_EmailNotificationDenials  DEFAULT ((0)) FOR EmailNotificationDenials
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblAppName ADD CONSTRAINT DF_tblAppName_DisplayAsUser  DEFAULT ((0)) FOR DisplayAsUser
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups ADD MaxLic int
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblGroups Drop CONSTRAINT PK_tblGroups
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblGroups Drop Column GroupId
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblGroups ADD GroupId int IDENTITY(1,1) NOT NULL
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblGroups WITH NOCHECK ADD CONSTRAINT PK_tblGroups PRIMARY KEY CLUSTERED (GroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblHostGroups Drop CONSTRAINT PK_tblHostGroups
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    Alter Table tblHostGroups Drop Column HostGroupId
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups ADD HostGroupId int IDENTITY(1,1) NOT NULL
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    ALTER TABLE tblHostGroups WITH NOCHECK ADD CONSTRAINT PK_tblHostGroups PRIMARY KEY CLUSTERED (HostGroupId ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [IDIndex] ON [dbo].[UserDetailedLogTemp] ([ID])
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [IX_UserDetailedLogTemp] ON [dbo].[UserDetailedLogTemp] ([ServerName] ASC,[AppName] ASC,[DateField] ASC,[Licenses] ASC,[LingerValue] ASC,[UserHost] ASC,[UserName] ASC)
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

BEGIN TRY  
    CREATE NONCLUSTERED INDEX [groupindex] ON [dbo].[UserDetailedLogTemp] ([DateField]) INCLUDE ([ServerName],[UserName],[UserHost],[AppName],[LingerValue],[Licenses],[ProjectID])
END TRY  
BEGIN CATCH  
    -- ignore if existing  
END CATCH; 
GO

Delete from tblTempAppName
go
INSERT into tblTempAppName (AppName, Description) select distinct AppName, Description from tblAppName(nolock)
go
Delete from tblTempServerName
go
INSERT into tblTempServerName (ServerName, Description) select distinct ServerName, Description from tblServerNames(nolock)
go
Delete from tblTempFeatureGroups
go
INSERT into tblTempFeatureGroups (AppGroupName,AppName) select tblAppGroupsJunction.AppGroupName,tblAppName.AppName from tblAppGroupsJunction inner join tblAppName on  Convert(int,tblAppGroupsJunction.AppName)=tblAppName.AppID
go
Delete from tblTempServerGroups
go
INSERT into tblTempServerGroups (ServerGroupName,ServerName) select tblServerGroupsJunction.ServerGroupName,tblServerNames.ServerName from tblServerGroupsJunction inner join tblServerNames on  Convert(int,tblServerGroupsJunction.ServerName)=tblServerNames.ServerID
go
Delete from tblTempFeatureServer
go
INSERT into tblTempFeatureServer (AppName,ServerName) select distinct AppName,ServerName from log union select distinct appname, ServerName from tblUserDetailedLog union select distinct appname,ServerName from tblUserDetailedLogPrev(nolock)
go
Delete from tblTempDetailedLog
go
INSERT into tblTempDetailedLog (ServerName,UserName,HostName,AppName) select distinct srvrtbl.ServerName as ServerName, usrtbl.UserName as UserName, hsttbl.HostName as HostName, apptbl.AppName as AppName from UserDetailedLogTemp maintbl  join tblServerNames srvrtbl on maintbl.ServerName = srvrtbl.serverID  join tblAppName apptbl on apptbl.AppID = maintbl.AppName  join tblUserDetails usrtbl on usrtbl.UserID = maintbl.UserName  join tblHostDetails hsttbl on hsttbl.HostID = maintbl.UserHost
go
Delete from tblTempUserName
go
INSERT into tblTempUserName (UserName) select distinct UserName from tblUserDetails(nolock)
go
Delete from tblTempHostName
go
INSERT into tblTempHostName (HostDesc) select distinct HostName from tblHostDetails(nolock)
go
Delete from tblTempGroupName
go
INSERT into tblTempGroupName (UserName,GroupId,UserDescription,GroupName) select distinct udt.UserName UserName,convert(varchar, gp.GroupId) as GroupId, udt.UserDescription1, ugps.GroupName from tblUserNameGroup gp join tblUserDetails udt on gp.UserName = udt.UserID join tblGroups ugps on gp.GroupId = ugps.GroupId
go
Delete from tblTempHostGroupName
go
INSERT into tblTempHostGroupName (HostName,HostGroupId,HostDescription,HostGroupName) select distinct hdt.HostName HostName,convert(varchar, gp.HostGroupId) as HostGroupId, hdt.HostDescription1 , hgps.HostGroupName FROM tblUserNameHostGroup gp join tblHostDetails hdt on gp.HostName=hdt.HostId join tblHostGroups hgps on gp.HostGroupId = hgps.HostGroupId
go
CREATE TABLE tblTempOldNewDate(Oldestdate datetime,Newestdate datetime)
go
Delete from tblTempOldNewDate
go
INSERT into tblTempOldNewDate (Oldestdate,Newestdate)SELECT MIN(t.Oldestdate)as Oldestdate , Max(t.newestdate) as Newestdate from(SELECT MIN(DateTime) as Oldestdate, MAX(DateTime) as newestdate from log UNION SELECT MIN(DateField) as Oldestdate, MAX(CheckedOutDate) as newestdate from tblUserDetailedLog UNION SELECT  MIN(DateField) as Oldestdate, MAX(CheckedOutDate) as newestdate from tblUserDetailedLogPrev UNION SELECT  MIN(DateField) as Oldestdate, MAX(DateField) as newestdate from UserDetailedLogTemp) as t
go

CREATE TYPE [dbo].[ProcessDetailsList] AS TABLE(
	[Active] [int] NULL,
	[MachineName] [varchar](250) NULL,
	[ProcessDescription] [varchar](250) NULL,
	[ProcessEndTime] [datetime] NULL,
	[ProcessID] [int] NULL,
	[ProcessName] [varchar](250) NULL,
	[processStarttime] [datetime] NULL,
	[projectID] [varchar](250) NULL,
	[total] [bit] NULL,
	[username] [varchar](250) NULL
)
GO


GO
/****** Object:  StoredProcedure [dbo].[sp_Insert_ProcessDetails]    Script Date: 4/3/2023 6:57:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   PROCEDURE [dbo].[sp_Insert_ProcessDetails] @ProcessDetailsList as  ProcessDetailsList readonly 
AS 
declare @prevname as varchar(250) , @Active as bit = 0, @MachineName as varchar(250),@ProcessDescription as varchar(250),@ProcessEndTime as datetime , @ProcessID as int=0 , @Processname as varchar(250) , @processStarttime as datetime, @projectID int, @total as bit , @username as varchar(250), @appname as varchar(250)
 declare Processlist  cursor for select * from @ProcessDetailsList 
open Processlist; 
fetch next from Processlist into  @Active, @MachineName, @ProcessDescription, @ProcessEndTime, @ProcessID, @Processname, @processStarttime,@projectID, @total, @username ; 
WHILE @@FETCH_STATUS = 0  
BEGIN 
--check processname is null or not 
if (@ProcessDescription is null or @ProcessDescription='') 
begin 
set @appname = @Processname 
end 
else 
begin 
set @appname = @ProcessDescription 
end 
------------------------------ insert username------------------------------------ 
 if nullif(@username , '') is not null 
begin 
IF NOT EXISTS (SELECT 1 FROM tblUserDetails WHERE UserName=@username) 
 INSERT INTO [tblUserDetails] ([UserName]) VALUES  (@username)  end 
------------------------------insert hostname----------------------------------------- 
if nullif(@MachineName, '') is not null 
begin 
IF NOT EXISTS (SELECT 1 FROM tblHostDetails WHERE HostName=@MachineName) INSERT INTO [tblHostDetails] ([HostName]) VALUES  (@MachineName)
 end 
 ---------------------------------insert appname------------------------------------------
if (@Processname is null or @Processname='') 
begin 
INSERT INTO tblAppName(AppName,NoLog,EmailNotificationDenials,Description)VALUES(@ProcessDescription,'False','False',@ProcessDescription) 
end 
else 
begin 
if NOT EXISTS (SELECT 1 FROM tblAppName WHERE AppName=@appname) 
INSERT INTO tblAppName(AppName,NoLog,EmailNotificationDenials,Description)VALUES(@appname,'False','False',@Processname) 
end 
-----------------insert server name ---------------------------------------------------------------------- 
IF NOT EXISTS (SELECT * FROM tblServerNames WHERE ServerName='JTB Process Monitor')   INSERT INTO tblServerNames(ServerName,EmailNotificationPing)VALUES('JTB Process Monitor','False') 
----------------------------insert prev log ----------------------- 
if not exists(select * from tblappname where appname = @appname and nolog ='true') 
begin 
if (@ProcessEndTime is null or @ProcessEndTime='') 
if NOT EXISTS ( SELECT * FROM tblUserDetailedLogPrev WHERE ServerName='JTB Process Monitor'	AND UserName=@username AND UserHost=@MachineName AND AppName=@appname AND CheckedOutDate=@ProcessStartTime AND LicHandle=@ProcessID) 
begin 
INSERT INTO tblUserDetailedLogPrev(ServerName,UserName,UserHost,AppName,DateField,CheckedOutDate,LicHandle,CheckedOutDateOrg,LicenseSystem,ProjectID)VALUES('JTB Process Monitor',@username,@MachineName,@appname,convert(varchar, getdate(), 20),@processStarttime,@ProcessID,@processStarttime,'JTBPM',@projectID) 
end 
else 
 update tbluserdetailedlogprev set datefield =convert(varchar, getdate(), 20) where ServerName='JTB Process Monitor'	AND UserHost=@MachineName	AND AppName=@appname	AND CheckedOutDate=@ProcessStartTime	AND LicHandle=@ProcessID
else 
begin 
if NOT EXISTS (SELECT * FROM tblUserDetailedLog WHERE ServerName='JTB Process Monitor' AND UserName=@username AND UserHost=@MachineName AND AppName=@appname AND CheckedOutDate=@processStarttime AND DateField=@ProcessEndTime AND  LicHandle=@ProcessID ) 
begin 
INSERT INTO tblUserDetailedLog(ServerName,UserName,UserHost,AppName,datefield,CheckedOutDate,LicHandle,ProjectID)VALUES('JTB Process Monitor',@UserName,@MachineName,@appname,CAST(@ProcessEndTime as datetime),cast(@processStarttime as datetime),@ProcessID , 0 ) 
DELETE FROM tblUserDetailedLogPrev WHERE ServerName='JTB Process Monitor' AND UserName=@username AND UserHost=@MachineName AND AppName=@appname AND CheckedOutDate=@processStarttime AND LicHandle=@ProcessID; 
end end end
FETCH NEXT FROM Processlist INTO  @Active,@MachineName,@ProcessDescription,@ProcessEndTime,@ProcessID,@Processname,@processStarttime,@projectID,@total,@username
END; 
CLOSE Processlist; 
DEALLOCATE Processlist;