--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON

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

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



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

--------- Insert your SQL statements here 

-- Query Name: Index Cache Usage
-- Description: Media Agent Index Cache Usage
-- Gets the following information about index cache
--      Index Access path
--      Assoiciated Media Agent
--      Shared status
--      Total Capacity
--      Disk Usabe and Free Space

DECLARE @tempTbl TABLE (
    MAId INT,
    MAName NVARCHAR(max), 
    IndexCachePath NVARCHAR(max), 
    IntermediateCachePath NVARCHAR(max), 
    IsShared INT, 
    IsUNC INT, 
    TotalCapacityMB BIGINT,
    DiskUsageMB BIGINT,
    FreeDiskSpaceMB BIGINT,
    IndexAccessPathEnabled INT,
    DbSizeMB BIGINT,
    LogsSizeMB BIGINT,
    ReportsSizeMB BIGINT,
    LegacySizeMB BIGINT,
    OtherSizeMB BIGINT,
    LocationTableSizeMB BIGINT,
    CacheType INT,
    NumberOfHostedIndices INT DEFAULT 0,
    NumberOfErrorIndices INT DEFAULT 0,
    NumberOfOutdatedIndices INT DEFAULT 0,
    ServerStatus TINYINT DEFAULT 1,
    CacheDisabledReason INT DEFAULT NULL
)

delete from @tempTbl

insert into @tempTbl(MAId, MAName, IndexCachePath, IntermediateCachePath, IsShared, IsUNC, TotalCapacityMB, DiskUsageMB, FreeDiskSpaceMB, IndexAccessPathEnabled, CacheType)
    SELECT DISTINCT
          cl.id AS 'MAId',
          dbo.NormalizeForXML(cl.name) AS 'MAName', 
          a.Path AS 'IndexCachePath', 
          (case WHEN ((a.Flags & 1)=1) THEN a.StagingCachePath ELSE 'N/A' END) AS 'IntermediateCachePath', 
          p.IsShared AS 'IsShared', 
          (CASE SUBSTRING(a.Path, 1, 2) WHEN '\\' THEN 1 ELSE 0 END) AS 'IsUNC', 
          (CASE WHEN cl.releaseId<14 THEN -1 ELSE (CASE WHEN c.CatalogServerClientId>0  AND cl.id<>c.CatalogServerClientId THEN a.TotalCapacityMB ELSE c.TotalCapacityMB END) END) AS 'TotalCapacityMB', 
          (CASE WHEN c.CatalogServerClientId>0 AND cl.id<>c.CatalogServerClientId THEN a.DiskUsageMB ELSE c.DiskUsageMB END) AS 'DiskUsageMB', 
          (CASE WHEN c.CatalogServerClientId>0 AND cl.id<>c.CatalogServerClientId THEN a.FreeDiskSpaceMB ELSE c.FreeDiskSpaceMB END) AS 'FreeDiskSpaceMB',
           a.Enabled AS 'IndexAccessPathEnabled',
           0 AS 'CacheType'
    FROM IdxPool p WITH (NOLOCK), IdxCache c WITH (NOLOCK), IdxAccessPath a WITH (NOLOCK), APP_Client cl WITH (NOLOCK), LicUsage lu WITH (NOLOCK)
    WHERE c.IdxCacheType=1 AND c.IdxCacheId=a.IdxCacheId AND p.IdxPoolId=c.IdxPoolId AND a.ClientId=cl.id
    and cl.id<>1 and lu.AppType=1002 and lu.OpType='Install' and lu.CId=cl.id
    and cl.releaseId<16
    AND cl.id in (SELECT distinct clientId FROM MMHost h)

-- Get V2 properties in a temp table
DECLARE @tmpIndexServerPropertis TABLE (
    maId INT,
    cachePath NVARCHAR(max),
    cacheEnabled TINYINT,
    totalDisk BIGINT,
    freeDisk BIGINT,
    usedDisk BIGINT,
    disabledReason TINYINT,
    logsCachePath NVARCHAR(max),
    logsCacheStatus TINYINT,
    logsCacheTotalDiskSize BIGINT,
    logsCacheUsedDiskSize BIGINT,
    logsCacheFreeDiskSize BIGINT,
    isOffline TINYINT,
    isMaintenance TINYINT
)
INSERT INTO @tmpIndexServerPropertis
    SELECT
        componentNameId,
        ISNULL([Idx: cache path], 'None'),
        ISNULL(CAST([Idx: cache enabled] AS INT), 0),
        ISNULL(CAST([Idx: total disk size] AS BIGINT), 0),
        ISNULL(CAST([Idx: free disk size] AS BIGINT), 0),
        ISNULL(CAST([Idx: used disk size] AS BIGINT), 0),
        ISNULL(CAST([Idx: disabled reason] AS TINYINT), 0),
        ISNULL([Idx: logs cache path], 'None'),
        ISNULL(CAST([Idx: logs cache status] AS TINYINT), 0),
        ISNULL(CAST([Idx: logs cache total disk size] AS BIGINT), 0),
        ISNULL(CAST([Idx: logs cache used disk size] AS BIGINT), 0),
        ISNULL(CAST([Idx: logs cache free disk size] AS BIGINT), 0),
        CASE WHEN (MM.MmHostSoftState = 0 AND MM.MmHostEnabled = 0) THEN 1 ELSE 0 END,
        CASE WHEN ((MM.Attribute & 16) = 16) THEN 1 ELSE 0 END
    FROM (
        SELECT componentNameId, attrName, attrVal
        FROM APP_ClientProp
    ) AS SOURCE PIVOT (
        MAX(attrVal) FOR attrName IN (
            [Idx: cache path],
            [Idx: cache enabled],
            [Idx: total disk size],
            [Idx: free disk size],
            [Idx: used disk size],
            [Idx: disabled reason],
            [Idx: logs cache path],
            [Idx: logs cache status],
            [Idx: logs cache total disk size],
            [Idx: logs cache used disk size],
            [Idx: logs cache free disk size]
        )
    ) AS PT
    JOIN MMHost MM WITH (NOLOCK) ON PT.componentNameId = MM.ClientId

-- Get index list for indices stats
IF object_id('tempdb.dbo.#AppsDisabled') IS NOT NULL DROP TABLE #AppsDisabled
IF object_id('tempdb.dbo.#AppsBackupDisableForInstance') IS NOT NULL DROP TABLE #AppsBackupDisableForInstance
IF object_id('tempdb.dbo.#tempValidApps') is not null DROP TABLE #tempValidApps
IF object_id('tempdb.dbo.#tmpLegalHoldBackupsets') is not null DROP TABLE #tmpLegalHoldBackupsets
IF object_id('tempdb.dbo.#tmpValidIndices') is not null DROP TABLE #tmpValidIndices
IF object_id('tempdb.dbo.#tmpValidMAs') is not null DROP TABLE #tmpValidMAs

CREATE TABLE #tmpValidIndices (
	currentIdxServer INT,
	dbId INT,
	appId INT,
	upToDate INT,
	errorLvl INT
)

