SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

DECLARE @result XML
DECLARE @MANotUsed XML
DECLARE @ArrayWithNoClient XML
DECLARE @ArrayWithOrphanSnap XML
DECLARE @ArrayWithOutClient XML 
DECLARE @LibrariesWithoutSP XML
DECLARE @CleanupSP XML
DECLARE @DIPError XML



IF object_id('tempdb.dbo.#tempXML') is not null DROP TABLE #tempXML
CREATE TABLE #tempXML (QueryId INT,Query NVARCHAR(MAX))

--------------MA Not Used----------------------------------


IF object_id('tempdb.dbo.#tempMANotUsed') is not null DROP TABLE #tempMANotUsed
CREATE TABLE #tempMANotUsed (ClientId INT,Name varchar(1024) )

INSERT INTO #tempMANotUsed

SELECT DISTINCT AC.Id ,AC.Name
FROM   APP_Client AC WITH(READUNCOMMITTED)
JOIN   MMHost H WITH(READUNCOMMITTED) ON H.ClientId = AC.id
WHERE  AC.id NOT IN (      
        /* Snap & Analytics */
        SELECT DISTINCT SourceClientId 
        FROM SMVolume WITH(READUNCOMMITTED) 
        WHERE PruneFlags = 1 
        UNION
        SELECT DISTINCT componentNameId 
        FROM APP_ClientProp WITH(READUNCOMMITTED) 
        WHERE (attrName='Snap Backups Enabled' AND attrVal = '1' AND modified = 0)
                OR (attrName='AnalyticSearchEngineEnabled' AND attrVal = '1' AND modified = 0)
        UNION
        /* Storage Targets */
        SELECT DISTINCT DTP.HostClientId
        FROM MMDataPath DTP WITH (READUNCOMMITTED) 
        INNER JOIN archGroupCopy AGC WITH (READUNCOMMITTED)  ON AGC.id = DTP.copyId
        AND AGC.isActive = 1
		UNION
		/*Index Store, Web Server, Search Engine */
		SELECT pkg.ClientId FROM siminstalledpackages AS pkg INNER JOIN App_Client AS c ON c.Id = pkg.ClientId AND simPackageID IN (55, 252, 257)
)
AND AC.id > 1


SET @result = (SELECT ClientId as '@MAId',name as '@MediaAgent'
from #tempMANotUsed 
for XML path ('MANotUsed'))

IF object_id('tempdb.dbo.#tempMANotUsed') is not null  DROP TABLE #tempMANotUsed

 SET @MANotUsed = (
 SELECT 
 @result 
 FOR XML PATH ('MANotUsedDetails'))

 INSERT INTO #tempXML VALUES (1,CAST(@MANotUsed AS NVARCHAR(MAX)))



 ------------------Array with no association Clients--------------


 IF object_id('tempdb.dbo.#tempArrayWithNoClient') is not null DROP TABLE #tempArrayWithNoClient
CREATE TABLE #tempArrayWithNoClient (ArrayId INT,ArrayName NVARCHAR(1024))

INSERT INTO #tempArrayWithNoClient

SELECT  DISTINCT CTRL.ControlHostId , CTRL.SMArrayId 
   FROM   SMControlHost CTRL (NOLOCK)
   WHERE CTRL.ControlHostId NOT IN ( SELECT DISTINCT ControlHostId FROM SMSnap (NOLOCK))
   AND CTRL.ControlHostId > 0

   
SET @result = (SELECT ArrayId AS '@ArrayId' ,ArrayName as '@ArrayName'
from #tempArrayWithNoClient 
for XML path ('ArrayWithNoClient'))

IF object_id('tempdb.dbo.#tempArrayWithNoClient') is not null DROP TABLE #tempArrayWithNoClient

 SET @ArrayWithNoClient = (
 SELECT 
 @result 
 FOR XML PATH ('ArrayWithNoClientDetails'))

 INSERT INTO #tempXML VALUES (2,CAST(@ArrayWithNoClient AS NVARCHAR(MAX)))


 ----------------------------Array With Orphaned Snaps---------------------------

  IF object_id('tempdb.dbo.#tempArrayWithOrphanSnap') is not null DROP TABLE #tempArrayWithOrphanSnap
CREATE TABLE #tempArrayWithOrphanSnap (ArrayId INT,ArrayName NVARCHAR(1024), SnapEngineName  NVARCHAR(1024), DeletedClientId INTEGER)

