/****** 2022-06-20 Version 2022.6.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 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(HostName int,HostGroupName int,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 CREATE NONCLUSTERED INDEX [IX_tblUserDetailedLog_DateField] ON [dbo].[tblUserDetailedLog] ([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_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 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(nolock) go Delete from tblTempDetailedLog go INSERT into tblTempDetailedLog (ServerName,UserName,HostName,AppName) select distinct convert(varchar, srvrtbl.ServerName) as ServerName, convert(varchar, usrtbl.UserName) as UserName, convert(varchar, hsttbl.HostName) as HostName, convert(varchar, 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 (HostName,HostGroupName,HostDesc) SELECT DISTINCT tblUNHG.HostName,tblUNHG.HostGroupId AS HostGroupName,tblHD.HostName as HostDesc from tblUserNameHostGroup tblUNHG INNER JOIN tblHostDetails tblHD ON tblUNHG.HostName = tblHD.HostID 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 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