Monthly Archives: September 2014
I noticed an issue while I was writing a SMA integration module. I once made an mistake in the .json file and I noticed I couldn’t import the updated module back to SMA even after I firstly deleted the old version. I’ll explain this issue using a sample dummy module.
Reproducing The Issue
To reproduce the issue, I firstly created a dummy module with 3 files:
The module .psm1 and the connection file .json (notice there’s a connection field named “ComputerName” in the .json file):
I zipped and imported this module into SMA, everything looks good so far. and the connection fields are correctly displayed (as expected):
I then update the .json file (Changed the connection field name from ComputerName to ComputerFQDN):
I zipped the updated module, tried to import the module into SMA, but got an error:
I then tried to delete the existing module and imported again, but I got the same error.
I also noticed that even after the module has been deleted, the connection is still available to be selected:
Since I couldn’t find any documentation on how to completely remove an Integration Module, I went ahead and developed a SQL script to completely remove the module and module connection from the various tables in the SMA database. Here’s the SQL script:
Declare @ModuleName Varchar(max)
Declare @ConnectionName Varchar(max)
Set @ModuleName = 'MyModule'
Set @ConnectionName = 'MyModuleConnection'
PRINT 'Deleting Connection Field values'
delete from Core.ConnectionFieldValues Where ConnectionKey in (Select ConnectionKey from Core.Connections Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName))
PRINT 'Deleting Connections'
delete from Core.Connections Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName)
Print 'Deleting Connection Fields'
delete from Core.ConnectionFields Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName)
PRINT 'Deleting Connection Types'
delete from Core.ConnectionTypes where Name = @ConnectionName
PRINT 'Deleting Activity parameters'
delete from Core.ActivityParameters where ActivityParameterSetKey in (Select ActivityParameterSetKey from Core.ActivityParameterSets where ActivityKey in (Select ActivityKey from Core.Activities Where ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))))
PRINT 'Deleting Core Activity parameter Sets'
delete from Core.ActivityParameterSets Where ActivityKey in (Select ActivityKey from Core.Activities Where ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName)))
PRINT 'Deleting Core Activities'
Delete from Core.Activities Where ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))
PRINT 'Deleting Job Modules'
delete from Core.JobModules Where ModuleVersionsKey in(Select ModuleVersionsKey from Core.ModuleVersions where ModuleKey in (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))
PRINT 'Deleting Module Version'
Delete from Core.ModuleVersions where ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName)
PRINT 'Deleting Modules'
Delete from core.Modules where ModuleName = @ModuleName
To use this script, you will need to change the @ModuleName and @ConnectionName variable to suit your module:
After I ran this script, I was able to import the updated module:
Although I’ve used this for multiple modules in multiple SMA environments and so far have not found any problems, I did not consult this workaround with SMA experts, please use this at your own risk. Please don’t blame me if it breaks your environment.
I’ve setup 2 servers for the SMA environment in my lab a while back. Yesterday, I loaded the SMA MP (version 188.8.131.52) into my OpsMgr management group. Needless to say, I followed the MP guide and configured the Database RunAs profile. However, soon after the MP is loaded, I started getting these 2 alerts:
- The Service Management Automation web service could not connect to the database.
- The Service Management Automation worker server could not connect to the database.
To troubleshoot these alerts, I firstly unsealed the management pack Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.mp, as this is where the monitors are coming from. The Data Source module of the monitor type uses System.OleDbProbe probe action module to make the connection to the database.
To simulate the problem, I used a small free utility called Database Browser Portable to test the DB connection. I launched Database Browser using the same service account as what I configured in the RunAs profile in OpsMgr, and selected OleDB as the connection type:
I populated the Connection String based on the parameters (monitoring object properties) passed into the data source module: Provider=SQLOLEDB;Server=SQLDB01.corp.tyang.org\;Database=SMA;Integrated Security=SSPI
Note the Database Instance property is empty. this is OK in my lab because I’m using the default SQL instance. I’ll explain this later.
The test connection result is positive:
However, after connected, when I clicked the connection, nothing happened, the list of tables did not get populated. I then tried using my own account (which has god rights on everything in the lab), and I got the same result.
Long story short, after trying different configuration changes on the SQL server, I finally found the issue:
On the SQL server, the Name Pipes protocol was disabled
After I enabled it, I was able to populate the tables in Database Browser:
And within few minutes, the alerts were auto closed.
While I was troubleshooting this issue, I came across a blog post from Stanislav Zhelyazkov. In the blog post, Stan mentioned adding the DB instance name in the registry (where the discoveries are looking for). However, when I added “MSSQLSERVER” in the registry and forced re-discovery, the monitors became critical again and I received several 11852 event in Operations Manager event log:
I email Stan and he got back to me and told me he’s using a named instance in his lab and these monitors are working fine in his lab after he added the SQL instance name in the registry. He also told me he didn’t recall specifying the SQL Instance name during the SMA setup but the setup went successful. My guess is that the SQL Browser service must be running on his SQL server, so the setup had no problem identifying the named instance.
Based on my experience and Stan’s experience, we’d like to make the following recommendations:
- Enable the Name Pipes protocol
- If using the default SQL instance, please do not manually populate the registry key
- If using a named instance, please add the SQL instance name in the registry if it’s not populated after setup.
Thanks Stan for his input on this one!
This blog has been a bit quiet lately. This is because I have been very busy, and it’s just all the things I’ve been working on have not been eventuated yet. I just want to quickly post a short update here to share some information with everyone.
ConfigMgr 2012 Client Management Pack Update
I have spent the last couple of weeks updating the ConfigMgr 2012 Client Management Pack. I thought it would only take me few days but it turned out it has taken a lot longer than what I expected (2 solid weeks with over 10 hours each day including weekends). Having said that, there has been a lot of changes and bug fixes in the new release. I have completed the beta version last night and I’m currently testing it with my fellow SCCDM MVP Stanislav Zhelyazkov. I’ll let the beta version running in the test environments for few more days, so hopefully if nothing goes wrong, it will be released in the coming days.
A Custom OpsMgr PowerShell Module for SMA
I started writing a number of OpsMgr PowerShell functions that directly interact with OpsMgr SDK. These functions can be used to create management packs, various rules and monitors. I then transformed these functions into a standalone PowerShell module which can be imported into SMA as Integration Modules. Unlike the built-in OpsMgr module in SMA, this is not a portable module, it does not require SMA runbook workers to install the native OpsMgr 2012 module.
I will be presenting this module in the Melbourne System Center, Security & Infrastructure group next Thursday night (25th September 2014) at Microsoft’s Melbourne office in Southbank with Dan Kregor. We will demonstrate how to automate OpsMgr management packs creation using this module, alone with SMA, Orchestrator and Sharepoint 2013.
To date, I have spent over 2 months working on this project and have already written around 2000 lines of code. I’m really excited with what this solution does and I think it’s pretty cool. If you live in Melbourne and interested in attending, the RSVP detail can be found on the user group website: http://mscsig.azurewebsites.net/
After the user group meeting, I will also document this solution and make it available to the community.