SCOM - Adding Alert Context to Emails

I don't see a lot about this out here, so I am posting.

Operations Manager notifications work pretty well. There are several community solutions out there to enhance it, but many of us still use the out of the box notifications. Over the years I have noticed people asking about adding alert context to email, but never receive any good answers. The alert context is powerful, and depending on the alert can provide a ton of data. The variables we use in email is simply xml.

For example, Alert Name: $Data[Default='Not Present']/Context/DataItem/AlertName$

Notice the slashes? Dataitem is the essentially where all of the alert context data is located. Want to see EVERYTHING in there?

Simply do this:
$Data[Default='Not Present']/Context$

It will be different properties and data for each alert, and in some cases might be empty.
However, I have found it VERY useful to append to the bottom of the emails.

If you want to take a step further and actually see the different items in alert context, use powershell.

import-module OperationsManager
$a = Get-SCOMAlert | ?{$_.name -like "*SOMEALERT*"} | select -first 1
#All of the data
([xml]($a.context)).dataItem | fl *
#just one property
([xml]($a.context)).dataItem.Type

Anyway, short and simple, hopefully it helps someone.

SCOM - Query Notification Subscription Data via SQL

I needed to provide data about SCOM notification subscriptions via SQL Reporting Services. I was hoping to find a simple answer, so I went to searching online. It seemed that no one had posted anything related to querying notifications from the OperationsManager database. I am well aware of the Powershell cmdlets that can be used to get subscription information, but I love SQL, queries, and SSRS. I started digging around but had quite a bit of trouble finding any subscription information, so I began doing database searches using subscription GUIDs. Surprisingly, I only found the data in a few tables. This is not normally what most of us are used to querying from SCOM, so I had to do a little learning, and I want pass the queries on to you.

The queries and data are from the OperationsManager database. You can use them with ReadOnly credentials.

First of all, lets take a look at the tables. My GUID search led me to the tables and columns below; this was very disheartening, but I kept moving forward.

• ManagementPack.MPRunTimeXML
• Module.ModuleConfiguration
• ManagementPack.MPXML
• ModuleType.MDTImplementationXML

Of the above 4 tables, we really only need 1 - Module. 

However, Microsoft recommends that we stick to views if we can help it, and we do have a view that is associated with the module table, which is "RuleModule."

There are a few other table/views that we need just to get some key data, so our complete list of needed views/tables are:

• RuleModule
• ModuleType
• RuleView

The rest of the details come from the xml configuration in RuleModule, which turns something that should be relatively simple, into something a little more complicated. This is not a tutorial on how to query and parse xml columns, so I will not go into those details.

SCOM notification essentially have 4 components:

1. Channel - SMTP, command, SMS, etc. In the xml, this is actually called "Protocol".
2. Subscriber - This is the recipient. A subscriber can have multiple addresses and protocols,
3. Address - Email Address, phone number, command location. In the xml this is called "Device". Addresses are found as part of a subscriber.
4. Subscription - The subscription is actually a SCOM rule that triggers based on the criteria specified. The rule module is where we find the components of the subscription we need.

The Queries

Subscription Query

Let's start with the subscription. Like I said earlier, a subscription is a rule. Rather than writing a query to only get one subscription, I decided to get all subscriptions, which could then be filtered.

Select r.RuleId, r.RuleModuleId, r.enabled, R.DisplayName,D.C.value('RecipientId[1]','varchar(4000)') SubscriberId
FROM (
Select RuleId, rm.RuleModuleId, enabled, r.DisplayName, cast(RuleModuleConfiguration as XML) xmlquery
FROM
RuleModule rm
join RuleView r on rm.RuleId = r.Id
where r.Category = 'Notification'
and RuleModuleName = 'CD1'
) r
Cross Apply xmlquery.nodes('//DirectoryReference') D(C)

You can filter this query to give you a single subscription Id by name, Id, enabled/disabled, etc.

Subscriber and Device Query
The next query gets the subscriber and the subscriber's devices. When you run the query, rows may look like they are repeating. The row will repeat for each device. Because a subscriber could have an unknown number of devices, I did want to give variable columns counts. Therefore, you can use your own grouping and SQL magic to manipulate the results.

