From time to time there is a need to list all instances of a specific activity type in Orchestrator. For example it is not recommended to have any send platform event activities in production, instead we can use a SQL database for logging. There are two ways to investigate if there are any send platform events in the environment, review each runbook or ask the database. In this blog post I will show you how to list all instances of a specific activity type.
The first thing we need to do is to find the uniqueID for the type of activity in the OBJECTTYPES table. The following question will give us all info about the Send Platform Event, but you can query for any kind of activity.
select * from OBJECTTYPES where name = ‘Send Platform Event’
The OBJECTTYPE table will give us the uniqueID for the activity type, in this example 87E28B20-3E83-45E0-985A-FEEA6CE09084. The table will also tell us where to look for configuration data for these activities, in the PrimaryDataTables and SecondaryDataTables. If we want to list all Send Platform Events activities we have we can run the following query. The “AND (Deleted = ‘0’) part will make sure we only list activities that are not marked as deleted.
select * from OBJECTS where (ObjectType = ’87E28B20-3E83-45E0-985A-FEEA6CE09084′) AND (Deleted = ‘0’)
If we now want to add on in which runbook we are using them we can join the ParentID from the OBJECTS table with the uniqueID in the POLICIES table, like the following query. The Objects table will give us general information about each activity.
SELECT OBJECTS.Name AS [Activity Name], POLICIES.Name AS [Runbook Name] FROM OBJECTS INNER JOIN POLICIES
OBJECTS.ParentID = POLICIES.UniqueID WHERE (OBJECTS.ObjectType = ’87E28B20-3E83-45E0-985A-FEEA6CE09084′) AND (OBJECTS.Deleted = ‘0’)
We now see activity names and runbook names. But what if we also want to include configuration of the Send Platform Event activity? Then we need to join the table seen in the first query, the PrimaryDataTable for the Send Platform Event activity type, example query
SELECT OBJECTS.Name AS [Activity Name], POLICIES.Name AS [Runbook Name], TASK_SENDOPALISEVENT.EventType, TASK_SENDOPALISEVENT.EventSummary, TASK_SENDOPALISEVENT.EventDetails FROM OBJECTS
INNER JOIN POLICIES ON OBJECTS.ParentID = POLICIES.UniqueID INNER JOIN TASK_SENDOPALISEVENT ON OBJECTS.UniqueID = TASK_SENDOPALISEVENT.UniqueID WHERE (OBJECTS.ObjectType = ’87E28B20-3E83-45E0-985A-FEEA6CE09084′) AND (OBJECTS.Deleted = ‘0’)
AS you can see some EventDetails and EventSummary includes a GUID. When we configure a Send Platform Event activity to publish data from the data bus we see the uniqueID of the activity in the text. As we can see in the figure many of my Send Platform Events will use data from the data bus.