Disclaimer:  This is a VERY BASIC example of ONE way you can implement a custom database logging process with (long inhale…) Microsoft System Center Orchestrator 2012 R2.  And even though I’ve been beat over the head a hundred times that Orchestrator is “going away”, to be replaced by SMA/Azure Automation, well, it’s still out there and still has active users.

More important, is this: Most people I know who work in a System Center 2012 R2 shop aren’t even aware that most of them already have licensing to cover the use of other System Products, including Orchestrator.  They only use Configuration Manager and (maybe) Virtual Machine Manager.  There’s so much power hiding inside the other products, such as Operations Manager and Orchestrator.  What a shame, and what a waste of potential.  Check your licensing and make use of it!

Introduction

One of the most common aspects involved with building Orchestrator Runbooks is the output of process status to somewhere that allows you to go back and review what happened.  An ASCII or UNICODE text file.  A structured data file.  The Windows Event Log.  A spreadsheet.  Or in this case, a SQL Server database table.

To put this another way, it’s the capturing of each step in the Runbook, as it is processed, so you can see what’s going on.  It’s really no different than the output of task information from scripts, applications, and other program code.  It’s incredibly useful for tracking down what happened (or didn’t happen), when, where, and why.

For this example, I’m starting with a very basic (and admittedly incomplete) Runbook which queries Active Directory for disabled user accounts.  If any are found, I want to write a log entry that says how many were found.  If none are found, I want to write a log entry that none were found.  In a “real-world” scenario, this Runbook would likely do much more with that data set, such as move the accounts to a new OU or remove them from security groups.  But this article is focused on the event logging only.

Step 1 – The Database part

I created a new Database on the same SQL instance where my Orchestrator database lives.  Is this required? No.  Is this recommended? No.  Does it hurt anything? No.  It just is.  So, whatever.  In any case, my database is named “EventsLog” and has permissions applied to allow a specific user account (“OrchEventsUser”) to SELECT/INSERT/DELETE/UPDATE rows on tables within that database.  The database just happens to have only one table.  The account has no rights  granted to the Orchestrator database, because we don’t need to access that database for this purpose.

Here’s a view of the database table and some sample rows:

orch2.PNG

And here’s a view of the table schema:

orch3.PNG

You can use any structure, or custom indexing you desire.  This is just a quick table example.

Step 2 – The Runbook part

I decided to employ Orchestrator Variables for some of the database interface aspects.  This makes the Runbook more portable.  For example, if you’re developing in one environment, and intend to copy the same Runbook to another environment, and the two environments happen to use different event-logging databases, table names, and even column names, you can just update the variables and the Runbook still works.

Global Settings / Variables

Out of habit, I create a sub-folder named “Databases” and maybe additional sub-folders for individual databases, as needed.  Within the appropriate sub-folder, I created the following group of variables.

orch5

Again, this isn’t required if you’re only going to be dealing with one database and one table.  You can skip this and in the section below, rather than using “{variable}” entries, just type in the desired names and values.

In my case, I have to support three separate environments, with three separate database systems (one of them being my test environment).  In domain B, the database is named “ActionsLog” and in domain C, it’s named “OrchEvents”, while in my test environment it’s named “EventsLog”, you get the idea.

The Runbook

Before I start to build the Runbook itself, I need to configure the Active Directory connection settings:

  • In the Runbook Designer console, click Options / Active Directory
  • Click Add
  • Enter a name (e.g. “AD-Contoso”)
  • Select the “…” for the configuration type, and select “Microsoft Active Directory Domain Configuration” (it should be the only selection available), click OK
  • In the Properties table:
    • Username = a domain account with permissions to do what you intend within the domain (query only, modify, etc.)
    • Enter the password
    • Enter the Domain Controller FQDN (e.g. “dc1.contoso.com”)
    • Enter the Parent Container LDAP path (e.g. “ou=users,ou=corp,dc=contoso,dc=com” or just “dc=contoso,dc=com”)
    • Click OK

Now, I create a new Runbook.  I drag the following objects (aka “activities”) from the tools pallet onto the workspace:

  • Scheduling > Monitor Date/Time
  • Active Directory > Get User
  • Utilities > Write to Database

orch8

Right-click and copy “Write to Database” and paste a duplicate elsewhere.  Rename each activity object to suit the example below.  Then drag connectors from each as shown.

orch1

Tip: To modify the connector features, double-click on them, select Options and change the colors, and line weights.  To show the connector labels, from the Runbook Designer console, click Options / Configure and check “Show Link Labels”, and click Finish.