INSERT INTO #tempArrayWithOrphanSnap

 SELECT  DISTINCT CTRL.ControlHostId AS id, CTRL.SMArrayId ArrayName, ENG.SnapEngineName SnapEngine, VOL.SourceClientId DeletedClientId
FROM   SMVolume VOL (NOLOCK) INNER JOIN SMVolSnapMap MAP (NOLOCK) ON VOL.SMVolumeId = MAP.SMVolumeId
                                INNER JOIN SMSnap SNAP (NOLOCK) ON MAP.SMSnapId = SNAP.SMSnapId
                                LEFT JOIN APP_Client CLNT (NOLOCK) ON CLNT.Id = VOL.SourceClientId
                                INNER JOIN SMControlHost CTRL (NOLOCK) ON CTRL.ControlHostId = SNAP.ControlHostId
                                INNER JOIN SMSnapShotEngine ENG (NOLOCK) ON ENG.SnapShotEngineId = SNAP.SnapShotEngineId
WHERE CLNT.id IS NULL OR (CLNT.status & 2 = 2/*CV_STATUS_UNINSTALLED*/)

   
SET @result = (SELECT ArrayId AS '@ArrayId' ,ArrayName AS '@ArrayName' , SnapEngineName  AS '@SnapEngineName' , DeletedClientId AS '@DeletedClientId'
from #tempArrayWithOrphanSnap 
for XML path ('ArrayWithOrphanSnap'))

 IF object_id('tempdb.dbo.#tempArrayWithOrphanSnap') is not null DROP TABLE #tempArrayWithOrphanSnap

 SET @ArrayWithOrphanSnap = (
 SELECT 
 @result 
 FOR XML PATH ('ArrayWithOrphanSnapDetails'))

 INSERT INTO #tempXML VALUES (3,CAST(@ArrayWithOrphanSnap AS NVARCHAR(MAX)))


 -------------------------------Array With Failed Deleted Snap-------------------
 
 

IF object_id('tempdb.dbo.#tempArrayWithFailTodeleteSnap') is not null DROP TABLE #tempArrayWithFailTodeleteSnap
CREATE TABLE #tempArrayWithFailTodeleteSnap (ArrayId INT,ArrayName NVARCHAR(1024), ClientId  INT, JobCount INTEGER,SnapCount INT)

INSERT INTO #tempArrayWithFailTodeleteSnap

SELECT  DISTINCT CTRL.ControlHostId AS id, CTRL.SMArrayId ArrayName, CLNT.Id clientId, COUNT(CTRL.ControlHostId), T.SnapCount
FROM   (
                                                SELECT  VOL.SourceClientId ClientId, SNAP.ControlHostId, VOL.JobId, COUNT(DISTINCT SNAP.SMSnapId) SnapCount
                                                FROM   SMVolume VOL (NOLOCK) INNER JOIN SMVolSnapMap MAP (NOLOCK) ON VOL.SMVolumeId = MAP.SMVolumeId
                                                                                INNER JOIN SMSnap SNAP (NOLOCK) ON MAP.SMSnapId = SNAP.SMSnapId
                                                WHERE ( VOL.PruneFlags = 0  OR VOL.PruneFlags > 1 )
                                                                                AND VOL.MountStatus = 98
                                                GROUP BY VOL.SourceClientId, SNAP.ControlHostId, VOL.JobId

                                ) T
                               INNER JOIN APP_Client CLNT (NOLOCK) ON CLNT.Id = T.ClientId
                                INNER JOIN SMControlHost CTRL (NOLOCK) ON T.ControlHostId = CTRL.ControlHostId
                                GROUP BY CTRL.ControlHostId, CTRL.SMArrayId, CLNT.Id, T.SnapCount

   
SET @result = (SELECT ArrayId AS '@ArrayId' ,ArrayName '@ArrayName', ClientId  As '@ClientId', JobCount AS '@JobCount' ,SnapCount AS '@SnapCount'
from #tempArrayWithFailTodeleteSnap 
for XML path ('ArrayWithFailtoDeleteSnap'))

IF object_id('tempdb.dbo.#tempArrayWithFailTodeleteSnap') is not null DROP TABLE #tempArrayWithFailTodeleteSnap

 SET @ArrayWithOutClient = (
 SELECT 
 @result 
 FOR XML PATH ('ArrayWithFailtoDeleteSnapDetails'))

 INSERT INTO #tempXML VALUES (4,CAST(@ArrayWithOutClient AS NVARCHAR(MAX)))



------------------------DIP Error--------------------------------------------------------

if object_id('tempdb.dbo.#tmpCCRLast7Days') is not null DROP TABLE #tmpCCRLast7Days
if object_id('tempdb.dbo.#tmpResult1') is not null DROP TABLE #tmpResult1
IF object_id('TempDB.dbo.#tmpDataPath1') IS NOT NULL drop table #tmpDataPath1
If object_id('TempDB.dbo.#tmpDataInterfacePairs') IS NOT NULL drop table #tmpDataInterfacePairs

create table #tmpResult1
(
       clientId        integer,
       MAHostClientId      integer,
)

create table #tmpDataPath1  
(
       DataPathId     integer,
       HostClientId       integer,
       ReadinessFlag      integer,
       DrivePoolId        integer
)

CREATE TABLE #tmpDataInterfacePairs 
(
       clientId    integer,
       clientInterface varchar(1024),
       mediaAgentId integer,
       mediaAgentInterface varchar(1024),
       errorCode1      integer,
       errorCode2      integer,
       errorCode       varchar(1024),
       flag            integer
)

CREATE TABLE #tmpCCRLast7Days(CCRId INT)

DECLARE @Last7Days INT
SELECT @Last7Days = DATEDIFF(SS, '1970-01-01', DATEADD(DD, -7, GETUTCDATE()))

INSERT INTO #tmpCCRLast7Days
SELECT DISTINCT MAX(CCR.CCRId) AS CCRId
FROM CCRCommCellReadiness CCR
       INNER JOIN CCRToSubClient CCRSC
              ON CCR.CCRId = CCRSC.CCRId
              AND CCR.CCREndTime >= @Last7Days
              AND CCRResultFlag & 8 = 8  --CR_DIP_CONNECTION_FAILED 
GROUP BY CCRSC.ClientId, CCRSC.SubClientId, CCRSC.DataPathId

insert into #tmpDataPath1
select distinct MD.DataPathId, MDP.ClientId, MD.ReadinessFlag, MD.DrivePoolId
From MMDrivePool as MDP WITH (READUNCOMMITTED), MMDataPath as MD WITH (READUNCOMMITTED)
where MD.DrivePoolId = MDP.DrivePoolId 

insert into #tmpResult1 
select distinct CCRS.ClientId, mcf.HostClientId
from CCRToSubClient as CCRS WITH (READUNCOMMITTED) INNER JOIN #tmpDataPath1 as mcf 
ON mcf.DataPathId = CCRS.DataPathId
INNER JOIN #tmpCCRLast7Days CCR
ON CCR.CCRId = CCRS.CCRId
AND CCRResultFlag & 8 = 8  --CR_DIP_CONNECTION_FAILED 

