Category Archives: TSQL

OleDbProbe – VariantType=”0” – Empty

Today I found an issue with the OleDbProbe module in which I found no solution in my searches, so it was one of those cases where I needed to step through various debugging tools and scour through code to solve the problem. It’s can be rewarding, because it gives me something to write about, but it sure can be time consuming. I’ve used this module many times in the past, but this is the first time I’ve seen odd behavior in the results.

The Problem

I had plugged my query into the module and simulated the workflow, and the results of each column was <Column VariantType=”0” />, as shown below.

- <DataItems>
- <DataItem type="System.OleDbData" time="2016-06-04T14:15:45.9305344-05:00" sourceHealthServiceId="D4E9691B-9F54-0F31-786C-BAF110FB769F">
<HRResult>0</HRResult>
<ResultLength>8</ResultLength>
<Result>Success</Result>
<InitializationTime>909</InitializationTime>
<OpenTime>0</OpenTime>
<ExecutionTime>20</ExecutionTime>
<FetchTime>0</FetchTime>
<RowLength>5</RowLength>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
- <Columns>
<Column VariantType="0" />
</Columns>
<OriginalDataLength>0</OriginalDataLength>
<ErrorDescriptionLength>0</ErrorDescriptionLength>
<ResultCode>0</ResultCode>
</DataItem>
</DataItems>

As we can see from the results, the query succeeded. One would assume with a successful query comes valid and filled columns. Wondering why the results were all empty, I expanded the query selection to include all columns, and what I found was quite interesting. Below is just the columns section. As we can see here, about half the columns filled with data and with their corresponding types. And about half did not fill, with VariantType=”0”, which means it’s empty.

<Columns>
<Column VariantType="3">147</Column>
<Column VariantType="3">1</Column>
<Column VariantType="8">{A6B2A91A-BA8E-6D80-06BD-3F12BF28652D}</Column>
<Column VariantType="3">64</Column>
<Column VariantType="3">147</Column>
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="3">64</Column>
<Column VariantType="8">{EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD}</Column>
<Column VariantType="3">8</Column>
<Column VariantType="8">Microsoft.Windows.Computer</Column>
<Column VariantType="8">Windows Computer</Column>
<Column VariantType="0" />
<Column VariantType="3">273</Column>
<Column VariantType="3">147</Column>
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="0" />
<Column VariantType="1" />
<Column VariantType="0" />
<Column VariantType="0" />
</Columns>

I thought there might be some sort of pattern to this, but I haven’t discovered it. I looked at the column properties in the database, and did not find anything resembling a consistent pattern that would indicate any reason why some columns are filled during execution and some are not. I gave up on trying to answer the question of “why” some columns refuse to fill, and found the solution by casting in the sql SELECT statement.

The Solution

The problem was solved by casting the columns that did not fill in the SELECT statement.

Here is the first query that returned empty columns.

SELECT me.Name
FROM vManagedEntity AS me
inner join vManagedEntityType AS met ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
inner join vManagedEntityProperty AS mep ON mep.ManagedEntityRowId = me.ManagedEntityRowId
WHERE met.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' AND
mep.ToDateTime IS NULL

And here is the modified query with CAST in the SELECT statement.

SELECT CAST (me.Name as NVARCHAR)
FROM vManagedEntity AS me
inner join vManagedEntityType AS met ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
inner join vManagedEntityProperty AS mep ON mep.ManagedEntityRowId = me.ManagedEntityRowId
WHERE met.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' AND
mep.ToDateTime IS NULL

That’s it folks. Hope this helps if you find yourself in the same bind as me.

Grey Agents With Reason (gray agents)

A few years ago I wrote some TSQL to return all grey agents with the reason code. This worked fine in SCOM 2007, but it doesn’t work in 2012 environments for some reason. I basically just modified the WHERE clause, removing a bunch of SELECT statements – I’m not sure why I added those additional SELECT statements, but there must have been a reason.

I am reposting the TSQL here, updated for SCOM 2012. There was also a small bug fixed with the outage days column –  my initial query did not use UTC time in the DATEDIFF calculation, which would cause a negative value for newly grey agents and was off n hours depending on your local time zone.

/*
Gray agents with reason
Jonathan Almquist (jonathan@scomskills.com)
Updated 02-24-2014
*/
USE OperationsManagerDW
SELECT
    ME.Path,
    HSO.StartDateTime AS OutageStartDateTime,
    DATEDIFF (DD, HSO.StartDateTime, GETUTCDATE()) AS OutageDays,
    HSO.ReasonCode,
    DS.Name AS ReasonString