For this example, I start the Runbook sequence with a scheduler object that runs every hour, but you can substitute another initiator, such as the “Initialize Data” object, it doesn’t really matter.  It just needs something to get it moving.  Like me and a morning cup of coffee.

After that, it calls the Active Directory activity named “Get User”.  I renamed “Get User” to “Get Disabled User Accounts”.  Then I connect the output of that activity to the one “Write to Database” activity.  I renamed it to “Log – Found”.  Rename the other to “Log – None Found”.

The Connectors

Here’s where it gets a little tricky.

The connector from “Get Disabled User Accounts” to “Log – Found” has to be configured differently than the one to “Log – Not Found”.  To do this, double-click the connector to “Log – Found”.

  • Select the “Include” category.
  • It should show “Get Disabled User Accounts returns success
  • Click OK

Now, configure the connector to “Log – Not Found”:

  • Select “General” and change the name to “None Found”
  • Select the “Include” category
  • Click on the word “returns” so it highlights the entire string value
  • Click Remove
  • Select the “Exclude” category
  • Click Add
  • Un-check “failed” and “warning” and select “success”
  • Click OK

orch7

Why?  Because some activities won’t return “failed” when they don’t return anything.  Y

Using the Variables

To reference these variables, I open the properties for one of the “Log -…” objects…

orch4.PNG

In the Table name box, right-click and choose “Subscribe > Variable” and select “DB-TableName”.  The entry changes to the “{DB-TableName}” format.  You can also type in the {VariableName} entries, rather than using the selection process.

Then for the Data section below it, I click “Add” and use the same “Subscribe > Variable” process to enter the column names.  Each column is entered as “Field name” and the desired value to write to the column is entered in the “Value” box.  For this example, I ended up with:

  • {Column-Type} = 1
  • {Column-Category} = ACCOUNTS
  • {Column-Detail} = “disabled accounts were found”

Also, you may notice that in the details “value” column, there’s another embedded variable “{Count from “Get Disabled User Accounts}“.  To insert that, place your cursor at the far-left in the value box, right-click, select “Subscribe > Published Data” and select the “Count” row item, and click OK.  Back in the value box, be sure to add a space after it as well.

orch6.PNG

Once everything is hooked up, click the Runbook Tester link in the console menu.  Then execute the Runbook and verify it works properly.  If you don’t have any disabled accounts in your domain, create a test account and disable it.

The most common challenge I see in this situation is getting the database permissions set properly to allow for the connection and the table row insertions.    That’s beyond the scope of this article, but for “quick-and-dirty” results:

  • Add a new SQL login (mixed or windows mode, doesn’t matter)
  • Add a User Mapping for the event log database
  • Configure the role membership to include db_reader and db_writer (or just db_owner, who cares) as well as public.
  • Then grant select/insert/delete/update rights on the event log database itself

Wrapping Up

This basic level of Runbook processing is often included in much larger, more complex Runbook models.  Orchestrator can be used to automate nearly anything, and reduce hours, if not days, of manual work into a few minutes, and freeing up staff to do other things.

For example, a Runbook sequence to address a terminated employee condition might take care of the following chores:

  • Disable the user account
  • Disable the user computer accounts in AD
  • Move the user and computer accounts to designated OU locations
  • Remove the computer from certain ConfigMgr collections
  • Update the asset inventory database
  • Update the HR database
  • Update the employee skills and certification tracking database
  • Update the phone system database
  • Create report of all group and distribution list memberships
  • Notify staff of personnel changes (facilities, security, help desk, management)
  • Write a log of every step

All of that being triggered by a single event, for instance, a change to the HR database “emp_status” column.

Final Thoughts

This is obviously a very basic example.  I haven’t included any discussion about further capabilities, or configuring this Runbook for use by other Runbooks (return values, etc.).  It’s just a standalone example.

You can adapt this same general concept to other databases, including third-party databases.  It’s like a virtual Lego kit (hopefully, no one at Lego gets upset at me saying that).  Orchestrator is incredibly (INCREDIBLY) powerful and flexible.  You can build just about anything, but the key is to start with a plan and build it incrementally and test along the way.

On a somewhat more abstracted level, this way of modeling a process is what I’ve always seen as the future of programming.  It’s not really “new” in that one regard either.  Lots of smart people have been been working on graphical process modeling for decades.  MIT, Yahoo, Google, Microsoft, and IBM, to name a few.  But that’s for another blog post, some day.

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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