OpsMgr 2007 R2 – OleDB module bug


The OleDB module in OpsMgr 2007 R2 still have issues dealing with datetime or nvarchar(max) fields. Even if I reported the issue during the RC evaluation it’s still there. Since I spent a lot of time troubleshooting it I’d like to share the workaround with the community.

Issue: OleDB module returns datetime and nvarchar(max) fields as NULL (or empty I didn’t check the exact value returned).

Let’s take the following query, it gets the last event collected by every HealthService:

select ME.Path As [Path], LoggingComputer, MAX(TimeGenerated)As ‘LastEvent’,
CASE WHEN Isnull(MAX(TimeGenerated),’01-01-80′) < DateAdd(hh,-2,getutcdate()) Then ‘KO’ Else ‘OK’ END
from dbo.ManagedEntityGenericView ME WITH(NOLOCK)
inner join dbo.ManagedTypeView MT WITH(NOLOCK) on ME.MonitoringClassId=MT.Id AND MT.Name = ‘Microsoft.SystemCenter.HealthService’
left join  dbo.EventView WITH (NOLOCK) on LoggingComputer = Path and TimeGenerated > dateadd(hh,-8,getutcdate()) or LoggingComputer=SUBSTRING(Path,1,CHARINDEX(‘.’, Path)-1)
where IsDeleted=0
group by Path, LoggingComputer

We can build a task who executes the query using an System.OleDbTriggerProbe. The result will be like this (notice the first and third columns are missing values):

image

The only workaround I found is to cast the fields to a limited nvarchar, say nvarchar(255). Changing the query like this:

select CAST(ME.Path as nvarchar(255)) As [Path], LoggingComputer, CAST(MAX(TimeGenerated) as nvarchar(50)) As ‘LastEvent’,
CASE WHEN Isnull(MAX(TimeGenerated),’01-01-80′) < DateAdd(hh,-2,getutcdate()) Then ‘KO’ Else ‘OK’ END
from dbo.ManagedEntityGenericView ME WITH(NOLOCK)
inner join dbo.ManagedTypeView MT WITH(NOLOCK) on ME.MonitoringClassId=MT.Id AND MT.Name = ‘Microsoft.SystemCenter.HealthService’
left join  dbo.EventView WITH (NOLOCK) on LoggingComputer = Path and TimeGenerated > dateadd(hh,-8,getutcdate()) or LoggingComputer=SUBSTRING(Path,1,CHARINDEX(‘.’, Path)-1)
where IsDeleted=0
group by Path, LoggingComputer

We get the complete output:

image

Obviously this is far from perfect, for example a datetime must remain a datetime to be able to manage it in monitors. Alas this is it at this time, hopefully someone in the OpsMgr team will fix this soon or later.

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: