
--Name:- CommVault  Latest Data Protection 
--Description:- Gives laptop client region,client name,operating system, install date, service pack, storage policy, latest --databackup time, days since lastbackup
--Description: -Gives Media agent client,client name, os, install date, service pack,

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 77
DECLARE @surveyXML NVARCHAR(MAX)
--------- END - GENERATED CODE ---------

--------- BEGIN SURVEY QUERY ---------
DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0
IF @csReleaseId = 15
	SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF @csReleaseId > 15 OR @csReleaseId = 15 AND @csSPVersion >= 7
BEGIN
	RETURN -- Use query 240
END

DECLARE @timzonename    NVARCHAR(1024) = ''
SELECT  @timzonename = dbo.GetClientTimeZone(2)

IF ISNULL(@timzonename, '') = ''
BEGIN
    SELECT @timzonename = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @timzonename = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@timzonename, CHARINDEX(':', @timzonename, CHARINDEX(':', @timzonename, 0) + 1) + 1, 255)
END



IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL
	DROP TABLE #ClientDetails
IF OBJECT_ID('tempdb..#ClientInstalledProp') IS NOT NULL
	DROP TABLE #ClientInstalledProp
IF OBJECT_ID('tempdb..#InstallHistory') IS NOT NULL
	DROP TABLE #InstallHistory
IF OBJECT_ID('tempdb..#UninstallHistory') IS NOT NULL
	DROP TABLE #UninstallHistory
IF OBJECT_ID('tempdb..#MONTHS') IS NOT NULL
	DROP TABLE #MONTHS
IF OBJECT_ID('tempdb..#InstalledClients') IS NOT NULL
	DROP TABLE #InstalledClients
IF OBJECT_ID('tempdb..#LocationXML') IS NOT NULL
	DROP TABLE #LocationXML

CREATE TABLE #ClientDetails
(
	ClientId INT,
	ClientName NVARCHAR(255),
	DisplayName NVARCHAR(255),
	VMName NVARCHAR(255),
	ReleaseId INT,
	Deconfigured INT,
	OSName NVARCHAR(255) DEFAULT NULL ,
	Version VARCHAR(255) DEFAULT NULL,
	StoragePolicy NVARCHAR(144) DEFAULT 'N/A',
	LastBackupTime INT DEFAULT NULL,
	InstallDate DATETIME DEFAULT NULL,
	DeconfigDate DATETIME DEFAULT NULL,
	DeleteDate DATETIME DEFAULT NULL,
	ClientType INT DEFAULT 0,
	Timezone varchar(255),
	AdditionalUpdates NVARCHAR(MAX),
	ClientHostName NVARCHAR(510),
	ExceptionType BIGINT DEFAULT NULL
)
/* Exception types:
--type =1 List of laptop clients With No Storage Policy
--type =2 List of laptop clients with no subclient policy
--type =4 List of laptop clients which belong to no group
--type =8 List of laptop clients with system state
--type =16 List of client Base Client and FS iDA at different patch levels indicates and error during Install Updates
--type =32 List of Clients with only Base Client installed
--type =64 List of Media Agents without Storage Policies 
--type =128 List of clients With No Storage Policy
--type = 256 Define CS client
*/

CREATE TABLE #ClientInstalledProp 
(
	id INT, name NVARCHAR(255), displayName NVARCHAR(255), description NVARCHAR(MAX), netHostName NVARCHAR(255), OSId INT,
	OSName NVARCHAR(255), hwId INT, hwName VARCHAR(255), licenses INT, bkpEnabled INT,
	rstEnabled INT, deleted INT, priority INT, clientFlag INT, isCommServer INT,
	encryption INT, rsaPubKey NVARCHAR(1024), rsaPriKeySF NVARCHAR(1024), releaseId INT, status INT,
	virtual INT, isLicensed INT, physClientId INT, physClientName NVARCHAR(1024), DCinstalled INT,
	contentIndexingEnabled INT, CIActivityEnabled INT, updateStatus INT, snapBackupsEnabled INT, snapBackupsMAInstalled INT,
	collectDelegateInfo INT, donotSingleInstance INT, noPhysMachPresent INT, proxyFSClientId INT, proxyFSClientName NVARCHAR(1024),
	evmgrcPort INT, miniSRM INT, virtualDiscovered INT, EDCDiscovered INT, specialStatusFlags INT, outlookAddin INT,
	commNetClientCount INT, clientVersion VARCHAR(255), releasedIDWhenClientWasInstalled INT, ArchiverEnabled INT, virtualEntityFlags INT,
	isAdvancedFSPackageInstalled INT, isRemoteCacheEnabled INT, isWebServerInstalled INT, cvdPort INT, dataSecurityEnabled INT,sharePointFarmClient INT,
	canSchedule integer,canClientGroup integer,canSubclientPolicy integer,canPseudoClientCreate integer,canAdmin integer,canLogMonitor integer,canAlert integer,
	canReport INT, SPVersionInfo VARCHAR(255), SPVersionAndPatchInfo VARCHAR(MAX), isIndexingV2 INT, isQsnapInstalled INT, isDescriptionInHTMLFormat integer, 
	refCopyClientType integer, isFileShareArchiverClientPackageInstalled INT, isAgentLicenseConsumed INT, clientType INT, clusterType INT, isIndexingV2NAS INT,
	isVirtualServerPresent INT, is1TouchClient INT, clientGuid VARCHAR(40), cloudStatus INT, nasOSType INT, nasCapabilities BIGINT, isExchangePackageInstalled INT,
	clientAppType INT, isIndexingV2VSA INT, retireClientPhase INT, agentList VARCHAR(MAX), indexingV2EnabledMask BIGINT,vmVendor integer, managementVersion VARCHAR(255)
)

