Forum Discussion

DoubleP's avatar
DoubleP
Level 5
7 years ago

Generate a list of all active policies

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's avatar
    GeForce123
    7 years ago

    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 )) ) ) )
     

5 Replies

Sort By
  • GeForce123's avatar
    GeForce123
    Level 5
    7 years ago

    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's avatar
      DoubleP
      Level 5
      7 years ago

      The Master servers are linux.

       

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

      • Thiago_Ribeiro's avatar
        Thiago_Ribeiro
        Moderator
        7 years ago

        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