Select r.SubscriberName, r.SubscriberId,
D.C.value('Name[1]','varchar(4000)') DeviceName,
D.C.value('Protocol[1]','varchar(4000)') DeviceProtocol,
D.C.value('Address[1]','varchar(4000)') DeviceAddress
FROM (Select  N.C.value('Name[1]','varchar(4000)') SubscriberName,
N.C.value('RecipientId[1]','varchar(4000)') SubscriberId,
n.c.query('.') as xmlquery
from (
Select cast(MDTImplementationXML as xml) Recxml
FROM [dbo].[ModuleType] mt
Where MDTName = 'Microsoft.SystemCenter.Notification.Recipients'
)a Cross Apply Recxml.nodes('//Recipient') N(C)) r
Cross Apply xmlquery.nodes('//Device') D(C)

 Complete Query of Subscriptions with Subscribers and Devices
Both queries can be used alone to provide good information. However, what I really wanted was a list of subscriptions and who those subscription go to. For this I combined the two queries to give a nice result set that I could use in an SSRS report. Once I wrote the SSRS report, I used SSRS URL parameters to call the report directly from a SCOM notification.

-- This is the 2 queries joined together to provide the data that we want
Select Subscriptions.DisplayName, Subscriptions.RuleId,
Case subscriptions.enabled When 0 Then 'No' Else 'Yes' End as SubscriptionEnabled,
Subscribers.SubscriberName, Subscribers.DeviceName, Subscribers.DeviceProtocol,
Subscribers.DeviceAddress
FROM (
--This is the whole subscriber query with Address
Select r.SubscriberName, r.SubscriberId,
D.C.value('Name[1]','varchar(4000)') DeviceName,
D.C.value('Protocol[1]','varchar(4000)') DeviceProtocol,
D.C.value('Address[1]','varchar(4000)') DeviceAddress
FROM (Select  N.C.value('Name[1]','varchar(4000)') SubscriberName,
N.C.value('RecipientId[1]','varchar(4000)') SubscriberId,
n.c.query('.') as xmlquery
from (Select cast(MDTImplementationXML as xml) Recxml FROM [dbo].[ModuleType] mt
Where MDTName = 'Microsoft.SystemCenter.Notification.Recipients' )a Cross Apply Recxml.nodes('//Recipient') N(C)) r
Cross Apply xmlquery.nodes('//Device') D(C)
) Subscribers
Join
(--These are the subscriptions
Select r.RuleId, r.RuleModuleId, r.enabled, R.DisplayName,
D.C.value('RecipientId[1]','varchar(4000)') SubscriberId
FROM (Select RuleId, rm.RuleModuleId, enabled, r.DisplayName,
 cast(RuleModuleConfiguration as XML) xmlquery
FROM
RuleModule rm
join RuleView r on rm.RuleId = r.Id
where r.Category = 'Notification'
and RuleModuleName = 'CD1'
--and Enabled <> 0
) r
Cross Apply xmlquery.nodes('//DirectoryReference') D(C)
) Subscriptions on Subscribers.SubscriberId = Subscriptions.SubscriberId
--Where ((Subscriptions.RuleId = Replace(Replace(@SubscriptionId,'{',''),'}','')
--or @SubscriptionId IS NULL)
--and DeviceProtocol in ('SMS','SMTP'))
order by 1,2

Please let me know if you have any questions or have any issues running the above queries.

Orchestrator - Misrepresented and Misunderstood

Purpose:

1.       Talk about the misrepresentation of Orchestrator.

2.       Point out some things about Orchestrator that people don't quite understand.

3.       Convince people to give Orchestrator a try.

Summary:

System Center Orchestrator is a product in the System Center Suite. If you have heard of Orchestrator, you have probably heard it along with other buzzwords like "automation" and "cloud." This text is not about writing a cool runbook, automation, pitching cloud services, or discussing how System Center can solve all of the world's IT problems (it can though). I want to discuss a misconstrued view of Orchestrator that I continue to see over and over, which I think prevents IT Organizations from using it or trying it out.

I think that Orchestrator is both misrepresented and misunderstood.

What was my original perception of Orchestrator?

As an Operations Manager and Service Manager guy, I knew that I could build automation for nearly anything without ever relying on any products outside thoese two systems. I have been using Operations Manager to automate IT tasks, and perform cleanup as a result of alerts for years.

Service Manager provides the tools to take a ticket system and turn it into an automation machine using the authoring console, Powershell, and the native flexibility of the system.

So when Microsoft began touting Orchestrator, my first reaction was, “why in the world do I need that?” The simple answer is, I don't…

As a matter of fact, I have never had to use Orchestrator for anything…ever. I still haven't found a task, workflow, process, or anything else I couldn't complete by using the other System Center products that were already implemented, even when they were communicating outside the realm of Microsoft. (Thanks PowerShell!)

So, in a sense, Orchestrator almost seems like a novelty - an unnecessary System Center Product…but it's not.

Why use a complex system when a simple system is available?

Let's take a look at Operations Manager and Service Manager. We know they both can do just about anything that we want in terms of process and automation. When you get down deep, both products can get somewhat complicated. They both run complex operations on the back-end, which allows it to use a simple front-end. I mean, have you looked at the databases or stored procedures? My point is, there is a lot going on that we don't see. Even without much of anything configured, the systems are churning away.

The beauty in Orchestrator is its back-end simplicity. What is Orchestrator doing? Nothing. A little maintenance here or there, checking schedules, checking the clock, etc. What are the others doing? Constantly performing complex operations even at base load. And what are they doing when you begin automating your processes, performing remediation, or waiting for a criteria match? Even more. So much in fact, you can easily drop 40 GB of ram or more with several separated disks on the SQL server to maintain an acceptable level of performance while the systems are performing their operations.

So why are so many people taking such complex operations and stuffing them full of more complex operations creating a memory and disk eater, when we have this nice, little calculator waiting for its command? A nice little calculator that will do anything you tell it to do, and communicate with any machine you want to communicate with. Yet a lot of people still don't use it.

I think the answer is simply misrepresentation.

Everyone talks about all the cool, complex things Orchestrator can do, all the systems it can talk to, and all the integration packs that are available. It actually sounds kind of scary. I mean, who has time to do all that?

But, at its base, Orchestrator is not much more than a scheduling engine - a simple calculator.

"Simple" is what Orchestrator was meant to be all along. Take a complex operation and break it down into simple steps.

Do this…

o    Find something you would like to do – such as automation, remediation, communication between 2 systems. Whatever it is, start out with a goal.

o    Document the exact technical process that should happen on paper. If you can't write your process on paper, you can't write it with a computer.

o    Read through Kevin Holman's Orchestrator quick start guide.

§  http://blogs.technet.com/b/kevinholman/archive/2013/10/18/orchestrator-2012-r2-quickstart-deployment-guide.aspx

o    Take 15 minutes and install Orchestrator 2012 R2.

o    Take another 10 minutes to download and install the integration packs.

§  http://www.microsoft.com/en-us/download/details.aspx?id=39622

o    Create a Runbook

Remember this…

1.       You will stumble through your first runbook, but keep at it, it will get easier.

2.       The more complex operations you remove from other systems and enter into Orchestrator, the easier it will be to maintain, document, and transfer knowledge.

3.       Don't make it complicated. Don't write a giant PowerShell script and enter it into Orchestrator; this defeats the purpose of simplicity.  Break out your steps into multiple activities.

SCOM System Center Management Service is now Microsoft Monitoring Agent

Microsoft Monitoring Agent is a new agent that replaces the Operations Manager Agent and combines .NET Application Performance Monitoring (APM) in System Center with the full functionality of IntelliTrace Collector in the Microsoft Visual Studio development system for gathering full application-profiling traces. Microsoft Monitoring Agent can collect traces on demand or can be left running to monitor applications and collect traces. You can limit the disk space that the agent uses to store collected data. When the amount of data reaches the limit, the agent begins to overwrite the oldest data and store the latest data in its place.

You can use Microsoft Monitoring Agent together with Operations Manager or as a stand-alone tool for monitoring web applications that were written on the Microsoft .NET Framework. In both cases, you can direct the agent to save application traces in an IntelliTrace log format that can be opened in Microsoft Visual Studio Ultimate. The log contains detailed information about application failures and performance issues.

You can use Windows PowerShell commands to start and stop monitoring and collect IntelliTrace logs from web applications that are running on Internet Information Services (IIS). To open IntelliTrace logs that are generated from APM exceptions and APM performance events, you can use Visual Studio. For information about supported versions of IIS and Visual Studio, see Microsoft Monitoring Agent Compatibility.

http://technet.microsoft.com/en-us/library/dn465153.aspx

SCOM 2012 Upgrade ACS Schema does not Update

I didn't write the blog post, but I did experience the issue, and it is worth repeating. It seems this is going to affect anyone upgrading from SCOM 2012 to SP1 who uses ACS. The issue is that the schema doesn't get updated, causing crashes to ADTServer. If you want to full details, you can refer to this blog post http://nocentdocent.wordpress.com/2013/02/12/sysctr-2012-sp1-acs-upgrade-error-scom-sysctr/.

Here are the steps you need to take, assuming you are having the issue. Again, if you are not sure you are having the issue, refer to the post above.

1. Check the current Schema version of ACS
1. Open SQL Management Studio
2. Run the query Select * FROM dtConfig
3. Check the row with a comment called "database schema version"
4. Make sure it is at version 7. If it is at version 7, continue. If not, do NOT do the rest of the steps.
2. Backup your ACS Database (Mine is Called OperationsManagerAC)
3. Find the SQL script called DbUpgV7toV8.sql
4. Making the assumption you already upgraded ACS, you can find the script in C:\Windows\System32\Security\AdtServer
5. Execute the script against your acs database
6. You might have to give it a day or so before another partition is created. If you don't receive any errors, you should be good.

Create and Assign Service Manager Incidents Directly from SCOM on Demand

Update:
As you read through below, you will notice that Microsoft has been nice enough to use some of the IDs I was using with the release of SP1. (This post was pre SP1) You will have to make small modifications to your script and Ids but the below solution still works.

The Issue
If you use Operations Manager and Service Manager, you know by now that SCOM will automatically create Incidents in Service Manager. However, for most organizations, this just doesn’t make sense because they do not have a 1-to-1 Alert-to-Action ratio. You can set up basic criteria to limit the automatic creation, but this usually still results in too many unnecessary incidents. As a result, most organizations do not utilize this connector, which at one point was one of the most requested features of SCOM – to do really cool things with ticketing systems.
The Solution
So, instead, I have created a solution that will allow you to create incidents on demand directly from a SCOM Alert, while utilizing all the cool features of the Service Manager SCOM Alert connector. All you have to do is right click the alert(s) to create the on-demand tickets.
What are some features of the solution in conjunction with the native Connector:

• Right click one more multiple alerts and assign incidents directly to the specified group/user
• Closing the alert closes the ticket and vice-versa
• The Assigned User and the Ticket Id are maintained in the alert as sourced from SCSM
• The affected component in SCOM is automatically added as a related configuration item in SCSM
• Easily can be extended to do more fun stuff with only basic PowerShell Knowledge

How Does it Work
The solution utilizes the following components:

1. SCOM and SCSM obviously
2. A very small PowerShell Script
3. SCOM CMDLETS

Workflow:

1. A user right clicks the alert and sets the resolution State.
2. A Command Subscription triggers based on the resolution state, sets a couple of custom fields, and changes the resolution state to “Generate Incident” a
3. The SCSM Alert connector triggers based on the new resolution state, generates an incident, and applies an incident template based on data in the custom fields.

How to Implement the Solution

These Steps need to be performed in SCOM

Step One
Copy the following PowerShell script code and save on your SCOM management server as UpdateCustomFieldPowershell.ps1. (I took this code from another blog online and modified it as my own. Unfortunately, I don’t know who wrote the original script.)

Param($alertid) 
$alertid = $alertid.toString()
write-eventlog -logname "Operations Manager" -source "Health Service Script" -eventID 1234 -entrytype "Information" -message "Running UpdateCustomFieldPowershell"
Import-Module OperationsManager; "C:\Program Files\System Center 2012\Operations Manager\Powershell\OperationsManager\Functions.ps1"; "C:\Program Files\System Center 2012\Operations Manager\Powershell\OperationsManager\Startup.ps1"
$alert = Get-SCOMAlert -Criteria "Id = '$alertid'"
write-host $alert
If ($alert.CustomField2 -ne "AlertProcessed")
    {
$AlertResState = (get-SCOMAlertResolutionState -ResolutionStateCode ($Alert.ResolutionState)).Name
$AlertResState
   # $alert.CustomField1 = $alert.NetBIOSComputerName
     $alert.CustomField1 = $AlertResState
     $alert.CustomField2 = "AlertProcessed"
$alert.ResolutionState  = 254
    $alert.Update("")
    }
