Policy error bug?

Hi,

I don't have that version handy but here goes.  You may want to install SQL Server Management Studio for this one.

http://www.microsoft.com/download/en/details.aspx?id=8961

So when you click on the filter "Computers that differ from policy" the following takes place.

The first stored procedure is called, i.e.:
dbo.ComputerListGet 1,31,8,1

Which calls "ComputerListGetFiltered " where 31 is the filter type, so this goes on to call SQL:

SELECT ComputerID     

FROM dbo.ComputerListWithPolicyViolation( @SubestateID )

ORDER BY ComputerID

Depending on the subestate you're in this goes on to possibly call:

ComputerListWithPolicyViolationAll

for the default subestate.

Which finally makes the following SQL query:

SELECT DISTINCT ComputerID    
FROM ComputerPolicyStates cps WITH (NOLOCK)        
JOIN dbo.AliveComputers c WITH (NOLOCK)ON c.ID=cps.ComputerID         JOIN dbo.AggregatedCompliancePolicyTypes acpt WITH (NOLOCK) ON cps.[Type] = acpt.PolicyType       
WHERE (cps.State = 2 OR cps.State = 4)              
AND ComputerID IN ( 
SELECT ComputerID FROM dbo.ComputerGroupMapping WITH (NOLOCK) WHERE GroupID <> 1)              
AND c.Managed=1 

So this is the query that is returning the ID of that machine.  It might be therefore worth breaking down the above SQL command to see how it's being included.

Firstly though it might be worth finding the ID of this machine first by running:

SELECT ID from ComputersAndDeletedComputers where Name ='ILPAD01'

You can then identify this ID popping up in the above command.

So breaking the above query down into parts you could start with:

SELECT DISTINCT ComputerID    FROM ComputerPolicyStates cps WITH (NOLOCK)        JOIN dbo.AliveComputers c WITH (NOLOCK)ON c.ID=cps.ComputerID         JOIN dbo.AggregatedCompliancePolicyTypes acpt WITH (NOLOCK) ON cps.[Type] = acpt.PolicyType       WHERE (cps.State = 2 OR cps.State = 4) 

Does the machine id appear?

If not, add on next clause:

AND ComputerID IN ( SELECT ComputerID FROM dbo.ComputerGroupMapping WITH (NOLOCK) WHERE GroupID <> 1)  

If it appears then, it would suggest looking at the table ComputerGroupMapping , etc..

Hope this helps set you off on the right direction.

Regards,

Jak

Hi,

I don't have that version handy but here goes.  You may want to install SQL Server Management Studio for this one.

http://www.microsoft.com/download/en/details.aspx?id=8961

So when you click on the filter "Computers that differ from policy" the following takes place.

The first stored procedure is called, i.e.:
dbo.ComputerListGet 1,31,8,1

Which calls "ComputerListGetFiltered " where 31 is the filter type, so this goes on to call SQL:

SELECT ComputerID     

FROM dbo.ComputerListWithPolicyViolation( @SubestateID )

ORDER BY ComputerID

Depending on the subestate you're in this goes on to possibly call:

ComputerListWithPolicyViolationAll

for the default subestate.

Which finally makes the following SQL query:

SELECT DISTINCT ComputerID    
FROM ComputerPolicyStates cps WITH (NOLOCK)        
JOIN dbo.AliveComputers c WITH (NOLOCK)ON c.ID=cps.ComputerID         JOIN dbo.AggregatedCompliancePolicyTypes acpt WITH (NOLOCK) ON cps.[Type] = acpt.PolicyType       
WHERE (cps.State = 2 OR cps.State = 4)              
AND ComputerID IN ( 
SELECT ComputerID FROM dbo.ComputerGroupMapping WITH (NOLOCK) WHERE GroupID <> 1)              
AND c.Managed=1 

So this is the query that is returning the ID of that machine.  It might be therefore worth breaking down the above SQL command to see how it's being included.

Firstly though it might be worth finding the ID of this machine first by running:

SELECT ID from ComputersAndDeletedComputers where Name ='ILPAD01'

You can then identify this ID popping up in the above command.

So breaking the above query down into parts you could start with:

SELECT DISTINCT ComputerID    FROM ComputerPolicyStates cps WITH (NOLOCK)        JOIN dbo.AliveComputers c WITH (NOLOCK)ON c.ID=cps.ComputerID         JOIN dbo.AggregatedCompliancePolicyTypes acpt WITH (NOLOCK) ON cps.[Type] = acpt.PolicyType       WHERE (cps.State = 2 OR cps.State = 4) 

Does the machine id appear?

If not, add on next clause:

AND ComputerID IN ( SELECT ComputerID FROM dbo.ComputerGroupMapping WITH (NOLOCK) WHERE GroupID <> 1)  

If it appears then, it would suggest looking at the table ComputerGroupMapping , etc..

Hope this helps set you off on the right direction.

Regards,

Jak