How to see the status of configuration policy jobs

Document ID : KB000074802
Last Modified Date : 08/06/2018
Show Technical Document Details
Introduction:
When a configuration policy is applied to all computers or a group of computer, it could take time to have it deployed on all machines.
How to see quickly the deployment status on the computers ?
Instructions:
1- In Microsoft SQL Server Management Studio, execute this SQL Query in order to create the stored procedure itcm_check_config_policy.


use mdb


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'itcm_check_config_policy' AND type = 'P') DROP PROCEDURE itcm_check_config_policy
GO

CREATE PROCEDURE itcm_check_config_policy
AS
SET NOCOUNT ON
DECLARE @C_name NVARCHAR(255), @C_policy NVARCHAR(2048), @C_state NVARCHAR(1024), @C_completiondate NVARCHAR(1024)
DECLARE @C_activationdate NVARCHAR(1024)
DECLARE @C_policy2 NVARCHAR(2048), @C_policy3 NVARCHAR(1024), @C_policy4 NVARCHAR(1024)

CREATE TABLE #TMP (name nvarchar(255), policy nvarchar(2048), state nvarchar(1024), completiondate nvarchar(1024), activationdate NVARCHAR(1024))

DECLARE curs1 CURSOR FOR
select o2.name, p1.value, p2.value, p3.value, p4.value
from csm_object o1 , csm_object o2, csm_link l1,
csm_property p1, csm_property p2, csm_property p3, csm_property p4
where o1.class=108 and l1.child=o1.id and l1.parent=o2.id
and o2.class=102 and p1.object=o1.id
and p1.name='state' and p2.name='list_c1' and p2.object=o1.id
and p3.name='configstatetime' and p3.object=o1.id
and p4.name='activationtime' and p4.object=o1.id

order by o2.id

OPEN curs1 
FETCH NEXT FROM curs1
INTO @C_name, @C_state, @C_policy, @C_completiondate, @C_activationdate

WHILE @@FETCH_STATUS=0
BEGIN
   
   SELECT @C_policy2=@C_policy
   SET @C_policy4=''
   WHILE @C_policy2<>''
   BEGIN
      SELECT @C_policy3=SUBSTRING(@C_policy2,1,CHARINDEX('~',@C_policy2)-1)
      
      IF @C_policy3=''
      BEGIN
         SET @C_policy4=@C_policy4+'Default Computer Policy'
      END
      ELSE
      BEGIN
         SET @C_policy4=@C_policy4+@C_policy3
         SET @C_policy2=SUBSTRING(@C_policy2,CHARINDEX('~',@C_policy2)+1, LEN(@C_policy2))
         SET @C_policy2=SUBSTRING(@C_policy2,CHARINDEX('~',@C_policy2)+1, LEN(@C_policy2))
         SET @C_policy3=SUBSTRING(@C_policy2,1,CHARINDEX('~',@C_policy2)-1)
         SET @C_policy4=@C_policy4+'@'+@C_policy3
      END 
         
      
      IF CHARINDEX(';',@C_policy2)=0 
      BEGIN
         SET @C_policy2=''
      END
      ELSE
      BEGIN
         SELECT @C_policy2=SUBSTRING(@C_policy2,CHARINDEX(';',@C_policy2)+1, LEN(@C_policy2))
         SET @C_policy4=@C_policy4+' - '
      END
   END
   INSERT INTO #TMP VALUES (@C_name, @C_policy4, @C_state, @C_completiondate, @C_activationdate)
      
   
   FETCH NEXT FROM curs1
   INTO @C_name, @C_state, @C_policy, @C_completiondate,@C_activationdate
      
END
CLOSE curs1
DEALLOCATE curs1


select * from #TMP ORDER BY name, policy 

DROP TABLE #TMP
GO




2- Then execute this SQL Request to see the current deployment status of configuration policies :

exec itcm_check_config_policy


User-added image