When I was writing the article I posted a few days ago about importing IPs programmatically (More Fun with COM: Importing Integration Packs via PowerShell), I had to do a lot of testing to make sure the script was going to actually work and import the Integration Packs correctly. Along the way, I would up with some IPs that weren’t imported correctly (which I’m sure has never happened to anyone, right?). I quickly discovered that I was in need of a tool to help me clean up the database of things like that.
After reading my other post on IPs, Understanding IP Installation: What Does Register/Unregister/Deploy/Undeploy Really Mean?, you should now know that after you install an IP, the data is still in the database. we never delete it (on purpose). However, if there is a problem with that data, like after you try to uninstall an IP, there’s not really any way within the product to help you recover from that and get the database back to a state where the IP can be re-installed. This is where the IP Cleaner tool comes in.
The goal of this tool is to allow you to see inside the Orchestrator database and understand the current state of Integration Packs that have been installed there. It will also allow to clean up your database and remove the data associated with Integration Packs at a depth of your choosing. Here’s a quick look at the application:
When you start it up, you get a configuration dialog that asks you to specify where the management server are SQL server and credentials to access them (if the current user’s credentials won’t work). You can also specify some WMI settings for when you need to access remote computers (like remote Runbook Servers or Runbook Designers) via WMI. There is lots of descriptive text on the dialog to tell you what to do.
Once you define these settings, you see the main application window.
Right off the bat you’ll see the red rows. This indicates that I have Integration Packs that have been installed at some point, but have subsequently been removed (un-deployed and unregistered). But of course the data is still in the database. Below that I have a number of other IPs that are installed and deployed, and I can see some general info about each one:
Also, if I click on a row to select it, I can click on “Get Details for Selected IP”, which fills in the lower-left section of the dialog with even more detailed information about the IP, including:
Once I’ve seen the details of the IP, I can choose to perform some actions to clean up the database for this IP. I can go all the way from simply deleting old job history data for activities that are already deleted (and no longer used) to completely wiping out the IP and its activities from the database. I just select the appropriate options on the right and click Perform Selected Actions. When I do that, I’ll get a confirmation dialog like the one below, showing what I selected:
After I type “ERASE DATA” and click OK, the records are purged from the database I can see a continuous update to the logging tab. It even shows me what tables are being affected.
Seems relatively straight-forward, right? The main goal of this tool is to allow you to clean up the database of deleted activities or IPs. But you can also use it just for exploring information without actually deleting anything. For instance:
So you see this is a tool that has a lot of potential uses. Even better, since the source code for this tool is published on CodePlex, you can see behind the curtain at how all of this information was gathered so you can use that code in your own tools, or simply reference the assemblies in the tool and call the public methods yourself.
This tool has not been through extensive testing, so before you even think about using it, you need to be sure you’re using it on a non-production system and have really good backups of the database in case it blows up for some reason. If you want to try it out and be a guinea pig (umm, I mean beta tester), email me – my email alias at Microsoft is rhearn (the part before the @ sign). If you can’t figure out the rest, well, perhaps you should just wait for the release
I will accept requests only until about June 8. Then I’ll be coming back in here and editing this post to remove the above (I don’t want to keep getting requests for the next few years!).
I’d love to get additional suggestions for functionality, but keep in mind the scope of this tool is kind of limited. I know there’s a huge assortment of tasks an admin needs to to, and for those we’ll end up building more things like this. But for now, keep the asks to IP-related actions. On the list already for improvements before this thing goes final include a command-line interface (so you can use it in a script).
Barring any data center meltdowns, you should expect to see an initial drop of this utility by the end of June. Until then… you’ll just have to wait with excitement! (or test the beta).
Robert, ‘GetAllIpInfo’ defines a dictionary using the ‘ipName’ as a key. It is possible that more than one record in the ‘[Orchestrator].[dob].[CAPS]’ table has the same name. In this event, your method throws an exception that the entry already exists in the dictionary:
I’ve changed this to use the ‘packID’ as the key as follows:
Of course, that required me to alter the code in ‘LoadGridView’ as follows:
ipNameCell.Tag = key;
string value = string.Empty;
_ipList.TryGetValue(key, out value);
ipNameCell.Value = value;
Robert, it is possible that 'GetDeployedIpVersion' throws an unhandled exception. This occurred when trying to .OpenRemoteBaseKey for a remote computer that does not allow remote changes to its registry. To work around this, I put a try/catch around the Registry operations.
Robert, it is possible to throw a SqlException (Message = "Incorrect syntax near 'IN'.") in 'GetActivityInstancesCountForIP' for a 'IPType.Unknown' there are no activities declared.
Here is the generated command:
Select COUNT(*) FROM [Orchestrator].[dbo].[OBJECTS] WHERE ObjectType IN
This is because 'BuildActivityTypesListForQuery(packID) returned and empty string.