--Name:- Clients Configured Without Storage Policy
--Description:- Total Number Of clients, Number of Clients without Storage Policy configured, List of clients without Storage Policy configured, % of Client Without Storage Policy Configured

SET NOCOUNT ON

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

--------- END - GENERATED CODE ---------
--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
DECLARE @total_client INTEGER
DECLARE @no_sp_client INTEGER
DECLARE @temp_total DECIMAL 

IF object_id('tempdb.dbo.#no_sp_subclient') IS NOT NULL DROP TABLE #no_sp_subclient
CREATE TABLE #no_sp_subclient
(
	clientid INTEGER,
	clientName VARCHAR(256),
	agentName NVARCHAR(MAX),
	instanceName NVARCHAR(MAX),
	backupSet NVARCHAR(MAX),
	subclient NVARCHAR(MAX),
	subclientId int,
	appTypeId int,
	instanceId int
)

DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        

SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)

-- Get selected client groups list
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
	SET @specialGroupExists =1

-- Fetch the list of subclients with no storage policy association.
-- Following are exceptions to include in the list.
-- Indexing subclient
-- Do not backup subclient
-- Subclients of Uninstalled, deleted and Agentless clients
-- deleted subclients
-- Subclients of Instances DefaultDummyInstanceForOracle and defaultDummyInstanceForSAP
-- Subclients of dummy backupsets

IF  (@specialGroupExists =1 )
BEGIN
	SET @total_client = (
		SELECT COUNT(*) 
		FROM	APP_Client AS C WITH (NOLOCK) 
				INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
					ON CGA.clientId = C.id
				INNER JOIN @includeGroups CG 
					ON CGA.clientGroupId = CG.groupId 
		 WHERE	C.id > 1 AND (C.status & ( 2|4 ) ) =0      -- uninstalled/deleted client
			AND (C.specialClientFlags & 1) = 0
			AND (C.specialClientFlags & 34) <> 34 -- Pseudo Commserve Client
			AND C.id NOT IN (SELECT PMClientId FROM APP_VMToPMMap WITH (NOLOCK) WHERE VMClientId = 2) -- CS cluster nodes
		)

	INSERT INTO #no_sp_subclient
	SELECT
		DISTINCT client.id, client.name, ida.name, inst.name, bs.name, sc.subclientName, sc.id, sc.appTypeId, SC.instance
	FROM APP_Application AS sc WITH (NOLOCK) 
		INNER JOIN APP_Client AS client WITH (NOLOCK) ON sc.clientId = client.id
		INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientId = client.id
		INNER JOIN @includeGroups CG ON CGA.clientGroupId = CG.groupId
		INNER JOIN APP_BackupSetName AS bs WITH (NOLOCK) ON bs.id = sc.backupSet
		INNER JOIN APP_InstanceName AS inst WITH (NOLOCK) ON inst.id = sc.instance
		INNER JOIN APP_iDAType AS ida WITH (NOLOCK) ON ida.type = sc.appTypeId
	WHERE ISNULL(dataArchGrpID, 1) = 1 AND ISNULL(logArchGrpID, 1) = 1
		AND sc.appTypeId < 600 AND sc.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
		AND (sc.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR sc.subclientStatus&8 = 0)
		AND sc.appTypeId <> 136 --physical clients of SAP HANA
		AND not(sc.subclientStatus & 536) = 536 -- Indexing subclient
		AND (sc.subclientStatus & (16|32)) = 0 -- Do Not Backup
		AND (sc.subclientStatus & (2|4)) = 0 -- deleted subclient 
		AND (bs.status & 24) <> 24	-- 'defaultDummyBackupSet'
		AND inst.name NOT IN ('DefaultDummyInstanceForOracle', 'defaultDummyInstanceForSAP')
		AND (client.status & (2|4)) = 0 -- uninstalled/deleted/agentless client	END	
		AND (client.specialClientFlags & 1) = 0
		AND (client.specialClientFlags & 34) <> 34 -- Pseudo Commserve Client
		AND client.id NOT IN (SELECT PMClientId FROM APP_VMToPMMap WITH (NOLOCK) WHERE VMClientId = 2) -- CS cluster nodes
