Opalis DB SQL Queries

Here are some interesting SQL queries against the Opalis DB that I have come up with

Policies

  • Select runtime information about non deleted policies
    • select
      *
      from POLICIES_VIEW where Deleted is
      null
  • Select all currently running policies
    • select
      *
      from POLICIES_VIEW where Deleted is
      null and runningpolicyinstances > 0 order
      by RunningPolicyInstances desc
  • Find all objects in a Policy
    • select
      *
      from POLICIES pol inner
      join
      Objects obj on obj.ParentID = pol.UniqueID where pol.Name like
      ‘POLICYNAME’ and obj.Deleted is
      not
      null
  • From here it looks like you can join in tables that relate to the various objects in a policy to get their unique configuration information. It looks like all QIK created objects store their configuration information in the QIKOBJECT table and all objects created from a non-QIK IP create their own table to store their specific configuration information in. Given this, here is a query that will include all QIKOBJECT specific information for a policy
    • select
      *
      from POLICIES pol inner
      join
      Objects obj on obj.ParentID = pol.UniqueID inner
      join QIKOBJECT qik on qik.UniqueID = obj.UniqueID where pol.Name like
      ‘POLICYNAME’ and obj.Deleted is
      not
      null

Action Servers

  • List of the Max Running Policies variable for all Action Servers
    • select Computer,
      Description, MaxRunningPolicies from ACTIONSERVERS
  • List of all currently running policies for a given action server
    • select ACTIONSERVERS.Computer,POLICIES.Name from POLICY_PUBLISH_QUEUE inner
      join ACTIONSERVERS on POLICY_PUBLISH_QUEUE.AssignedActionServer = ACTIONSERVERS.UniqueID inner
      join POLICIES on POLICY_PUBLISH_QUEUE.PolicyID = POLICIES.UniqueID where ACTIONSERVERS.Computer like
      ‘COMPUTER_NAME’
Advertisements
Tagged with: ,
Posted in General Information
One comment on “Opalis DB SQL Queries
  1. vev says:

    It’s very interesting and useful.
    Maybe you answer on my question?
    How to change current datastorage for Opalis on new datastorage on new database server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: