Software Metering Deep Dive and Automation Part 3: Use It Or Lose It - The Orchestrator Runbook Automation

Welcome back to ‘the blog’ and this series on Software Metering and automating the removal of unused software with 2012 Configuration Manager (and Orchestrator). If you have been keeping score, this is the third installment in this series. Thus far the post breakdown is as follows -

Part 1 - Software Metering Deep Dive and Automation Part 1: Use It Or Lose It - The Basics

Part 2 - Software Metering Deep Dive and Automation Part 2: Use It Or Lose It - The Collections

In this, the third posting of the series, I will be taking the automated software removal process to a new level with some System Center 2012 Orchestrator integration. I will demonstrate a sample solution that provides notification to an end user that unused software has been detected on their primary system, and that it will be removed in a specific amount of time. The Orchestrator Runbook solution will then provide a grace period in which the user can opt out of the software removal (by using the software). Finally, after the grace period has expired, the Runbook solution will send a final notification email and trigger the software removal process. In this post I will walk through the prerequisites, lay out the Runbook logic, and finally make available a downloadable copy of the sample Orchestrator Runbooks. This is going to be a pretty massive posting, there is allot to cover. While I will not go into detail (step by step) into the configuration of each component, I will discuss how each tie together. If you find yourself needing additional assistance with the solution please let me know, I will provide what assistance I can. Additionally if there is enough interest I can create a follow up post (potentially a video) detailing how to implement the solution start to finish.

Download Location for the Runbook Samples detailed in this blog - Gallery Download Location.

As always (broken record speech), this Runbook is only a sample and is provided in efforts to get the creative juices flowing. Without doubt, this Runbook solution is not feature complete for each and every environment. That said, this sample should provide foundation, and many example concepts from which to work from in your own environments.

Process Overview:

The flow / result of the following process will be –

  • Collection is configured to populate with all machines that have not run a specific software in x days.
  • Software metering maintenance Runbook runs on a daily schedule.
  • As new machines are detected in the appropriate collection, the metering automation will be executed against these objects.
  • If the machine is new to the collection, an email will be sent to the primary user (based off of User Device Affinity data). The email will state that unused software has been detected and that the software must be used in seven days or that it will be automatically removed. At this point, information about the computer including the time that this initial email was sent is logged in an external database.
  • If it has been detected that the computer has been in the collection for more than seven days, a second and final email is sent to the primary user. The computer is then added to an uninstallation collection, CM policy is refreshed on the computer, and the external database is updated to reflect this activity. At this point if the Configuration Manager end is set up correctly, the software should be removed from the system.
  • After 30 days, records will be removed from the external database, and any remaining collection rules will be removed.
  • Additionally if a machine is found without User Device Affinity data, an email will be sent to an administrator (this can be extended to open up a service ticket, etc.). There is also some other minor maintenance type automation included in this solution.

Prerequisites:

In order to use this sample solution, the following prerequisites must be in place.

  • Software Inventory must be enabled and inventorying .exe files (or at least those that you would like to include in this process).
  • Runbook Metering based Collections must be in place. Refer back to post 1 and 2 of this series for more information on creating these collections.
  • User Device Affinity must exist for each machine. The sample workflow provided here relies on user device affinity in order to gather an email address.
  • An external database must be created (detailed in this post).
  • An SMTP Server for email notification.
  • The Active Directory, 2012 Configuration Manager, and Data Manipulation Integrations packs must be deployed to the Orchestrator Runbook Designer.

The Collections:

If you have read part two of this series you will be familiar with the two tier collection structure previously discussed.

  • Collection 1 holds all machines that have run a particular piece of software within a specified time period (30 days in my example).
  • Collection 2 holds all machines with the software, except for those in Collection 1. In other words Collection 2 holds all machines that have a piece of software installed, but have not run it in a particular time period.

