SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 197
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------


--------- BEGIN SURVEY QUERY ---------

declare @releaseId int
SET @releaseId =(SELECT releaseId from APP_Client  where id =2)

IF object_id('tempdb.dbo.#CommcellThresholdInfo') is not null DROP TABLE #CommcellThresholdInfo
create table #CommcellThresholdInfo(entityId INT, entityName NVARCHAR(MAX), attrName NVARCHAR(MAX), attrType INT, attrVal NVARCHAR(MAX)	)

IF(@releaseId>=15)
BEGIN
	INSERT INTO #CommcellThresholdInfo
	SELECT 1,'GXGlobalParam','DDBPendingDeleteCriticalLimit',10,
	ISNULL((SELECT value from MMConfigs (NOLOCK) where name like 'DA_CONFIG_MAX_DELETEDAF_COUNT_FOR_SIDB'
		and value>=nmin and value <=nMax),10000)

	INSERT INTO #CommcellThresholdInfo
	SELECT 1,'GXGlobalParam','DDBPendingDeleteWarningLimit',10,
		ISNULL((SELECT nmin from MMConfigs where name like 'DA_CONFIG_MAX_DELETEDAF_COUNT_FOR_SIDB'),5000)

	DECLARE @MMCONFIG_AVG_QI_TIME_LIMIT_CRITICAL_EVENT_PERCENT INT = ISNULL((SELECT  value from MMConfigs where name like 'MMCONFIG_AVG_QI_TIME_LIMIT_CRITICAL_EVENT_PERCENT' and value>=nmin and value <=nMax),90)
	DECLARE @MMCONFIG_MAX_AVGQITIME_FOR_SIDB INT = ISNULL((SELECT value from MMConfigs where name like 'MMCONFIG_MAX_AVGQITIME_FOR_SIDB' and value>=nmin and value <=nMax),2000)
	DECLARE @MMCONFIG_AVG_QI_TIME_LIMIT_EVENT_PERCENT INT = ISNULL((SELECT value from MMConfigs where name like 'MMCONFIG_AVG_QI_TIME_LIMIT_EVENT_PERCENT' and value>=nmin and value <=nMax),70)

 
	INSERT INTO #CommcellThresholdInfo
	SELECT 1,'GXGlobalParam','AvgQITimeCriticalLimit',10,
		@MMCONFIG_AVG_QI_TIME_LIMIT_CRITICAL_EVENT_PERCENT*@MMCONFIG_MAX_AVGQITIME_FOR_SIDB/100

	INSERT INTO #CommcellThresholdInfo
	SELECT 1,'GXGlobalParam','AvgQITimeWarningLimit',10,
		@MMCONFIG_AVG_QI_TIME_LIMIT_EVENT_PERCENT*@MMCONFIG_MAX_AVGQITIME_FOR_SIDB/100
END

IF(@releaseId>15)
BEGIN
INSERT INTO #CommcellThresholdInfo
SELECT  componentNameId,'ClientProp','criticalSpaceMB',attrType, CASE ISNUMERIC(attrVal) WHEN 1 THEN CAST([attrVal] AS FLOAT) ELSE 0 END    from APP_ClientProp WITH (NOLOCK)  where attrName like 'Idx: min space' and modified =0-- critical
INSERT INTO #CommcellThresholdInfo
SELECT  componentNameId,'ClientProp','warningSpaceMB',attrType, CASE ISNUMERIC(attrVal) WHEN 1 THEN CAST([attrVal] AS FLOAT) ELSE 0 END    from APP_ClientProp WITH (NOLOCK)  where attrName like 'Idx: alert space' and modified =0-- warning
END
ELSE 
BEGIN
IF object_id('tempdb.dbo.#DiskFreeV10Threshold') is not null DROP TABLE #DiskFreeV10Threshold
CREATE TABLE #DiskFreeV10Threshold(entityId INT, DiskFreeThresholdMB INT,DiskFreeWarningThreshholdMB INT 	)

INSERT INTO #DiskFreeV10Threshold(entityId,DiskFreeThresholdMB,DiskFreeWarningThreshholdMB)
 SELECT DISTINCT mh.ClientId , IC.DiskFreeThresholdMB,IC.DiskFreeWarningThreshholdMB 
		  FROM    MMHost MH
            INNER JOIN APP_Client CL ON CL.id = MH.ClientId
            INNER JOIN simAllGalaxyRel R ON R.id = CL.releaseId 
            INNER JOIN IdxAccessPath IAP ON IAP.ClientId = MH.ClientId
            INNER JOIN IdxCache IC ON IC.IdxCacheId = IAP.IdxCacheId AND IC.IdxCacheType = 1
            INNER JOIN IdxPool IP ON IP.IdxPoolId = IC.IdxPoolId
           
    AND mh.ClientId >1 

INSERT INTO #CommcellThresholdInfo
SELECT  entityId,'ClientProp','criticalSpaceMB',1,DiskFreeThresholdMB  --CRITICAL
FROM #DiskFreeV10Threshold

INSERT INTO #CommcellThresholdInfo
SELECT  entityId,'ClientProp','warningSpaceMB',1,DiskFreeWarningThreshholdMB -- warning
FROM #DiskFreeV10Threshold
IF object_id('tempdb.dbo.#DiskFreeV10Threshold') is not null DROP TABLE #DiskFreeV10Threshold 
END
DECLARE  @Usage XML

SET @Usage = (SELECT 
    entityId	AS '@entityId',
	entityName  AS '@entityName',
	attrName	AS '@attrName',
	attrType	AS '@attrType',
	attrVal		AS '@attrVal'
	FROM #CommcellThresholdInfo
    
FOR XML PATH('CommcellThresholdInfo'))

SET @surveyXML = (SELECT @Usage
FOR XML PATH('CommcellThresholdInfoXML'))

DROP TABLE #CommcellThresholdInfo


--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

--SELECT cast(@surveyXML as xml)
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF