SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [SQL].[ScheduledAction]'
GO
ALTER TABLE [SQL].[ScheduledAction] DROP CONSTRAINT [PK_ScheduledAction]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [IX_SiteID_SPGuid] from [dbo].[SharePointObjects]'
GO
DROP INDEX [IX_SiteID_SPGuid] ON [dbo].[SharePointObjects]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating types'
GO
CREATE TYPE [EventCollection].[CollectionHistoryTableType] AS TABLE
(
[ServerID] [int] NOT NULL,
[EventSourceID] [nvarchar] (255) NOT NULL,
[Level] [tinyint] NOT NULL,
[CollectedEventCount] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TYPE [dbo].[RolePropertiesType] AS TABLE
(
[RoleID] [int] NOT NULL,
[Title] [nvarchar] (255) NOT NULL,
[Description] [nvarchar] (max) NULL,
[SPID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
CREATE TYPE [dbo].[SecurityAuditLogType] AS TABLE
(
[Time] [datetime] NOT NULL,
[UserID] [int] NULL,
[ActionID] [int] NOT NULL,
[Target] [nvarchar] (max) NULL,
[Details] [nvarchar] (max) NULL,
[ItemSPGuid] [uniqueidentifier] NOT NULL,
[SiteID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[Alerts]'
GO
CREATE TABLE [EventCollection].[Alerts]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (512) NOT NULL,
[Query] [nvarchar] (2048) NOT NULL,
[AppliesToAllFarms] [bit] NOT NULL,
[EmailRecipients] [nvarchar] (2048) NOT NULL,
[OccuranceDelayTimeInSeconds] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Alerts] on [EventCollection].[Alerts]'
GO
ALTER TABLE [EventCollection].[Alerts] ADD CONSTRAINT [PK_Alerts] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[AlertFarmAssociations]'
GO
CREATE TABLE [EventCollection].[AlertFarmAssociations]
(
[FarmId] [int] NOT NULL,
[AlertId] [int] NOT NULL,
[IsActive] [bit] NOT NULL,
[LastExecutionStatus] [tinyint] NOT NULL,
[LastSent] [datetime2] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AlertFarmAssociations] on [EventCollection].[AlertFarmAssociations]'
GO
ALTER TABLE [EventCollection].[AlertFarmAssociations] ADD CONSTRAINT [PK_AlertFarmAssociations] PRIMARY KEY CLUSTERED  ([FarmId], [AlertId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[CounterValuesForServerCounterInstance]'
GO
CREATE PROCEDURE [PerformanceCounters].[CounterValuesForServerCounterInstance]
	@serverID int,
	@counterID int,
	@instanceID int,
	@startedOn datetime,
	@endedOn datetime
AS
begin
	if (
		(select count(*)
			from PerformanceCounters.CounterValues CounterValues
			where CounterValues.TerminalServerID = @serverID and CounterValues.CounterID = @counterID and CounterValues.InstanceID = @instanceID
			and CounterValues.CollectionDate < @endedOn and CounterValues.CollectionDate >= @startedOn) > 0)
	begin
		select
			CollectionDate,
			Value
		from PerformanceCounters.CounterValues CounterValues
		where CounterValues.TerminalServerID = @serverID and CounterValues.CounterID = @counterID and CounterValues.InstanceID = @instanceID
			and CounterValues.CollectionDate < @endedOn and CounterValues.CollectionDate >= @startedOn
		order by CollectionDate asc
	end
	else
	begin
		select top(1)
			CollectionDate,
			Value
		from PerformanceCounters.CounterValues CounterValues
		where CounterValues.TerminalServerID = @serverID and CounterValues.CounterID = @counterID and CounterValues.InstanceID = @instanceID
		order by CollectionDate desc
	end
end
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[ServersDiskLatestPerformance]'
GO
CREATE PROCEDURE [PerformanceCounters].[ServersDiskLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME

BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @DiskQueueCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskQueue] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskQueue] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskReadCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskRead] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskRead] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskWriteCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskWrite] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskWrite] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskTransferCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskTransfer] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskTransfer] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
	ELSE IF @PerformanceCounterID = @DiskFreeSpaceCounterID
	BEGIN
		select TerminalServerID, DiskID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[DiskFreeSpace] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND DiskID = T.DiskID)
		from
		(SELECT TerminalServerID, DiskID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[DiskFreeSpace] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, DiskID ) AS T
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[ServersLatestPerformance]'
GO
CREATE PROCEDURE [PerformanceCounters].[ServersLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE @CPUCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @CPUCounterID
	BEGIN
		select TerminalServerID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].CPU where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID)
		from
		(select TerminalServerID, MAX(CollectionDate) as MaxCollectionDate
		from [PerformanceCounters].CPU 
		group by TerminalServerID) as T
	END
	ELSE IF @PerformanceCounterID = @MemoryCounterID
	BEGIN
		select TerminalServerID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].Memory where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID)
		from
		(select TerminalServerID, MAX(CollectionDate) as MaxCollectionDate
		from [PerformanceCounters].Memory 
		group by TerminalServerID) as T
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[ServersNetworkLatestPerformance]'
GO
CREATE PROCEDURE [PerformanceCounters].[ServersNetworkLatestPerformance]
	@PerformanceCounterID INT,
	@TerminalServerID INT
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	IF @PerformanceCounterID = @NetworkReceivedCounterID
	BEGIN
		select TerminalServerID, NetworkAdapterID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[NetworkReceived] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND NetworkAdapterID = T.NetworkAdapterID)
		from
		(SELECT TerminalServerID, NetworkAdapterID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[NetworkReceived] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, NetworkAdapterID ) AS T
	END
	ELSE IF @PerformanceCounterID = @NetworkSentCounterID
	BEGIN
		select TerminalServerID, NetworkAdapterID, MaxCollectionDate, Value = (select TOP 1(Value) from [PerformanceCounters].[NetworkSent] where CollectionDate = T.MaxCollectionDate AND TerminalServerID = @TerminalServerID AND NetworkAdapterID = T.NetworkAdapterID)
		from
		(SELECT TerminalServerID, NetworkAdapterID, MAX(CollectionDate) AS MaxCollectionDate
		FROM [PerformanceCounters].[NetworkSent] WHERE TerminalServerID = @TerminalServerID
		GROUP BY TerminalServerId, NetworkAdapterID ) AS T
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[RoleProperties]'
GO
CREATE TABLE [dbo].[RoleProperties]
(
[RoleID] [int] NOT NULL,
[LoadingInfoID] [int] NOT NULL,
[Title] [nvarchar] (255) NOT NULL,
[Description] [nvarchar] (max) NULL,
[SiteID] [int] NOT NULL,
[SPID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_RoleProperties] on [dbo].[RoleProperties]'
GO
ALTER TABLE [dbo].[RoleProperties] ADD CONSTRAINT [PK_RoleProperties] PRIMARY KEY CLUSTERED  ([LoadingInfoID], [SiteID], [SPID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetLoadedPermissionLevels]'
GO
CREATE PROCEDURE [dbo].[GetLoadedPermissionLevels]
	@LoadingInfoID int

	AS
	SET NOCOUNT ON	
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_LoadingInfoID  int

	SELECT @Loc_LoadingInfoID = @LoadingInfoID

	SELECT rp.Title, 
		   rp.SiteID,
		   (CASE WHEN EXISTS (SELECT ra.RoleID
				FROM RoleAssignments as ra WHERE ra.LoadingInfoID = @Loc_LoadingInfoID AND ra.RoleID = rp.RoleID AND ra.SiteID = rp.SiteID)						
				THEN CONVERT(bit, 1)
				ELSE CONVERT(bit, 0) 
			END) as IsUsed
	FROM RoleProperties rp
	WHERE rp.LoadingInfoID = @Loc_LoadingInfoID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SecurityAuditLogs]'
GO
CREATE TABLE [dbo].[SecurityAuditLogs]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Time] [datetime] NOT NULL,
[UserID] [int] NULL,
[ActionID] [int] NOT NULL,
[Target] [nvarchar] (max) NULL,
[Details] [nvarchar] (max) NULL,
[ItemSPGuid] [uniqueidentifier] NOT NULL,
[SiteID] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_SecurityAuditLogs] on [dbo].[SecurityAuditLogs]'
GO
ALTER TABLE [dbo].[SecurityAuditLogs] ADD CONSTRAINT [PK_SecurityAuditLogs] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [SecurityAuditLogs_SiteID_Time] on [dbo].[SecurityAuditLogs]'
GO
CREATE NONCLUSTERED INDEX [SecurityAuditLogs_SiteID_Time] ON [dbo].[SecurityAuditLogs] ([SiteID], [Time] DESC)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[AuditActionTypes]'
GO
CREATE TABLE [dbo].[AuditActionTypes]
(
[ActionID] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AuditActionTypes] on [dbo].[AuditActionTypes]'
GO
ALTER TABLE [dbo].[AuditActionTypes] ADD CONSTRAINT [PK_AuditActionTypes] PRIMARY KEY CLUSTERED  ([ActionID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[InsertRoleProperties]'
GO
CREATE PROCEDURE [dbo].[InsertRoleProperties]
	@LoadingInfoID int,
	@SiteID int,
	@RoleProperties RolePropertiesType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [dbo].[RoleProperties]
           ([RoleID] , 
			[LoadingInfoID],
			[Title],
			[Description],
			[SiteID],
			[SPID]
		   )
	SELECT rp.[RoleID],
		   @LoadingInfoID,
		   rp.[Title],
		   rp.[Description],
		   @SiteID,
		   rp.[SPID]
	FROM @RoleProperties rp
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[InsertSecurityAuditLogs]'
GO
CREATE PROCEDURE [dbo].[InsertSecurityAuditLogs]
	@Logs SecurityAuditLogType READONLY
AS
BEGIN
	SET NOCOUNT ON	
	
	INSERT INTO [dbo].[SecurityAuditLogs] ([Time], [UserID], [ActionID], [Target], [Details], [ItemSPGuid], [SiteID])           
	SELECT l.[Time], l.[UserID], l.[ActionID], l.[Target], l.[Details], l.[ItemSPGuid], l.[SiteID]
	FROM @Logs l
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[AlertHistory]'
GO
CREATE TABLE [EventCollection].[AlertHistory]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[FarmId] [int] NOT NULL,
[AlertId] [int] NOT NULL,
[Status] [tinyint] NOT NULL,
[SentTo] [nvarchar] (2048) NOT NULL,
[SentOn] [datetime2] NOT NULL,
[TotalExecutionTimeInMiliseconds] [int] NOT NULL,
[QueryExecutionTimeInMiliseconds] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AlertHistory] on [EventCollection].[AlertHistory]'
GO
ALTER TABLE [EventCollection].[AlertHistory] ADD CONSTRAINT [PK_AlertHistory] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_AlertHistory_SentOn_AlertId] on [EventCollection].[AlertHistory]'
GO
CREATE NONCLUSTERED INDEX [IX_AlertHistory_SentOn_AlertId] ON [EventCollection].[AlertHistory] ([SentOn] DESC, [AlertId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[FarmPerformanceCountersState]'
GO
CREATE TABLE [PerformanceCounters].[FarmPerformanceCountersState]
(
[FarmID] [int] NOT NULL,
[Enabled] [bit] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_FarmPerformanceCountersState] on [PerformanceCounters].[FarmPerformanceCountersState]'
GO
ALTER TABLE [PerformanceCounters].[FarmPerformanceCountersState] ADD CONSTRAINT [PK_FarmPerformanceCountersState] PRIMARY KEY CLUSTERED  ([FarmID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[PerformanceCountersConfiguration]'
GO
CREATE TABLE [PerformanceCounters].[PerformanceCountersConfiguration]
(
[AgentID] [int] NOT NULL,
[CacheInterval] [int] NOT NULL,
[CollectionInterval] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_PerformanceCountersConfiguration] on [PerformanceCounters].[PerformanceCountersConfiguration]'
GO
ALTER TABLE [PerformanceCounters].[PerformanceCountersConfiguration] ADD CONSTRAINT [PK_PerformanceCountersConfiguration] PRIMARY KEY CLUSTERED  ([AgentID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[GetBasicAlertInfo]'
GO
CREATE PROCEDURE [EventCollection].[GetBasicAlertInfo]
AS
	SET NOCOUNT ON	
	SELECT a.ID, a.Name, ISNULL(MAX(fa.LastSent), '1.1.0001') AS LastSent, ISNULL(MAX(fa.LastExecutionStatus), 2) AS LastExecutionStatus FROM [EventCollection].[Alerts] as a
	LEFT JOIN [EventCollection].[AlertFarmAssociations] as fa 
	ON a.ID = fa.AlertId
	GROUP BY a.ID, a.Name
	ORDER BY a.Name
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[CollectionHistory]'
GO
CREATE TABLE [EventCollection].[CollectionHistory]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Date] [date] NOT NULL,
[ServerID] [int] NOT NULL,
[EventSourceID] [nvarchar] (255) NOT NULL,
[Level] [tinyint] NOT NULL,
[CollectedEventCount] [int] NOT NULL CONSTRAINT [DF_CollectionHistory_CollectedEventCount] DEFAULT ((0))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_CollectionHistory] on [EventCollection].[CollectionHistory]'
GO
ALTER TABLE [EventCollection].[CollectionHistory] ADD CONSTRAINT [PK_CollectionHistory] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_Date_ServerID] on [EventCollection].[CollectionHistory]'
GO
CREATE NONCLUSTERED INDEX [IX_Date_ServerID] ON [EventCollection].[CollectionHistory] ([Date], [ServerID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[GroupServersCountersAVGLast]'
GO
CREATE PROCEDURE [PerformanceCounters].[GroupServersCountersAVGLast]
	@StartTime datetime,
	@CounterID int,
	@InstanceID int,
	@TerminalServerID int,
	@CategoryID int
AS
BEGIN
	SET NOCOUNT ON
		SELECT [PerformanceCounters].[CounterValues].[TerminalServerID],
			[PerformanceCounters].[CounterValues].[CounterID],
			[PerformanceCounters].[CounterValues].[InstanceID],
			y.AverageValue,
			[PerformanceCounters].[CounterValues].Value as LastValue,
			y.MinValue,
			y.MaxValue,
			y.NumberOfRows
	   
		FROM [PerformanceCounters].[CounterValues]
		INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[CounterValues].[TerminalServerID]
		INNER JOIN 
		(
			SELECT
				TerminalServerID,
				CounterID,
				InstanceID,
				MAX(CollectionDate) AS MaxDate
			FROM
				[PerformanceCounters].[CounterValues]
			WHERE CollectionDate > DATEADD(d, -1, GETDATE())
			GROUP BY TerminalServerID, CounterID, InstanceID
		) AS x
		ON	[PerformanceCounters].[CounterValues].[TerminalServerID] = x.TerminalServerID 
			AND [PerformanceCounters].[CounterValues].[CounterID] = x.CounterID 
			AND [PerformanceCounters].[CounterValues].[InstanceID] = x.InstanceID 
			AND [PerformanceCounters].[CounterValues].CollectionDate = x.MaxDate

		LEFT JOIN
		(
		SELECT [TerminalServerID],
				CounterID,
				InstanceID,
				AVG([Value]) as AverageValue,
				MIN([Value]) as MinValue,
				Max([Value]) as MaxValue,
				Count(*) as NumberOfRows
			FROM [PerformanceCounters].[CounterValues]
			WHERE CollectionDate > @StartTime
			GROUP BY [TerminalServerID], [CounterID], [InstanceID] ) as y

		ON [PerformanceCounters].[CounterValues].[TerminalServerID] = y.TerminalServerID 
			AND [PerformanceCounters].[CounterValues].[CounterID] = y.CounterID
			AND [PerformanceCounters].[CounterValues].[InstanceID] = y.InstanceID
		INNER JOIN Instances ON Instances.InstanceID = CounterValues.InstanceID
		Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1
		AND PerformanceCounters.CounterValues.CounterID = @CounterID
		AND ((@InstanceID = 0 AND Instances.CategoryID = @CategoryID) OR (PerformanceCounters.CounterValues.InstanceID = @InstanceID))
		AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[GroupServersDiskAVGLast]'
GO
CREATE PROCEDURE [PerformanceCounters].[GroupServersDiskAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @DiskQueueCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskQueue].[TerminalServerID],
				[PerformanceCounters].[DiskQueue].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskQueue].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskQueue]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskQueue].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskQueue]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskQueue].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskQueue].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskQueue].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					[DiskID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[DiskQueue]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], [DiskID] ) as y

			ON [PerformanceCounters].[DiskQueue].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskQueue].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskReadCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskRead].[TerminalServerID],
				[PerformanceCounters].[DiskRead].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskRead].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskRead]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskRead].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskRead]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskRead].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskRead].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskRead].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					[DiskID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[DiskRead]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], [DiskID] ) as y

			ON [PerformanceCounters].[DiskRead].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskRead].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskWriteCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskWrite].[TerminalServerID],
				[PerformanceCounters].[DiskWrite].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskWrite].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskWrite]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskWrite].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskWrite]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskWrite].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskWrite].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskWrite].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					[DiskID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[DiskWrite]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], [DiskID] ) as y

			ON [PerformanceCounters].[DiskWrite].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskWrite].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskTransferCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskTransfer].[TerminalServerID],
				[PerformanceCounters].[DiskTransfer].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskTransfer].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskTransfer]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskTransfer].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskTransfer]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskTransfer].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskTransfer].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskTransfer].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					[DiskID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[DiskTransfer]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], [DiskID] ) as y

			ON [PerformanceCounters].[DiskTransfer].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskTransfer].[DiskID] = y.DiskID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @DiskFreeSpaceCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[DiskFreeSpace].[TerminalServerID],
				[PerformanceCounters].[DiskFreeSpace].[DiskID],
				y.AverageValue,
			   [PerformanceCounters].[DiskFreeSpace].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[DiskFreeSpace]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[DiskFreeSpace].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					DiskID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[DiskFreeSpace]

				GROUP BY  
					TerminalServerID, DiskID
			) AS x
			ON	[PerformanceCounters].[DiskFreeSpace].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[DiskFreeSpace].[DiskID] = x.DiskID AND [PerformanceCounters].[DiskFreeSpace].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					[DiskID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[DiskFreeSpace]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], [DiskID] ) as y

			ON [PerformanceCounters].[DiskFreeSpace].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[DiskFreeSpace].[DiskID] = y.DiskID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
	END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[GroupServersNetworkAVGLast]'
GO
CREATE PROCEDURE [PerformanceCounters].[GroupServersNetworkAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @NetworkReceivedCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[NetworkReceived].[TerminalServerID],
				[PerformanceCounters].[NetworkReceived].[NetworkAdapterID],
				y.AverageValue,
			   [PerformanceCounters].[NetworkReceived].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[NetworkReceived]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[NetworkReceived].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					NetworkAdapterID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[NetworkReceived]

				GROUP BY  
					TerminalServerID, NetworkAdapterID
			) AS x
			ON	[PerformanceCounters].[NetworkReceived].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[NetworkReceived].[NetworkAdapterID] = x.NetworkAdapterID AND [PerformanceCounters].[NetworkReceived].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					NetworkAdapterID,
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[NetworkReceived]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], NetworkAdapterID ) as y

			ON [PerformanceCounters].[NetworkReceived].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[NetworkReceived].[NetworkAdapterID] = y.NetworkAdapterID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @NetworkSentCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[NetworkSent].[TerminalServerID],
				[PerformanceCounters].[NetworkSent].[NetworkAdapterID],
				y.AverageValue,
			   [PerformanceCounters].[NetworkSent].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[NetworkSent]
			INNER JOIN [EventCollection].[MonitoringServers] on [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[NetworkSent].[TerminalServerID]
			INNER JOIN 
			(
				SELECT
					TerminalServerID,
					NetworkAdapterID,
					MAX(CollectionDate) AS MaxDate
				FROM
					[PerformanceCounters].[NetworkSent]

				GROUP BY  
					TerminalServerID, NetworkAdapterID
			) AS x
			ON	[PerformanceCounters].[NetworkSent].[TerminalServerID] = x.TerminalServerID AND [PerformanceCounters].[NetworkSent].[NetworkAdapterID] = x.NetworkAdapterID AND [PerformanceCounters].[NetworkSent].CollectionDate = x.MaxDate

			LEFT JOIN
			(
			SELECT [TerminalServerID],
					NetworkAdapterID,
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[NetworkSent]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID], NetworkAdapterID ) as y

			ON [PerformanceCounters].[NetworkSent].[TerminalServerID] = y.TerminalServerID AND [PerformanceCounters].[NetworkSent].[NetworkAdapterID] = y.NetworkAdapterID
			Where [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
	END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[GroupServersPerformanceAVGLast]'
GO
CREATE PROCEDURE [PerformanceCounters].[GroupServersPerformanceAVGLast]
	@StartTime datetime,
	@PerformanceCounterID int,
	@TerminalServerID int
AS
DECLARE	@CpuCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		IF @PerformanceCounterID = @CpuCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[CPU].[TerminalServerID],
				y.AverageValue,
			   [PerformanceCounters].[CPU].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[CPU]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[CPU].TerminalServerID AND [PerformanceCounters].[CPU].CollectionDate = (
				SELECT TOP 1
					CollectionDate AS MaxDate
				FROM
					[PerformanceCounters].[CPU]
				WHERE TerminalServerID = [EventCollection].[MonitoringServers].[ID]
				ORDER BY CollectionDate DESC				
			)

			LEFT JOIN
			(
			SELECT [TerminalServerID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[CPU]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID] ) as y

			ON [PerformanceCounters].[CPU].[TerminalServerID] = y.TerminalServerID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
		ELSE IF @PerformanceCounterID = @MemoryCounterID
			BEGIN
			SELECT @PerformanceCounterID as CounterID,
				[PerformanceCounters].[Memory].[TerminalServerID],
				y.AverageValue,
			   [PerformanceCounters].[Memory].Value as LastValue,
			   y.MinValue,
			   y.MaxValue,
			   y.NumberOfRows
	   
			FROM [PerformanceCounters].[Memory]
			INNER JOIN [EventCollection].[MonitoringServers] ON [EventCollection].[MonitoringServers].[ID] = [PerformanceCounters].[Memory].TerminalServerID AND [PerformanceCounters].[Memory].CollectionDate = (
				SELECT TOP 1
					CollectionDate AS MaxDate
				FROM
					[PerformanceCounters].[Memory]
				WHERE TerminalServerID = [EventCollection].[MonitoringServers].[ID]
				ORDER BY CollectionDate DESC				
			)

			LEFT JOIN
			(
			SELECT [TerminalServerID],
				  AVG([Value]) as AverageValue,
				  MIN([Value]) as MinValue,
				  Max([Value]) as MaxValue,
				  Count(*) as NumberOfRows
			  FROM [PerformanceCounters].[Memory]
			  WHERE CollectionDate > @StartTime
			  GROUP BY [TerminalServerID] ) as y

			ON [PerformanceCounters].[Memory].[TerminalServerID] = y.TerminalServerID
			WHERE [EventCollection].[MonitoringServers].[PerformanceCountersEnabled] = 1 AND (@TerminalServerID = 0 OR [EventCollection].[MonitoringServers].[ID] = @TerminalServerID)
			END
	END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[ServerCurrentDiskUsage]'
GO
CREATE PROCEDURE [PerformanceCounters].[ServerCurrentDiskUsage]
	@TerminalServerID int
AS
	BEGIN
		SET NOCOUNT ON
		SELECT (Size - x.Value)  AS UsedSpace, CAST(Size AS REAL) AS TotalSpace
		FROM [dbo].[Discs] INNER JOIN (SELECT TOP 1 TerminalServerID, DiskID, Value
										FROM [PerformanceCounters].[DiskFreeSpace]
										WHERE TerminalServerID = @TerminalServerID
										ORDER BY CollectionDate DESC) AS x
		ON [dbo].[Discs].TerminalServerID = x.TerminalServerID
		AND [dbo].[Discs].DiscID = x.DiskID
	END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetNumberOfPrincipals]'
GO
CREATE PROCEDURE [dbo].[GetNumberOfPrincipals]
	@LoadingInfoID int
AS
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	
	SELECT 	@Loc_LoadingInfoID = @LoadingInfoID

	SELECT COUNT(p.PrincipalID)
	FROM Principals as p
	JOIN LoadedPrincipals as lp ON p.PrincipalID = lp.PrincipalID AND lp.LoadingInfoID = @Loc_LoadingInfoID
	WHERE p.ExternalUser = 0
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetSharePointObjectsById]'
GO
CREATE PROCEDURE [dbo].[GetSharePointObjectsById]
	@ObjectIds FilterIDsType READONLY
AS

SET NOCOUNT ON
DECLARE @Loc_ObjectIds FilterIDsType
	
INSERT INTO @Loc_ObjectIds
SELECT * FROM @ObjectIds

SELECT spo.ID, spo.SPGuid, spo.WebAppID AS [WebApplicationID], spo.SiteID FROM[dbo].[SharePointObjects] AS spo
JOIN @Loc_ObjectIds AS objectId ON objectId.ID = spo.ID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[SiteCollectionAuditLoadTimes]'
GO
CREATE TABLE [dbo].[SiteCollectionAuditLoadTimes]
(
[SiteID] [int] NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[AuditEnabledOnLastLoad] [bit] NOT NULL CONSTRAINT [DF_SiteCollectionsAuditLoadTimes_AuditEnabled] DEFAULT ((0))
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_SiteCollectionsAuditLoadTimes] on [dbo].[SiteCollectionAuditLoadTimes]'
GO
ALTER TABLE [dbo].[SiteCollectionAuditLoadTimes] ADD CONSTRAINT [PK_SiteCollectionsAuditLoadTimes] PRIMARY KEY CLUSTERED  ([SiteID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[InsertOrUpdateSiteCollectionAuditLoadTime]'
GO
CREATE PROCEDURE [dbo].[InsertOrUpdateSiteCollectionAuditLoadTime]
	@SiteId int,
	@StartTime datetime = NULL,
	@EndTime datetime = NULL,
	@AuditEnabledOnLastLoad bit

AS
BEGIN
	SET NOCOUNT ON
		
	IF NOT EXISTS (SELECT 1	FROM [dbo].[SiteCollectionAuditLoadTimes] WHERE [SiteID] = @SiteId )
	BEGIN
		INSERT INTO [dbo].[SiteCollectionAuditLoadTimes] ([SiteID], [StartTime], [EndTime], [AuditEnabledOnLastLoad])
		VALUES (@SiteId, @StartTime, @EndTime, @AuditEnabledOnLastLoad)
	END
	ELSE IF @StartTime IS NOT NULL
	BEGIN
		UPDATE [dbo].[SiteCollectionAuditLoadTimes] SET [StartTime] = @StartTime, [EndTime] = @EndTime, [AuditEnabledOnLastLoad] = @AuditEnabledOnLastLoad
		WHERE [SiteID] = @SiteId
	END	
	ELSE IF @EndTime IS NOT NULL
	BEGIN
		UPDATE [dbo].[SiteCollectionAuditLoadTimes] SET [EndTime] = @EndTime, [AuditEnabledOnLastLoad] = @AuditEnabledOnLastLoad
		WHERE [SiteID] = @SiteId
	END	
	ELSE 
	BEGIN
		UPDATE [dbo].[SiteCollectionAuditLoadTimes] SET [AuditEnabledOnLastLoad] = @AuditEnabledOnLastLoad
		WHERE [SiteID] = @SiteId
	END
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [EventCollection].[Agents]'
GO
ALTER TABLE [EventCollection].[Agents] ADD
[Version] [nvarchar] (255) NULL CONSTRAINT [DF_Agents_Version] DEFAULT ('1.0.0')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[v_FarmDescriptions]'
GO
ALTER VIEW [dbo].[v_FarmDescriptions]
WITH SCHEMABINDING
AS 
WITH SecurityEnabled (RBSecurityEnabled)
      AS
      (
            SELECT case WHEN OptionValue = 'False' THEN 0
						ELSE 1
						END 
            FROM [dbo].[Options] o
			WHERE o.OptionID = 2000     
      ),
	 IsAdminTable (IsAdmin) 
	 AS
	 (
			SELECT TOP 1 v.IsAdmin 
			FROM [dbo].[v_IsUserAdmin] v
	 ),
	 MySPDocKitRoleFarms 
	 AS
	 (
			SELECT DISTINCT rf.FarmID
			FROM [dbo].[SPDocKitRoleFarms] rf
			JOIN [dbo].[v_MySPDocKitRoles] r ON r.ID = rf.RoleID
	 )
  SELECT fd.[ID]
      ,fd.[Name]
      ,fd.[FarmData]
      ,fd.[IsSnapshot]
      ,fd.[IsManual]
      ,fd.[IsBackup]
      ,fd.[DifferencesDetected]
      ,fd.[CreatedBy]
      ,fd.[LoadingInfoID]
      ,fd.[IsGoodConfiguration]
	  ,fd.[SnapshotId]
	  ,f.[ID] as FarmID
	  ,f.[FarmGuid]
	  ,l.[CreatedOn]
	  ,f.[FarmType]
  FROM SecurityEnabled sec, IsAdminTable a, [dbo].[FarmDescriptions] fd
  INNER JOIN [dbo].[LoadingInfos] l ON l.ID = fd.LoadingInfoID
  INNER JOIN [dbo].[Farms] f ON f.ID = l.FarmID
  WHERE sec.RBSecurityEnabled = 0 OR a.IsAdmin = 1 OR f.ID IN (SELECT myFarms.FarmID FROM MySPDocKitRoleFarms myFarms)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[UpdateCollectionHistory]'
GO
CREATE PROCEDURE [EventCollection].[UpdateCollectionHistory]	
	@Date date,	
	@TableVariable EventCollection.CollectionHistoryTableType READONLY


AS
BEGIN
	SET NOCOUNT ON	

	MERGE EventCollection.CollectionHistory AS target
	USING (
		SELECT ServerID, EventSourceID, [Level], CollectedEventCount FROM @TableVariable
		) AS source
	ON (target.[Date] = @Date AND target.ServerID = source.ServerID AND target.[Level] = source.[Level] AND target.EventSourceID = source.EventSourceID)
	WHEN MATCHED THEN
		UPDATE SET CollectedEventCount = target.CollectedEventCount + source.CollectedEventCount
	WHEN NOT MATCHED THEN
		INSERT ([Date], ServerID, EventSourceID, [Level], CollectedEventCount) VALUES(@Date, source.ServerID, source.EventSourceID, source.[Level], source.CollectedEventCount);--DONT_TOUCH_ME
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetChildrenForParent]'
GO
ALTER PROCEDURE [dbo].[GetChildrenForParent]
	@LoadingInfoID INT,
	@FarmID INT,
	@ParentID INT
AS
BEGIN 
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_LoadingInfoID int
	DECLARE	@Loc_FarmID int
	DECLARE	@Loc_ParentID int

	SELECT @Loc_FarmID  = @FarmID,
		@Loc_LoadingInfoID = @LoadingInfoID,
		@Loc_ParentID = @ParentID

	SELECT	[SPObject].[ID],
			[SPObject].[Url], 
			[SPObject].[Name],
			[SPObject].[SPGuid],
			(CASE WHEN EXISTS 
				(
				SELECT [dbo].[SharePointObjectMemberships].[ParentID] FROM [dbo].[SharePointObjectMemberships] WHERE [dbo].[SharePointObjectMemberships].[LoadingInfoID] = @Loc_LoadingInfoID
				AND [dbo].[SharePointObjectMemberships].[ParentID] = [SPObject].[ID]
				) THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END),
			[SPObject].[SPObjectTypeID],
			[scope].[HasUniqueRoleAssignments],
			[SpObject].[ContentTypeID]
	FROM [dbo].[v_SharePointObjects] AS [SPObject]
	JOIN [dbo].[SharePointObjectMemberships] AS [Membership] ON [Membership].[ParentID] = @Loc_ParentID AND [Membership].[LoadingInfoID] = @Loc_LoadingInfoID AND [Membership].[ChildID] = [SPObject].[ID]
	JOIN [dbo].[ScopeAssignments] as [scope] ON [scope].SPObjectID = [SPObject].ID AND [scope].LoadingInfoID = @Loc_LoadingInfoID
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetListsWithUniquePermissions]'
GO
CREATE PROCEDURE [dbo].[GetListsWithUniquePermissions]
	@SiteCollectionIDs FilterIDsType READONLY,
	@LoadingInfoID int,
	@MaxDepth int = 32
