OpsCenter SQL query - problem

Hi,

You can use this

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName, MAX(domain_PolicyClient.policyversionno)
from domain_PolicyClient
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

It adds another column at the end (MAX(domain_PolicyClient.policyversionno) that you can ignore. I don't know enough about SQL to know how to hide it :).

Ok thx, but it still shows me deleted policies, what does it mean policyversionno? Some got =1 some =13 or 10

??

This query shows me poliecies which are not shown in netbck GUI..

There is also "active" flag, but I cannot what what does it mean.

Ah ok, sorry, I got side tracked by the policy version number. They indicate how many times a policy has changed, 13 being 13 changes.

I'll work on the deleted bit :)

Fixed

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

Hm thx, it looked good, but I have checked, and some policies are inactive :) heh, I think I will have to do this manualy..

Included the check for active only (text in BOLD)

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0) AND domain_policy.active IN (1)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

Hi,

Have you verified its working now?

select domain_PolicyClient.PolicyName, domain_PolicyClient.clientName
from domain_PolicyClient, domain_policy
WHERE domain_PolicyClient.PolicyName = domain_Policy.Name AND domain_policy.status IN (0) AND domain_policy.active IN (1)
GROUP BY domain_PolicyClient.clientName, domain_PolicyClient.PolicyName
ORDER BY domain_PolicyClient.PolicyName

No this query still shows me deactivated policies. While I change domain_policy.active IN (0) this shows me all deactivated policy (and its good outpust, its the same numer of rows we did manually)

So still is something wrong with query :)

Hmm, works fine on mine. There must be some other discrepancy that you're catching in your environment.