-------------------------------------------------------------------------------
-- QOS_Views SQL Script
--
-- Version:	3.1
-- Date	24 February 2016
--
-- ****************************************************************************
-- ****            NOTE: This is NOT supported by CA Support.              ****
-- ****  Please use the CA Communities Forum for questions and assistance  ****
-- ****  http://communities.ca.com/community/ca-infrastructure-management  ****
-- ****************************************************************************
--
-- Description
-- -----------
-- This MS SQL procedure generates easy-to-use views in the NIS for accessing
-- QOS data. These views are a combination of the RAW, Hourly & Daily summary
-- tables.
--
-- The Script creates views named V_qosname, for example V_QOS_CPU_USAGE. These
-- view names are truncated to 128 characters long.
--
-- Supported Platforms
-- -------------------
-- This Script is designed to be use with UIM/NMS V7.6 or above that connects
-- to a Microsoft SQL backend database. 
-- It's been tested on NMS V7.6, UIM V8.0, V8.1, V8.2 & V8.31.
--
-- Please use QOS_View V2.9 for previous versions UIM/NMS.
--
-- Version History & Usage Examples
-- --------------------------------
-- This can now be found at the end of this script.
--
-- Install Notes
-- -------------  
-- Run the script in SQL Management Studio and it will create the spn_QOS_Views
-- stored procedure and will then execute the new stored procedure which will
-- create a full set of Views based on the current contents of S_QOS_DATA.
--
-- The stored procedure can then be run as needed to create new views is new
-- QOS metic types have been created. spn_QOS_Views could also be scheduled to
-- run on a regular basis if new metrics are being added often.
--
-- Example command that can be run from UIM SLM: EXEC spn_QOS_Views
--
-- ****************************************************************************
-- ************* Review this section before executing this script *************
-- ****************************************************************************
--
USE CA_UIM  -- Change this DB name to match your UIM DB name (e.g. NimsoftSLM)
GO
-------------------------------------------------------------------------------

-- Check to see if spn_QOS_Views already exists.
DECLARE @view_name varchar(255),
		@cmd varchar(255),
		@drop_views bit
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'spn_QOS_Views')
BEGIN
	-- If the spn_QOS_Views doesn't exist just flag the old QOS views to be dropped
	SET @drop_views = 1
END
ELSE
BEGIN
	-- If it exists, check to see if it is the same version.
	IF NOT EXISTS (SELECT 1 FROM sys.sql_modules WHERE definition LIKE '%-- QOS_Views V3.1%')
	BEGIN
		-- If it's a different version, Flag old QOS views to be dropped
		-- and drop spn_QOS_Views
		SET @drop_views = 1
		DROP PROCEDURE spn_QOS_Views
	END
	ELSE
		-- If it's the same version, keep the Views and just drop spn_QOS_Views
		DROP PROCEDURE spn_QOS_Views
END

-- Drop old QOS views if flagged
IF @drop_views = 1
BEGIN
	DECLARE qos_cursor CURSOR FOR
	SELECT name from sys.objects where type = 'v' and name LIKE 'V_QOS_%' ORDER BY 1

	OPEN qos_cursor
		-- Perform the first fetch.
		FETCH NEXT FROM qos_cursor INTO @view_name
 
		-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @cmd = 'DROP VIEW ' + @view_name
			EXEC (@cmd)
		FETCH NEXT FROM qos_cursor INTO @view_name
		END

	CLOSE qos_cursor
	DEALLOCATE qos_cursor
END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spn_QOS_Views
AS
BEGIN
	-----------------
	-- QOS_Views V3.1
	-----------------
	
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with
	-- SELECT statements.
	SET NOCOUNT ON;

	DECLARE @qos varchar(255), 
			@pos int,
			@count_views int,
			@r_table varchar(255), 
			@view varchar(255), 
			@cview varchar(4096), 
			@ver numeric (10,4), 
			@msg varchar(4096),
			@cmdpart1 varchar(1024),
			@cmdpart2 varchar(1024),
			@cmdpart3 varchar(1024),
			@cmdpart4 varchar(1024)

	-- Check the NIM_SLM database verison. This script only supports UIM 7.6 and above		
	SELECT @ver = ModuleVersion FROM tbnVersion WITH (NOLOCK) where ModuleName = 'NIS_SLM'
	IF @ver < 4.85 or (SELECT count(1) FROM sys.objects WHERE type = 'U' AND name Like 'DN_QOS_DATA_%') = 0
	BEGIN
		PRINT 'This olded version of UIM/NMS is not supported by this version of QOS_Views.'
		PRINT 'Please use QOS_Views V2.9 for older versions of NMS'
	END
	ELSE
	BEGIN
		CREATE TABLE view_temp (message varchar(max))
		DECLARE qos_cursor CURSOR FOR
		SELECT DISTINCT qos,r_table FROM S_QOS_DATA WITH (NOLOCK)
		ORDER BY 2

		OPEN qos_cursor

			-- Perform the first fetch.
			FETCH NEXT FROM qos_cursor INTO @qos, @r_table 
 
			-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
			WHILE @@FETCH_STATUS = 0
			BEGIN

				-- Doctor up the QOS names removing and replacing characters that may cause issues
				SELECT @qos= REPLACE(@qos,'+','_PLUS_')
				SELECT @qos= REPLACE(@qos,'%','_PERC_')
				SELECT @qos= REPLACE(@qos,'_-_','_')
				SELECT @qos= REPLACE(@qos,'''','_')
				SELECT @qos= REPLACE(@qos,'(','_')
				SELECT @qos= REPLACE(@qos,')','_')
				SELECT @qos= REPLACE(@qos,'-','_')
				SELECT @qos= REPLACE(@qos,':','')
				SELECT @qos= REPLACE(@qos,'.','')
				SELECT @qos= REPLACE(@qos,'/','_')
				SELECT @qos= REPLACE(@qos,'\','_')
				SELECT @qos= REPLACE(@qos,'___','_') 
				SELECT @qos= REPLACE(@qos,'__','_') 
				
				-- Doctor up the QOS names truncating large QOS names to deal with Maximum View names of 128 characters
				IF LEN(@qos) > 126
				BEGIN
					SELECT @qos= LEFT(@qos, 126)
				END

				-- Create view
				SET @view = 'V_'+@qos
				PRINT 'Working on view for ' +@qos
		
				-- only create the view if it does not already exist
				IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'v' AND name = @view)
				BEGIN
					-- Create the base view
					SET @cmdpart1 = 'CREATE VIEW '+@view+' AS '+
						'SELECT Q.ci_metric_id,Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.tz_offset,D.sampletime as inserttime '
					SET @cmdpart2 = 'FROM S_QOS_DATA Q JOIN (select table_id, sampletime, samplevalue, tz_offset, sampletime as inserttime '
					SET @cmdpart3 = 'UNION ALL select table_id, sampletime,  sampleavg as samplevalue, tz_offset, sampletime as inserttime '
					SET @cmdpart4 = 'UNION ALL select table_id, sampletime,  sampleavg as samplevalue, tz_offset, sampletime as inserttime '
					SET @msg = 'Created Base'
			
					-- Add samplestdev & samplerate fields if existing	
					IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(@r_table) and name = 'samplestdev')
					BEGIN
						SET @cmdpart1 = @cmdpart1 + ', D.samplestdev, D.samplerate '
						SET @cmdpart2 = @cmdpart2 + ', samplestdev, samplerate '
						SET @cmdpart3 = @cmdpart3 + ', samplestdev, 3600 as samplerate '
						SET @cmdpart4 = @cmdpart4 + ', samplestdev, 86400 as samplerate '
						SET @msg = @msg + '+StDev+Rate'
					END
			
					-- Add samplemax field if existing
					IF EXISTS (select 1 from sys.columns where object_id = OBJECT_ID(@r_table) and name = 'samplemax')
					BEGIN
						SET @cmdpart1 = @cmdpart1 + ', D.samplemax '
						SET @cmdpart2 = @cmdpart2 + ', samplemax '
						SET @cmdpart3 = @cmdpart3 + ', samplemax '
						SET @cmdpart4 = @cmdpart4 + ', samplemax '
						SET @msg = @msg + '+Max'
					END

					-- Compile the CMD lines together into a single statement
					SET @cview = @cmdpart1 + @cmdpart2 + 'from ' + @r_table + ' WITH (NOLOCK) ' + @cmdpart3 + 'from HN_QOS_DATA_' +
						RIGHT(@r_table,4) + ' WITH (NOLOCK) ' + @cmdpart4 + 'from DN_QOS_DATA_' + RIGHT(@r_table,4) +
						' WITH (NOLOCK)) D ON Q.table_id=D.table_id'
	
					-- Execute the built command to create the View
					BEGIN TRY
						EXEC(@cview)
						-- Create a log message with the View name and the complete command used to create it.
						SET @msg = @msg + ': ' + @view + ' - ' + @cview
					END TRY
					BEGIN CATCH
						-- Create a log message with the View name and the Error Message
						SET @msg = 'Error: ' + @view + ' - ' + ERROR_MESSAGE()
					END CATCH;
				-- Output a log message to the view_temp table
				Insert into view_temp VALUES (@msg)
				END	

				-- This is executed as long as the previous fetch succeeds.
				FETCH NEXT FROM qos_cursor INTO @qos, @r_table
			END
		CLOSE qos_cursor
		DEALLOCATE qos_cursor

		-- Show what's been created
		SELECT @count_views = count(1) FROM view_temp
		IF @count_views = 0
		BEGIN
			SELECT 'No New Views Created' as message
		END
		ELSE
		BEGIN
			SELECT * from view_temp
		END
		DROP TABLE view_temp
	END
END
GO

EXEC spn_QOS_Views

-------------------------------------------------------------------------------
-- Usage Examples:
-- When you want to see the raw CPU data for a server called Apollo, use a 
-- query like this:
--
-- SELECT * FROM V_QOS_CPU_USAGE
-- WHERE source='Apollo'
--
-- To get the average, min and max values measured in the past hour, use this:
--
-- select AVG(samplevalue) as avg ,MIN(samplevalue) as min,MAX(samplevalue) as
-- max from V_QOS_CPU_USAGE
-- where sampletime>GETDATE()-'01:00:00'
-- order by 1 desc
--
-- Top 20 servers sorted by CPU Usage, assuming data is collected every 5 mins
--
-- SELECT TOP(20) source,target,sampletime,samplevalue,samplestdev,samplerate
-- FROM V_QOS_CPU_USAGE
-- WHERE (sampletime > GETDATE() - '00:05:00') AND (source = target)
-- ORDER BY samplevalue DESC
--
-------------------------------------------------------------------------------
--
-- Version history:
--
-- 3.1  Adrian Spittles - Converted the script into a Stored Procedure that can
--      be executed with a single command from within the SQL Query tool in SLM
--      or easily scheduled to be run on a regular basis. Added error trapping
--      and improved creation and error logging output.
-- 3.0  Adrian Spittles - Dropped support for UIM/NMS <= V7.5 (DB V4.80).
--      Simplified database creation logic and removed most Try/Catch
--      statements and replaced with if Exist statements to improve script
--      performance and readability. Added HN_QOS_DATA_XXXX & DN_QOS_DATA_XXXX
--      tables to the views to allow customers that summarise their data to
--      Hourly and Daily archives to use them in reporting and dashboards.
-- 2.9  Adrian Spittles - Added check to look for large QOS names, truncating
--      them if needed.
-- 2.8  Adrian Spittles - Added ci_metric_id to the Views so as to be able to
--      use the CM_COMPUTER_SYSTEMS table to do RN_QOS_DATA_xxxx lookups
-- 2.7  James Christensen - Replaced the individual character replacement
--      blocks with REPLACE statements. Added the replacement of the following
--      characters (+,_-_,and __). Added the creation of a temporary table and
--      the inserting of status messages for debugging. Added a final select
--      from the S_QOS_DATA table so the row count can be compared to the row
--      count from the select from the temporary table to verify that all QOS
--      objects were processed.
-- 2.5  James Christensen - Added some additional edits received from a
--      customer (see the REPLACE statements). Also added the ability to get
--      feedback from the script when running it from the Service Level Manager
--      SQL Query tool. NOTE: This does not seem to run from the UMP SLM
--      portlet -> SQL Query tool.
-- 2.4  James Christensen - Added more meaningful print statements so that it
--      can be determined which create view try worked and changed the query
--      to determine the NIS DB version
-- 2.3  Adrian Spittles - Added 'inserttime' alias for NIS DB V4.8 or above
-- 2.2  Adrian Spittles - Added 'samplemax' field into the view for NIS
--      verisons prior to NIS DB V4.8
-- 2.1  Adrian Spittles - Added 'samplemax' field into the view for NIS DB V4.8
--      or above & Error trapping RN tables that don't have samplemax fields
-- 2.0  Adrian Spittles - Changed all Select Statement to be Non-Locking
-- 1.9  Adrian Spittles - Removed compressed and inserttime fields from Views
--      for NIS DB V4.8 or above, these are no longer available in NMS 5.60
-- 1.8	James Christensen - Fixed issue that if the QOS name had a '%' or a ':'
--      or a '.' in it the View would fail to create
-- 1.7	Adrian Spittles - Added Error trapping to catch RN tables that don't
--      have samplestdev & samplerate fields
-- 1.6	Adrian Spittles - Added Error trapping for Drop statement for Views
--      that Don't Exist 
-- 1.5	Adrian Spittles - Fixed issue that if the QOS name had a '(' or a ')'
--      in it the View would fail to create
-- 1.4	Adrian Spittles - Fixed issue that if the QOS name had a '_' in it the
--      View would fail to create
-- 1.3	Adrian Spittles - Added Robot and Probe fields for backward
--      compatibility support with QOS_Server probe
-- 1.2	Adrian Spittles - Fixed issue that if the QOS name had a '-' in it the
--      View would fail to create
-- 1.1	James Christensen - Added Origin field for better multi-tenancy support
-- 1.0	Remko Dobber - This is the first release
-------------------------------------------------------------------------------