exit

Step Two
We need to create some new alert resolution states. The alert resolution states will trigger the script. You want to create a resolution state for each support group you would assign an alert. You can use whatever format you want. I used the format of “Assign to GROUPNAME”. Also keep in mind the Resolution State Ids and order you will use. I made my alphabetical. DO NOT use the resolution state 0,1,254, or 255.
To create new resolution states:

• Go to the SCOM Console
• Go to the Administration Workspace
• Go to Settings
• Select Alerts
• Select the new button, create a resolution state and assign an Id. Resolution states will always be ordered by their Id
• Repeat for each resolution state

After you create your alert resolution states, you will need to create one more that triggers the SCSM Connect. Name this Alert Resolution State “Generate Incident.” Also, make sure this is the exact name as the script requires. If you want to change the name, you will have to update the script. Also, set the Id to 254.
Step Three
We need to set up a command channel and subscription that will trigger and run the script.

• Open the SCOM Console
• Go the the Administration Workspace
• Go to Channels
• Create a new Command Channel
• Enter the full path of the above script
• Enter the command line parameters as shown in the example below (Be sure the use the double and single quotes correctly)
  • "C:\OpsMgrProductionScripts\SCOMUpdateCustomField.ps1" '$Data/Context/DataItem/AlertId$'
• Enter the startup folder as C:\windows\system32\windowspowershell\v1.0\
• Save the new Channel

Next, we need to set up the subscriber for the command channel.

• Open the SCOM Console
• Go the the Administration Workspace
• Open subscribers
• Create a new subscriber
• In the addresses tab, click Add
• In the subscriber address, set the channel type to command and then select the channel you set up in the previous steps.
• Save the address and the subscriber

Next, we need to set up the Command Subscription

• Open the SCOM Console
• Go the the Administration Workspace
• Open Subscriptions
• Create a new Subscription
• On the subscription criteria, check the checkbox “with a specific resolution state”
• Select all the new resolution states except “Generate Incident” (Do not select anything other than the assignment states)
• On the subscribers, add the new subscriber you created in the previous steps
• On the Channels, add the new channel you created in the previous steps
• Save the subscription

Step Four
The last thing we have to do in SCOM is set up the Alert connector. The alert connector will be triggered based on the resolution status of “Generate Incident”.

• Open the SCOM Console
• Go the the Administration Workspace
• Go to connectors and select Internal Connectors
• Open the SCSM Alert Connector
• Create a new subscription in the connector
• In the criteria of the subscription

These Steps need to be performed in SCSM 

Step One
The first thing you want to do is enable and connect your SCSM SCOM Alert Connector. If you do not know how to do that, you can refer to technet. http://technet.microsoft.com/en-us/library/hh524325.aspx. Verify it works before moving any further.
Step Two

• Create a new Management Pack dedicated to storing the SCOM Incident Templates in SCSM
• Create a SCOM incident template for each group that you want to assign via SCOM. Typically, this is about 10-20 templates. For testing purposes, I would just start with one or two.
• Add the correct group as the assigned to in each template. It is not necessary to fill any other information.

Step Three

• In SCSM open the SCOM Alert Connector
• Go to the alert routing rules and add a new rule
  • For each rule select one of the templates that you created
  • On the select criteria type, select the Custom Field radio button
  • For custom field one, enter the exact name of the resolution state you used in SCOM. For example, if you are going to assign to the server team, and the name of resolution state is called “Assign to ServerTeam”, this is the exact phrase you need to enter into Custom Field one.
• Select Custom Field two from the drop down
• For custom field two, enter “AlertProcessed”
• Click OK
• Repeat for each template

Time for Testing! 

Now you are ready to test. Find an alert in SCOM, right click the alert and set it to a resolution state for assignment. Give the subscription time to run and the SCSM connector time to run. Usually, if the connector is running every 2 minutes, it takes the total process about 5 minutes to complete. While the actual workflows are running in a second, it simply takes time for both of them to trigger.

