SET NOCOUNT ON
SET NOEXEC OFF
IF dbo.fnIsCas() = 1
BEGIN
RAISERROR(N'This script needs to be run in primary site', 15, 1)
SET NOEXEC ON
END
GO
DECLARE @PolicyCollMap TABLE (PADBID INT, CollectionID INT)
DECLARE @CollectionID INT
DECLARE @PADBID INT
DECLARE @TotalAddedRows INT = 0
DECLARE @AddedRows INT = 0
INSERT INTO @PolicyCollMap (PADBID, CollectionID)
SELECT PC.PADBID, PC.CollectionID FROM PolicyCollMapFlat PC
INNER JOIN Collections C ON PC.CollectionID = C.CollectionID
INNER JOIN PolicyAssignment PA ON PA.PADBID = PC.PADBID
INNER JOIN MDMPolicy MP ON MP.PolicyID = PA.PolicyID
WHERE PC.AutoTarget = 1 AND C.CollectionType = 1 AND MP.PolicyType = 1
WHILE EXISTS (SELECT * FROM @PolicyCollMap)
BEGIN
SELECT TOP (1) @PADBID = PADBID, @CollectionID = CollectionID, @TotalAddedRows = 0 FROM @PolicyCollMap
WHILE (1 = 1)
BEGIN
MERGE TOP (1000) MDMUserPolicyAssignment MDMUPA
USING
(
SELECT DISTINCT PA.PolicyID, U.CloudUserID, MDMP.PolicyBodyHash, MDMP.PolicyType, GETUTCDATE()
FROM Collections C
INNER JOIN PolicyCollMapFlat PCMF ON PCMF.AutoTarget = 1 AND PCMF.PADBID = @PADBID AND PCMF.CollectionID = C.CollectionID AND C.CollectionID = @CollectionID AND C.CollectionType = 1 -- User Collection
INNER JOIN PolicyAssignment PA ON PA.PADBID = PCMF.PADBID
INNER JOIN ResPolicyCollMap RPCM ON PCMF.PADBID = RPCM.PADBID AND RPCM.CollectionID = PCMF.CollectionID
INNER JOIN User_DISC U ON U.ItemKey = RPCM.MachineID
INNER JOIN MDMPolicy MDMP ON MDMP.PolicyID = PA.PolicyID AND MDMP.PolicyType = 1
WHERE ISNULL(U.CloudUserID, N'00000000-0000-0000-0000-000000000000') != N'00000000-0000-0000-0000-000000000000'
) Source ( PolicyID, CloudUserID, PolicyBodyHash, PolicyType, LastUpdateTime )
ON (MDMUPA.PolicyID = Source.PolicyID AND
MDMUPA.UserID = Source.CloudUserID AND
MDMUPA.PolicyType = Source.PolicyType)
WHEN NOT MATCHED THEN
INSERT ( PolicyID, UserID, PolicyBodyHash, PolicyType, LastUpdateTime)
VALUES ( Source.PolicyID, Source.CloudUserID, Source.PolicyBodyHash, Source.PolicyType, Source.LastUpdateTime);
SET @AddedRows = @@ROWCOUNT
SET @TotalAddedRows = @TotalAddedRows + @AddedRows
IF @AddedRows < 1000
BEGIN
BREAK
END
END
PRINT N'Added ' + CAST(@TotalAddedRows AS NVARCHAR(100)) + N' users into MDMUserPolicyAssignment for PADBID = ' + CAST(@PADBID AS NVARCHAR(100)) + N' and CollectionID = ' + CAST(@CollectionID AS NVARCHAR(100))
DELETE @PolicyCollMap WHERE PADBID = @PADBID AND CollectionID = @CollectionID
END