AS

	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_SiteCollectionIDs FilterIDsType
	DECLARE	@Loc_LoadingInfoID int

	SELECT @Loc_LoadingInfoID = @LoadingInfoID 

	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs
	
	IF OBJECT_ID('tempdb..#ListsWithUniqueItems') IS NOT NULL
		DROP TABLE #ListsWithUniqueItems

	CREATE TABLE #ListsWithUniqueItems ( 		 
						[ID] INT,
						[PathLevel] INT,
						[ListID] INT,
					)

	DECLARE @Depth          INT
	DECLARE @NumberOfNewRows   INT

	SET @Depth = 0
	SET @NumberOfNewRows = 1
	WHILE @Depth <= @MaxDepth and @NumberOfNewRows > 0 
	BEGIN
		IF @Depth = 0
			BEGIN
				INSERT INTO #ListsWithUniqueItems([ID], [PathLevel], [ListID])
				SELECT SPObjects.ID, 0 AS PathLevel, SPObjects.ID
				FROM dbo.v_SharePointObjects AS SPObjects	
				INNER JOIN ScopeAssignments as sa ON sa.LoadingInfoID = @Loc_LoadingInfoID AND sa.SPObjectID = SPObjects.ID
				INNER JOIN @Loc_SiteCollectionIDs sc ON sc.ID = SPObjects.SiteID
				WHERE SPObjects.SPObjectTypeID = 3                                  				
			END
		ELSE
			BEGIN
				INSERT INTO #ListsWithUniqueItems([ID], [PathLevel], [ListID])
					SELECT membership.ChildID, @Depth, Lists.ListID
					FROM SharePointObjectMemberships as membership 
					JOIN #ListsWithUniqueItems as Lists on membership.ParentID = Lists.ID AND membership.LoadingInfoID = @Loc_LoadingInfoID AND Lists.PathLevel = (@Depth-1)
				END
		SET @NumberOfNewRows = @@ROWCOUNT								
		SET @Depth = @Depth + 1                                        
	END

	SELECT sc.Name AS SiteCollection,
		   list.ID AS ListID,  
		   list.Name AS List,
		   list.Url AS ListUrl,
		   COUNT(*) AS NumOfItemsWithUniquePermissions
	FROM #ListsWithUniqueItems as listItems
	INNER JOIN SharePointObjects item ON item.ID = listItems.ID 
	INNER JOIN ScopeAssignments sa ON sa.SPObjectID = item.ID AND sa.LoadingInfoID = @Loc_LoadingInfoID
	INNER JOIN SharePointObjects list ON list.ID = listItems.ListID 
	INNER JOIN SharePointObjects sc ON sc.ID = list.SiteID
	WHERE sa.HasUniqueRoleAssignments = 1 AND listItems.PathLevel > 0
	GROUP BY sc.Name, list.ID, list.Name, list.Url
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetSecurityAuditLogs]'
GO
CREATE PROCEDURE [dbo].[GetSecurityAuditLogs]
	@StartTime datetime,
	@EndTime datetime,
	@SPObjectTypeIDs FilterIDsType READONLY,
	@SiteCollectionIDs FilterIDsType READONLY,
	@UserIDs FilterIDsType READONLY,
	@ActionIDs FilterIDsType READONLY