CREATE TABLE #LocationXML
(
	clientId INT, clientLocation XML, latitude DECIMAL(16,13), longitude DECIMAL(16,13), countryCode NVARCHAR(3)
)

DECLARE @CSNode INT
DECLARE @CSClientId INT
SET @CSNode = ISNULL((select TOP 1 clientid from APP_Platform WITH (NOLOCK) where platformType = 1 order by clientid ASC), 0)

-- Find out if this is a cluster CS.
DECLARE @isClusterCS INT = 0
SELECT @isClusterCS = attrVal FROM App_ClientProp WITH (NOLOCK) 
WHERE componentNameId = @CSNode
    AND attrName LIKE 'Virtual Client' 
    AND modified = 0

-- If it is a cluster CS, we need to consider the active physical node of CommServ as CommServ client id    
IF @isClusterCS = 0
    SET @CSClientId = @CSNode
ELSE
    BEGIN 
        --Use currently active physical node        
        SELECT @CSClientId = attrVal
        FROM App_ClientProp WITH (NOLOCK) 
        WHERE componentNameId = @CSNode AND attrName like 'CS Active Physical Node' AND modified = 0
    END

DECLARE @nstring NVARCHAR(MAX)

DECLARE @SpRevision NVARCHAR(20) = ''
SELECT	@SpRevision = revision FROM GxDBVersions WITH(NOLOCK) where name = 'getClientListAndInitialProps'

DECLARE	@n INT = 1, @p INT = 1, @q INT = 1
DECLARE @r VARCHAR(20) = '', @s VARCHAR(20) = ''

IF @SpRevision LIKE 'v%'
BEGIN
	SET @SpRevision = @SpRevision+'.'
	SET @q = CHARINDEX('.', @SpRevision, @p+1)
	WHILE @q > 0
	BEGIN
		SET @s = SUBSTRING(@SpRevision, @p+1, @q-@p-1)
		IF  @n <= 4
			SET @r += (CASE @q-@p-1 WHEN 1 THEN '000' WHEN 2 THEN '00' WHEN 3 THEN '0' ELSE '' END + @s)
		ELSE
			SET @r += (CASE @q-@p-1 WHEN 1 THEN '0' ELSE '' END + @s)
		
		SET @n += 1
		SET @p = @q
		SET @q = CHARINDEX('.', @SpRevision, @p+1)
	END
	SET @SpRevision = @r
END

IF	ISNUMERIC(@SpRevision) = 0
	SET @SpRevision = '9223372036854775807'
	
DECLARE @SpVersion BIGINT = CAST(@SpRevision AS BIGINT)

IF	@csReleaseId < 15
BEGIN
	ALTER TABLE #ClientInstalledProp DROP COLUMN isAdvancedFSPackageInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN isRemoteCacheEnabled
	ALTER TABLE #ClientInstalledProp DROP COLUMN isWebServerInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN dataSecurityEnabled
	ALTER TABLE #ClientInstalledProp DROP COLUMN SPVersionInfo
	ALTER TABLE #ClientInstalledProp DROP COLUMN SPVersionAndPatchInfo

	ALTER TABLE #ClientInstalledProp DROP COLUMN canSchedule
	ALTER TABLE #ClientInstalledProp DROP COLUMN canClientGroup
	ALTER TABLE #ClientInstalledProp DROP COLUMN canSubclientPolicy
	ALTER TABLE #ClientInstalledProp DROP COLUMN canPseudoClientCreate
	ALTER TABLE #ClientInstalledProp DROP COLUMN canAdmin
	ALTER TABLE #ClientInstalledProp DROP COLUMN canLogMonitor	
	ALTER TABLE #ClientInstalledProp DROP COLUMN sharePointFarmClient	
	ALTER TABLE #ClientInstalledProp DROP COLUMN canAlert
	ALTER TABLE #ClientInstalledProp DROP COLUMN displayName
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2	
	ALTER TABLE #ClientInstalledProp DROP COLUMN isQsnapInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN canReport
	ALTER TABLE #ClientInstalledProp DROP COLUMN isDescriptionInHTMLFormat
	ALTER TABLE #ClientInstalledProp DROP COLUMN refCopyClientType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isFileShareArchiverClientPackageInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN isAgentLicenseConsumed
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientType
	ALTER TABLE #ClientInstalledProp DROP COLUMN clusterType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2NAS
	ALTER TABLE #ClientInstalledProp DROP COLUMN isVirtualServerPresent
	ALTER TABLE #ClientInstalledProp DROP COLUMN is1TouchClient
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientGuid
	ALTER TABLE #ClientInstalledProp DROP COLUMN cloudStatus
	ALTER TABLE #ClientInstalledProp DROP COLUMN nasOSType
	ALTER TABLE #ClientInstalledProp DROP COLUMN nasCapabilities
	ALTER TABLE #ClientInstalledProp DROP COLUMN isExchangePackageInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientAppType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2VSA
	ALTER TABLE #ClientInstalledProp DROP COLUMN retireClientPhase
	ALTER TABLE #ClientInstalledProp DROP COLUMN agentList
	ALTER TABLE #ClientInstalledProp DROP COLUMN indexingV2EnabledMask
	ALTER TABLE #ClientInstalledProp DROP COLUMN vmVendor
	ALTER TABLE #ClientInstalledProp DROP COLUMN managementVersion

	IF @SpVersion < 00010075002600060000 -- Below 9.0 SP8
		ALTER TABLE #ClientInstalledProp DROP COLUMN cvdPort

	IF @SpVersion < 00010075002600030000 -- Below 9.0 SP3 (REL_9_0_0_B92_VMPROV)
		ALTER TABLE #ClientInstalledProp DROP COLUMN virtualEntityFlags
