/*
Description: This SQL script creates the HECUSTDB Database incl. all necessary
			 VIEWS for automation of the Host Europe proactive Windows Patchmanagement
			 during the Linux Patchmanagement System. It will also Grant Access
			 to the master DB for SQL Server state information to check the WSUS Database
			 (SUSDB) with Nagios and create the user 'he-patchmgmt' with datareader
			 access to the HECUSTDB.
Author:		 SLA
Date:		 14.06.2012
*/
/****** Object: Database [HECUSTDB] for Windows Patchmanagement ******/
CREATE DATABASE HECUSTDB
GO

/****** Object: Schema [PUBLIC_VIEWS] for Windows Patchmanagement VIEWS ******/
USE [HECUSTDB]
GO
CREATE SCHEMA [PUBLIC_VIEWS] AUTHORIZATION [dbo]
GO

/****** Object: View [PUBLIC_VIEWS].[vHostEurope] for Windows Patchmanagement ******/
USE [HECUSTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [PUBLIC_VIEWS].[vHostEurope]
AS
SELECT 
TG.Name AS 'Customer',
CT.FullDomainName AS 'FQDN',
CT.IPAddress AS 'IP',
CT.LastReportedStatusTime AS 'LastSyncTime',
CSFMU.Downloaded,
CSFMU.NotInstalled,
CSFMU.Failed,
CSFMU.InstalledPendingReboot,
CSFMU.Downloaded + CSFMU.NotInstalled AS 'TotalNeededUpdates'
FROM 
SUSDB.dbo.tbComputerTarget CT
INNER JOIN SUSDB.dbo.tbComputerSummaryForMicrosoftUpdates CSFMU ON CT.TargetID = CSFMU.TargetID
INNER JOIN SUSDB.dbo.tbTargetInTargetGroup TITG ON CT.TargetID = TITG.TargetID
INNER JOIN SUSDB.dbo.tbTargetGroup TG ON TITG.TargetGroupID = TG.TargetGroupID
GO

/****** Object: View [PUBLIC_VIEWS].[vHostEuropeUpdates] for Windows Patchmanagement ******/
USE [HECUSTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [PUBLIC_VIEWS].[vHostEuropeUpdates]
AS
SELECT
TG.Name AS 'customer_id',
CT.FullDomainName AS 'hostname',
CT.IPAddress AS 'ipaddr',
UT.Title AS 'description'
FROM
SUSDB.dbo.tbComputerTarget CT
INNER JOIN SUSDB.dbo.tbTargetInTargetGroup TITG ON CT.TargetID = TITG.TargetID
INNER JOIN SUSDB.dbo.tbTargetGroup TG ON TITG.TargetGroupID = TG.TargetGroupID 
INNER JOIN SUSDB.dbo.tbUpdateStatusPerComputer USPC ON CT.TargetID = USPC.TargetID AND (USPC.SummarizationState IN (2,3) AND (NOT (USPC.SummarizationState IN (0,1,4,5,6))))
INNER JOIN SUSDB.dbo.tbUpdate U ON USPC.LocalUpdateID = U.LocalUpdateID
INNER JOIN SUSDB.PUBLIC_VIEWS.vUpdateText UT ON U.UpdateID = UT.UpdateID AND UT.LocaleID = '1033'
GO

/****** Object: GRANT VIEW SERVER STATE for Nagios monitoring Server state Access ******/
USE [master]
GRANT VIEW SERVER STATE TO public
GO

/****** Object: CREATE USER 'he-patchmgmt' on HECUSTDB and add datareader access ******/
USE [HECUSTDB]
GO
CREATE USER [he-patchmgmt] FOR LOGIN [he-patchmgmt]
GO
USE [HECUSTDB]
GO
EXEC sp_addrolemember N'db_datareader', N'he-patchmgmt'
GO