AS
	SET NOCOUNT ON

	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE	@Loc_StartTime datetime
	DECLARE	@Loc_EndTime datetime
	DECLARE	@Loc_SPObjectTypeIDs FilterIDsType
	DECLARE	@Loc_SiteCollectionIDs FilterIDsType
	DECLARE	@Loc_UserIDs FilterIDsType
	DECLARE	@Loc_ActionIDs FilterIDsType

	SELECT @Loc_StartTime  = @StartTime,
		  @Loc_EndTime = @EndTime 

	INSERT INTO @Loc_SPObjectTypeIDs
	SELECT * FROM @SPObjectTypeIDs
	
	INSERT INTO @Loc_SiteCollectionIDs
	SELECT * FROM @SiteCollectionIDs

	INSERT INTO @Loc_UserIDs
	SELECT * FROM @UserIDs
	
	INSERT INTO @Loc_ActionIDs
	SELECT * FROM @ActionIDs

	SELECT sal.[Time], COALESCE(p.[DisplayName], 'Unknown') AS [User], aat.[Name] AS [Action], sal.[Target], sal.[Details], spo.[Url] AS [Url], sal.[ItemSPGuid], sal.[SiteID], sal.[UserID],  p.[Email], p.[Name] AS [Username], ot.[Name] AS [ObjectType]
	FROM SecurityAuditLogs sal
		LEFT OUTER JOIN Principals p ON sal.[UserID] = p.[PrincipalID] 
		LEFT OUTER JOIN AuditActionTypes aat ON  sal.[ActionID] = aat.[ActionID]
		LEFT OUTER JOIN SharePointObjects spo ON sal.[SiteID] = spo.[SiteID] AND sal.[ItemSPGuid] = spo.[SPGuid]
		INNER JOIN v_SharePointObjects vspo ON sal.[SiteID] = vspo.[ID]
		INNER JOIN SharePointObjectTypes ot ON  spo.[SPObjectTypeID] = ot.[ID]
	WHERE sal.[Time] >= @StartTime AND sal.[Time] <= @EndTime 
		AND sal.[SiteID] IN (SELECT ID FROM @Loc_SiteCollectionIDs)
		AND sal.[ActionID] IN (SELECT ID FROM @Loc_ActionIDs)
		AND (sal.[UserID] IN (SELECT ID FROM @Loc_UserIDs) OR sal.[UserID] = -1 OR sal.[UserID] IS NULL)
		AND (spo.[SPObjectTypeID] IN (SELECT ID FROM @Loc_SPObjectTypeIDs) OR spo.[Url] IS NULL)
	ORDER BY sal.[Time] DESC
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[GetSiteCollectionAuditLoadTimeBySiteId]'
GO
CREATE PROCEDURE [dbo].[GetSiteCollectionAuditLoadTimeBySiteId]
	@SiteId int 

