Following some of my recent articles about how Integration Packs (IPs) are handled during registration, installation, and unregistration (see Understanding IP Installation: What Does Register/Unregister/Deploy/Undeploy Really Mean? ), and as a precursor to a new tool I will be unveiling this week (trying to build the excitement a bit!), I wanted to talk about installing IPs and the database in a bit more depth.
I will focus on Toolkit-based IPs rather than “Native” IPs because as Orchestrator moves forward, you’ll see that most of the new IPs we develop will be created using our SDK and packaged using the Integration Toolkit (just like the IPs you would create). Using the Toolkit is truly the fastest and easiest way to make new IPs, and we’re going to be making some key enhancements to the SDK that will allow new activities to do things that we couldn’t do before (which is why we made non-Toolkit IPs in the past).
When you register a Toolkit-based IP, what you’re doing is adding new information to the Orchestrator database. Specifically, a new entry is created in the CAPS table for the Integration Pack which contains some high-level info about the IP. New information is also added to the CONFIGURATION table, which holds the definition of each of the activities in the IP as well as any configuration classes for the IP (the ones that specify an options menu setting). This table is likes to the OBJECTTYPES table, which holds the key information for all of the specific activity types known to the system. Here’s how these tables are represented in SQL:
As you might notice, the CAPS and CONFIGURATION tables are not linked in any way. So there’s no identifier of an activity that you can use to link to the IP to tell what activities are in an IP. Fortunately, a change we made in Orchestrator from the previous Opalis version was to place all of an IP’s dependent files in a subdirectory using the GUID of the IP’s product ID. This change had a beneficial effect in that this path now appears in the DataValue field in the CONFIGURATION table and now becomes searchable!
For instance, if I want to find out what activities belong to a specific IP, I can use something like this:
Where DataName = 'QIKOBJECT' AND DataValue like '%254FD433-CF28-4402-A785-BCB13AE11876%'
Just insert the correct GUID for the IP you’re looking for and you’ll return a list of the activities in the IP.
When you do the above, you get get the activity types, or definitions. But what if you want to see a list of all the specific instances where these activities are used? It helps to understand all of the table relationships that exist when you create a runbook and add activities to it. Here’s a quick look:
- Runbooks are contained in Folders (which have to exist before you can create a Runbook – by default it’s the root folder).
- Runbooks have diagrams (POLICY_IMAGES) that are created when you save the runbook
- Runbooks have associated Runbook Servers (DESIGNATED_ACTION_SERVERS) if something other than the default
- Runbooks contain Activities (OBJECTS)
- Activities can be special “Native” activities, like Invoke Runbook (CUSTOM_START) with associated child tables (CUSTOM_START_PARAMETERS) or they can simply be Toolkit-based activities (QIKOBJECT)
- Activities have associated actions, like:
- Looping (OBJECTLOOPING)
- Links (LINKS) with the conditions that cause those links to be followed (TRIGGERS)
- Auditing (OBJECT_AUDIT)
Understanding these relationships can help you figure out how to query for those sorts of things. For example, if I want to get a list of all the instances of an activity type being used in runbooks, I can use this query:
Select * FROM [Orchestrator].[dbo].[OBJECTS]
WHERE ObjectType IN
(Select TypeGUID from [Orchestrator].[dbo].[CONFIGURATION]
Where DataName = 'QIKOBJECT' AND DataValue like '%254FD433-CF28-4402-A785-BCB13AE11876%')
It can also be expressed this way using a JOIN:
Select * FROM [Orchestrator].[dbo].[OBJECTS] obj
JOIN [Orchestrator].[dbo].[CONFIGURATION] cfg on obj.ObjectType = cfg.TypeGUID
WHERE cfg.DataName = 'QIKOBJECT' AND cfg.DataValue like '%254FD433-CF28-4402-A785-BCB13AE11876%'
How you write it is really up to you and what you prefer. Sometimes the JOIN is easier to read for some people, sometimes the other way is. JOINS are useful when you want to return columns from several of the tables you’re querying instead of just one table.
Now let’s say that instead of just listing the activities, you want a list of all the runbooks where the activities from this IP are being used? Well that’s simple – you just extend the query a little, like this:
Select DISTINCT Name FROM [Orchestrator].[dbo].[POLICIES] WHERE UniqueID IN
(Select obj.ParentID from [Orchestrator].[dbo].[OBJECTS] obj
JOIN [Orchestrator].[dbo].[CONFIGURATION] cfg on obj.ObjectType = cfg.TypeGUID
WHERE cfg.DataName = 'QIKOBJECT' AND cfg.DataValue like '%254FD433-CF28-4402-A785-BCB13AE11876%')
All I did was change the OBJECTS part to bring back only the ParentID column, and use that to filter the list of Runbooks (the parent of the activity) and then display the name of the runbook in the results.
I’ll have a few more of these examples coming in the days ahead, but I wanted to leave you with one more for the road…
If you want a list of all of the Runbooks that contain “deleted” activities (from this IP), and display the names of those, here’s how you would do that:
Select pol.Name as [Runbook Name], obj.Name as [Activity Name]
FROM [Orchestrator].[dbo].[POLICIES] pol
JOIN [Orchestrator].[dbo].[OBJECTS] obj on pol.UniqueID = obj.ParentID
JOIN [Orchestrator].[dbo].[CONFIGURATION] cfg on obj.ObjectType = cfg.TypeGUID
WHERE obj.Deleted = 1
AND cfg.DataName = 'QIKOBJECT'
AND cfg.DataValue like '%254FD433-CF28-4402-A785-BCB13AE11876%'
Look for more handy queries and info over the next few days!