SQL Query to get absolute path of all Orchestrator Runbooks

Orchestrator stores information about folders and Runbooks in parent-child hierarchy within SQL Server Database. Folders and Runbooks can be nested with each other in great depths. At times, we need the entire path of the Runbook from the hierarchy and there is no easy out of box way to retrieve it through the product. SQL Query comes to the rescue!!

We can use the below sql query to obtain all the folder and Runbook paths within an Orchestrator installation. It will get runbooks that are not deleted and are enabled. ‘00000000-0000-0000-0000-000000000000’ in below query is the top level parent in the hierarchy and refers to the Policies folder.

with RunbookPath as
(
select 'Policies\' + cast(name as varchar(max)) as [path], uniqueid from dbo.folders b
where b.ParentID='00000000-0000-0000-0000-000000000000' and disabled = 0 and deleted= 0
union all
select cast(c.[path] + '\' + cast(b.name as varchar(max)) as varchar(max)), b.uniqueid from dbo.FOLDERS b
inner join
RunbookPath c on b.ParentID = c.UniqueID
where b.Disabled = 0 and b.Deleted = 0
)
select [Path] from RunbookPath

Cheers!!

Advertisements

SC Orchestrator Tip: How to copy a Runbook from one folder to another

Time and again, Orchestrator Runbook authors want to copy a runbook from one folder to another for a variety of reasons. The reasons could be folder hierarchy reorganization, want to use the same runbook functionality and add newer functionality but do not want to change the existing runbook- may be getting used already in other runbooks etc..

Orchestrator runbook designer does not have any feature out of the box to copy a runbook from one folder to another. If you right click on a runbook the following options are presented.

Options
Quite visible that the “Copy” option is not available. There are options to select the activities from within source runbook, copy them and then paste them on target runbook. This however, do not copy the runbook settings and configuration to the target runbook. The author still needs to manually update the target runbook with these settings and configuration.

An easy out-of-box way to copy runbook from one folder to another is available in Orchestrator however in a disguised manner. There is a great feature called Export and Import runbooks that can be used to copy a runbook.

Simply, export the source runbook and after exporting, import it to the target folder.

Hope you would find this tip helpful..

Solving SC Orchestrator Web Service HTTP 404 error

In the last blog post, we saw that SC Orchestrator R2 eval has taken steps to reduce the occurrence of HTTP 500 error by modifying the stored procedures responsible for calculating the authorization cache. In this post, we will look into the solution to the problem related to occurrence of HTTP 404 errors.

We saw that [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache table holds the records for authorizationcache and this authorization cache is cleared every 10 minutes and is also maintained (older authorization entry timestamp compared to current time is deleted) every 30 minutes. These authorization cache and their schedules are maintained within Orchestrator SQL database. The authorization cache is also created if the authorization cache is empty and a request is made to the web service.

As noted above, if we can clear the authorizationcache table and then invoke the web service, the authorization cache would be calculated from scratch and new Runbooks would find its way within the authorization cache.

Therefore steps to remove the HTTP 404 error are

1. Use the below SQL statement to empty the [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache table

Truncate table [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache table

2. Open Browser and browse the Web Service to view its metadata. This action will re-populate the authorization cache. The url is  generally of the form http://localhost:81/Orchestrator2012/Orchestrator.svc which could be different in your case.

Hope this post would help solve some of your Orchestrator web service issues.

Cheers!!

Better Authorization Cache Management in SC Orchestrator R2 eval

One of issues in system center Orchestrator 2012 Sp1 web services is that it returned either http 404 or 500 error in a lot of situations. You can experience the same if you create a new runbook and immediately invoke the web services requesting for this new runbook. You will also notice that this new runbook is not visible within the Orchestrator Web console. There are primarily two reasons for these errors to occur.

  1. Authorization Cache

Orchestrator maintains an authorization cache for folders and Runbooks. This authorization cache reflects the users with their rights on the folders or Runbooks.

This authorization cache is cleared every 10 minutes and is also maintained (older authorization entry timestamp compared to current time is deleted) every 30 minutes. These authorization cache and their schedules are maintained within Orchestrator SQL database. The authorization cache is also created if the authorization cache is empty and a request is made to the web service. This authorization cache is used by the web service to determine the current user’s rights and permissions on the runbook or folder. This authorization cache is used by the Orchestrator web console for its operations as well.

Orchestrator does not remove table entries from database while you delete Runbooks and folder or while importing Runbooks. Instead, Orchestrator conducts a soft-delete i.e. it updates a column called [deleted] with ‘1’ as value. This causes the Orchestrator database to grow over a period of time while numerous imports and deletes happen and newer Runbooks are getting created.

The steps undertaken by Orchestrator to build the authorization cache are to execute a series of SQL stored procedures to fill the Table [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache. For filling up this table, Orchestrator queries the Database for all the folders and its Runbooks and this is one of the reasons for the http 404 and 500 errors. This is because Orchestrator is even processing records for folders and Runbooks that are in deleted state for calculating the authorization cache.

2. Web service SqlCommand Command timeout

The default setting for Command timeout of SQLCommand used within system center orchestrator web service 30 seconds. This does not take into consideration that there could be thousands of rows related to folders and Runbooks that could be used for calculating the authorization cache.

HTTP 404 error

HTTP 404 error occurs when an invocation is made to the web service for a folder or Runbook that still has not found its way to the authorization cache. Typically, this happens with a runbook created recently and the authorization maintenance (as mentioned above) has not yet been executed.

HTTP 500 error

HTTP 500 error occurs when an invocation is made to the web service for a folder or Runbook and any one of the below is happening

  • The authorization cache maintenance is in progress taking more than 30 seconds while the web service SQL Command timeout happens.
  • There are no entries in the authorization cache and Orchestrator takes more than 30 seconds to build the cache thereby again triggering the SQL Command timeout.

One of the change in SC Orchestrator R2 is better management of authorization cache to reduce the http 500 error. This has been done by changing the SQL Stored procedures. Now, the stored procedures for both folder and Runbook authorization cache has a filter condition that filters out all the deleted folders and Runbooks for the calculation of authorization cache. In effect, in R2 only those Folders and Runbooks are processed that are alive and kicking within the designer.

The SQL excerpt within the stored procedures looks like below.

Folder:

Select [UniqueId] from dbo.Folders where Deleted = 0

Runbook:

Select [UniqueId] from dbo.Runbook where Deleted = 0

In next post, I would provide a solution to troubleshoot these HTTP 404 and 500 errors.

Hope you would find this post useful!!

Cheers!!!

SC Orchestrator : Return data belonging to multiple activities using single activity

I have often seen requirements related to returning data using “Return Data” activity. Developers adopt different approaches for returning data. Some of them are implemented using best practices while others becomes anti-patterns. One of the requirements that often arises as follows

1. There are multiple activities within a runbook. The count could be anything beginning from 2.

2. Each activity has the potential to fail and error out.

3. The runbook should return the error if there is a failure.

The typical approach to implement the above problem looks like below..

Typical Runbook  Implementation

Typical Runbook Implementation

A closer look into the above runbook will reveal that there are multiple “return Data” activity used to return error information to the caller of this runbook; one for each activity.

The above would work as expected but definitely not the most elegant way of returning data. One of the way or pattern to handle such a situation is to use the below pattern.

Pattern for Returning Error Data

Within the above runbook, there is a single “return error” activity responsible for returning the error message for failure of any activity within the runbook. However, it raises few questions, primarily..

1. What should the published data look like within the “Return Error” activity.

2. How will “Return Error” activity know which activity has failed.

Let’s try to find out answers to above questions through step by step implementation which would also form the pattern to solve above mentioned requirements..

A. Configure the Runbook to return some data. In this case, a single “ErrorMessage” is configured for returning the error message. Refer to below image.

Configure Runbook Return Parameters

Configure Runbook Return Parameters

B. Configure the “Return Error” activity.

1. Within the return error activity, within the “Error Message” input field right click and go to published data and click on the first activity for which error data needs to be returned and also select “Show common published data”. In this case, it is Copy file activity.

Select the first activity

Select the first activity

2. Once “Show common published data” is selected, more published data is made available for usage including “Error summary text”. Select the item “Error summary text” as published data to be returned.

Select published data to return

Select published data to return

3. Repeat the above step 1 and 2 for rest of other activities for which you want to return the error message. Eventually the input field for “Error Message” should look like below.

Published data

Published data

The published data is of the form {Error summary text from activity1}{Error summary text from activity2}{Error summary text from activity3}{Error summary text from activity4}{Error summary text from activity5}. In this pattern, the error message published from each activity is placed side by side to each other.

But how does it works…

At any point of time, if there is an error in the runbook – it has to be because of failure of any one activity. The next activity executed after the  failed activity would be “Return Error” activity. within the “ErrorMessage” Parameter, only one published data out of all the five published data would be filled up with value. Rest of the published data would have null values.

One more important point to remember for the above pattern to work is that the links between the activities and “Return Error” activity must be configured as below such that the “Return Error” activity is executed only if there is a “error” or “warning” while executing the activity.

Link Configuration between activity and Return error activity

Link Configuration between activity and Return error activity

That’s it!!!! We have answered both the questions as well as developed the pattern needed to return dynamic data based on single Return data activity.

Hope you would find this articles and Post useful!