Troubleshooting

If there are any issues with the configuration, the event logs will usually tell you about failures. If it is not working, but you don’t see any failures, your criteria probably do not match.

Conclusion

This is a great alternative solution to automatically creating tickets from SCOM. You can still automatically create tickets as well simply by adding subscriptions to the SCSM SCOM Alert connector. If you have any issues, question, leave a comment.

SCOM Availability Report Monitoring Unavailable SOLVED (Unsupported)

I have seen numerous posts floating around regarding the SCOM Availability Reports showing "Monitoring Unavailable" even though the objects were healthy for the time period. For example, I can run the SCOM Availability Report, select "Exchange 2007 Service”, select the date range and expects lots of green, yellow, and red, but instead I primarily see dark gray.

The issue could be caused by the following:

• Bad Calculations during health rollups
• Bad Performance
• The Data Warehouse is behind
• and others

Several blog posts already exist regarding the above issues and can be found with Google/Bing. We will not be addressing those specific issues. We will be dealing with a very specific issue, which as far as I can tell is a bug, but I am not going to hold my breath.

Data Warehouse Availability Aggregation Process

SCOM has a process called the Data Warehouse Availability Aggregation Process. The Data Warehouse Availability Aggregation Process is somewhat complicated with about 10 steps that you probably don't care about. If you do, you can check this diagram, which gives a fairly decent picture of what is going on in the process.

Remember the issues various people continue to have with Management Server Resource Pools becoming unavailable in SCOM 2012? That resource pool unavailability is calculated just like any other object. Inside the Data Warehouse, a table called "dbo.HealthServiceOutage" keeps the outage data when this occurs on all objects. However, sometimes, it forgets to enter an outage end time.  And that is the key to our current issue.

So lets take a look at the Health Service Outage table.

Select top 10 * FROM dbo.HealthServiceOutage with ( NOLOCK )

 

 

 

 

 

 

 

 

 

 

You can see how each managed entity has reason for the outage, start time, and end time. However, in some cases, the end time will be NULL.

 

There are two reason for this.

1. The object that is "unavailable" is truly still unavailable. This SHOULD be NULL.
2. The object is now healthy, but an EndDateTime did not get written. Unless you have a big problem in your environment, you should have very few of these.

A bunch of Ids doesn't get us very much information, so lets enhance the query a bit. This query shows us ONLY the items with a NULL EndDateTime. It is also joined to the ManagedEntity table so we can see the actual names of the objects.

Select h.HealthServiceOutageRowId, h.StartDateTime, h.EndDateTime, h.ReasonCode, h.DWLastModifiedDateTime, me.ManagedEntityRowId, me.DisplayName, ME.FullName

FROM dbo.HealthServiceOutage h with ( NOLOCK )

JoinManagedEntity me on h.ManagedEntityRowId = me.ManagedEntityRowId

WhereEndDateTime IS NULL

 

Notice the "All Management Servers Resource Pool" EndDateTime is NULL. Assuming it is actually available, this should have the actual end date.

The Health Service Outage data is considered in the availability calculations of the Standard Data Set in the Data Warehouse. If you look at any of the State.StateDaily_[GUID] tables, you will see the "HealthServiceUnavailableMilliseconds" column is always maxed out, assuming the other columns are 0. We can make the Data Warehouse recalculate this data by modifying the "Health Service Outage" table.

If you want to make your objects available again, you can follow the steps below. Please note:

1. This is NOT supported by Microsoft
2. You SHOULD backup your Data Warehouse before making any changes
3. After we make the changes, you Data Warehouse might have to do A LOT of recalculate, causing a kick in performance for a short period of time, or it might cause you data warehouse to fall behind for a little while. If you are having performance issues with your Data Warehouse, you should address them first.

 

How to Make my objects available again in SCOM Availability Reports

BACKUP YOUR OPERATIONSMANAGERDW DATABASE

Open SQL Management Studio and connect to your OperationsManagerDW

The first thing we want to do, is make sure your Data Warehouse is not behind. Run the below query. If you get more than one of two rows, then follow this article and catch up your data warehouse.

DECLARE @DatasetId uniqueidentifier

