/****** 2022-11-20 Version 2022.11.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