END
ELSE
BEGIN
	SET @total_client = (
		SELECT COUNT(*) 
		FROM	APP_Client WITH (NOLOCK)
		WHERE	id > 1 AND (status & (2|4)) = 0      -- uninstalled/deleted client
			AND (specialClientFlags & 1) = 0
			AND (specialClientFlags & 34) <> 34 -- Pseudo Commserve Client
			AND id NOT IN (SELECT PMClientId FROM APP_VMToPMMap WITH (NOLOCK) WHERE VMClientId = 2) -- CS cluster nodes
		)

	INSERT INTO #no_sp_subclient
	SELECT
		DISTINCT client.id, client.name, ida.name, inst.name, bs.name, sc.subclientName, sc.id, sc.appTypeId, SC.instance
	FROM APP_Application AS sc WITH (NOLOCK) 
		INNER JOIN APP_Client AS client WITH (NOLOCK) ON sc.clientId = client.id
		INNER JOIN APP_BackupSetName AS bs WITH (NOLOCK) ON bs.id = sc.backupSet
		INNER JOIN APP_InstanceName AS inst WITH (NOLOCK) ON inst.id = sc.instance
		INNER JOIN APP_iDAType AS ida WITH (NOLOCK) ON ida.type = sc.appTypeId
	WHERE ISNULL(dataArchGrpID, 1) = 1 AND ISNULL(logArchGrpID, 1) = 1
		AND sc.appTypeId < 600 AND sc.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
		AND (sc.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR sc.subclientStatus&8 = 0)
		AND sc.appTypeId <> 136 --physical clients of SAP HANA
		AND not(sc.subclientStatus & 536) = 536 -- Indexing subclient
		AND (sc.subclientStatus & (16|32)) = 0 -- Do Not Backup
		AND (sc.subclientStatus & (2|4)) = 0 -- deleted subclient 
		AND (bs.status & 24) <> 24	-- 'defaultDummyBackupSet'
		AND inst.name NOT IN ('DefaultDummyInstanceForOracle', 'defaultDummyInstanceForSAP')
		AND (client.status & (2|4)) = 0 -- uninstalled/deleted/agentless clientEND
		AND (client.specialClientFlags & 1) = 0
		AND (client.specialClientFlags & 34) <> 34 -- Pseudo Commserve Client
		AND client.id NOT IN (SELECT PMClientId FROM APP_VMToPMMap WITH (NOLOCK) WHERE VMClientId = 2) -- CS cluster nodes
END

-- Remove activity disabled subclients from the resultset
DELETE S 
FROM #no_sp_subclient S 
INNER JOIN JMJobAction J
	ON J.opType = 4 AND J.action =1
	AND J.clientId = S.ClientId AND J.appType IN (S.AppTypeId, 0) 

-- instanceID column was added to JMJobAction table in V11 SP6
IF COL_LENGTH('JMJobAction', 'instanceID') IS NOT NULL
	EXEC sp_executesql N'DELETE S FROM #no_sp_subclient S INNER JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.instanceID = S.InstanceId AND J.instanceID > 1'
	
DELETE S 
FROM #no_sp_subclient S  
INNER JOIN JMJobAction J
	ON J.opType = 4 AND J.action =1
	AND J.appId = S.subclientId

DELETE S 
FROM #no_sp_subclient S 
INNER JOIN (
	SELECT	componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
	FROM	APP_SubClientProp
	WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) T ON S.subClientId = T.appId 
INNER JOIN JMJobAction J 
	ON J.opType = 4 AND J.action = 1 
	AND J.appId = T.subclientPolicyAppId

DELETE S 
FROM #no_sp_subclient S 
INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId
INNER JOIN JMJobAction J WITH (NOLOCK) ON CGA.clientGroupId = J.clientGroupId
	AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
	AND J.opType = 4 AND J.action = 1 


SET @no_sp_client = (SELECT COUNT(DISTINCT clientid) FROM #no_sp_subclient)
SET @temp_total = @total_client
IF @temp_total = 0
 BEGIN 
   SET @temp_total=1
 END




SET @surveyXML = (
	SELECT 
		(@total_client) AS '@Number_Of_Client', 
		@no_sp_client AS '@Client_Without_sp', 
		CASE 
			@specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120)
			ELSE N'All' 
		END AS '@clientGroups',
		CONVERT(DECIMAL(36,2),(CAST(@no_sp_client AS DECIMAL) / CAST(@temp_total AS DECIMAL))*100) AS '@Percent_Of_Client_Without_SP',
		(	
			SELECT 
				clientid AS '@clientId', 
				dbo.NormalizeForXML(clientName) AS '@clientName',
				agentName AS '@agentName',
				dbo.NormalizeForXML(instanceName) AS '@instanceName',
				dbo.NormalizeForXML(backupSet) AS '@bkpsetName',
				dbo.NormalizeForXML(subclient) As '@scName'
			FROM #no_sp_subclient 
			FOR XML PATH('CLIENTENTITY'),TYPE
		)
		FOR XML PATH('Client_Without_sp')
)

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

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

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