INSERT INTO #tmpDataInterfacePairs
SELECT tt.clientId, ap.srcInterface, tt.MAHostClientId, ap.destInterface, 0, 0, NULL, 0
FROM (SELECT DISTINCT clientId, MAHostClientId from #tmpResult1) as tt,
        (select FromClientId as srcClientId,FromClientInterfaceName as srcInterface,ToClientId as destClientId,ToClientInterfaceName as destInterface from CCRClientToClient WITH (READUNCOMMITTED)
        union 
        select ToClientId as srcClientId,ToClientInterfaceName as srcInterface,FromClientId as destClientId,FromClientInterfaceName  as destInterface from CCRClientToClient WITH (READUNCOMMITTED)) as ap
where tt.clientid=ap.srcClientId AND tt.MAHostClientId = ap.destClientId 

update #tmpDataInterfacePairs
set errorcode1=CCR.errorCode
from #tmpDataInterfacePairs tt, CCRClientToClient CCR WITH (READUNCOMMITTED)
where tt.clientId=CCR.fromClientId and tt.clientInterface = CCR.FromClientInterfaceName COLLATE SQL_Latin1_General_CP1_CI_AS and
        tt.mediaAgentId = CCR.ToClientId and tt.mediaAgentInterface = CCR.ToClientInterfaceName COLLATE SQL_Latin1_General_CP1_CI_AS and
        isDip = 1 and serviceType = 0

update #tmpDataInterfacePairs
set errorcode2=CCR.errorCode
from #tmpDataInterfacePairs tt,CCRClientToClient CCR WITH (READUNCOMMITTED)
where tt.mediaAgentId=CCR.fromClientId and tt.mediaAgentInterface = CCR.FromClientInterfaceName COLLATE SQL_Latin1_General_CP1_CI_AS and
        tt.clientId = CCR.ToClientId and tt.clientInterface = CCR.ToClientInterfaceName COLLATE SQL_Latin1_General_CP1_CI_AS and
        CCR.isDip = 1 and CCR.serviceType = 0

update #tmpDataInterfacePairs
set errorCode = cast(errorCode1 as varchar)+'*'+cast(errorCode2 as varchar)

update #tmpDataInterfacePairs
set flag = 1
where errorCode1 !=0 OR errorCode2 != 0


SET @result = (SELECT DIP.clientId AS '@ClientId', dip.clientInterface AS '@ClientInterface', DIP.mediaAgentId AS '@MAId', DIP.mediaAgentInterface AS '@MAInterface', DIP.errorCode AS '@errorCode'
FROM #tmpDataInterfacePairs DIP
WHERE DIP.flag = 1
for XML PATH ('DIPError'))

if object_id('tempdb.dbo.#tmpCCRLast7Days') is not null DROP TABLE #tmpCCRLast7Days
If object_id('TempDB.dbo.#tmpResult1') IS NOT NULL drop table #tmpResult1
If object_id('TempDB.dbo.#tmpDataPath1') IS NOT NULL drop table #tmpDataPath1
If object_id('TempDB.dbo.#tmpDataInterfacePairs') IS NOT NULL drop table #tmpDataInterfacePairs


SET @DIPError = (
 SELECT 
 @result 
 FOR XML PATH ('DIPErrorDetails'))

 INSERT INTO #tempXML VALUES (5,CAST(@DIPError AS NVARCHAR(MAX)))


 -------------------------------------Storage -------------------------------------
 
	---------------------------------Libraries not associated with Any Storage Policy--------------
IF object_id('tempdb.dbo.#tempLibrariesWithoutSP') is not null DROP TABLE #tempLibrariesWithoutSP
CREATE TABLE #tempLibrariesWithoutSP (LibraryId INT,LibraryName NVARCHAR(MAX),Flags INT)


IF object_id('tempdb.dbo.#tempLibWithoutMP') is not null DROP TABLE #tempLibWithoutMP
CREATE TABLE #tempLibWithoutMP (LibraryId INT,LibraryName NVARCHAR(MAX))

INSERT INTO #tempLibrariesWithoutSP

SELECT ML.LibraryId,AliasName,1
FROM MMLibrary ML WITH (NOLOCK)
LEFT OUTER JOIN (SELECT  MP.LibraryId 
              FROM MMDataPath DTP WITH (NOLOCK)
              INNER JOIN MMDrivePool DP WITH (NOLOCK) ON DTP.DrivePoolId = DP.DrivePoolId 
              INNER JOIN MMMasterPool MP WITH (NOLOCK) ON DP.MasterPoolId = MP.MasterPoolId
) T
ON ML.LibraryId = T.LibraryId
WHERE T.LibraryId IS NULL
AND ML.ExtendedAttributes & 33554432 <> 33554432 /* MMS2_HIDE_LIBRARY */
UNION
SELECT ML.LibraryId,AliasName,2
FROM MMLibrary ML WITH (NOLOCK)
LEFT OUTER JOIN (SELECT  MP.LibraryId 
              FROM MMDataPath DTP WITH (NOLOCK)
              INNER JOIN MMDrivePool DP WITH (NOLOCK) ON DTP.DrivePoolId = DP.DrivePoolId 
              INNER JOIN MMMasterPool MP WITH (NOLOCK) ON DP.MasterPoolId = MP.MasterPoolId
) T
ON ML.LibraryId = T.LibraryId
WHERE T.LibraryId IS NULL
AND ML.ExtendedAttributes & 33554432 = 33554432

 
INSERT INTO #tempLibWithoutMP
SELECT ML.LibraryId,AliasName
FROM MMLibrary ML WITH (NOLOCK)
  LEFT OUTER JOIN MMMountPath MP WITH (NOLOCK)
  ON ML.LibraryId = MP.LibraryId 
WHERE MP.MountPathId IS NULL
  AND ML.LibraryTypeId = 3

UPDATE #tempLibrariesWithoutSP 
SET Flags = Flags | 4
where LibraryId IN (select LibraryId from #tempLibWithoutMP)

 INSERT INTO #tempLibrariesWithoutSP
 select T.LibraryId,T.LibraryName,4
 from #tempLibWithoutMP T
 LEFT OUTER JOIN #tempLibrariesWithoutSP S ON S.LibraryId = T.LibraryId 
 where S.LibraryId IS NULL

IF object_id('tempdb.dbo.#tempLibWithoutMP') is not null DROP TABLE #tempLibWithoutMP

SET @result = (SELECT LibraryId AS '@LibraryId' ,LibraryName AS '@LibraryName' ,Flags AS '@Flags'
from #tempLibrariesWithoutSP 
for XML path ('LibrariesWithoutSP'))

IF object_id('tempdb.dbo.#tempLibrariesWithoutSP') is not null DROP TABLE #tempLibrariesWithoutSP

 SET @LibrariesWithoutSP = (
 SELECT 
 @result 
 FOR XML PATH ('LibrariesWithoutSPDetails'))

 INSERT INTO #tempXML VALUES (6,CAST(@LibrariesWithoutSP AS NVARCHAR(MAX)))

 
--------------------Storage--------------------
--------------------Storage Policy with Dedupe Ration less than 85% --------------------

IF object_id('tempdb.dbo.#tempSPCleanup') is not null DROP TABLE #tempSPCleanup
CREATE TABLE #tempSPCleanup (SPId INT,SPName  nvarchar(MAX),copyId INT,CopyName NVARCHAR(MAX),dedupSaving DECIMAL(10,2),flags INT)


--Storage Policy with Dedupe Ration less than 85% 

--//Assuming what we want is list of copies with dedupe savings < 85%

CREATE TABLE #DedupeCopy (
    archGrpCopyId INT, activeStoreId INT, dedupeFlags INT, 
    dataRead BIGINT, dataWritten BIGINT, 
    dedupRatio FLOAT, dedupSaving DECIMAL(10,2)
  )

IF OBJECT_ID('DedupCopySaving', 'P') IS NOT NULL
BEGIN
	EXEC DedupCopySaving

	INSERT INTO #tempSPCleanup
	SELECT AG.id, AG.name AS StoragePolicy, AGC.copy, AGC.name AS Copy, dedupSaving, 128
	FROM archGroup AG WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK),#DedupeCopy DC WITH (NOLOCK)
	WHERE AG.id = AGC.archGroupId 
		AND AGC.id = DC.archGrpCopyId
		AND dedupSaving < 85 
		AND dataWritten > 0
		AND AGC.type <> 5
