A topic came up recently on an internal distribution list that I have been planning on blogging about.  I figured it’s time to get it out to you because it is timely with the release of System Center Orchestrator Beta and future versions coming. 

What is so important and why?

We will all need a way to report on what policies in Opalis are currently in process of development and are not yet checked in.  Even more important, where they were last checked out and who checked them out last!  If they are checked out, they are not saved in the database and will be lost on migration.

Why is this particular topic is so important you ask?  Well, as part of your migration strategy to Orchestrator from Opalis,  you should have in it your plan to export all “key” workflows and sub workflows so they can be imported into your new Orchestrator environment.  You can browse through your Opalis Client and look at workflows one by one and see a padlock on them indicating they are checked out remotely or a pencil if they are checked out from your own machine.  The padlock ones are the tricky ones.

So what can we do to find all checked out Policies?

It just so happens that some of this data is stored directly in the Opalis Database.  Once you get this data, you need to do some manipulation of some data and then extract the data.  So what better way to do this than…you got it Smile create another workflow to automate it.  I’m going to keep this simple and straightforward and you can take it from here and make it as complex as you want.  It is just three simple activities linked together as shown below.

image

Query Database Activity

  1. Start simply by creating a new Policy and drop a Query DB activity on the designer palette.  The query DB object I am using is one of the foundation objects that came with Opalis found under the “Utilities” section on the right hand side of the designer.
  2. Open the Query Database activity and select the “Connection”image
  3. Ensure you are selecting the appropriate DB type and set your SERVER and CATALOG (Opalis would be the default catalog).
  4. Next, select the “Details” tab and type in the following query
    image

Get Account from SID Activity

  1. Next, I call this the “Get Account from SID” activity but it is doing a bit more than that.  This activity is setting published data for each item you have above and doing some extra PowerShell manipulation.  Drag a “Run.net” object from the “System” section of the foundation objects on the right hand side of the client.
  2. Link the two objects you have together and place the PowerShell code you see below into the details section (ensuring you are setting it to PowerShell for the type). 
    image
    An “eye chart” I know – I provided the workflow at the end of this blog so you can easily recreate and modify.  :)
  3. One very key step that I want to note above is the gathering of the UserID from the SID (see below).

       1: if ($lastModifiedBySid -ne "NULL")
       2: {
       3:     $LastModifiedBy = (new-object system.security.principal.securityidentifier($lastModifiedBySid)).translate([system.security.principal.ntaccount])}
       4:     $CheckOutUser = (new-object system.security.principal.securityidentifier($checkedOutUsersid)).translate([system.security.principal.ntaccount]
       5: )

  4. Finally, we need to established published data objects for each variable we have defined within our PowerShell script.  This gets us all our published data that can be leveraged in the next step. 
    image

Append Line Activity

  1. Next, this is the step that probably lacks the coolness that the rest of the process above clearly has Winking smile .  I grabbed the "Append Line” object from the foundation objects list out of the “Text File Management” section (sometimes a hard one to find).
  2. Type in the path you want to save your file to (I picked the temp folder on my Action Server) and then populate the published data you care about.
    image

That’s it!  Check it in and run it. 

Your results will look something like the following (hopefully you don’t name all your policies “New Policy”) Disappointed smile

image

Final note: The download is provided as an example – use at your own discretion and test first in a lab environment or sandbox to see results.

Downloadable Content Checked-Out-Policies.ois_export

Thanks for following along – if you want to cut to the chase, just download the provided file and import it into your environment, configure the appropriate pieces, and let it fly.  I hope it helps with the migration.

Till next time…

Happy Automating!