--@querytype 	CSV
--Name:- Clients (Activity)

--------- BEGIN SURVEY QUERY ---------
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0

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
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(MAX),
	DisplayName NVARCHAR(MAX),
	PhysClientName NVARCHAR(MAX),
	ReleaseId INT,
	Deconfigured INT,
	OSId INT,
	OSName NVARCHAR(MAX) DEFAULT NULL ,
	Version VARCHAR(255) DEFAULT NULL,
	ArchGrpId INT,
	StoragePolicy NVARCHAR(MAX) 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(MAX),
	ExceptionType BIGINT DEFAULT NULL,
	ReplaceComma INT
)
/* 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
--type =512 List of Media Agents without ransomware protection
--type =1024 Clients with deprecated Operating System
*/
/* Client types:
--type =1 List of laptop clients
--type =2 List of Media Agents
--type =4 List of Clients which are not having any package installed
--type =8 List of Clients with virtual Discovered and specialStatusFlags is set.. These client are deconfigured VMs.
--type =16 List of Infrastructure Clients
--type =256 Define CS client
--type =512 List of Clients which are having Metrics Reporting package installed
--type =1024 List of Clients which are having Web Server package installed
--type =2048 List of Clients which are having Command Center package installed
--type =4096 List of Clients which are having Commcell Console package installed
--type =8192 List of Clients which are having Work Flow package installed
*/
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 < 10121000200620000 -- 11.0 SP21
		ALTER TABLE #ClientInstalledProp DROP COLUMN agentList			
		
	IF  @SpVersion < 10121000200630000 -- 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, OSId, OSName, ClientHostName, ReplaceComma)
	SELECT	id, name, name, releaseId, ISNULL(deleted, 0), OSId, OSName, netHostName, 0
	FROM	#ClientInstalledProp
ELSE
IF	@csReleaseId = 15
	INSERT INTO #ClientDetails (ClientId, ClientName, DisplayName, ReleaseId, Deconfigured, OSId, OSName, Version, AdditionalUpdates, ClientHostName, ReplaceComma)
	SELECT	T.id, T.name, C.displayName, T.releaseId, ISNULL(T.deleted, 0), T.OSId, T.OSName, T.SPVersionInfo, T.SPVersionAndPatchInfo, T.netHostName, 0
	FROM	#ClientInstalledProp T
		INNER JOIN APP_Client C ON T.id = C.id
ELSE
	INSERT INTO #ClientDetails (ClientId, ClientName, DisplayName, ReleaseId, Deconfigured, OSId, OSName, Version, AdditionalUpdates, ClientHostName, ReplaceComma)
	SELECT	id, name, displayName, releaseId, ISNULL(deleted, 0), OSId, OSName, SPVersionInfo, SPVersionAndPatchInfo, netHostName, 0
	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 ISNULL(CP.created, 0) > 0 THEN dbo.UTCToLocalTime(DATEADD(s, CP.created, '1970-01-01'), @timzonename) ELSE NULL 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 ISNULL(CP.created, 0) > 0 THEN dbo.UTCToLocalTime(DATEADD(s, CP.created, '1970-01-01'), @timzonename) ELSE NULL 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, OSId, OSName, InstallDate, DeleteDate, ClientHostName, ReplaceComma)
SELECT	U.ClientId, MAX(U.ClientName), MAX(U.ReleaseId), 2, MAX(OS.id), (SELECT Name FROM simOperatingSystem WHERE id = MAX(OS.id)),
		I.InstallDate, MAX(U.UninstallDate), MAX(U.ClientHostName), 0
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

-- Correct OSName such as 'Microsoft Windows Server 2008 R2 (64-bit)' returned from getClientListAndInitialProps stored procedure
IF OBJECT_ID('ClientOSNameView', 'V') IS NOT NULL
	UPDATE	C SET OSName = V.osName
	FROM	#ClientDetails C INNER JOIN ClientOSNameView V ON C.ClientId = V.clientId

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, ArchGrpId = AG.id, 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 ArchGrpId = AG.id, 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 infrastructure client type

UPDATE  #ClientDetails SET ClientType += 16  --Infrastructure Clients
FROM #ClientDetails AS C
INNER JOIN App_clientProp CP WITH(NOLOCK)
                                ON CP.componentNameId = C.clientId
                                AND CP.attrName = 'IsInfrastructure' 
                                AND CP.attrVal = '1' 
                                AND CP.modified=0

-- update CS client type

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

UPDATE  #ClientDetails SET ClientType += 512 --type =512 List of Clients which are having Metrics Reporting package installed
FROM #ClientDetails AS C
INNER JOIN simInstalledPackages SIM WITH(NOLOCK) ON SIM.ClientId = C.clientId AND SIM.simPackageID in (727, 1177)

