

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/LMGetAlertDataSetForCI.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/LMGetAlertDataSetForCI.sp,v $ $Id: LMGetAlertDataSetForCI.sp,v 1.7.2.5.20.1 2021/06/10 19:33:14 snandhini Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='LMGetAlertDataSetForCI')
BEGIN
	print '>>> Drop Stored Procedure: LMGetAlertDataSetForCI <<<'
	drop procedure LMGetAlertDataSetForCI
END
IF EXISTS (select * from GxQscripts where name='LMGetAlertDataSetForCI')
	delete from GxQscripts where name = 'LMGetAlertDataSetForCI'
GO

IF EXISTS (select * from GXDBVersions where aliasname='LMGetAlertDataSetForCI')
	delete from GXDBVersions where aliasname = 'LMGetAlertDataSetForCI'
GO
print '... Creating Procedure: LMGetAlertDataSetForCI'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure LMGetAlertDataSetForCI
  @i_xml XML 
AS
  DECLARE @xmlStr xml
SET NOCOUNT ON
DECLARE @startIndex INTEGER = ISNULL((SELECT ref.value('@startIndex','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @endIndex INTEGER = ISNULL((SELECT ref.value('@endIndex','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),1000)
DECLARE @monitoringPolicyId INTEGER = ISNULL((SELECT ref.value('@monitoringPolicyId','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @requestType INTEGER = ISNULL((SELECT ref.value('@type','INTEGER') AS data FROM @i_xml.nodes('LogMonitoring_GenerateDataSetForCI') R ( ref )),0)
DECLARE @templateId INTEGER
SET @templateId = ISNULL((SELECT templateId FROM APP_MonitorTemplateAssoc WHERE monitorPolicyId = @monitoringPolicyId),0)
DECLARE @count INTEGER
SET @count = ISNULL((SELECT TOP 1 value FROM GxGlobalParam WHERE name like 'LMAlertIndexBucket' AND modified = 0),25)
--Logic to generate GUI audit
DECLARE @locId          integer = 0
DECLARE @startTime      integer = 0
DECLARE @endTime        integer = 0
DECLARE @lastOpId       integer = 0
DECLARE @lastOpIdOperation integer = 0
DECLARE @err            integer = 0
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @ColumnRow TABLE
(columnid INTEGER,
 rowId INTEGER,
columnName NVARCHAR(128),
columnXML XML)
CREATE TABLE #ColumnRowFinal
(columnid INTEGER,
 rowId INTEGER,
columnName NVARCHAR(128),
columnXML XML,
columnData NVARCHAR(max),
ColAndData XML)
CREATE CLUSTERED INDEX ColumnRowFinal_rowId_idx ON #ColumnRowFinal (rowid)
DECLARE @templ XML = CONVERT(XML,(SELECT templateXML FROM APP_Template WHERE templateId = @templateId))
DECLARE @minColId INTEGER = (SELECT MIN(columnId) FROM APP_TemplateColumn WHERE templateId = @templateId)
INSERT INTO @ColumnRow(columnid,columnXML)
SELECT R.col.value('@index','INTEGER') as colIndex,
		R.col.query('.')
FROM
@templ.nodes('LogMonitoring_TemplateXML/columns') as R(col)
UPDATE ColumnDataTemp
SET ColumnDataTemp.columnName = tempCol.columnName
FROM @ColumnRow ColumnDataTemp INNER JOIN APP_TemplateColumn tempCol
	ON ColumnDataTemp.columnid = (tempCol.columnId - @minColId) AND templateId = @templateId
DECLARE @startFromTime INTEGER = 0
DECLARE @now INTEGER = dbo.GEtUnixTime(GetUTCdate())
SET @startFromTime = ISNULL((SELECT modified FROM APP_MonitoringPolicyProp WHERE attrName = 'Do not monitor old data' AND attrVal = 1 AND monitorPolicyId = @monitoringPolicyId ),0)
DECLARE @minId INTEGER
DECLARE @maxId INTEGER
SET @minId = (SELECT MIN(AlertId) FROM NTNotificationsForCI WHERE AlertId  > @startIndex AND Created > @startFromTime)
SET @maxId = ISNULL((SELECT MAX(TopCount.AlertId) FROM
							 (SELECT DISTINCT TOP (@count) NTNotificationsForCI.AlertId FROM NTNotificationsForCI WHERE NTNotificationsForCI.AlertId >= @minId order by NTNotificationsForCI.AlertId) as TopCount
									),0)
--TableColAndColNameMap --Below Macros are defines in LogMonitoringConst.h
DECLARE @TableColAndColNameMap TABLE(tableColumn NVARCHAR(1024),tempColumn NVARCHAR(1024)) INSERT INTO @TableColAndColNameMap VALUES('alertId','Alert ID') INSERT INTO @TableColAndColNameMap VALUES('NTNotificationID','NTNotificationID') INSERT INTO @TableColAndColNameMap VALUES('EscalationLevel','EscalationLevel') INSERT INTO @TableColAndColNameMap VALUES('DetectedTimeUTC','Alert Detected Date') INSERT INTO @TableColAndColNameMap VALUES('DetectedTimeUTC','Alert Detected Time') INSERT INTO @TableColAndColNameMap VALUES('Severity','Alert Severity') INSERT INTO @TableColAndColNameMap VALUES('JobId','JobId') INSERT INTO @TableColAndColNameMap VALUES('CommCell','CommCell') INSERT INTO @TableColAndColNameMap VALUES('CommCellID','CommCell ID') INSERT INTO @TableColAndColNameMap VALUES('Client','Client') INSERT INTO @TableColAndColNameMap VALUES('ClientID','Client ID') INSERT INTO @TableColAndColNameMap VALUES('AlertName','Alert Name') INSERT INTO @TableColAndColNameMap VALUES('AlertType','Alert Type') INSERT INTO @TableColAndColNameMap VALUES('AlertCriteria','Alert Criteria')INSERT INTO @TableColAndColNameMap VALUES('Description','Alert Description')
DECLARE @templateColumns TABLE (colindex INTEGER,columnid INTEGER,ColumnName NVARCHAR(MAX), DataType INTEGER) DECLARE @templateXML XML DECLARE @colindex INTEGER DECLARE @userColName INTEGER DECLARE @columnid INTEGER DECLARE @ColumnName NVARCHAR(MAX) DECLARE @DataType INTEGER DECLARE @tableName NVARCHAR(1024) DECLARE @cursorName NVARCHAR(1024) DECLARE @colName NVARCHAR(1024)DECLARE @query NVARCHAR(MAX)
SET @templateXML = CONVERT(XML,(SELECT templateXML FROM APP_Template WHERE templateId = @templateId)) INSERT INTO @templateColumns SELECT ref.value('@index','INTEGER'), 0, ref.value('@columnName','NVARCHAR(1024)'), ref.value('@primaryDataType','INTEGER') FROM @templateXML.nodes('LogMonitoring_TemplateXML/columns') R(ref) SET @userColName = 0 SET @tableName  = '#testAlertTable' SET @cursorName = 'testAlertTableCur' IF object_id('tempdb.dbo.'+@tableName) IS NOT NULL DROP TABLE #testAlertTable CREATE TABLE #testAlertTable (column0 INTEGER) IF ( CURSOR_STATUS('global', @cursorName) >= 0 OR CURSOR_STATUS('local', @cursorName) >= 0 ) CLOSE testAlertTableCur IF ( CURSOR_STATUS('global',@cursorName) = -1 OR CURSOR_STATUS('local',@cursorName) = -1 ) DEALLOCATE testAlertTableCur DECLARE testAlertTableCur CURSOR FOR SELECT colindex,columnid,ColumnName,DataType FROM @templateColumns WHERE colindex > 0 ORDER BY colindex OPEN testAlertTableCur FETCH NEXT FROM testAlertTableCur INTO @colindex,@columnid,@ColumnName,@DataType WHILE @@FETCH_STATUS = 0 BEGIN SET @colName = (CASE @userColName WHEN 1 THEN @ColumnName WHEN 0 THEN 'column'+ CONVERT(NVARCHAR(20),@colindex) END) SET @query  =  'ALTER TABLE '+@tableName+' ADD '+@colName+ ' ' + (CASE @DataType WHEN 0 THEN 'INTEGER' WHEN 1 THEN 'NVARCHAR(MAX)' ELSE 'NVARCHAR(MAX)' END) EXECUTE sp_executesql @query FETCH NEXT FROM testAlertTableCur INTO @colindex,@columnid,@ColumnName,@DataType END IF ( CURSOR_STATUS('global', @cursorName) >= 0 OR CURSOR_STATUS('local', @cursorName) >= 0 ) CLOSE testAlertTableCur IF ( CURSOR_STATUS('global',@cursorName) = -1 OR CURSOR_STATUS('local',@cursorName) = -1 )    DEALLOCATE testAlertTableCur
CREATE UNIQUE CLUSTERED INDEX testAlertTable_column0_idx ON #testAlertTable(column0)
IF @requestType = 0
BEGIN
	DECLARE @tokXML XML
	IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#tokenValList'))
			 DROP TABLE #tokenValList
	IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#tokValuePair'))
			 DROP TABLE #tokValuePair
	IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#tokenColumns'))
			 DROP TABLE #tokenColumns
	CREATE TABLE #tokenValList(alertId INTEGER,tokenId INTEGER,ColIndex INTEGER,tokenText NVARCHAR(1024),ColumnName NVARCHAR(1024),fieldName NVARCHAR(1024),tokenValue NVARCHAR(MAX))
	CREATE TABLE #tokValuePair(alertId INTEGER,tok INTEGER, value NVARCHAR(MAX))
	CREATE TABLE #tokenColumns(columnId INTEGER, columnName NVARCHAR(1024),tokenValue NVARCHAR(MAX))
	INSERT INTO #tokenValList(alertId,tokenId)
	SELECT AlertId,X.c.value('@tokenId','INTEGER')
	FROM NTNotificationsForCI
	CROSS APPLY NTNotificationsForCI.TokenValList.nodes('CVGui_DetectedAlertsData/details/tokVal') as X(c)
	WHERE AlertId >= @minId AND AlertId <=@maxId
	INSERT INTO #tokValuePair(alertId,tok,value)
	SELECT AlertId,X.c.value('@tokenId','INTEGER'),j.j.value('@val','NVARCHAR(MAX)')
	FROM NTNotificationsForCI
	CROSS APPLY NTNotificationsForCI.TokenValList.nodes('CVGui_DetectedAlertsData/details/tokVal') as X(c)
	CROSS APPLY X.c.nodes('tokenValueList') j(j)
	WHERE AlertId >= @minId AND AlertId <=@maxId
DELETE FROM #tokenValList where tokenid = (1142 | (CAST(POWER(2, 24) AS BIGINT) * 74))
DELETE FROM #tokValuePair WHERE tok = (1142 | (CAST(POWER(2, 24) AS BIGINT) * 74))
	UPDATE NTNotificationsForCI
	SET Description = dbo.EncodeInvalidXMLChar(Description)
	WHERE AlertId >= @minId AND AlertId <=@maxId
	UPDATE #tokenValList SET tokenText = dbo.EncodeInvalidXMLChar(dbo.fn_EvFormatEventMsgText(0,0,tokenId,0,2))
	DELETE FROM #tokenValList WHERE tokenText LIKE '%/%' OR TokenValue LIKE '%-%'
	UPDATE #tokenValList SET fieldName = tokenText WHERE TokenId = 0
	UPDATE #tokenValList SET ColumnName = RTRIM(LTRIM(LOWER(tokenText))) WHERE TokenId <> 0
	UPDATE #tokenValList SET ColumnName = LTRIM(REPLACE(ColumnName,'<','')) WHERE TokenId <> 0
	UPDATE #tokenValList SET ColumnName = RTRIM(REPLACE(ColumnName,'>','')) WHERE TokenId <> 0
	UPDATE #tokenValList SET ColumnName = dbo.ProperCase(ColumnName) WHERE TokenId <> 0
	UPDATE #tokenValList SET fieldName = RTRIM(LTRIM(LOWER(tokenText))) WHERE TokenId <> 0
	UPDATE #tokenValList SET fieldName = LTRIM(REPLACE(fieldName,'<','')) WHERE TokenId <> 0
	UPDATE #tokenValList SET fieldName = RTRIM(REPLACE(fieldName,'>','')) WHERE TokenId <> 0
	UPDATE #tokenValList SET fieldName = RTRIM(REPLACE(fieldName,' ','_')) WHERE TokenId <> 0
	UPDATE #tokenValList SET FieldName = 'alert_'+FieldName WHERE TokenId <> 0
	UPDATE tokFInal SET tokFInal.tokenValue = dbo.EncodeInvalidXMLChar(tokVal.value) + ','
	FROM #tokValuePair tokVal INNER JOIN #tokenValList tokFInal ON tokFinal.tokenId = tokVal.tok AND tokFInal.alertId = tokVal.alertId
	UPDATE #tokenValList SET tokenValue = SUBSTRING(tokenValue,0,LEN(tokenValue)) WHERE LEN(tokenValue) > 2
	UPDATE tokFInal
	SET tokFInal.ColIndex = (tempCol.columnId - @minColId)
	FROM #tokenValList tokFInal INNER JOIN APP_TemplateColumn tempCol
	ON tokFInal.ColumnName = tempCol.columnName AND tempCol.templateId = @templateId
	DECLARE @alertId INTEGER
	DECLARE @id INTEGER = 0
	DECLARE @columnCount INTEGER =  (SELECT COUNT(*) FROM @TableColAndColNameMap)
	DECLARE @colNameTemp NVARCHAR(1024)
	DECLARE @insertStaticCol NVARCHAR(MAX) = 'INSERT INTO #testAlertTable '
	DECLARE @selectStaticCol NVARCHAR(MAX) = ' SELECT '
	DECLARE @staticColSel NVARCHAR(MAX) = ''
	DECLARE @staticColIns NVARCHAR(MAX) = ''
	DECLARE @staticColSel1 NVARCHAR(MAX) = ''
	DECLARE @staticColIns1 NVARCHAR(MAX) = ''
	DECLARE @maxCol INTEGER = (SELECT MAX(columnId) FROM APP_TemplateColumn WHERE templateId = @templateId)
	WHILE @id < @columnCount
	BEGIN
		SET  @colNameTemp = (SELECT columnName FROM APP_TemplateColumn WHERE templateId = @templateId AND columnId = (@minColId + @id))
		IF EXISTS (SELECT 1 FROM @TableColAndColNameMap WHERE tempColumn = @colNameTemp)
		BEGIN
			SET @colNameTemp = (SELECT tableColumn FROM @TableColAndColNameMap WHERE tempColumn = @colNameTemp)
			SET @staticColSel = @staticColSel + @colNameTemp
			SET @staticColIns = @staticColIns + 'column'+CONVERT(varchar(28),@id)
			IF (@id+1) < @columnCount
			BEGIN
				SET @staticColSel = @staticColSel + ','
				SET @staticColIns = @staticColIns + ','
			END
		END
		SET @id = @id + 1
	END
	--DO for last description column
	SET @colNameTemp = (SELECT columnName FROM APP_TemplateColumn WHERE templateId = @templateId AND columnId = @maxCol)
	SET @colNameTemp = (SELECT tableColumn FROM @TableColAndColNameMap WHERE tempColumn = @colNameTemp)
	SET @staticColSel = @staticColSel + @colNameTemp
	SET @staticColIns = @staticColIns + 'column'+CONVERT(varchar(28),@maxCol-@minColId)
	SET @insertStaticCol = @insertStaticCol + '(' + @staticColIns + ')'
	SET @selectStaticCol = @selectStaticCol + @staticColSel +
					' FROM NTNotificationsForCI WHERE AlertId >= '+
						CONVERT(NVARCHAR(32),@minId) + ' AND AlertId <= ' + CONVERT(NVARCHAR(32),@maxId)
	SET @insertStaticCol = @insertStaticCol + @selectStaticCol
	--print @insertStaticCol
	EXECUTE sp_executesql @insertStaticCol
	--SELECT * FROM #testAlertTable
	SET @columnCount = ((SELECT COUNT(*) FROM APP_TemplateColumn WHERE templateId = @templateId))
	WHILE(@id < @columnCount)
	BEGIN
			DECLARE @query1 NVARCHAR(MAX) = 'UPDATE #testAlertTable
									SET column'+ CONVERT(NVARCHAR(32),@id) +' = dbo.EncodeInvalidXMLChar(tokVal.tokenValue) ' +
									' FROM #testAlertTable testAlert INNER JOIN #tokenValList tokVal ' +
									' ON testAlert.column0 = tokVal.alertId AND tokVal.ColIndex = '+ CONVERT(NVARCHAR(32),@id)
			--print @query1
			EXECUTE sp_executesql @query1
			SET @id = @id + 1
	END
	INSERT INTO #ColumnRowFinal
	SELECT ColRow.columnid,ids.column0,ColRow.columnName,ColRow.columnXML,'',NULL
	FROM @ColumnRow ColRow,#testAlertTable ids
	DECLARE @i INTEGER = 0
	WHILE @i < @columnCount
	BEGIN
		DECLARE @queryUpdate NVARCHAR(MAX) =  'UPDATE colFinal
											SET colFinal.columnData = rowLm.column'+ CONVERT(NVARCHAR(128),@i)+
										'  FROM #ColumnRowFinal colFinal INNER JOIN #testAlertTable rowLm
										ON colFinal.rowId = rowLm.column0 AND colFinal.columnid = '+CONVERT(NVARCHAR(20),@i)
		--print @queryUpdate
		EXECUTE sp_executesql @queryUpdate
		SET @i = @i + 1
	END
	UPDATE #ColumnRowFinal
	SET ColAndData = ( SELECT columnData as '@dataAsWString', columnXML as 'node()' FOR XML PATH('rowColumn'))
	DECLARE @rowColumnWiseFinal TABLE(id INTEGER,ColumnList XML)
	INSERT INTO @rowColumnWiseFinal
	SELECT
	  rowId,(SELECT columnData as '@dataAsWString',columnXML as 'node()'
		FROM #ColumnRowFinal
		WHERE rowId = Result.rowId
		FOR XML PATH ('rowColumn'))
	FROM #ColumnRowFinal Result
	GROUP BY rowId
	SET @xmlStr = ISNULL((SELECT ColumnList as 'node()'
	FROM @rowColumnWiseFinal
	ORDER BY id
	FOR XML PATH('rows'),ROOT('LogMonitoring_DataSetRowsWithRowAsColumnList')),'<LogMonitoring_DataSetRowsWithRowAsColumnList/>')
END
ELSE IF @requestType = 1
BEGIN
	INSERT INTO #testAlertTable(column0)
	SELECT AlertId FROM NTNotificationsForCI with(nolock) WHERE AlertId >= @minId AND AlertId <= @maxId
	IF (SELECT COUNT(*) FROM #testAlertTable) >= 1
		SET @xmlStr = (SELECT 0 as '@errorCode' FOR XML PATH('App_GenericResp'))
	ELSE
		SET @xmlStr = (SELECT 1 as '@errorCode' FOR XML PATH('App_GenericResp'))
END
DROP TABLE #ColumnRowFinal
DROP table #testAlertTable
SELECT @xmlStr
GO

IF EXISTS (select * from GxQscripts where name = 'LMGetAlertDataSetForCI')
	delete from GxQscripts where name = 'LMGetAlertDataSetForCI'
GO

IF EXISTS (select * from GXDBVersions where aliasname='LMGetAlertDataSetForCI')
	delete from GXDBVersions where aliasname = 'LMGetAlertDataSetForCI'
GO

insert into GXDBVersions values(2, 'LMGetAlertDataSetForCI',  'v1.7.2.5.20.1', 'LMGetAlertDataSetForCI', 'v1.7.2.5.20.1')
GO

