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