UPDATE  #ClientDetails SET ClientType += 1024 --type =1024 List of Clients which are having Web Server package installed
FROM #ClientDetails AS C
INNER JOIN simInstalledPackages SIM WITH(NOLOCK) ON SIM.ClientId = C.clientId AND SIM.simPackageID in (252, 1174)

UPDATE  #ClientDetails SET ClientType += 2048 --type =2048 List of Clients which are having Command Center package installed
FROM #ClientDetails AS C
INNER JOIN simInstalledPackages SIM WITH(NOLOCK) ON SIM.ClientId = C.clientId AND SIM.simPackageID in (726, 1135)

UPDATE  #ClientDetails SET ClientType += 4096 --type =4096 List of Clients which are having Commcell Console package installed
FROM #ClientDetails AS C
INNER JOIN simInstalledPackages SIM WITH(NOLOCK) ON SIM.ClientId = C.clientId AND SIM.simPackageID in (701, 1118)

UPDATE  #ClientDetails SET ClientType += 8192 --type =8192 List of Clients which are having Work Flow package installed
FROM #ClientDetails AS C
INNER JOIN simInstalledPackages SIM WITH(NOLOCK) ON SIM.ClientId = C.clientId AND SIM.simPackageID in (23, 1023)

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 ISNULL(T.DeleteTime, 0) > 0 THEN dbo.UTCToLocalTime(DATEADD(s, T.DeleteTime, '1970-01-01'), @timzonename) ELSE NULL 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 = 8) OR (c.ClientType & 256 = 256)) 
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('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

--Media Agents without ransomware protection

INSERT INTO #ExceptionalClients 
SELECT DISTINCT CD.clientId
FROM #ClientDetails CD LEFT OUTER JOIN APP_ClientProp ACP ON CD.ClientId = ACP.componentNameId
WHERE CD.ClientType & 2 = 2 
AND ACP.attrname in ('dlpEnableAutomaticDecryption', 'enableDLP') 
GROUP BY CD.clientId HAVING SUM(CONVERT(int,attrVal)) <> 2

UPDATE CD
SET ExceptionType = ISNULL(ExceptionType,0) + 512
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

--Deprecated clients.0
IF ((@csReleaseId = 16 AND @csSPVersion >= 19) OR @csReleaseId > 16)
BEGIN
	IF object_id('tempdb.dbo.#DeprecatedClients') is not null
		DROP TABLE #DeprecatedClients

	CREATE TABLE #DeprecatedClients (clientId INT, operatingSystemVersion VARCHAR(64), operatingSystemName VARCHAR(64), bIsCS INTEGER)

	BEGIN TRY
		INSERT INTO #DeprecatedClients
			EXEC simGetClientsWithDeprecatedOS

		UPDATE CD
		SET ExceptionType = ISNULL(ExceptionType,0) + 1024
		FROM #ClientDetails CD INNER JOIN #DeprecatedClients DC ON CD.clientId = DC.ClientId

	END TRY
	BEGIN CATCH
	END CATCH

	IF object_id('tempdb.dbo.#DeprecatedClients') is not null
		DROP TABLE #DeprecatedClients
END
--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 = replace(Version, 'c', '.3')
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


UPDATE CD SET PhysClientName = CASE 
	WHEN CD.ClientId = ISNULL(PC.ClusterClientId, 0) THEN PC.PhysicalClientName
	WHEN ISNULL(AC.PhysicalClientId, 0) > 1 THEN (SELECT name FROM APP_Client WITH (NOLOCK) WHERE ID = AC.PhysicalClientId)
	ELSE NULL END
FROM
	#ClientDetails CD
	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 CD.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 PC ON PC.ClusterClientId = CD.ClientId
WHERE	
	AC.componentNameId IS NOT NULL OR PC.ClusterClientId IS NOT NULL

-- Replace comma in name strings
UPDATE	#ClientDetails SET ClientName = REPLACE(ClientName, ',', '<COMMA>'), ReplaceComma = 1
WHERE	CHARINDEX(',', ClientName) > 0

UPDATE	#ClientDetails SET DisplayName = REPLACE(DisplayName, ',', '<COMMA>'), ReplaceComma = 1
WHERE	CHARINDEX(',', DisplayName) > 0

UPDATE	#ClientDetails SET ClientHostName = REPLACE(ClientHostName, ',', '<COMMA>'), ReplaceComma = 1
WHERE	CHARINDEX(',', ClientHostName) > 0

UPDATE	#ClientDetails SET PhysClientName = REPLACE(PhysClientName, ',', '<COMMA>'), ReplaceComma = 1
WHERE	CHARINDEX(',', PhysClientName) > 0

UPDATE	CD SET StoragePolicy = REPLACE(AG.name, ',', '<COMMA>'), ReplaceComma = 1
FROM	#ClientDetails CD
		INNER JOIN archGroup AG ON CD.ArchGrpId = AG.id
WHERE	CD.ArchGrpId IS NOT NULL AND CHARINDEX(',', AG.name) > 0

UPDATE	CD SET OSName = REPLACE(CD.OSName, ',', '<COMMA>'), ReplaceComma = 1
FROM	#ClientDetails CD
WHERE	CHARINDEX(',', CD.OSName) > 0

IF object_id('tempdb.dbo.#LastOnlineTimeofClients') is not null
		DROP TABLE #LastOnlineTimeofClients
CREATE TABLE #LastOnlineTimeofClients(
    ClientId INT,
    LastUpdateTime BIGINT,
	LastOnlineTime BIGINT,
	LastOfflineTime BIGINT
)