FROM  vManagedEntity AS ME INNER JOIN
    vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = ME.ManagedEntityRowId INNER JOIN
    vStringResource AS SR ON HSO.ReasonCode = 
    REPLACE(LEFT(SR.StringResourceSystemName, LEN(SR.StringResourceSystemName)
    - CHARINDEX('.', REVERSE(SR.StringResourceSystemName))), 
    'System.Availability.StateData.Reasons.', '') INNER JOIN
    vDisplayString AS DS ON DS.ElementGuid = SR.StringResourceGuid
WHERE (HSO.EndDateTime IS NULL)
    AND (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%')
    AND DS.LanguageCode = 'ENU'
ORDER BY OutageStartDateTime
 
 
 
🙂

 

Agent Management–List Primary and Failover Configuration

Something I don’t like about using the SDK (powershell) to manage agents, are the get* member cmdlet’s to return information – large scale queries take too long! The SDK is typically pretty slow in this regard, and that’s a shame because I find myself writing TSQL to accomplish tasks that the SDK should be able to promptly handle.

Recently I wrote some TSQL that will return all agents with their associated primary and failover management servers. This is very informative when the question "where does this agent failover to", and it’s a speedy way to implement some sort of automation process to expedite agent assignment.

 

Here you go!

 

SELECT rgv.SourceObjectPath AS [Agent], rgv.TargetObjectPath AS [ManagementServer], 
       CASE
              WHEN rtv.DisplayName = 'Health Service Communication' THEN 'Primary'
              ELSE 'Failover'
       END AS [Type]
FROM ManagedTypeView mt INNER JOIN
       ManagedEntityGenericView AS meg ON meg.MonitoringClassId = mt.Id INNER JOIN
       RelationshipGenericView rgv ON rgv.SourceObjectId = meg.Id INNER JOIN
       RelationshipTypeView rtv ON rtv.Id = rgv.RelationshipId
WHERE mt.Name = 'Microsoft.SystemCenter.Agent' AND
       rtv.Name like 'Microsoft.SystemCenter.HealthService%Communication' AND
       rgv.IsDeleted = 0
ORDER BY rgv.SourceObjectPath ASC, rtv.DisplayName ASC

 

Something like this would take several minutes in small to medium sized environments, and maybe upwards of 15-30 minutes in larger environments. This little bit of TSQL returns in 1-2 seconds. Eat that!

 

🙂

Get Recursive Group Membership

Sometimes it is necessary to not only return a list of objects contained in a group, but also return all or specific objects nested in a group at different levels. For this we can leverage a built-in store procedure that is in your Operations Manager Data Warehouse. This procedure is used under the hood for the report group object picker. I’m simply leveraging it in this example to demonstrate how to get current group membership.

This is a good example group, since it contains several nested group each containing different types of objects.

There are three parameter you can change in this example:

GroupDisplayName = name of group you want to check

LevelCount = how far to look into a nested group scenario (this can also be used to find all hosted objects of group members)

StartLevel = at which level do you want to begin returning group/object membership (e.g.; do not return membership of root group, but start at second level)

This should be executed on your Operations Manager Data Warehouse, not the operational database.

 

 

DECLARE @GroupDisplayName as varchar(max) = 'All Operations Manager Objects Group',
        @LevelCount as int = 

1

,
        @StartLevel as int = 

1

,
        @ObjectListDate as datetime = getutcdate(),
        @ObjectList as xml

SET @ObjectList = '<Data><Objects><Object Use="Containment">' + 
    convert(varchar,(
        SELECT ManagedEntityRowId 
        FROM vManagedEntity 
        WHERE displayName = @GroupDisplayName)) + 
        '</Object></Objects></Data>'

CREATE TABLE #ObjectList (ManagedEntityRowId int)
INSERT INTO #ObjectList (ManagedEntityRowId)
EXEC [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
    @ObjectList = @ObjectList,
    @StartDate = @ObjectListDate,
    @EndDate = @ObjectListDate,
    @ContainmentLevelCount = @LevelCount,
    @ContainmentStartLevel = @StartLevel

SELECT ISNULL(vme.Path,vme.DisplayName) AS DisplayName, ManagedEntityDefaultName
FROM vManagedEntity as vme inner join
    #ObjectList on #ObjectList.ManagedEntityRowId = vme.ManagedEntityRowId

DROP TABLE #ObjectList

 

 

 

If you wanted to return a history of group membership, you could replace StartDate and EndDate parameters with your desired range.

 

 

 

_