0/ConfigMgr_XXX/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}Nonec4d8f81b-a374-4ee9-b298-41cba5214a3eCentralSite=Parameters!UserTokenSIDs.Valueselect dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) as UserSIDsUserSIDsSystem.StringCentralSite=Parameters!AppInfo.Valueselect distinct aa.CollectionID,aa.CollectionName from v_ApplicationAssignment aa where aa.ApplicationName=@AppInfo order by 2trueCollectionIDSystem.StringCollectionNameSystem.StringCentralSiteselect distinct aa.ApplicationName from v_ApplicationAssignment aa order by 1trueApplicationNameSystem.StringCentralSite=Parameters!AppInfo.Value=Parameters!CollID.ValueSELECT [aa].[AssignmentID]
FROM [v_ApplicationAssignment] [aa]
WHERE [ApplicationName] = @AppInfo
AND [aa].[CollectionID] = @CollID;trueAssignmentIDSystem.Int32CentralSite=Parameters!locale.Value=Parameters!UserSIDs.Value=Parameters!AssignmentID.ValueDECLARE @PolicyModelID INT;
SELECT @PolicyModelID = [PolicyModelID]
FROM [fn_rbac_DeploymentSummary]([dbo].[fn_LShortNameToLCID](@locale), @UserSIDs)
WHERE [AssignmentID] = @AssignmentID;
SELECT *
INTO [Deployment0]
FROM
(
SELECT [AppState].[MachineName],
[AppState].[ExtendedInfoDescriptionID],
[AppState].[StatusType],
NULL AS [ErrorCode],
[AppState].[EnforcementState],
[AppState].[IsMachineChangesPersisted],
[cs].[ClientStateDescription]
FROM [fn_rbac_AppDeploymentAssetDetails]([dbo].[fn_LShortNameToLCID](@locale), @UserSIDs) [AppState]
LEFT OUTER JOIN [cm_vit].[dbo].[v_CH_ClientSummary] [cs] ON [AppState].[MachineID] = [cs].
[ResourceID]
WHERE([AppState].[PolicyModelID] = @PolicyModelID)
AND ([AppState].[AssignmentID] = @AssignmentID)
AND ([AppState].[StatusType] = [AppState].[AppStatusType])
AND ([AppState].[StatusType] <> 5)
UNION ALL
SELECT [AppState].[MachineName],
[AppState].[ExtendedInfoDescriptionID],
[AppState].[StatusType],
NULL AS [ErrorCode],
[AppState].[EnforcementState],
[AppState].[IsMachineChangesPersisted],
[cs].[ClientStateDescription]
FROM [fn_rbac_AppDeploymentRNMAssetDetails]([dbo].[fn_LShortNameToLCID](@locale), @UserSIDs)
[AppState]
LEFT OUTER JOIN [cm_vit].[dbo].[v_CH_ClientSummary] [cs] ON [AppState].[MachineID] = [cs].
[ResourceID]
WHERE([AppState].[PolicyModelID] = @PolicyModelID)
AND ([AppState].[AssignmentID] = @AssignmentID)
AND ([AppState].[StatusType] = [AppState].[AppStatusType])
AND ([AppState].[StatusType] <> 5)
UNION ALL
SELECT [AppState].[MachineName],
0 AS [ExtendedInfoDescriptionID],
4 AS [StatusType],
NULL AS [ErrorCode],
4000 AS [EnforcementState],
[AppState].[IsMachineChangesPersisted],
[cs].[ClientStateDescription]
FROM [fn_rbac_CIDeploymentUnknownAssetDetails]([dbo].[fn_LShortNameToLCID](@locale), @UserSIDs)
[AppState]
LEFT OUTER JOIN [cm_vit].[dbo].[v_CH_ClientSummary] [cs] ON [AppState].[MachineID] = [cs].
[ResourceID]
WHERE([AppState].[PolicyModelID] = @PolicyModelID)
AND ([AppState].[AssignmentID] = @AssignmentID)
UNION ALL
SELECT [AppState].[MachineName],
[AppState].[ExtendedInfoDescriptionID],
[AppState].[StatusType],
[AppState].[ErrorCode],
[AppState].[EnforcementState],
[AppState].[IsMachineChangesPersisted],
[cs].[ClientStateDescription]
FROM [fn_rbac_AppDeploymentErrorAssetDetails]([dbo].[fn_LShortNameToLCID](@locale), @UserSIDs)
[AppState]
LEFT OUTER JOIN [cm_vit].[dbo].[v_CH_ClientSummary] [cs] ON [AppState].[MachineID] = [cs].
[ResourceID]
WHERE([AppState].[PolicyModelID] = @PolicyModelID)
AND ([AppState].[AssignmentID] = @AssignmentID)
AND ([AppState].[StatusType] = [AppState].[AppStatusType])
AND ([AppState].[ErrorCode] IS NOT NULL
AND [AppState].[StatusType] = 5)
) AS [tmp];
SELECT DISTINCT
*,
CASE [dt].[StatusType]
WHEN 1
THEN 'Compliant'
WHEN 2
THEN 'In_Progress'
WHEN 3
THEN 'Requirements_Not_Met'
WHEN 4
THEN 'Unknown'
WHEN 5
THEN 'Error'
END AS [Status]
FROM [deployment0] [dt]
ORDER BY [dt].[StatusType],
[dt].[EnforcementState],
[dt].[ErrorCode],
[dt].[MachineName];
DROP TABLE [Deployment0];trueMachineNameSystem.StringExtendedInfoDescriptionIDSystem.Int32StatusTypeSystem.Int32ErrorCodeSystem.Int64EnforcementStateSystem.Int32IsMachineChangesPersistedSystem.BooleanClientStateDescriptionSystem.StringStatusSystem.String=Fields!Status.Value=Fields!Status.Value=CountDistinct(Fields!MachineName.Value)trueOutside#VALYOutputShapeDoughnutPrimaryPrimaryBlack0pt8pt#5c5c5c