Generate a list of all active policies

[DoubleP]

We have OPSCenter version 8.0. I need to generate a list of all active policies. I don't need storage or success rate , etc. Just a list of policies.  I didn't see any saved templates for this. Is there a SQL query that can do this? 

[GeForce123 · Accepted solution]

All you want is a list of active policies? If you are on Windows, you could open the Admin Console and go to the policies section. Right click->filter and select choose Actie 'equal to' yes. Then select all the policies and Ctrl+C and paste into an excel spreadsheet. This would give you a lot more information than just the policy names.

If you can't do that, you can use the following SQL query in an OpsCenter report.

select DISTINCT TOP 20 START AT 1 domain_Policy.name as "domain_Policy.name",domain_Policy.active as "domain_Policy.active" from domain_JobArchive , domain_Policy , domain_MasterServer where domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId and ( ( (domain_JobArchive.endTime BETWEEN '137377474957070000' AND '137389570957070000') ) AND ( ( (CASE WHEN domain_Policy.active = 1 AND domain_Policy.status = 0 THEN 1 ELSE 0 END) = '1') ) AND ( (domain_Policy.status IN (0 )) ) AND ( (domain_Policy.isValid = '1') ) AND ( ( (domain_MasterServer.id IN (61,2530,8422140 )) ) ) ) 

[DoubleP]

The Master servers are linux.

I tried your query in OPSCenter, but "The report did not return any data."

[Thiago_Ribeiro]

Hi,

I did the test using the ops query sent by @GeForce123 and its working...You need to adjust for your environment, take a look

select DISTINCT TOP 20 START AT 1 domain_Policy.name as "domain_Policy.name",domain_Policy.active as "domain_Policy.active" from domain_JobArchive , domain_Policy , domain_MasterServer where domain_Policy.masterServerId = domain_JobArchive.masterServerId and domain_Policy.name = domain_JobArchive.policyName and domain_Policy.policyDomainName = domain_JobArchive.policyDomainName and domain_Policy.versionNo = domain_JobArchive.policyVersionNo and domain_MasterServer.id = domain_Policy.masterServerId

Thiago

[GeForce123]

The master server can be Linux and you can still use the Admin Console on a Windows machine, like a laptop.

In regards to the query not working, not sure what to tell you. I just copied it into a custom SQL report and it ran just fine (I changed TOP 20 to TOP 1000 so it would return all 700+ of my policies).

I'd suggest just using the Admin Console method.

[DoubleP]

Thanks; the script worked.