END
ELSE IF	@csReleaseId = 15
BEGIN
	ALTER TABLE #ClientInstalledProp DROP COLUMN sharePointFarmClient	
	ALTER TABLE #ClientInstalledProp DROP COLUMN canAlert
	ALTER TABLE #ClientInstalledProp DROP COLUMN displayName
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2
	ALTER TABLE #ClientInstalledProp DROP COLUMN isQsnapInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN canReport
	ALTER TABLE #ClientInstalledProp DROP COLUMN isDescriptionInHTMLFormat
	ALTER TABLE #ClientInstalledProp DROP COLUMN refCopyClientType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isFileShareArchiverClientPackageInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN isAgentLicenseConsumed
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientType
	ALTER TABLE #ClientInstalledProp DROP COLUMN clusterType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2NAS
	ALTER TABLE #ClientInstalledProp DROP COLUMN isVirtualServerPresent
	ALTER TABLE #ClientInstalledProp DROP COLUMN is1TouchClient
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientGuid
	ALTER TABLE #ClientInstalledProp DROP COLUMN cloudStatus
	ALTER TABLE #ClientInstalledProp DROP COLUMN nasOSType
	ALTER TABLE #ClientInstalledProp DROP COLUMN nasCapabilities
	ALTER TABLE #ClientInstalledProp DROP COLUMN isExchangePackageInstalled
	ALTER TABLE #ClientInstalledProp DROP COLUMN clientAppType
	ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2VSA
	ALTER TABLE #ClientInstalledProp DROP COLUMN retireClientPhase	
	ALTER TABLE #ClientInstalledProp DROP COLUMN agentList	
	ALTER TABLE #ClientInstalledProp DROP COLUMN indexingV2EnabledMask	
	ALTER TABLE #ClientInstalledProp DROP COLUMN vmVendor	
	ALTER TABLE #ClientInstalledProp DROP COLUMN managementVersion

	IF @SpVersion < 10092000400110000 -- Below 10.0 SP7a
	BEGIN
		ALTER TABLE #ClientInstalledProp DROP COLUMN canSchedule
		ALTER TABLE #ClientInstalledProp DROP COLUMN canClientGroup
		ALTER TABLE #ClientInstalledProp DROP COLUMN canSubclientPolicy
		ALTER TABLE #ClientInstalledProp DROP COLUMN canPseudoClientCreate
		ALTER TABLE #ClientInstalledProp DROP COLUMN canAdmin
		ALTER TABLE #ClientInstalledProp DROP COLUMN canLogMonitor		

		IF @SpVersion < 10092000400080000 -- Below 10.0 SP5a
			ALTER TABLE #ClientInstalledProp DROP COLUMN dataSecurityEnabled
	END
END
ELSE IF	@csReleaseId = 16
BEGIN
	IF @SpVersion < 10121000200090000 -- Below 11.0 SP4a
		ALTER TABLE #ClientInstalledProp DROP COLUMN clientType

	IF @SpVersion <= 10121000200120000 -- 11.0 SP5
		ALTER TABLE #ClientInstalledProp DROP COLUMN clusterType

	IF @SpVersion < 10121000200150000 -- 11.0 SP6
		ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2NAS

	IF @SpVersion < 10121000200170000 -- 11.0 SP7
	BEGIN
		ALTER TABLE #ClientInstalledProp DROP COLUMN isVirtualServerPresent
		ALTER TABLE #ClientInstalledProp DROP COLUMN is1TouchClient
	END

	IF @SpVersion < 10121000200240000 -- 11.0 SP9
		ALTER TABLE #ClientInstalledProp DROP COLUMN clientGuid

	IF  @SpVersion < 10121000200240201  --11.0 SP9 HF
		ALTER TABLE #ClientInstalledProp DROP COLUMN cloudStatus
	
	IF  @SpVersion < 10121000200280202  --11.0 SP11 HF
	BEGIN
		ALTER TABLE #ClientInstalledProp DROP COLUMN nasOSType
		ALTER TABLE #ClientInstalledProp DROP COLUMN nasCapabilities
	END

	IF  @SpVersion < 10121000200330201  --11.0 SP12 HF
		ALTER TABLE #ClientInstalledProp DROP COLUMN isExchangePackageInstalled

	IF  @SpVersion < 10121000200430000 -- 11.0 SP13
		ALTER TABLE #ClientInstalledProp DROP COLUMN clientAppType

	IF  @SpVersion < 10121000200560000 -- 11.0 SP16
		ALTER TABLE #ClientInstalledProp DROP COLUMN isIndexingV2VSA
		
	IF  @SpVersion <= 10121000200610000 -- 11.0 SP21
		ALTER TABLE #ClientInstalledProp DROP COLUMN retireClientPhase
		
	IF  @SpVersion <= 10121000200610000 -- 11.0 SP21
		ALTER TABLE #ClientInstalledProp DROP COLUMN agentList	
	
	IF  @SpVersion <= 10121000200620000 -- 11.0 SP22
		ALTER TABLE #ClientInstalledProp DROP COLUMN indexingV2EnabledMask

	IF  @SpVersion <= 10121000200650000 -- 11.0 SP25
		ALTER TABLE #ClientInstalledProp DROP COLUMN vmVendor

	IF  @SpVersion <= 10121000200650000 -- 11.0 SP25
		ALTER TABLE #ClientInstalledProp DROP COLUMN managementVersion
END

-- getClientLocation XML
IF @csReleaseId > 14 -- v10 and v11 clients have the field set in APP_ClientProp table
BEGIN
  BEGIN TRY
	INSERT INTO #LocationXML(clientId, clientLocation)
	SELECT componentNameId, REPLACE(attrVal, '/ >', '/>') 
	FROM   (SELECT componentNameId, attrVal, ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS RowId
			FROM APP_ClientProp 
			WHERE attrName = 'Client Geo Location' AND LEFT(attrVal, 16) = '<App_GeoLocation' AND modified = 0) T
	WHERE  RowId = 1
  END TRY
  BEGIN CATCH
	INSERT INTO #LocationXML(clientId, clientLocation)
	SELECT componentNameId, REPLACE(attrVal, '/', '') + '</App_GeoLocation>'
	FROM   (SELECT componentNameId, SUBSTRING(attrVal, 0, CHARINDEX('>', attrVal)+1) AS attrVal, ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS RowId
			FROM APP_ClientProp 
			WHERE attrName = 'Client Geo Location' AND LEFT(attrVal, 16) = '<App_GeoLocation' AND modified = 0) T
	WHERE  RowId = 1
  END CATCH

	UPDATE #LocationXML SET latitude = csinfo.n.value('@latitude','DECIMAL(16,13)')
							,longitude = csinfo.n.value('@longitude','DECIMAL(16,13)') 
							,countryCode = csinfo.n.value('@countrycode','NVARCHAR(3)') 
	FROM #LocationXML as results
	CROSS APPLY results.clientLocation.nodes('/App_GeoLocation') AS csinfo(n)
	WHERE csinfo.n.value('@status_code','NVARCHAR(MAX)') = 'SUCCESS'

END

-- getClientListAndInitialProps procedure is used for displaying clients in CommCell GUI
INSERT INTO #ClientInstalledProp
EXEC getClientListAndInitialProps 0, 1, 1

IF	@csReleaseId < 15
	INSERT INTO #ClientDetails (ClientId, ClientName, DisplayName, ReleaseId, Deconfigured, OSName, ClientHostName)
	SELECT	id, name, name, releaseId, ISNULL(deleted, 0), OSName, netHostName
	FROM	#ClientInstalledProp
ELSE
IF	@csReleaseId = 15
	INSERT INTO #ClientDetails (ClientId, ClientName, DisplayName, ReleaseId, Deconfigured, OSName, Version, AdditionalUpdates, ClientHostName)
	SELECT	T.id, T.name, C.displayName, T.releaseId, ISNULL(T.deleted, 0), T.OSName, T.SPVersionInfo, T.SPVersionAndPatchInfo, T.netHostName
	FROM	#ClientInstalledProp T
		INNER JOIN APP_Client C ON T.id = C.id
ELSE
	INSERT INTO #ClientDetails (ClientId, ClientName, DisplayName, ReleaseId, Deconfigured, OSName, Version, AdditionalUpdates, ClientHostName)
	SELECT	id, name, displayName, releaseId, ISNULL(deleted, 0), OSName, SPVersionInfo, SPVersionAndPatchInfo, netHostName
	FROM	#ClientInstalledProp

-- get clien install/uninstall history
CREATE TABLE #InstallHistory (ClientId INT, ClientName NVARCHAR(255), SimOSId INT, ReleaseId INT, InstallDate DATETIME)
CREATE TABLE #UninstallHistory (ClientId INT, ClientName NVARCHAR(255), SimOSId INT, ReleaseId INT, UninstallDate DATETIME, ClientHostName NVARCHAR(510))

INSERT INTO #InstallHistory
SELECT	C.id, C.Name, C.simOperatingSystemId, C.releaseId,  
		CASE WHEN CP.created IS NULL THEN NULL ELSE dbo.UTCToLocalTime(dbo.GetDateTime(CP.created), @timzonename) END