In post 2 I targeted Collection 2 with an uninstallation package. So as would have it, in that configuration, as any machine that contains the metered software, has not used the software in x days, the computer would automatically be added to the collection, and the software would be automatically removed. This in itself is an amazing feat.

How would this work from a collection perspective if we wanted to add a configurable delay, some notification, other automation and workflow?

For the sample Runbook solution that I will detail here, I add a third collection. So building off of the already discussed collection structure

  • Collection 1 - All systems that have run the software in x days. Neither this collection, nor the purpose of the collection has changed from the previous blog post.
  • Collection 2 - All systems with the software, except those in Collection 1 (all systems that have not run the software in x days). This collection has not changed from the previous discussion, however the purpose has. As we will see, instead of targeting the software removal at this collection, this collection will now be monitored by an Orchestrator Runbook. It is from this collection that we trigger the software removal automation.
  • Collection 3 - This is new to the process. This is now the collection where the software removal Application / Package will be deployed. As the integrated Orchestrator Runbook finds that a machine has unused software, that the initial notification has been delivered, and the removal grace period has elapsed, the Runbook will drop the system into this collection, triggering the software removal.

Screen Shot of how I have organized these collections (click image for better view):

As we work though this solution, we will refer back to this collection structure and the purpose of each collection will be clarified even further.

The Database:

When possible my goal is to build automation that does not include any external data source. That said, I have found this is not always possible. As I dug into the software removal process, I found that in order to give the user a grace period in which to opt out of the software removal, there was a need to track the elapsed time of this grace period. This is what the database is for. The database is very simple and consists of three Columns, ComputerName, State, and Date. You can see here that I've named the database OrchestratorOperation and the table Metering. You can name it whatever you like, but will need to make some adjustments to the Runbooks if using my exported samples.

The following script will create the database for you:

Database

  1. CREATE DATABASE OrchestratorOperatons
  2. GO
  3.  
  4. Use OrchestratorOperatons
  5.  
  6. CREATE TABLE Metering
  7. (
  8. ComputerName varchar(50),
  9. State varchar(50),
  10. Date datetime,
  11. )

The Runbooks:

This process, while seemingly simple (send an email, wait some time, place machine in collection), I found to be quite complex when accounting for not just the software removal, but also external database maintenance, cleaning up the software removal collection, dealing with User Device Affinity, email address, etc. etc. While this process does include eight Runbooks, these Runbooks have been broken down into modular pieces. For instance at some point we will need to determine a user’s email address based on the user account. I've created a single Runbook for the purpose of determining the email address. The Address can then be used as Return Data which is fed into a separate Runbook for consumption. So while there are eight Runbooks here, really only the first three are specific to the Software Metering Process, the other five can be used as modules or functions in many Runbook solutions.

I will spend the majority of the Runbook Explanation on the first three Runbooks. These three truly make up the software metering / software removal automation piece. The remaining Runbooks should be for the most part self-explanatory. I will fill in any gaps that I think are not obvious.

1. Metering Start - this is where the process starts.

  • Monitor Date / Time – this monitors time and executes the metering software automation process a a specified interval.
  • Get Collection - discovers all computes in Collection 2 (refer to above section for information on collections).
  • Link 1 - I have place link logic here that stops the process if the collection is empty.
  • Check History - for each computer discovered in the target collection, this process will query the external database, pulling back both the state and the time stamp. If there is no record for the machine then the New Record Runbook is executed. This Runbook is detailed below.
  • Split Fields – If a record is found for the machine, this activity splits the query results into three strings (Computer Name, State, and Date). These fields are used throughout the Runbook. Also to note, this activity comes from the community provided Data Manipulation Integration Pack.
  • From here the Runbook branches on link 4 and 6, after each of these is a PowerShell script providing Date Diff functionality.
  • If only the initial email has been sent the solution branches to 6, and the Date Diff value is 7. If the value is greater than 7 the Grace Period End / Begin uninstall Runbook is executed.
  • If the initial and final email has been sent the solution branches to 4, and the Date Diff value is 30. If the value is greater than 30 the Remove Collection Rule Runbook is executer.
  • Finally each path ends at a junction after which a Prune DB Query is executed against the external database. This removes all records that are 30 days or older.

The New Record Runbooks adds a record into the external database, gathers the user name and email address, and then sends the initial email. Most of this is completed through modular Runbook execution.

The Begin Uninstallation Runbook adds the computer to the Uninstallation collection and sends the final email. Most of this is done through modular Runbook execution.

This Runbook simply adds the computer to the specified collection, waits 300 seconds, and the triggers a Configuration Manager Policy refresh on the client. 

This Runbook will remove the computer specific collection rule once it is determined that the rule is no longer needed.

Here we are querying the Configuration Manager database for the User Device Affinity information for the computer. If no User Device Affinity is found, an email is sent to the administrative user specified in the Orchestrator variables. In a production scenario this email notification could be replaced with a Service Manager Incident or other more appropriate action.

Here I am gathering the users email address from active directory using the user name gathered from the User Device Affinity Runbook.

Finally, this Runbook sends an email. Note that throughout this sample several different emails that are sent, but only one send email Runbook exists. This is just another example of a modular Runbook that can be re-used across many different Runbook solutions. This is achieved by piping most of the email specific data into this Runbook using the Initialize Data activity (shown below).

 

Variables:

In effort to make this Runbook solution portable (or so that you could import this into your own environment), I have created multiple variables that will need to be configured up front. These include items such as the SMTP server that will be used for email delivery. In using variables this information can be entered in one place (Global Settings) saving us the effort of identifying each specific activity and link that requires configuration. I have crafted the solution in such a way that it should work after only setting each variable, in other words there should not be a need to modify the Runbooks themselves. Once comfortable with the solution you may want to make adjustments to the amount of specified variables and re-craft the solution to use the standards established in your own environment. Each variable is provided with a description that should clue into how to populate the variable value.

Conclusion:

Throughout this series of blog posting we have looked at the internals of software metering, collection creation based off of software metering data, basic software removal based on collection membership, and finally in this posting some sample automation that provides advanced warning of pending software removal and an orchestrated final removal process. This is yet but one more example of the cool automation that an be created when using Configuration Manager and Orchestrator together. This solution has many different parts that need to come together in order to work, if you need assistance on any of them please drop a line on this blog. If there is enough interest I will create a follow up post with detailed instruction (potentially a video) on how to configure each piece. I hope this has been helpful and as always please feel free to leave feedback or contact me directly.

Comments

  • Anonymous
    April 10, 2013
    Neil, Great post! Any chance you can post the runbooks for download? Regards, Dave

  • Anonymous
    April 18, 2013
    Hi Neil, There is an error in the databasename. In the runbook the database is called "OrchestratorOperation" and in your blog post you create the database with "CREATE DATABASE OrchestratorOperatons" with a S at the end. Regards Tom

  • Anonymous
    April 30, 2013
    Hi, If the user had initially got the application from the Application Catalog, and approval was granted. Would that approval still remain? i.e. when app is removed, then the user could simply reinstall as approval was previously granted. Would a different runbook need to remove that approval? Peer

  • Anonymous
    August 02, 2013
    Great Post!

  • Anonymous
    February 03, 2014
    Need demo video Please..

  • Anonymous
    June 15, 2014
    Keep the good work.

  • Anonymous
    June 19, 2014
    Absolutely brilliant! I've been looking for some hints to point me in the right direction and then i found your guide. It has everything i need! Thank you!

  • Anonymous
    August 13, 2014
    This is a great blog series but what verison of the Data Manipulation IP do you need??

  • Anonymous
    January 05, 2016
    Hi - thanks for the great explanations. Im having an issue finding where / how the Email step gets the variables for Computer name and Software. Can you explain how this informaton gets in to the email?