SELECT

@DatasetId = DatasetId

FROM Dataset d

WHERE (d.DatasetDefaultName ='State data set')

SelectAggregationDateTime, AggregationTypeId

FromStandardDatasetAggregationHistory

Where DatasetId = @DatasetId

And

DirtyInd = 1

Assuming your data warehouse is not behind, let's continue. Paste and run the following query into SQL Management Studio.

Select h.HealthServiceOutageRowId, h.StartDateTime, h.EndDateTime, h.ReasonCode, h.DWLastModifiedDateTime,

me.ManagedEntityRowId, me.DisplayName, ME.FullName

FROM dbo.HealthServiceOutage h with ( NOLOCK )

Join ManagedEntity me on h.ManagedEntityRowId = me.ManagedEntityRowId

Where  DisplayName = 'All Management Servers Resource Pool' and EndDateTime IS NULL

If you have results, then you have an EndDateTime that is NULL. Before assuming that is should not be NULL, you should go into SCOM and verify the state of the object first to make sure it is available. If the state of the object is available, but your query returned one or more NULL EndDateTime entries, then lets continue.

Now we need to update the HealthServiceOutage table and enter an EndDateTime for the results above. The query below does the following:

1. Gets the rows from the query above
2. Updates the DWLastModifiedDateTime to the current UTC Date and Time
3. Updates the EndDateTime to match the StartDateTime

Why are we modifying the DWLastModifiedDateTime?

We want the data warehouse to recalculate the states, so the reports accurately reflect availability. We must update this column, otherwise recalculation will not happen.

If you a savvy SQL Query person, then you can update the below query and enter an EndDateTime of your choosing in UTC. I have decided that I want to make the EndDateTime the same of the StartDateTime, because I don't really know how long the resource pool was down, but it is usually a very short time.

NOTE: THIS QUERY WILL MAKE CHANGES TO THE HEALTHSERVICEOUTAGE TABLE

Paste and execute the following query into SQL Management Studio.

Update dbo.HealthServiceOutage

SetDWLastModifiedDateTime = GETUTCDATE(),EndDateTime=StartDateTime

WhereHealthServiceOutageRowId

IN (

Select h.HealthServiceOutageRowId

FROM dbo.HealthServiceOutage h with ( NOLOCK )

JoinManagedEntity me on h.ManagedEntityRowId = me.ManagedEntityRowId

Where  DisplayName ='All Management Servers Resource Pool' and EndDateTime IS NULL)

 

If you want to verify the changes, you can run the following Query.

Select h.HealthServiceOutageRowId,h.StartDateTime,h.EndDateTime,h.ReasonCode, h.DWLastModifiedDateTime,me.ManagedEntityRowId, me.DisplayName, ME.FullName

FROM dbo.HealthServiceOutage h with ( NOLOCK )

JoinManagedEntity me on h.ManagedEntityRowId = me.ManagedEntityRowId

Where  DisplayName ='All Management Servers Resource Pool' and EndDateTime =StartDateTime

order by DisplayName

After we make the change, the next time Standard Data Set Maintenance runs, it will recalculate Availability. It will make the DW look like it is behind, but just give it time to catch up and calculate the state. This could take several hours. You can run the query below periodically and verify that your row count is getting smaller. My row count increased to about 350, and over about 12 hours reduced down to the normal one or two rows.

DECLARE @DatasetId uniqueidentifier

SELECT

@DatasetId = DatasetId

FROM Dataset d

WHERE (d.DatasetDefaultName ='State data set')

SelectAggregationDateTime, AggregationTypeId

FromStandardDatasetAggregationHistory

Where DatasetId = @DatasetId

And

DirtyInd = 1

Once the Standard Data Set calculations are finished, run your reports and verify they are no longer gray.

On another note, the above steps will work for objects other than the Resource Pool. However, the resource pool is what caused ALL of my objects to be gray in reports. It is possible that other objects with NULL EndDateTime entries can cause that specific object to be gray in a report. I also had other objects that did not have an EndDateTime properly set. So, for each of the objects, I simply verified the were available in SCOM, then I set the EndDateTime and the DWLastModifiedDateTime. Remember, if an object in SCOM is unavailable then the EndDateTime SHOULD BE NULL.