AS
	SET NOCOUNT ON
	--moram radit lokalne kopije da forceam recompile nista drugo ne pomogne
	DECLARE @Loc_SiteId int
	
	SELECT @Loc_SiteId = @SiteId

	SELECT sca.[SiteID], sca.[StartTime], sca.[EndTime], sca.[AuditEnabledOnLastLoad], vspo.[Url]
	FROM [SiteCollectionAuditLoadTimes] sca
		INNER JOIN [v_SharePointObjects] vspo ON sca.[SiteID] = vspo.[ID]
	WHERE sca.[SiteID] = @Loc_SiteId
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[GetSpGroupSettings]'
GO
ALTER PROCEDURE [dbo].[GetSpGroupSettings]
	@PrincipalID int ,
	@LoadingInfoID int
AS
SELECT		lgs.[GroupName],
		    lgs.[Description],
			lgs.[AllowMembersEditMembership],
			lgs.[AutoAcceptRequestToJoinLeave],
			lgs.[MembershipRequestEmails],
			lgs.[OnlyAllowMembersViewMembership],
			ownerLoadedGroupSettings.SharepointID as OwnerSPID,
			p.Name as OwnerIdentifier,
			p.PrincipalTypeID as OwnerType,
			lgs.[AllowRequestToJoinLeave],
			lgs.SharepointID
