connecting to sql from a remote host

ever install sql on a box and then you can't connect to it from a remote host?  you're trying to install a system center product and you're at the point where it asks where your sql server is and it's time to create some DBs... i've done this plenty of times and depending on whether you're installing an eval version or hosting all roles on a single machine, these settings can vary and can cause a headache.   here's a simple post of a few checks and solutions.

first, see if you can telnet over 1433 from the remote host to the sql server (telnet SQL-IP 1433).  if you can do this, then chances are you aren't reading this :)

what i usually do at this point is RDP directly to the sql server and open the sql server configuration manager.  assuming you're on x64, drill down on the left-side to sql server network configuration and open the protocols for your instance.  this will be mssqlserver if you chose the default instance but could be anything if you chose the named instance.  i'm using sql server 2008 r2 eval and have a named instance of vmm since that happens to be the toy of choice tonight.  ensure that tcp/ip and shared memory are enabled.  open the properties for tcp/ip and click on the ip addresses tab.  this is the important part - ensure the following:

active: yes
enabled: yes
ip address: x.x.x.x
tcp dynamic ports: - must be blank to signify you want it disabled
tcp port: 1433

at the bottom for IPall:

tcp dynamic ports: - must be blank to signify you want it disabled
tcp port: 1433

on the protocols tab ensure that enabled and listen all are set to yes, click ok and then restart the sql service.


now, see if you can telnet... betcha can.

exchange 2010 management pack: guidance, tuning and known issues

i just ran across this today and it's a world of help for any scom admin working with the exchange 2010 mp.

http://support.microsoft.com/kb/2592561

in particular there's a tidbit about modifying the alert severity and priority - something i've been trying to figure out for a while but with all the conflicts posts, i just gave up (hence my last post).  well, this kb article reinvigorated the thought that it COULD be done, and sure enough, c'est fini!

how to override the alert severity or priority for an alert in the exchange 2010 mp-
the trick is that because of the correlation engine, you have to target the rule towards the root management server class (since that's where the correlation engine resides).  i've read articles where they say it's a bad idea to modify any rules and to only modify the monitors - but modifying the monitors doesn't work and modifying the rules does - so... yea.  if anyone has any insight as to whether or not i'm committing a sin by overriding a rule, then please enlighten me.

find which alert you want to change the severity or priority for:

right-click any of them > overrides > override the rule > for all object of another class...

choose root management server and click ok (not shown because i'm lazy)

in my case i want this alert (which is normally critical) to be informational.  so i set the severity to 0 (use the table below for other options).  notice the override target is the root management server class and i've saved this off to my exchange 2010 overrides mp.

Severity:
	Critical	2
	Warning	1
	Information	0
Priority:
	High	2
	Medium	1
	Low	0

and... wa-lah!  after resetting the health from the health explorer on that specific DB and closing the alert, then just wait a few minutes and when the alert triggers again it'll be informational.

this will also help in filtering out specific alerts so they don't create tickets in service manager if you only allow "warning" and "critical" alerts to create IRs in service manager.

how to exclude specific alerts from the scom alert connector

recently i was working with our ad & messaging team trying to fine-tune the exchange alerts and it came up that the team wanted to receive e-mail notification from scom re: some of the alerts but didn't want those same alerts sent to the scom alert connector to be created as tickets in service manager.  at first i thought this would be impossible and was toying with the idea of using orchestrator to do something fancy, but couldn't even come up with a solution using scorch.


after posting to the forums, i was able to get some good feedback and help with a solution.  here is that solution:


at a high-level we're going to go old school before scom had detailed alert subscriptions in the UI.  we're going to export the unsealed notifications internal library mp from scom.  edit the xml to exclude some specific alerts by RuleID.


first let's determine which alert(s) we want to exclude.  a quick glance at the existing exchange 2010 alerts shows a plethora that we'd ideally like to demote to "informational", but thanks to the correlation engine, this isn't possible :( keep in mind we use the scom alert connector in conjunction with service manager so all these alerts create IRs in service manager and our end goal is to only receive an e-mail notification from scom - but not create an IR in scsm.

to find the ruleID - open any of the alerts and switch to the "alert context" tab.  scroll to the bottom and you'll find the ruleID.  copy the ruleID.

next, from the scom console, go to the administration pane and open management packs.  find the notifications internal library and export it.  use something like notepad ++ to edit the xml.


first we need to find the product connector subscription.  if you scroll to the bottom of the xml, you'll start to see familiar subscription names.  note: this mp can have thousands of lines (mine has 35,600+).  it'll ultimately depend on the number of subscriptions you have in the environment.


normal email subscriptions created in scom will have an elementID similar to this:

subscriptions in the scom alert connector will have en elementID similar to this:

in my case I want to exclude alerts created by the exchange mp so i'm going to modify the section of the subscription that relates to that.  i copy the above elementID for Exchange and do a find in the current MP to see where else it shows up.  note: only copy ProductConnectorSubscriptione5a1d162_0280_4951_9453_df0910f956eb - not the text before or after it.


you should only find it in one other place.

scroll down past all the groupIDs and typeIDs.  you'll eventually arrive to the beginning of the criteria section and the expressions.  this is where you want to be.  for best practice i comment out what exactly my customizations are.  after the criteria, expression and and tags, i add my own expression which will exclude the specific ruleID i found above.

notice the property is RuleId, the Operator is NotEqual and the Value is the value of the RuleID we found above.  if you have more than one exclusion, you just keep adding like this:

an important note:  once you edit the XML section of the productconnectorsubscription, you cannot modify it via the console again or else it'll wipe out your customizations.  here's some testing i did to better illustrate this:

Created a new exchange e-mail subscription in the scom console - kept the custom logic

Created a new subscription on the scom alert connector (alertsync) - kept the custom logic

Modified the e-mail scom subscription by adding a rule - kept the logic

Modified the exchange scom alert connector subscription - removed the logic

basically, if you modify the Exchange subscription via the GUI shown above, you'll lose the custom logic for excluding the alerts.  so, definitely back the file up once you've got it right and then in the future, if you want to make modifications to it via the scom console, just make sure you have that backup so you can transplant the custom XML after you've modified it via the scom console.  hope this helps!


another good resource is here.

how to remove a SCOM CI Connector from Service Manager (not supported by Microsoft)

so this is by no means microsoft supported but after doing this and then using the environment for 6+ months i can safely say that we have had no negative side effects of this.

this procedure removed the "stale" SCOM CI Connector from the SCSM console.

first i found the BaseManagedEntityId that is associated with the connector.  in my case this ended in 1558D.  so my delete SQL queries included where [tablename] like '%1558D%'.

these are the tables where the SCOM CI Connector resides:

Dbo.MT_Connector - deleted SCOM Alert Connector  (1558D)

Dbo.connector - deleted BaseManagedEntityId ending in 1558D

Dbo.recursiveMembership - deleted ContainedEntityID ending in 1558D

Dbo.BaseManagedEntity - deleted BaseManagedEntityId ending in 1558D

i think there's a stored proc you can run after this which will clean things up, but i don't know it off the stop of my head.  you can also just wait 24 hours i believe...

happy deleting!

Add Business Services and Configuration Items from SCOM to SCSM

1. Import same SEALED version of MP to both SCOM and SCSM (same version is critical)
2. Launch PowerShell on SCSM MS and add-pssnapin smcmdletsnapin (more info here http://technet.microsoft.com/en-us/library/ff461183.aspx)
3. add-scsmallowlistclass -ClassName (insert class name here)
1. Class names for DAs created in SCOM begin with Service_ such as Service_705e85be9b0442fbb6dee387258ac789
1. You won't need to explicitly add DA's created in SCOM to the allow-list since they are sub-classes of System.Entity which is in the allow-list by default.  Just make sure both MPs in SCOM and SCSM are sealed.
2. Class names for Microsoft created MPs are Microsoft.SQL or Microsoft.Exchange
3. Class names for MPs created in the Authoring Tool can be pretty much anything but should follow some sort of nomenclature such as CompanyName.Application
4. Once the classes are added to the allow list then open the SCOM CI Connector in Service Manager. If you don't already have one, then you can create one in the Connectors pane under Administration
5. Refresh the list and select the MPs you want to sync.
6. Enable (if not already done so) the connector and sync.

Step 5.

Add Affected Items to the Title or Description of a Service Manager IR

I recently began creating Incidents in Service Manager from alerts created in Operations Manager via the SCOM Alert Connector.  I'm pushing this out slowly to the various groups (SQL, Windows Server, App Support, etc...) so I can pay more attention to each one as I roll it out and do some hand holding.  The SQL group was welcoming of it last week, but requested that the Affected Item(s) show in the Title of the Incident so they can quickly see (either via the e-mail notification or in the console) which server or DB was affected because otherwise you have to open the ticket and scroll down and you might end up with a situation like this:

I know a DB is offline, some IS Packages failed, etc... but considering I have thousands of DBs and many packages... this view doesn't really help me if I want to quickly identify which DB is offline.

I reached out to Travis and asked him how to do this.  He responded with this blog post which says it's not possible natively within Service Manager because the max cardinality for those relationships can be > 1.  More info here: http://blogs.technet.com/b/servicemanager/archive/2011/04/06/faq-why-can-t-i-add-some-columns-that-i-want-to-views.aspx.

At a high-level, here is how I solved this and here's the policy I created in Opalis specifically for the SQL team.  

1. Use Monitor Object to look for newly created or updated IRs
2. Get the relationship between the object GUID returned from Monitor Object and whatever class you're looking for.  In this case, I chose Windows Computer class.
3. Query the ServiceManager DB to match the GUID of the Affected Item to it's Principal Name
4. Repeat steps 2 and 3 for other classes such as SQL DB Engine or SQL Database
5. Update Incident

Since depending on what alert is thrown, it's going to use items from different classes, you might need to add many get-relationship and query sql objects.  In my example, I've added three since for the alerts we receive, they seem to all involve three classes.

1. Windows Computer
2. SQL DB Engine
3. SQL Database

Here are the screenshots from the first 3 objects and then the last one:

To query the SQL DB Engine, you'll need this:

USE ServiceManager

SELECT [AgentName_69B79706_8AE1_470D_5FD7_0AAA7A126CCA]

FROM [ServiceManager].[dbo].[MT_Microsoft$SQLServer$DBEngine]

WHERE BaseManagedEntityId = 'Related Object Guid from...'

For SQL Database:

USE ServiceManager

SELECT [DisplayName]

FROM [ServiceManager].[dbo].[MT_Microsoft$SQLServer$Database]

WHERE BaseManagedEntityId ='Related Object Guid from...'

You'll just have to use SQL Management Studio and dig inside the Service Manager DB to find which table and column you need to query depending on the object.

Now when a new IR comes through, the policy will run and the Title will look like this:

If you rather, you can update the Description field or both.  Just add or modify the Fields section in the Update Object Properties.

How to Integrate VMM 2008 R2 into SCOM 2007 R2

• Ensure Virtual Machine Manager service is running as svc-vmm or domain account other than Local System
• VMM Agent can be running Local System
• Add svc-vmm (or other domain account running the VMM service) to the Local Admins on the SCOM RMS
• Remove previous VMM 2008 MPs from SCOM before importing VMM 2008 R2 MPs
• VMM 2008 R2 MP is located with the VMM media ONLY - this tripped me up for a solid 5 minutes
• Open VMM logged in as svc-vmm (or other domain account running the VMM service) and launch SCVMM console - if prompted for credentials or server, click OK
• Navigate to Administration > System Center and double-click Operations Manager Server - type the FQDN of the OpsMgr RMS
• Navigate to Administration > System Center and double-click Operations Manager Reporting URL - type the FQDN of where SSRS is installed for SCOM Reporting with a /ReportServer at the end - example http://SCOMRPT01/ReportServer

Not too difficult, but as per everything Microsoft if your permissions aren't correct, or you haven't removed all previous VMM MPs from SCOM - you'll get cryptic errors.  Enjoy!

This is also a helpful document: http://technet.microsoft.com/en-us/library/ee236428.aspx

Stale/Gray objects in Operations Manager

Occasionally you'll have grayed out agents in your Monitoring view in SCOM.  This can be due to the fact that a server was decommissioned before you had a chance to properly uninstall the agent and delete it from the Administration pane.  Even after you delete it from the Administration pane the object may still appear grayed out.  For this, I turned to SQL Management Studio.

First I run this query against the OperationsManager DB:
SELECT * FROM dbo.[BasemanagedEntity] where FullName Like '%computername%'

This shows me all instances that still reside in the DB for that computer.  Scroll over the the IsManaged and IsDeleted column.  You'll often, not always, notice a mixture of 1's and 0's.  Ideally if you want this server deleted IsManaged = 0 and IsDeleted = 1.  All it takes is one of the items in the IsDeleted column to show 0 for the object to show in the console in a gray state.

Which leads us to the SQL query to remove these from the console:

USE [OperationsManager]
UPDATE dbo.[BaseManagedEntity]
SET
[IsManaged] = 0,
[IsDeleted] = 1,
[LastModified] = getutcdate()
WHERE FullName like '%computername%'

Now IsManaged = 0 and IsDeleted = 1.  Refresh your SCOM Console and...

No more gray agent :)

The query to run the data purging Stored Proc is as follows:


DECLARE @GroomingThresholdUTC datetime
SET @GroomingThresholdUTC = DATEADD(d,-2,GETUTCDATE())
UPDATE BaseManagedEntity
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1
UPDATE Relationship
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1
UPDATE TypedManagedEntity
SET LastModified = @GroomingThresholdUTC
WHERE [IsDeleted] = 1
EXEC p_DataPurging

I believe this runs each night but just in case you want to force it.  Should only take a few seconds.

If that doesn't fix it, follow this guide: http://blogs.technet.com/b/kevinholman/archive/2008/09/29/agent-pending-actions-can-get-out-of-synch-between-the-console-and-the-database.aspx

Failed to create process due to error '0x80070002 : The system cannot find the file specified. ', this workflow will be unloaded.

I recently was receiving this message on one of my monitored DC's....

Event Type: Error
Event Source: Health Service Modules
Event Category: None
Event ID: 21400
Date: 8/24/2011
Time: 9:08:02 AM
User: N/A
Computer: SERVERNAME
Description:
Failed to create process due to error '0x80070002 : The system cannot find the file specified.
', this workflow will be unloaded.

Command executed: "C:\WINDOWS\System32\cscript.exe" /nologo "C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 1\84\DiscoverVMMSelfService.vbs" {E68CED9E-FB3F-B856-E3D5-F3A91FCA395B} {72BEBFB5-0B16-F3BA-8B9A-281D81DBA0C3} SERVERNAME
Working Directory: C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 1\84\

One or more workflows were affected by this.

Workflow name: Microsoft.SystemCenter.VirtualMachineManager.2008.SelfService.DiscoveryRule
Instance name: SERVERNAME
Instance ID: {72BEBFB5-0B16-F3BA-8B9A-281D81DBA0C3}
Management group: SCOMManagementGroup

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

>>>

Turns out the issue was that cscript.exe was not in C:\WINDOWS\SYSTEM32.  Another issue could be that  Windows Server Support Tools are not installed to C:\Program Files\.

getting vmware components to show up in service manager

if you have quest's qmx xtensions for vmware monitoring, it's easy to get that information into service manager so you can report on those components.

once the scom ci connector is setup, you'll need to import the sealed eXcSoftware.nonWindows MP (mine is version 7.0.0.23).  once that is imported you'll need to import the Network Device Library MP.  once that is imported you can then import the unsealed QMX.VMWare MP (mine is version 7.0.0.19).  they must be done in this order.


once the MPs are imported, you'll need to allow those classes:
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.WindowsProxyComputer
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.AVAILABILITY
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.CONFIGURATION
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.CPU
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.DISK
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.FAN
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.MEMORY
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.NETWORK
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.POWER
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.PROCESS
add-scsmallowlistclass -ClassName eXcSoftware.nonWindows.nonWindowsObject.SECURITY
add-scsmallowlistclass -ClassName QMX_Monitor_VMWare_OpsMgrMP.Group

open the scom ci connector > click on management packs and refresh.  type in your account password for the connector and eXcSoftware.nonWindows and QMX_Monitor_VMWare_OpsMgrMP should now show up.  put a checkbox next to those and click 'ok'.

keep in mind, if you use the free version of QMX for VMware then ONLY your vCenter host will show up (not the ESX hosts or guests).  in my case i'm also monitoring some IronPort's so those show up as well.  if you want all the ESX hosts to show up then you need to get paid licenses for EACH ESX host ~ costly.