------------------------------------------------
--
-- QOS_Views SQL Script
--
-- Version:	2.0
-- Date	07 December 2011
--
-- Version history:
-- 1.0	Remko Dobber - This is the first release
-- 1.1	James Christensen - Added Origin field for better multi-tenancy support
-- 1.2	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.4	Adrian Spittles - Fixed issue that if the QOS name had a '_' in it the View would fail to create
-- 1.5	Adrian Spittles - Fixed issue that if the QOS name had a '(' or a ')' in it the View would fail to create
-- 1.6	Adrian Spittles - Added Error trapping for Drop statement for Views that Don't Exist 
-- 1.7	Adrian Spittles - Added Error trapping to catch RN tables that don't have samplestdev & samplerate fields
-- 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.9  Adrian Spittles - Removed compressed and inserttime fields from the View, for NIS DB V4.8 or above, these are no longer avialable in NMS 5.60
-- 2.0  Adrian Spittles - Changed all Select Statement to be Non-Locking
-- 2.1  Adrian Spittles - Added 'samplemax' field into the view for NIS DB V4.8 or above & Error trapping to catch RN tables that don't have samplemax fields
-- 2.2  Adrian Spittles - Added 'samplemax' field into the view for NIS verisons prior to NIS DB V4.8
-- 2.3  Adrian Spittles - Added 'inserttime' alias for NIS DB V4.8 or above
-- 2.4  James Christensen - Added more meaningful print statements so that it can be determmined which create view try worked and changed the query to determine the NIS DB version
-- xxxx Nilo Garcia - Adaptation for Oracle database
--
-- Description:
-- This sql procedure  generates easy-to-use views in the NIS for accessing raw qos data.
-- The intention of this script is that is is used instead of the qos_server custom probe.
-- The Script creates views named V_qosname, for example V_QOS_CPU_USAGE.
--
-- **** Please note that this is not supported by Nimsoft Support. This Script is intended to 
-- be used as is, feel free to modify for your own purpose. ****
--
-- Install Notes:
-- Check the name of your Nimsoft SLM database, it may be called NimbusSLM or NimsoftSLM
-- Modify the the first line of the Script "USE NimsoftSLM" to reflect the correct DB name.
--
-- Run the script in SQL Management Studio and it will create the views based on the current
-- contents of S_QOS_DATA.
--
-- The first time the script is run you will see a whole bunch of error similar to the one below,
-- this is normal as the views cannot be dropped as they have not been created before. Run the script
-- a second time and the errors should have gone away.
-- Error: "Cannot drop the view 'V_QOS_xxxxxxxxxx', because it does not exist..."
--
-- If new qos types are add added to the S_QOS_DATA table then this script will need to be rerun.
--
-- 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, minimum and maximum 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 being 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
--
------------------------------------------------

declare
  V_QOS_NAME varchar2(500);
  V_VIEW varchar2(500);
  V_CVIEW varchar2(4000);
  V_EXISTE_OBJETO integer;
  V_VER numeric(10,4);

  cursor c_qos_cursor is
    select distinct QOS,R_TABLE
      from S_QOS_DATA
     order by 2;

  cursor C_TAB_ABBREV is
    select 'ORACLE' WORD, 'ORCL' ABBREV from DUAL
    union
    select 'PERCENT', 'PERC' from DUAL
    union
    select 'TABLESPACE', 'TBSP' from DUAL
    union
    select 'PRINCIPAL', 'PRINC' from DUAL
    union
    select 'PROCESSES', 'PROCS' from DUAL
    union
    select 'DATABASE', 'DB' from DUAL
    union
    select 'SESSIONS', 'SESS' from DUAL
    union
    select '__', '_' from DUAL
    union
    select 'UTILIZATION', 'UTLZTN' from DUAL
    union
    select 'STATISTICS', 'STAT' from DUAL
    union
    select 'CURRENT', 'CURR' from DUAL
    union
    select 'CONNECTION', 'CONN' from DUAL
    union
    select 'EXTENSION', 'EXT' from DUAL
    union
    select 'REQUEST', 'REQ' from DUAL
    union
    select 'SUCCEEDED', 'SUCC' from DUAL
    union
    select 'TOTAL', 'TOT' from DUAL
    union
    select 'OBJECT', 'OBJ' from DUAL
    union
    select 'LOGMON_VARIABLE', 'LOGMON' from DUAL
    union
    select 'MEMORY', 'MEM' from DUAL;

BEGIN

  select MODULEVERSION into V_VER from TBNVERSION where MODULENAME = 'NIS_SLM';

  for r_qos_cursor in c_qos_cursor loop

    -- Check QOS name for '-' character & replace with '_' if exist
    V_QOS_NAME := translate(r_qos_cursor.qos, '-/()%:.#', '____');
    
    -- Create view
    v_view := 'V_' || V_QOS_NAME;

    -- If some view name was length more than 30 characters,
    -- we'll try to substitute some words for abbreviations
    if length(V_VIEW) > 30 then
      for R_TAB_ABBREV in C_TAB_ABBREV LOOP
        v_view := regexp_replace(v_view, r_tab_abbrev.word, r_tab_abbrev.abbrev);
      end LOOP;
    end if;

    -- NMS V5.60 and above uses new DB version 4.800 and above. compressed & inserttime fields
    -- are missing from this new format.

    -- Test to see what version of the NIS DB is being used.
    if V_VER < 4.8 then
      V_CVIEW := 'CREATE VIEW ' || V_VIEW || ' AS ' ||
        'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.compressed,D.tz_offset,D.inserttime ' ||
        'FROM S_QOS_DATA Q JOIN ' || R_QOS_CURSOR.R_TABLE || ' D ON Q.table_id=D.table_id';
    else
      V_CVIEW := 'CREATE VIEW ' || V_VIEW || ' AS ' ||
        'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.samplemax,D.tz_offset,D.sampletime as inserttime ' ||
        'FROM S_QOS_DATA Q JOIN ' || R_QOS_CURSOR.R_TABLE || ' D ON Q.table_id=D.table_id';
  	END if;
		
    SELECT COUNT(1) INTO V_EXISTE_OBJETO FROM USER_VIEWS WHERE VIEW_NAME = V_VIEW;

    if V_EXISTE_OBJETO > 0 then
      execute immediate 'DROP VIEW ' || V_VIEW;
    end if;

    BEGIN
      -- Create the View, Catch tables that don't have samplestdev & samplerate fields
      EXECUTE IMMEDIATE V_CVIEW;
      DBMS_OUTPUT.PUT_LINE('Created View: ' || V_VIEW);
    EXCEPTION
      when OTHERS then
        if V_VER < 4.8 then
          V_CVIEW := 'CREATE VIEW ' || V_VIEW || ' AS ' ||
            'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.compressed,D.tz_offset,D.inserttime ' ||
            'FROM S_QOS_DATA Q JOIN ' || R_QOS_CURSOR.R_TABLE || ' D ON Q.table_id=D.table_id';
        else
          V_CVIEW := 'CREATE VIEW ' || V_VIEW || ' AS ' ||
            'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.samplestdev,D.samplerate,D.tz_offset,D.sampletime as inserttime ' ||
            'FROM S_QOS_DATA Q JOIN ' || R_QOS_CURSOR.R_TABLE || ' D ON Q.table_id=D.table_id';
        END if;
        
        begin
          execute immediate V_CVIEW;
          DBMS_OUTPUT.PUT_LINE('Created View without samplemax: ' || V_VIEW);
        exception
          when OTHERS then
            V_CVIEW := 'CREATE VIEW ' || V_VIEW ||' AS ' ||
              'SELECT Q.source,Q.target,Q.origin,Q.robot,Q.probe,D.sampletime,D.samplevalue,D.tz_offset,D.sampletime as inserttime ' ||
              'FROM S_QOS_DATA Q JOIN ' || R_QOS_CURSOR.R_TABLE || ' D ON Q.table_id=D.table_id';		
               
            execute immediate V_CVIEW;
            DBMS_OUTPUT.PUT_LINE('Created View without samplemax, samplestdev and samplerate: ' || V_VIEW);
        end;
    end;

    -- Useful Print statements for debuging this SQL procedure, just uncomment to use
    -- dbms_output.put_line(v_ver);
    -- dbms_output.put_line('qos: ' || r_qos_cursor.qos || ' ' || R_QOS_CURSOR.R_TABLE || ' ' || v_view );
    -- dbms_output.put_line(v_cview);
    
    -- This is executed as long as the previous fetch succeeds.
	end loop;

END;