END

DROP TABLE #DedupeCopy

---------------- Storage Policy With DataAging Disabled------------------

IF object_id('tempdb.dbo.#tempSPWithDADisable') is not null DROP TABLE #tempSPWithDADisable
CREATE TABLE #tempSPWithDADisable (SPId INT,SPName  nvarchar(1024),CopyID INT,CopyName nvarchar(1024),AdditionalDetails INT)

INSERT INTO #tempSPWithDADisable
SELECT AG.id, AG.name , AGC.copy,
AGC.name ,
CASE WHEN AGR.flags & 4 = 4 THEN 0 ELSE 1 END AS AdditionalDetails
FROM archGroup AG WITH (NOLOCK)
INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.id=AGC.archGroupId
INNER JOIN archAgingRule AGR WITH (NOLOCK) ON AGC.id = AGR.copyId
WHERE AGC.type <> 5 --transitive copies
AND (AGR.flags & 4 = 4 --data aging disabled
OR (retentionDays = -1 AND fullCycles = -1 AND archiverRetDays = -1)) -- data aging enabled but infinite retention
AND AG.flags & 256 <> 256 /* GDSP - CVA_SP_GLOBAL_DEDUP_FLAG  */

UPDATE  T
SET T.Flags = T.Flags | 256
from #tempSPCleanup T
INNER JOIN  #tempSPWithDADisable DA ON T.SPId = DA.SPId AND T.copyId = DA.copyId AND AdditionalDetails = 0

UPDATE  T
SET T.Flags = T.Flags | 512
from #tempSPCleanup T
INNER JOIN  #tempSPWithDADisable DA ON T.SPId = DA.SPId AND T.copyId = DA.copyId AND AdditionalDetails = 1

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,T.copyId,T.CopyName,0,256
from #tempSPWithDADisable T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID AND T.copyId = S.copyId 
where (S.SPId IS NULL OR S.copyId IS NULL) AND T.AdditionalDetails = 0


INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,T.copyId,T.CopyName,0,512
from #tempSPWithDADisable T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID AND T.copyId = S.copyId 
where (S.SPId IS NULL OR S.copyId IS NULL) AND T.AdditionalDetails = 1

IF object_id('tempdb.dbo.#tempSPWithDADisable') is not null DROP TABLE #tempSPWithDADisable


-------- Storage Policy Not Using GDSP----------------

IF object_id('tempdb.dbo.#tempSPWithoutGSDP') is not null DROP TABLE #tempSPWithoutGSDP
CREATE TABLE #tempSPWithoutGSDP (SPId INT,SPName  nvarchar(1024),CopyID INT,CopyName nvarchar(1024))


INSERT INTO #tempSPWithoutGSDP
select AG.id, AG.name ,AGC.Copy,
    AGC.name  
FROM archGroup AG WITH (NOLOCK)
  INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.id = AGC.archGroupId
WHERE dedupeFlags&262144 = 262144 --CVA_SIDB_STORE_ENABLED_FLAG; dedupe is enabled
  AND dedupeFlags&134217728 = 0 --CVA_USE_GLOBAL_DEDUP_STORE_FLAG; is not using GDSP
  AND dedupeflags&268435456 = 0 --CVA_HOST_GLOBAL_DEDUP_STORE_FLAG; is not a GDSP
  AND AGC.type <> 5


UPDATE  T
SET T.Flags = T.Flags | 64
from #tempSPCleanup T
INNER JOIN #tempSPWithoutGSDP DA ON T.SPId = DA.SPId AND T.copyId = DA.copyId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,T.copyId,T.CopyName,0,64
from #tempSPWithoutGSDP T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID AND T.copyId = S.copyId 
where S.SPId IS NULL OR S.copyId IS NULL 


IF object_id('tempdb.dbo.#tempSPWithoutGSDP') is not null DROP TABLE #tempSPWithoutGSDP

------------------Storage Policy Copies With More Than 10 Datapaths---------------------

IF object_id('tempdb.dbo.#tempSPWithMoreDataPath') is not null DROP TABLE #tempSPWithMoreDataPath
CREATE TABLE #tempSPWithMoreDataPath (SPId INT,SPName  nvarchar(1024),CopyID INT,copyName nvarchar(1024))


INSERT INTO #tempSPWithMoreDataPath
SELECT AG.id, AG.name ,AGC.Copy,
    AGC.name 
FROM archGroup AG WITH (NOLOCK)
  INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.id=AGC.archGroupId and AGC.type <> 5
  INNER JOIN (SELECT CopyId 
        FROM MMDataPath WITH (NOLOCK)
        GROUP BY CopyId 
        HAVING count(DrivePoolId) > 10) DP 
    ON AGC.id=DP.CopyId

UPDATE  T
SET T.Flags = T.Flags | 32
from #tempSPCleanup T
INNER JOIN  #tempSPWithMoreDataPath DA ON T.SPId = DA.SPId AND T.copyId = DA.copyId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,T.copyId,T.CopyName,0,32
from #tempSPWithMoreDataPath T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID AND T.copyId = S.copyId 
where S.SPId IS NULL OR S.copyId IS NULL 


IF object_id('tempdb.dbo.#tempSPWithMoreDataPath') is not null DROP TABLE #tempSPWithMoreDataPath


-------------------------Storage Policies that Backup DDB or Index Cache only---------
IF object_id('tempdb.dbo.#tempSPBackupDDB') is not null DROP TABLE #tempSPBackupDDB
CREATE TABLE #tempSPBackupDDB (SPId INT,SPName  nvarchar(1024))

declare @ddbArchGroup TABLE (agId int)

-- get all the storage policies backing up ddb subclients
insert into @ddbArchGroup
SELECT distinct APP.dataArchGrpID
FROM APP_Application APP WITH (NOLOCK)
  INNER JOIN APP_SubClientProp AC WITH (NOLOCK) 
    ON APP.id = AC.componentNameId AND AC.attrName IN ('DDB Backup', 'Index SubClient') 
	AND AC.cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient')) AND AC.attrVal = '1'
WHERE APP.dataArchGrpID > 1

INSERT INTO #tempSPBackupDDB
SELECT ag.id , ag.name  from @ddbArchGroup res INNER JOIN archGroup ag ON res.agId = ag.id

UPDATE T
SET T.Flags = T.Flags | 16
from #tempSPCleanup T
INNER JOIN  #tempSPBackupDDB DA ON T.SPId = DA.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,16
from #tempSPBackupDDB T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

IF object_id('tempdb.dbo.#tempSPBackupDDB') is not null  DROP TABLE #tempSPBackupDDB

-----------------------Hidden SP with NO ASSOCIATION-------------
IF object_id('tempdb.dbo.#tempHSPWithoutAssoc') is not null DROP TABLE #tempHSPWithoutAssoc
CREATE TABLE #tempHSPWithoutAssoc (SPId INT,SPName  nvarchar(1024))

INSERT INTO #tempHSPWithoutAssoc
SELECT AG.id, AG.name
FROM archGroup AG WITH (NOLOCK)
LEFT OUTER JOIN APP_Application APP WITH (NOLOCK) ON AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID 
WHERE APP.id IS NULL AND AG.id > 1
AND flags & 256 <> 256 /* GDSP - CVA_SP_GLOBAL_DEDUP_FLAG  */
AND flags & 16384 <> 16384 /* GACP - CVA_SP_GLOBAL_AUXCOPY_POLICY_FLAG */
AND flags & 64 = 64 /* CVA_SP_HIDDEN_FLAG */

UPDATE  T
SET T.Flags = T.Flags | 8
from #tempSPCleanup T
INNER JOIN  #tempHSPWithoutAssoc DA ON T.SPId = DA.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,8
from #tempHSPWithoutAssoc T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

IF object_id('tempdb.dbo.#tempHSPWithoutAssoc') is not null DROP TABLE #tempHSPWithoutAssoc

------------------------Storage Policies that have reached MAX capacity------------
IF object_id('tempdb.dbo.#tempSPMaxCapacity') is not null DROP TABLE #tempSPMaxCapacity
CREATE TABLE #tempSPMaxCapacity (SPId INT,SPName  nvarchar(1024))


INSERT INTO #tempSPMaxCapacity
SELECT id, name
FROM archGroup WITH (NOLOCK)
WHERE flags&65536 = 65536 --CVA_SP_STOP_NEW_SUBCLIENT_ASSOC_DUE_TO_STORE_LIMIT_FLAG

UPDATE  T
SET T.Flags = T.Flags | 4
from #tempSPCleanup T
INNER JOIN  #tempSPMaxCapacity DA ON T.SPId = DA.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,4
from #tempSPMaxCapacity T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

IF object_id('tempdb.dbo.#tempSPMaxCapacity') is not null DROP TABLE #tempSPMaxCapacity

-----------------Storage Policy with More than 3 copies----------
IF object_id('tempdb.dbo.#tempSPMoreCopies') is not null DROP TABLE #tempSPMoreCopies
CREATE TABLE #tempSPMoreCopies (SPId INT,SPName  nvarchar(1024))

INSERT INTO #tempSPMoreCopies
SELECT AG.Id,AG.name
FROM  archGroup AG  WITH (NOLOCK)
  INNER JOIN (SELECT AGC.archGroupId 
        FROM archGroupCopy AGC WITH (NOLOCK)
		where AGC.type <> 5
        GROUP BY AGC.archGroupId 
        HAVING count(AGC.id) > 3
        ) T 
    ON AG.id = T.archGroupId


UPDATE  T
SET T.Flags = T.Flags | 2
from #tempSPCleanup T
INNER JOIN  #tempSPMoreCopies DA ON T.SPId = DA.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,2
from #tempSPMoreCopies T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

IF object_id('tempdb.dbo.#tempSPMoreCopies') is not null DROP TABLE #tempSPMoreCopies


---------Storage POlicy with No Association-----------
IF object_id('tempdb.dbo.#tempSPwithNoAssoc') is not null DROP TABLE #tempSPwithNoAssoc
CREATE TABLE #tempSPwithNoAssoc (SPId INT,SPName  nvarchar(1024))


CREATE TABLE #tmpSPs(id INT, name nvarchar(288))

INSERT INTO #tmpSPs(id, name)
SELECT AG.id, AG.name
FROM archGroup AG WITH (NOLOCK)
LEFT OUTER JOIN APP_Application APP WITH (NOLOCK) ON AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID 
WHERE APP.id IS NULL AND AG.id > 1
AND flags & 256 <> 256 /* GDSP - CVA_SP_GLOBAL_DEDUP_FLAG  */
AND flags & 16384 <> 16384 /* GACP - CVA_SP_GLOBAL_AUXCOPY_POLICY_FLAG */
AND flags & 64 <> 64 /* CVA_SP_HIDDEN_FLAG */