INSERT INTO #LastOnlineTimeofClients
SELECT ToClientId, MAX(lastUpdateTime), 0, 0
FROM CCRClientToClient CCR WITH (NOLOCK)
INNER JOIN #ClientDetails C ON C.ClientId = CCR.ToClientId AND CCR.FromClientId = 2 AND CCR.serviceType = 0
GROUP BY ToClientId

CREATE INDEX #LastOnlineTimeofClients_idx ON #LastOnlineTimeofClients (ClientId)

UPDATE L
SET L.LastOnlineTime = CCR.LastOnlineTime,
	L.LastOfflineTime = CCR.LastOfflineTime
FROM #LastOnlineTimeofClients L
INNER JOIN CCRClientToClient CCR WITH (NOLOCK) ON CCR.FromClientId = 2 AND CCR.ToClientId = L.ClientId AND CCR.LastUpdateTime = L.LastUpdateTime AND CCR.serviceType = 0

SELECT 
	CASE WHEN ReplaceComma = 1 THEN -C.ClientId ELSE C.ClientId END AS ClientId,
	ClientName AS ClientName, 
	ISNULL(PhysClientName, '') AS PhysicalClientName,
	LEFT(ISNULL(OSName, ''), 255) AS OperatingSystem,
	ISNULL(Timezone, '') AS Timezone,
	ClientType AS Type,
	Deconfigured AS InstallStatus, 
	ISNULL(InstallDate, '1970-01-01') AS InstallDate, 
	COALESCE(DeconfigDate, DeleteDate, '1970-01-01') AS UninstallTime,
	ISNULL(Version, '') AS ServicePack,
	ISNULL(StoragePolicy, '') AS StoragePolicy,
	CASE WHEN ISNULL(LastBackupTime, 0) > 0 THEN dbo.UTCToLocalStringTime(DATEADD(s, LastBackupTime, '1970-01-01'), @timzonename) 
		ELSE '1970-01-01' END AS LatestDataProtection,
	ISNULL(ClientHostName, '') AS ClientHostName,
	ISNULL(DisplayName, ClientName) AS ClientDisplayName, 
	ISNULL(X.latitude, 0.0) AS Latitude,
	ISNULL(X.longitude, 0.0) AS Longitude,
	ISNULL(X.countryCode, '') AS CountryCode,
	ISNULL(ExceptionType, 0) AS ExceptionType,
	CASE WHEN ISNULL(LastOnlineTime, 0) > 0 THEN dbo.UTCToLocalStringTime(DATEADD(s, LastOnlineTime, '1970-01-01'), @timzonename) 
        ELSE '1970-01-01' END AS LastOnlineTime,
	CASE WHEN ISNULL(LastOfflineTime, 0) > 0 THEN dbo.UTCToLocalStringTime(DATEADD(s, LastOfflineTime, '1970-01-01'), @timzonename) 
        ELSE '1970-01-01' END AS LastOfflineTime
FROM	#ClientDetails C
	LEFT OUTER JOIN #LocationXML X
		ON C.ClientId = X.ClientId
	LEFT OUTER JOIN #LastOnlineTimeofClients CCR WITH (NOLOCK) ON CCR.ClientId = C.ClientId
	
IF OBJECT_ID('tempdb..#ClientDetails') IS NOT NULL
	DROP TABLE #ClientDetails
IF object_id('tempdb.dbo.#LastOnlineTimeofClients') is not null
	DROP TABLE #LastOnlineTimeofClients

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

SET NOCOUNT OFF