CREATE TABLE #tmpValidMAs (
	clientId INT
)

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 >= 16 OR (@csReleaseId = 15 AND @csSPVersion >= 7)
BEGIN
	DECLARE @dynQuery NVARCHAR(MAX)
	CREATE TABLE #AppsBackupDisableForInstance(appId INT, PRIMARY KEY(appId))
	IF COL_LENGTH('CommServ.dbo.JMJobAction', 'instanceId') IS NOT NULL
	BEGIN
		-- Get backups disabled for instance
		SET @dynQuery = 'SELECT DISTINCT APP.id
		FROM APP_Application APP WITH (NOLOCK)
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND J.instanceId = APP.instance AND J.instanceId > 1'
		INSERT INTO #AppsBackupDisableForInstance(appId)
			EXEC sp_executeSQL @dynQuery
	END
    --> List of apps that has backup activity disabled thru different entry points
    CREATE TABLE #AppsDisabled(appId INT, PRIMARY KEY(appId))
    INSERT INTO #AppsDisabled
    SELECT DISTINCT APP.id   -- Backup disabled for Client Group
    FROM APP_Application APP WITH (NOLOCK)
        JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON APP.clientId = CGA.clientId
        JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
        AND CGA.clientGroupId = J.clientGroupId AND J.clientGroupId > 0 AND J.clientId = 1 AND J.appType = 0 AND J.appId = 1
    UNION
    SELECT DISTINCT APP.id   -- Backup disabled for Subclient Policy
    FROM APP_Application APP WITH (NOLOCK)
        JOIN (
            SELECT componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
            FROM APP_SubClientProp WITH (NOLOCK)
            WHERE attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
        ) T ON APP.id = T.appId
        JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
        AND J.appId = T.subclientPolicyAppId
    UNION
    SELECT DISTINCT APP.id   -- Backup disabled for Subclient
    FROM APP_Application APP WITH (NOLOCK)
        JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
        AND J.appId = APP.id
    UNION
    SELECT DISTINCT APP.id   -- Backup disabled for Client: 1) For all appTypes 2) For a particular appType
    FROM APP_Application APP WITH (NOLOCK)
        JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
        AND J.clientId = APP.ClientId AND J.appType IN (APP.appTypeId, 0)
    UNION
    SELECT appId FROM #AppsBackupDisableForInstance

    CREATE TABLE #tmpLegalHoldBackupsets (bsId INT)
    INSERT INTO #tmpLegalHoldBackupsets (bsId)
    SELECT DISTINCT BS.id FROM APP_BackupSetName BS WITH (NOLOCK)
    JOIN APP_BackupSetProp PROP WITH (NOLOCK) ON PROP.componentNameId = BS.id
    WHERE PROP.attrName = 'OnDemand Backupset' AND PROP.attrVal = '1' AND PROP.modified = 0

    CREATE TABLE #tempValidApps(appId INT, bsId INT, firstBackup BIGINT, lastBackup BIGINT, scCreationTime BIGINT)
    INSERT INTO #tempValidApps(appId, bsId, firstBackup, lastBackup, scCreationTime)
    SELECT DISTINCT APP.id, APP.backupSet, MIN(AF.cTime), MAX(AF.ctime), APP.refTime
    FROM APP_Application APP WITH(NOLOCK)
    JOIN ARCHFILE AF WITH(NOLOCK) ON AF.appId = APP.id and AF.isValid = 1 AND AF.fileType IN(2, 6)
    JOIN archFileCopy AFC WITH(NOLOCK) ON AF.id = AFC.archFileId AND AFC.isValid = 1 AND(AFC.flags & 256 = 0)
    WHERE APP.appTypeId != 137
    AND APP.subclientStatus & 4214 = 0 --CV_STATUS_UNINSTALLED, CV_STATUS_DELETED, CV_STATUS_DUMMY, CV_STATUS_HIDDEN, CV_STATUS_CMD_LINE, CV_STATUS_LOGCMD_LINE
    AND NOT EXISTS(SELECT 1 FROM #AppsDisabled APPD WHERE APPD.appId = APP.id) -- Apps that have backup activity disabled
    AND NOT EXISTS(SELECT 1 FROM #tmpLegalHoldBackupsets ODBS WHERE ODBS.bsId = APP.backupSet) -- Apps that belong to on demand backupset
    GROUP BY APP.id, APP.backupSet, APP.refTime

	IF @csReleaseId > 15
	BEGIN
		IF OBJECT_ID('CommServ.dbo.IdxDbState', 'U') IS NOT NULL
			SET @dynQuery = 'SELECT DISTINCT DB.currentIdxServer AS [currentIdxServer], DB.id AS [dbId], APP.id AS [appid], ISNULL(ST1.dbUptoDate, 1) AS [upToDate], ISNULL(ST1.errorLvl, 0) AS [errorLvl]
			FROM App_IndexDBInfo DB WITH(NOLOCK)
			JOIN APP_Application APP WITH(NOLOCK) ON APP.backupSet = DB.backupSetId
			JOIN APP_IDAName IDA WITH(NOLOCK) ON APP.clientId = IDA.clientId AND APP.appTypeId = IDA.appTypeId
			LEFT JOIN APP_IDAProp PROP WITH(NOLOCK) ON PROP.componentNameId = IDA.id AND PROP.attrName = ''Subclient Index'' AND PROP.attrVal = ''1'' AND PROP.modified = 0
			LEFT JOIN IdxDbState ST1 WITH(NOLOCK) ON DB.id = ST1.dbId
			WHERE PROP.id IS NULL OR DB.dbName = APP.GUID'
		ELSE 
			SET @dynQuery = 'SELECT DB.currentIdxServer AS [currentIdxServer], DB.id AS [dbId], APP.id AS [appid], ISNULL(ST1.dbUptoDate, 1) AS [upToDate], ISNULL(ST1.errorLvl, 0) AS [errorLvl]
				FROM App_IndexDBInfo DB WITH(NOLOCK)
				JOIN APP_Application APP WITH(NOLOCK) ON APP.backupSet = DB.backupSetId
				LEFT JOIN IdxDbState ST1 WITH(NOLOCK) ON DB.id = ST1.dbId'
		INSERT INTO #tmpValidIndices(currentIdxServer, dbId, appId, upToDate, errorLvl)
			EXEC sp_executeSQL @dynQuery
		DELETE FROM #tmpValidIndices WHERE appId NOT IN (SELECT appId FROM #tempValidApps)
	END

	SET @dynQuery = 'SELECT ClientId FROM MMHost WITH(NOLOCK)'
	IF COL_LENGTH('CommServ.dbo.MMHost', 'RolesBitMask') IS NOT NULL
		SET @dynQuery += ' WHERE RolesBitMask & 2 = 2'
	INSERT INTO #tmpValidMAs
		EXEC sp_executeSQL @dynQuery
END

-- Insert V2 index server state
insert into @tempTbl(MAId, MAName, IndexCachePath, IntermediateCachePath, IsShared, IsUNC, TotalCapacityMB, DiskUsageMB, FreeDiskSpaceMB, IndexAccessPathEnabled, CacheType, NumberOfHostedIndices, NumberOfOutdatedIndices, NumberOfErrorIndices, ServerStatus, CacheDisabledReason)
    SELECT DISTINCT
        cl.id AS 'MAId',
        dbo.NormalizeForXML(cl.name) AS 'MAName', 
        T.cachePath AS 'IndexCachePath', 
        'N/A' AS 'IntermediateCachePath', 
        0 AS 'IsShared', 
        0 AS 'IsUNC', 
        T.totalDisk AS 'TotalCapacityMB', 
        T.usedDisk AS 'DiskUsageMB', 
        T.freeDisk AS 'FreeDiskSpaceMB',
        T.cacheEnabled AS 'IndexAccessPathEnabled',
        0 AS 'CacheType',
        COUNT(DB.dbId) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfHostedIndices',
        SUM(DB.uptoDate) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfOutdatedIndices',
        SUM(DB.errorLvl) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfErrorIndices',
        CASE
        WHEN T.isOffline = 1 THEN 1
        WHEN T.isMaintenance = 1 THEN 2
        ELSE 0
        END AS 'ServerStatus',
        T.disabledReason AS 'CacheDisabledReason'
    FROM APP_Client cl WITH (NOLOCK)
        JOIN LicUsage lu WITH (NOLOCK) ON lu.CId = cl.id
		JOIN #tmpValidMAs h ON cl.id = h.ClientId
        JOIN @tmpIndexServerPropertis T ON cl.id = T.maId
        LEFT JOIN #tmpValidIndices DB WITH (NOLOCK) ON cl.id = DB.currentIdxServer
    WHERE 
        cl.id<>1 and lu.AppType=1002 and lu.OpType='Install'
        and cl.releaseId>=16
    
-- Index logs cache: introduced in SP20, we can have an index cache directory split in two, index + logs, that can be placed in different disks
insert into @tempTbl(MAId, MAName, IndexCachePath, IntermediateCachePath, IsShared, IsUNC, TotalCapacityMB, DiskUsageMB, FreeDiskSpaceMB, IndexAccessPathEnabled, CacheType, NumberOfHostedIndices, NumberOfOutdatedIndices, NumberOfErrorIndices, ServerStatus, CacheDisabledReason) 
   SELECT DISTINCT
        cl.id AS 'MAId',
        dbo.NormalizeForXML(cl.name) AS 'MAName', 
        T.logsCachePath AS 'IndexCachePath', 
        'N/A' AS 'IntermediateCachePath', 
        0 AS 'IsShared', 
        0 AS 'IsUNC', 
        T.logsCacheTotalDiskSize AS 'TotalCapacityMB', 
        T.logsCacheUsedDiskSize AS 'DiskUsageMB', 
        T.logsCacheFreeDiskSize AS 'FreeDiskSpaceMB',
        T.logsCacheStatus AS 'IndexAccessPathEnabled',
        1 AS 'CacheType',
        COUNT(DB.dbId) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfHostedIndices',
        SUM(DB.uptoDate) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfOutdatedIndices',
        SUM(DB.errorLvl) OVER (PARTITION BY DB.currentIdxServer) AS 'NumberOfErrorIndices',
        CASE
        WHEN T.isOffline = 1 THEN 1
        WHEN T.isMaintenance = 1 THEN 2
        ELSE 0
        END AS 'ServerStatus',
        T.disabledReason AS 'CacheDisabledReason'
    FROM  APP_Client cl WITH (NOLOCK)
        JOIN LicUsage lu WITH (NOLOCK) ON lu.CId = cl.id
		JOIN #tmpValidMAs h ON cl.id = h.ClientId
        JOIN @tmpIndexServerPropertis T ON cl.id = T.maId
        LEFT JOIN #tmpValidIndices DB WITH (NOLOCK) ON cl.id = DB.currentIdxServer
    WHERE 
        cl.id<>1 and lu.AppType=1002 and lu.OpType='Install'
        and cl.releaseId>=16
        and T.logsCacheStatus = 1
-- WE NEED TO CONSIDER ALL MA which are marked disable due to disk full reason
update T
SET T.IndexAccessPathEnabled = 1
FROM @tempTbl T
INNER JOIN (select DISTINCT componentnameid from app_clientprop WITH (NOLOCK) WHERE (attrname = 'Idx: disabled reason' or attrname = 'Idx: logs cache disabled reason') and isnumeric(attrval)=1 and modified =0
and attrval  = '3' ) D
ON T.MAID = D.componentnameid
-- User IdxCacheStats table for cache details if SP >= 16
IF OBJECT_ID('CommServ.dbo.IdxCacheStats') IS NOT NULL
BEGIN
    UPDATE T
    SET 
        T.DbSizeMB = CS.DbSizeMB,
        T.LogsSizeMB = CS.LogsSizeMB,
        T.ReportsSizeMB = CS.ReportsSizeMB,
        T.LegacySizeMB = CS.LegacySizeMB,
        T.OtherSizeMB = CS.OtherSizeMB,
        T.LocationTableSizeMB = CS.LocationTableSizeMB
    FROM @tempTbl T
    INNER JOIN  IdxCacheStats CS ON CS.MAId = T.MAId
END
SET @surveyXML = (
    SELECT
          MAId AS '@MAId',
          MAName AS '@MAName', 
          IndexCachePath AS '@IndexCachePath', 
          IntermediateCachePath AS '@IntermediateCachePath', 
          IsShared AS '@IsShared', 
          IsUNC AS '@IsUNC', 
          TotalCapacityMB AS '@TotalCapacityMB', 
          DiskUsageMB AS '@DiskUsageMB', 
          FreeDiskSpaceMB AS '@FreeDiskSpaceMB',
          IndexAccessPathEnabled AS '@IndexAccessPathEnabled',
          DbSizeMB AS '@DbSizeMB',
          LogsSizeMB AS '@LogsSizeMB',
          ReportsSizeMB AS '@ReportsSizeMB',
          LegacySizeMB AS '@LegacySizeMB',
          OtherSizeMB AS '@OtherSizeMB',
          LocationTableSizeMB AS '@LocationTableSizeMB',
          CacheType AS '@CacheType',
          NumberOfHostedIndices AS '@NumberOfHostedIndices',
          NumberOfOutdatedIndices AS '@NumberOfOutdatedIndices',
          NumberOfErrorIndices AS '@NumberOfErrorIndices',
          ServerStatus AS '@ServerStatus',
          CacheDisabledReason AS '@CacheDisabledReason'
    FROM @tempTbl
    FOR XML PATH ('MediaAgentIndexCacheInfo')
)

--------- 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 ---------