--do not consider storage pools that are associated with plans
IF OBJECT_ID (N'App_PlanProp', N'U') IS NOT NULL
BEGIN
  DELETE PLANS 
    FROM #tmpSPs PLANS
      INNER JOIN App_PlanProp PP ON PLANS.id = PP.attrVal
      WHERE PP.attrName IN ('Storage policy', 'Log storage policy')
END

/* Exclude aged jobs or SP with no jobs) */
DELETE AG 
FROM #tmpSPs AG
INNER JOIN (
	SELECT dataArchGrpId
	FROM  JMBkpStats WITH (NOLOCK)
	WHERE dataStatus = 0 AND dataArchGrpId > 1
	GROUP BY dataArchGrpId
	) DAG ON AG.id = DAG.dataArchGrpId

DELETE AG 
FROM #tmpSPs AG
INNER JOIN (
	SELECT logArchGrpId
	FROM  JMBkpStats WITH (NOLOCK)
	WHERE dataStatus = 0 AND logArchGrpId > 1
	GROUP BY logArchGrpId
	) DAG ON AG.id = DAG.logArchGrpId

INSERT INTO #tempSPwithNoAssoc
SELECT id, name FROM #tmpSPs

DROP TABLE #tmpSPs
UPDATE  T
SET T.Flags = T.Flags | 1
from #tempSPCleanup T
INNER JOIN  #tempSPwithNoAssoc DA ON T.SPId = DA.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,1
from #tempSPwithNoAssoc T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

---------Empty Storage Policies-----------
-- get Empty Storage Policies (Storage policies without any data, without any associations, and created more than 30 days ago)
-- flag - 1024
IF object_id('tempdb.dbo.#tempEmptySP') is not null DROP TABLE #tempEmptySP
CREATE TABLE #tempEmptySP (SPId INT,SPName  nvarchar(1024))

IF OBJECT_ID (N'ArchGroupCoreHistory', N'U') IS NOT NULL
BEGIN
INSERT INTO #tempEmptySP
  SELECT AGCH.ArchGroupId, AGCH.ArchGroupName from #tempSPwithNoAssoc TSNA
    INNER JOIN ArchGroupCoreHistory AGCH ON TSNA.SPId = AGCH.ArchGroupId
    LEFT JOIN JMJobDataStats JMDS ON TSNA.SPId = JMDS.archGrpId AND JMDS.jobId IS NULL
    GROUP BY AGCH.ArchGroupId, AGCH.ArchGroupName
    HAVING MIN(Modified)+30 < GETUTCDATE()
END

UPDATE  T
SET T.Flags = T.Flags | 1024
from #tempSPCleanup T
INNER JOIN  #tempEmptySP ES ON T.SPId = ES.SPId 

INSERT INTO #tempSPCleanup
select T.SPID,T.SPName,0,'',0,1024
from #tempEmptySP T
LEFT OUTER JOIN #tempSPCleanup S ON S.SPID = T.SPID 
where S.SPId IS NULL

IF object_id('tempdb.dbo.#tempEmptySP') is not null  DROP TABLE #tempEmptySP
IF object_id('tempdb.dbo.#tempSPwithNoAssoc') is not null  DROP TABLE #tempSPwithNoAssoc

SET @result = (SELECT SPId AS '@SPId',  SPName AS '@SPName', copyId AS '@copyId', CopyName AS '@CopyName',dedupSaving AS '@dedupSaving',flags AS '@Flags'
FROM #tempSPCleanup

for XML PATH ('CleanupSP'))

SET @CleanupSP = (
 SELECT 
 @result 
 FOR XML PATH ('CleanupSPDetails'))

 INSERT INTO #tempXML VALUES (7,CAST(@CleanupSP AS NVARCHAR(MAX)))


 -----------------Final XML-----------------
  SET @surveyXML = (
SELECT
    (SELECT 
		QueryId AS '@QueryId',
        Query AS '@Query'
        
    FROM #tempXML FOR XML PATH ('MAPolicyCleanupXML'), TYPE)
	FOR XML PATH('MAPolicyCleanup')
	)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

--select cast(@surveyXML as XML) 
SET @outputXML = 
(
    SELECT
        @queryId AS '@QueryId',
        @EndTime AS '@LogDate',
        (@EndTime - @LogDate) AS '@QueryRunningTime',
        @surveyXML
    FOR XML PATH('Rpt_CSSXMLDATA')
)

IF object_id('tempdb.dbo.#tempXML') is not null  DROP TABLE #tempXML