FROM	APP_Client C 
		LEFT JOIN (SELECT componentNameId, MIN(created) AS created FROM APP_ClientProp WHERE created>0 GROUP BY componentNameId) CP 
		ON C.id = CP.componentNameId 
WHERE	C.id > 1 --AND (C.specialClientFlags & 1) = 0
UNION
SELECT	CId, Name, SimOSId, ReleaseId, OpTimeUTC
FROM	LicUsageHistory
WHERE	ReleaseId IN (15,14,13) AND AppType = 1001 AND OpType IN ('Install')

INSERT INTO #UninstallHistory(ClientId, ClientName, SimOSId, ReleaseId, UninstallDate, ClientHostName)
SELECT	C.id, C.Name, C.simOperatingSystemId, C.releaseId,  
		CASE WHEN CP.created IS NULL THEN NULL ELSE dbo.UTCToLocalTime(dbo.GetDateTime(CP.created), @timzonename) END,
		C.net_hostname
FROM	APP_Client C LEFT JOIN APP_ClientProp CP 
		ON C.id = CP.componentNameId AND CP.attrname = 'PlatformDeleted 4' AND CP.attrVal = '1'
WHERE	C.id > 2 --AND (C.specialClientFlags & 1) = 0
UNION
SELECT	CId, Name, SimOSId, ReleaseId, OpTimeUTC, Name
FROM	LicUsageHistory 
WHERE	ReleaseId IN (15,14,13) AND AppType = 1001 AND OpType IN ('Deleted', 'Uninstall')

UPDATE	U SET ClientName = C.name, ClientHostName = C.net_hostname
FROM	#UninstallHistory U INNER JOIN APP_Client C ON U.ClientId = C.id
WHERE	U.ClientName <> C.name OR U.ClientHostName <> C.net_hostname