FROM LoadedGroupSettings lgs
LEFT OUTER JOIN Principals p ON lgs.PrincipalOwnerID = p.PrincipalID
LEFT OUTER JOIN LoadedGroupSettings ownerLoadedGroupSettings ON ownerLoadedGroupSettings.LoadingInfoID = @LoadingInfoID AND ownerLoadedGroupSettings.PrincipalID = lgs.PrincipalOwnerID	
JOIN v_Principals vp ON vp.PrincipalID = lgs.PrincipalID		
WHERE @PrincipalID = lgs.PrincipalID AND @LoadingInfoID = lgs.LoadingInfoID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [EventCollection].[MonitoringViewers]'
GO
CREATE TABLE [EventCollection].[MonitoringViewers]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[HostName] [nvarchar] (255) NOT NULL,
[Version] [nvarchar] (255) NOT NULL,
[LastSeenOnline] [datetime] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_MonitoringViewers] on [EventCollection].[MonitoringViewers]'
GO
ALTER TABLE [EventCollection].[MonitoringViewers] ADD CONSTRAINT [PK_MonitoringViewers] PRIMARY KEY CLUSTERED  ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Rebuilding [SQL].[ScheduledAction]'
GO
CREATE TABLE [SQL].[tmp_rg_xx_ScheduledAction]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[Date] [datetime] NOT NULL,
[ActionId] [int] NOT NULL,
[ActionData] [xml] NOT NULL,
[NotifyEmails] [nvarchar] (max) NOT NULL,
[UndoDate] [datetime] NULL,
[StatusId] [int] NOT NULL,
[Log] [xml] NULL,
[CreatedBy] [nvarchar] (100) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[Name] [nvarchar] (100) NOT NULL,
[RelatedActionId] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [SQL].[tmp_rg_xx_ScheduledAction] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [SQL].[tmp_rg_xx_ScheduledAction]([Id], [Date], [ActionId], [ActionData], [NotifyEmails], [UndoDate], [StatusId]) SELECT [Id], [Date], [ActionId], [ActionData], [NotifyEmails], [UndoDate], [StatusId] FROM [SQL].[ScheduledAction]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [SQL].[tmp_rg_xx_ScheduledAction] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[SQL].[ScheduledAction]')
IF @idVal IS NOT NULL
    DBCC CHECKIDENT(N'[SQL].[tmp_rg_xx_ScheduledAction]', RESEED, @idVal)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [SQL].[ScheduledAction]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[SQL].[tmp_rg_xx_ScheduledAction]', N'ScheduledAction'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ScheduledAction] on [SQL].[ScheduledAction]'
GO
ALTER TABLE [SQL].[ScheduledAction] ADD CONSTRAINT [PK_ScheduledAction] PRIMARY KEY CLUSTERED  ([Id])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[AllDiskPerformanceHistorical]'
GO
CREATE PROCEDURE [PerformanceCounters].[AllDiskPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@DiskIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE @DiskQueueCounterID INT=27;--DONT_TOUCH_ME
DECLARE @DiskReadCounterID INT=28;--DONT_TOUCH_ME
DECLARE @DiskWriteCounterID INT=29;--DONT_TOUCH_ME
DECLARE @DiskTransferCounterID INT=30;--DONT_TOUCH_ME
DECLARE @DiskFreeSpaceCounterID INT=31;--DONT_TOUCH_ME
DECLARE @SQLQuery AS NVARCHAR(MAX)
		DECLARE @ParamDefinition AS NVarchar(2000)
		Set @ParamDefinition = ' @StartedOn datetime,
								 @EndedOn datetime'
	IF @PerformanceCounterType = @DiskFreeSpaceCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,(D.Size - Value) AS Value
					,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
				FROM [PerformanceCounters].[DiskFreeSpace] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.Discs D ON PC.DiskID = D.DiscID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskQueueCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskQueue] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskTransferCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskTransfer] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskReadCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskRead] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				[dbo].[Discs] D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @DiskWriteCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
				CollectionDate
				,Value
				,(S.Name + '' - '' + D.VolumeName + ''('' + D.Name + '')'') AS Series
			FROM [PerformanceCounters].[DiskWrite] PC INNER JOIN
				[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
				dbo.Discs D ON PC.DiskID = D.DiscID
			WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
		END
	IF(@DiskIds IS NOT NULL AND @DiskIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND PC.DiskID IN(' + @DiskIds + ')'
		END
	EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[AllNetworkPerformanceHistorical]'
GO
CREATE PROCEDURE [PerformanceCounters].[AllNetworkPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@NetworkAdapterIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE	@NetworkReceivedCounterID INT = 15;--DONT_TOUCH_ME
DECLARE @NetworkSentCounterID INT = 16;--DONT_TOUCH_ME
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLQuery AS NVARCHAR(MAX)
	DECLARE @ParamDefinition AS NVarchar(2000)
	Set @ParamDefinition = ' @StartedOn datetime,
								@EndedOn datetime'
	IF @PerformanceCounterType = @NetworkReceivedCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,Value
					,(S.Name + '' ('' + COALESCE(N.NetConnectionID, N.Name) + '')'') AS Series
				FROM [PerformanceCounters].[NetworkReceived] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.NetworkAdapters N ON PC.NetworkAdapterID = N.NetworkAdapterID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	ELSE IF @PerformanceCounterType = @NetworkSentCounterID
		BEGIN
			SET @SQLQuery = N'SELECT 
					CollectionDate
					,Value
					,(S.Name + '' ('' + COALESCE(N.NetConnectionID, N.Name) + '')'') AS Series
				FROM [PerformanceCounters].[NetworkSent] PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1 INNER JOIN
					dbo.NetworkAdapters N ON PC.NetworkAdapterID = N.NetworkAdapterID
				WHERE CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
		END
	IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
		END
	IF(@NetworkAdapterIds IS NOT NULL AND @NetworkAdapterIds != '')
		BEGIN
			SET @SQLQuery = @SQLQuery + N' AND PC.NetworkAdapterID IN(' + @NetworkAdapterIds + ')'
		END
	EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [PerformanceCounters].[AllServersPerformanceHistorical]'
GO
CREATE PROCEDURE [PerformanceCounters].[AllServersPerformanceHistorical]
	@StartedOn datetime,
	@EndedOn datetime,
	@TerminalServerIds NVARCHAR(MAX),
	@PerformanceCounterType int
AS
DECLARE	@CpuCounterID INT = 2;--DONT_TOUCH_ME
DECLARE @MemoryCounterID INT = 4;--DONT_TOUCH_ME
	BEGIN
		SET NOCOUNT ON
		DECLARE @SQLQuery AS NVARCHAR(MAX)
		DECLARE @ParamDefinition AS NVarchar(2000)
		Set @ParamDefinition = ' @StartedOn datetime,
								 @EndedOn datetime'
		IF @PerformanceCounterType = @CpuCounterID
			BEGIN
			SET @SQLQuery = N'
				SELECT S.Name AS Series, CollectionDate, Value
				FROM
					PerformanceCounters.CPU PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1
				WHERE 
					CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
			END
		ELSE
			BEGIN
			SET @SQLQuery = N'
			SELECT S.Name AS Series, CollectionDate, (Value/1024)  AS Value
				FROM
					PerformanceCounters.Memory PC INNER JOIN
					[EventCollection].[MonitoringServers] S on PC.TerminalServerID = S.ID and S.PerformanceCountersEnabled = 1
				WHERE 
					CollectionDate >= @StartedOn AND CollectionDate <= @EndedOn'
			END
		IF(@TerminalServerIds IS NOT NULL AND @TerminalServerIds != '')
			BEGIN
				SET @SQLQuery = @SQLQuery + N' AND S.ID IN(' + @TerminalServerIds + ')'
			END
		EXEC sp_executesql @SQLQuery, @ParamDefinition, @StartedOn, @EndedOn
	END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_SiteID_SPGuid] on [dbo].[SharePointObjects]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_SiteID_SPGuid] ON [dbo].[SharePointObjects] ([SiteID], [SPGuid])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[SecurityAuditLogs]'
GO
ALTER TABLE [dbo].[SecurityAuditLogs] ADD CONSTRAINT [FK_SecurityAuditLogs_AuditActionTypes] FOREIGN KEY ([ActionID]) REFERENCES [dbo].[AuditActionTypes] ([ActionID])
ALTER TABLE [dbo].[SecurityAuditLogs] ADD CONSTRAINT [FK_SecurityAuditLogs_Principals] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Principals] ([PrincipalID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[RoleProperties]'
GO
ALTER TABLE [dbo].[RoleProperties] ADD CONSTRAINT [FK_RoleProperties_Roles] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[Roles] ([RoleID])
ALTER TABLE [dbo].[RoleProperties] ADD CONSTRAINT [FK_RoleProperties_LoadingInfo] FOREIGN KEY ([LoadingInfoID]) REFERENCES [dbo].[LoadingInfos] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[SiteCollectionAuditLoadTimes]'
GO
ALTER TABLE [dbo].[SiteCollectionAuditLoadTimes] ADD CONSTRAINT [FK_SiteCollectionsAuditLoadTimes_SharePointObjects] FOREIGN KEY ([SiteID]) REFERENCES [dbo].[SharePointObjects] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[AlertFarmAssociations]'
GO
ALTER TABLE [EventCollection].[AlertFarmAssociations] ADD CONSTRAINT [FK_AlertFarmAssociation_Farms] FOREIGN KEY ([FarmId]) REFERENCES [dbo].[Farms] ([ID])
ALTER TABLE [EventCollection].[AlertFarmAssociations] ADD CONSTRAINT [FK_AlertFarmAssociation_Alerts] FOREIGN KEY ([AlertId]) REFERENCES [EventCollection].[Alerts] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[AlertHistory]'
GO
ALTER TABLE [EventCollection].[AlertHistory] ADD CONSTRAINT [FK_AlertHistory_Farms] FOREIGN KEY ([FarmId]) REFERENCES [dbo].[Farms] ([ID])
ALTER TABLE [EventCollection].[AlertHistory] ADD CONSTRAINT [FK_AlertHistory_Alerts] FOREIGN KEY ([AlertId]) REFERENCES [EventCollection].[Alerts] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [EventCollection].[CollectionHistory]'
GO
ALTER TABLE [EventCollection].[CollectionHistory] ADD CONSTRAINT [FK_CollectionHistory_MonitoringServers] FOREIGN KEY ([ServerID]) REFERENCES [EventCollection].[MonitoringServers] ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [PerformanceCounters].[FarmPerformanceCountersState]'
GO
ALTER TABLE [PerformanceCounters].[FarmPerformanceCountersState] ADD CONSTRAINT [FK_FarmPerfromanceCountersState_Farms] FOREIGN KEY ([FarmID]) REFERENCES [dbo].[Farms] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [PerformanceCounters].[PerformanceCountersConfiguration]'
GO
ALTER TABLE [PerformanceCounters].[PerformanceCountersConfiguration] ADD CONSTRAINT [FK_PerformanceCountersConfiguration_Agent] FOREIGN KEY ([AgentID]) REFERENCES [EventCollection].[Agents] ([ID]) ON DELETE CASCADE
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering permissions on [dbo].[RoleProperties]'
GO
GRANT SELECT ON  [dbo].[RoleProperties] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetListsWithUniquePermissions]'
GO
GRANT EXECUTE ON  [dbo].[GetListsWithUniquePermissions] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetLoadedPermissionLevels]'
GO
GRANT EXECUTE ON  [dbo].[GetLoadedPermissionLevels] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetNumberOfPrincipals]'
GO
GRANT EXECUTE ON  [dbo].[GetNumberOfPrincipals] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSecurityAuditLogs]'
GO
GRANT EXECUTE ON  [dbo].[GetSecurityAuditLogs] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSharePointObjectsById]'
GO
GRANT EXECUTE ON  [dbo].[GetSharePointObjectsById] TO [spdockit_report_viewer]
GO
PRINT N'Altering permissions on [dbo].[GetSiteCollectionAuditLoadTimeBySiteId]'
GO
GRANT EXECUTE ON  [dbo].[GetSiteCollectionAuditLoadTimeBySiteId] TO [spdockit_report_viewer]
GO
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (0, 'Unknown')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (1, 'Permissions Granted')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (2, 'Permissions Removed')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (3, 'User Removed From Site Collection')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (4, 'Permission Inheritance Restored')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (5, 'Permission Inheritance Broken')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (6, 'Permission Level Created')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (7, 'Permission Level Deleted')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (8, 'Permission Level Modified')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (9, 'Permission Level Inheritance Broken')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (10, 'Group Created')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (11, 'Group Deleted')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (12, 'Group Member Added')
INSERT INTO AuditActionTypes (ActionID, Name) VALUES (13, 'Group Member Removed')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
UPDATE Options SET OptionValue = '7.0.0' WHERE OptionID = 14
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO Options (OptionID, OptionValue) VALUES (1003, '[1.1,1.2)')
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