UPDATE	#ClientDetails SET InstallDate = I.InstallDate
FROM	(SELECT ClientId, MIN(InstallDate) AS InstallDate FROM #InstallHistory GROUP BY ClientId) I
WHERE	#ClientDetails.ClientId = I.ClientId

UPDATE	#ClientDetails SET DeconfigDate = I.UninstallDate
FROM	(SELECT CId, MAX(OpTimeUTC) AS UninstallDate FROM LicUsageHistory WHERE OpType IN ('Deleted', 'Uninstall') GROUP BY CId) I
WHERE	#ClientDetails.ClientId = I.CId AND #ClientDetails.Deconfigured = 1

INSERT INTO #ClientDetails (ClientId, ClientName, ReleaseId, Deconfigured, OSName, InstallDate, DeleteDate, ClientHostName)
SELECT	U.ClientId, MAX(U.ClientName), MAX(U.ReleaseId), 2, MAX(OS.Name), I.InstallDate, MAX(U.UninstallDate), MAX(U.ClientHostName)
FROM	#UninstallHistory U INNER JOIN (
		SELECT ClientId, MIN(InstallDate) AS InstallDate, MAX(InstallDate) AS LastInstallDate
		FROM #InstallHistory GROUP BY ClientId
		) I ON U.ClientId = I.ClientId AND U.UninstallDate > I.LastInstallDate
		INNER JOIN simOperatingSystem OS ON U.SimOSId = OS.id
		LEFT OUTER JOIN (SELECT ClientId FROM #ClientDetails) C ON U.ClientId = C.ClientId
WHERE	C.ClientId IS NULL
GROUP BY U.ClientId, I.InstallDate

UPDATE	C SET Version = CP.attrVal
FROM	#ClientDetails C INNER JOIN APP_ClientProp CP ON C.ClientId = CP.componentNameId AND CP.attrName = 'SP Version Info' AND CP.modified = 0
WHERE   C.Version IS NULL

UPDATE  C SET Version = CASE ReleaseId 
	WHEN 13 THEN  '8' 
	WHEN 14 THEN  '9' + ISNULL((CASE HighestSP WHEN 0 THEN '' ELSE ' SP' + CAST(HighestSP AS varchar(10)) + (CASE SpMinorVersion WHEN 0 THEN '' WHEN 1 THEN 'a' WHEN 2 THEN 'b' END) END), '')
	WHEN 15 THEN '10' + ISNULL((CASE HighestSP WHEN 0 THEN '' ELSE ' SP' + CAST(HighestSP AS varchar(10)) END), '')
	WHEN 16 THEN '11' + ISNULL((CASE HighestSP WHEN 0 THEN '' ELSE ' SP' + CAST(HighestSP AS varchar(10)) END), '')
	END
FROM	#ClientDetails C INNER JOIN simInstalledPackages S ON C.ClientId = S.ClientId AND simPackageID = 1 -- Base Client Package
WHERE   C.Version IS NULL

UPDATE	C SET AdditionalUpdates = CP.attrVal
FROM	#ClientDetails C INNER JOIN APP_ClientProp CP ON C.ClientId = CP.componentNameId AND CP.attrName = 'SP Version And Patch Info' AND CP.modified = 0 
WHERE	C.AdditionalUpdates IS NULL 

UPDATE	C SET AdditionalUpdates = S.AdditionalPatches
FROM	#ClientDetails C INNER JOIN simInstalledPackages S ON C.ClientId = S.ClientId AND simPackageID = 1 -- Base Client Package
WHERE	C.AdditionalUpdates IS NULL 

UPDATE C
SET C.Timezone = attrVal
FROM #ClientDetails C INNER JOIN App_ClientProp AC ON C.ClientId=AC.componentNameId AND modified = 0 AND attrName = 'timezone'

;WITH LastJobOfClient (clientId, jobId) AS
(
	SELECT	A.clientId, MAX(J.jobId)
	FROM	JMBkpStats J INNER JOIN APP_Application A ON J.appId = A.id
	WHERE	J.commCellId = 2 AND J.status IN (1, 3, 14)
	GROUP BY A.clientId
)
, LastJobTimeOfClient (clientId, jobEndTime, archGrpId) AS
(
	SELECT	L.clientId, J.servEndDate, CASE WHEN J.dataArchGrpID > 1 THEN J.dataArchGrpID ELSE J.logArchGrpID END
	FROM	JMBkpStats J INNER JOIN LastJobOfClient L ON J.jobId = L.jobId AND J.commCellId = 2
)
UPDATE	#ClientDetails SET LastBackupTime = LJ.jobEndTime, StoragePolicy = AG.name
FROM	LastJobTimeOfClient LJ INNER JOIN archGroup AG ON LJ.archGrpId = AG.id
WHERE	#ClientDetails.ClientId = LJ.clientId

;WITH StoragePolicyOfClient (clientId, archGrpId) AS
(
	SELECT	A.clientId, CASE WHEN A.dataArchGrpID > 1 THEN A.dataArchGrpID ELSE A.logArchGrpID END
	FROM	APP_Application A INNER JOIN APP_BackupsetName BS ON A.backupSet = BS.id
	WHERE	(A.subclientStatus & 8) > 0 AND (BS.status & 8) > 0 AND (A.dataArchGrpID > 1 OR A.logArchGrpID > 1)
)
UPDATE	#ClientDetails SET StoragePolicy = AG.name
FROM	StoragePolicyOfClient S INNER JOIN archGroup AG ON S.archGrpId = AG.id
WHERE	#ClientDetails.ClientId = S.clientId

UPDATE	#ClientDetails SET ClientType = 1  -- Laptops
WHERE	ClientId IN (SELECT id FROM APP_Client WHERE (status & 0x1000) > 0) 

UPDATE	#ClientDetails SET ClientType += 2  -- MediAgents
WHERE   ClientId IN (SELECT ClientId FROM MMHost)
 
UPDATE	#ClientDetails SET ClientType += 4  -- Clients which are not having any package installed
FROM	#ClientDetails AS C
		LEFT OUTER JOIN simInstalledPackages S ON S.ClientId = C.ClientId
WHERE	S.ClientId IS NULL

--update VM client type

UPDATE	#ClientDetails SET ClientType += 8  -- Clients with virtual Discovered and specialStatusFlags is set.. These client are deconfigured VMs.
FROM #ClientDetails AS C
INNER JOIN #ClientInstalledProp CP ON CP.id = C.ClientId
WHERE  CP.virtualDiscovered = 1

-- update CS client type

UPDATE #ClientDetails SET ClientType += 256 -- CS client
WHERE ClientId = @CSClientId

IF @csReleaseId < 16
	UPDATE	C SET DisplayName = V.attrVal
	FROM 	#ClientDetails AS C
		INNER JOIN APP_ClientProp V WITH (NOLOCK) ON C.ClientId = V.componentNameId
	WHERE 	(C.ClientType & 8) > 0
		AND V.attrName = 'Virtual Machine Name' AND V.modified = 0

DROP TABLE #ClientInstalledProp

IF @csReleaseId >= 15
BEGIN
	IF OBJECT_ID('tempdb..#tempVMs') IS NOT NULL DROP TABLE #tempVMs
	CREATE TABLE #tempVMs (VMClientId INT, Deconfigured INT, DeleteTime INT)

	SET @nstring = N'
	INSERT INTO #tempVMs
	SELECT	DISTINCT C.id, CASE WHEN T.componentNameId IS NOT NULL OR J.clientId IS NULL THEN 1 ELSE 0 END, T.DeleteTime
	FROM	APP_Client C WITH (NOLOCK)
		INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON C.id = CP.componentNameId AND C.id > 2
		AND	CP.attrName = ''Virtual Server Discovered Clients'' AND CP.attrVal = ''1'' AND CP.modified =0
		LEFT OUTER JOIN (
		SELECT	componentNameId, MAX(CAST(attrVal AS INT)) AS DeleteTime
		FROM	APP_ClientProp CP WITH (NOLOCK)
		WHERE	attrName = ''Virtual Machine Deletion Time'' AND ISNULL(attrVal, ''0'') <> ''0'' AND modified = 0
		GROUP BY componentNameId
		) T ON C.id = T.componentNameId
		LEFT OUTER JOIN (
		SELECT	DISTINCT Q.clientId
		FROM	JMQinetixUpdateStatus Q WITH (NOLOCK)
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.jobId = Q.jobId AND B.commCellId = Q.commCellId
					AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND B.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 
		) J ON C.id = J.clientId'

	EXEC sp_executesql @nstring

	UPDATE	C SET Deconfigured = T.Deconfigured, DeleteDate = CASE WHEN T.DeleteTime IS NULL THEN NULL ELSE dbo.UTCToCellLocalTime(dbo.GetDateTime(T.DeleteTime), 2) END
	FROM	#ClientDetails C INNER JOIN #tempVMs T ON C.ClientId = T.VMClientId
		LEFT OUTER JOIN APP_IDAName I ON C.ClientId = I.clientId AND I.status&(2|4) = 0
	WHERE	I.clientId IS NULL
END


IF OBJECT_ID('tempdb..#VMBackupTime') IS NOT NULL DROP TABLE #VMBackupTime
CREATE TABLE #VMBackupTime (ClientId INT, BackupTime INT)

INSERT	INTO #VMBackupTime (ClientId, BackupTime)  
SELECT	Q.clientId, MAX(CAST(V.attrVal AS INT))
FROM	JMQinetixUpdateStatus Q INNER JOIN APP_VMProp V 
	 ON Q.JobId = V.jobId AND Q.commCellId = V.commCellId AND Q.clientId = V.VMclientId AND V.attrName = 'vmBackupEndTime' 
WHERE	Q.status IN ('0', '3')
GROUP BY Q.clientId

UPDATE	c SET LastBackupTime = vm.BackupTime
FROM	#ClientDetails c INNER JOIN #VMBackUPTime vm ON c.ClientId = vm.ClientId AND c.ClientType >= 8
WHERE	vm.BackupTime IS NOT NULL AND (c.LastBackupTime IS NULL OR vm.BackupTime > c.LastBackupTime)

DROP TABLE #VMBackUPTime

--Update ExceptionType of clients

IF OBJECT_ID('tempdb..#ExceptionalClients') IS NOT NULL DROP TABLE #ExceptionalClients
CREATE TABLE #ExceptionalClients (clientId int) 
--Laptop clients with no storage policies

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD INNER JOIN APP_Application AP ON CD.ClientId = AP.clientId
WHERE (AP.dataArchGrpId IS NULL OR AP.dataArchGrpId = 1)
	AND CD.Deconfigured = 0 AND CD.ClientType & 1 = 1
	AND AP.subclientStatus & (2|4|16|32) = 0

UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 1
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

DELETE FROM #ExceptionalClients

--Laptop clients with no subclient policies
DECLARE @backupSetScpAssoc TABLE(backupSetId INT)

INSERT INTO @backupSetScpAssoc(backupSetId) 
	SELECT DISTINCT BP.componentNameId
	FROM  APP_Application AS AP WITH (NOLOCK) INNER JOIN  APP_BackupSetProp AS BP  WITH (NOLOCK) 
	ON CAST(AP.backupSet AS NVARCHAR(MAX)) = BP.attrVal                   
	WHERE AP.appTypeId = 1030 AND AP.instance = 1 AND BP.attrName = 'Associated subclient Policy' AND BP.attrType = 7 AND BP.modified=0

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD LEFT OUTER JOIN (APP_Application AP INNER JOIN @backupSetScpAssoc BP ON BP.BackupSetId = AP.Backupset AND AP.subclientStatus & 8 = 8 AND AP.subclientStatus & 536 <> 536) ON CD.ClientId = AP.ClientId
WHERE CD.Deconfigured = 0 AND CD.ClientType & 1 = 1 
	AND AP.id IS NULL
	
UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 2
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

DELETE FROM #ExceptionalClients

--Laptop clients which belong to no groups

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD LEFT OUTER JOIN APP_ClientGroupAssoc G ON CD.ClientId = G.clientId
WHERE CD.Deconfigured = 0 AND CD.ClientType & 1 = 1 
	AND G.clientId IS NULL
		
UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 4
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

DELETE FROM #ExceptionalClients

--Laptop clients with system state

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD INNER JOIN APP_Application AP ON CD.ClientId = AP.ClientId 
	INNER JOIN APP_SubClientProp SC ON SC.componentNameId = AP.id
WHERE CD.Deconfigured = 0 AND CD.ClientType & 1 = 1 
	AND AP.subclientStatus & 536 <> 536
	AND SC.attrName = 'System State' AND SC.cs_attrName = CHECKSUM(N'System State') 
	AND SC.attrVal = '1' AND SC.modified = 0 AND SC.attrType = 2
		
UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 8
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

DELETE FROM #ExceptionalClients

--Media Agents without storage policies

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD LEFT OUTER JOIN MMDataPath DP ON CD.ClientId = DP.HostClientId
WHERE CD.ClientType & 2 = 2 AND DP.HostClientId IS NULL
	
UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 64
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

DELETE FROM #ExceptionalClients

--Clients without storage policies

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD LEFT OUTER JOIN APP_Application AP ON CD.ClientId = AP.clientId AND AP.subclientStatus & (2|4|16|32) = 0
WHERE CD.Deconfigured = 0
GROUP BY CD.ClientId
HAVING MAX(ISNULL(AP.dataArchGrpId, 0)) <= 1
		
UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 128
FROM #ClientDetails CD INNER JOIN #ExceptionalClients EC ON CD.clientId = EC.ClientId

IF OBJECT_ID('tempdb..#ExceptionalClients') IS NOT NULL DROP TABLE #ExceptionalClients

--Convert to dotted format
UPDATE #ClientDetails
SET Version = replace(Version, '.0 SP', '.')
UPDATE #ClientDetails
SET Version = replace(Version, ' SP', '.')
UPDATE #ClientDetails
SET Version = replace(Version, ' HPK', '.')
UPDATE #ClientDetails
SET Version = replace(Version, '+', '')
UPDATE #ClientDetails
SET Version = replace(Version, '-', '')
UPDATE #ClientDetails
SET Version = replace(Version, 'a', '.1')
UPDATE #ClientDetails
SET Version = replace(Version, 'b', '.2')
UPDATE #ClientDetails
SET Version = 
	CASE LEN(Version) - LEN(REPLACE(Version, '.', '')) 
		WHEN 0 THEN Version + '.0.0'
		WHEN 1 THEN Version + '.0'
		WHEN 2 THEN Version
	END


DECLARE @surveyXML2 XML

SET @surveyXML2 = (
	SELECT 
		ClientName AS '@clientName', 
		DisplayName AS '@displayName', 
		CASE 
			WHEN C.ClientId = ISNULL(pseduClients.ClusterClientId, 0 ) THEN dbo.NormalizeForXML(pseduClients.PhysicalClientName)
			ELSE (SELECT dbo.NormalizeForXML(name) FROM APP_Client WHERE ID = AC.PhysicalClientId) 
		END AS '@physicalClientName',
		C.ClientId AS '@clientId',
		ClientHostName AS '@ClientHostName',
		OSName AS '@operatingSystem',
		InstallDate AS '@installdate', 
		Version AS '@servicePack',
		StoragePolicy AS '@StoragePolicy',
		CASE WHEN ISNULL(LastBackupTime, 0) > 0 THEN dbo.UTCToLocalStringTime(dbo.GetDateTime(LastBackupTime), @timzonename) 
			ELSE NULL END AS '@latestDataProtection',
		Deconfigured AS '@lastbkp', 
		ClientType AS '@type',
		ISNULL(DeconfigDate, DeleteDate) AS '@uninstallTime',
		Timezone AS '@timezone',
		CASE WHEN AdditionalUpdates IS NULL THEN NULL
			 WHEN CHARINDEX(',Additional Updates:,', AdditionalUpdates) = 0 THEN AdditionalUpdates 
			 ELSE SUBSTRING(AdditionalUpdates, CHARINDEX(',Additional Updates:,', AdditionalUpdates) + 21, LEN(AdditionalUpdates)) 
		END AS '@additionalUpdates',
		ExceptionType AS '@exceptionType', 
		X.latitude AS '@latitude',
		X.longitude AS '@longitude',
		X.countryCode AS '@countryCode'
	FROM	#ClientDetails C
	LEFT OUTER JOIN #LocationXML X
	ON C.ClientId = X.ClientId
	LEFT OUTER JOIN
	(
		SELECT	componentNameId, MIN(attrVal) AS PhysicalClientId
		FROM	APP_ClientProp WITH (NOLOCK)
		WHERE	attrName = 'Active Physical Node' AND attrVal <> componentNameId AND modified = 0
		GROUP BY componentNameId
	) AS AC ON C.ClientId = AC.componentNameId
	LEFT OUTER JOIN 
	(
		SELECT c.id AS ClusterClientId, MIN(c1.name) AS PhysicalClientName
		FROM APP_Client AS c WITH (NOLOCK)
			INNER JOIN APP_Application AS app WITH (NOLOCK)
				ON c.simOperatingSystemId = 56 --Oracle RAC
				AND c.id = app.clientId
			INNER JOIN (SELECT instaceId, MIN(clientId) AS clientId FROM APP_OracleRacInstance AS ori WITH (NOLOCK) GROUP BY instaceId) AS i
				ON app.instance = i.instaceId 
			INNER JOIN APP_Client AS c1 WITH (NOLOCK)
				ON c1.id = i.clientId
		GROUP BY c.id
		UNION
		SELECT cp.componentNameId AS ClusterClientId, MIN(cp.attrName) AS PhysicalClientName
		FROM APP_ClientProp AS cp WITH (NOLOCK)
				WHERE cp.attrType = 125 --PROPERTY_DAG_MEMBERSERVER for Exchange DAG
				AND cp.modified = 0
		GROUP BY cp.componentNameId
	) AS pseduClients
		ON pseduClients.ClusterClientId = C.ClientId
	FOR XML PATH ('Client'),type
)

SET @surveyXML = (
	SELECT @surveyXML2
FOR XML PATH('ClientDetails') 
)

IF (@surveyXML IS NULL)
	SET @surveyXML = (SELECT NULL FOR XML PATH ('CLIENTS'))


IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL
	DROP TABLE #ClientDetails


--------- 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')
)
--SELECT cast (@surveyXML as XML)
SET NOCOUNT OFF

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

