Reporting on SCOM Data: Piecing the data together (Part 1)
In my last post, Reporting on SCOM Data: Single Pane of Glass, I provided an overview of what we provided our customer that enabled them to view the Health State of their entire enterprise in one location. In this post, I'm going to begin covering how we pulled together the data to present those views. Special shout out to Jimmy Harper for help with the SCOM queries. Jimmy is the best SCOM guy that I know, so if you're into SCOM then you might want to check out his blog.
Our customer wanted to see data aggregated from the host/server level up to the Region and Office level. They had a predetermined list of Regions and Offices that we loaded into some related tables and were able to generate our PK's for each of those entities. Those tables are fairly simple in nature:
CREATE TABLE Office (
[OfficeId] [int] IDENTITY(1,1) NOT NULL,
[OfficeName] [nvarchar](max) NULL,
[TimeAdded] [datetime] NOT NULL,
[Latitude] [numeric](18, 6) NULL,
[Longitude] [numeric](18, 6) NULL,
)
CREATE TABLE Region (
[RegionId] [int] IDENTITY(1,1) NOT NULL,
[RegionName] [nvarchar](max) NULL,
)
CREATE TABLE RegionOffice (
[RegionOfficeId] [int] IDENTITY(1,1) NOT NULL,
[RegionId] [int] NOT NULL,
[OfficeId] [int] NOT NULL,
)
In addition to the Region and Office tables that we want to roll up with, we need to define the Host table and its load table.
CREATE TABLE Host (
[HostId] [int] IDENTITY(1,1) NOT NULL,
[OfficeId] [int] NOT NULL,
[DnsHostName] [nvarchar](255) NULL,
[OU] [nvarchar](max) NULL,
[ADSiteName] [nvarchar](128) NULL,
[TimeAdded] [datetime] DEFAULT GETUTCDATE(),
[EntityHealth] [tinyint] NULL,
[AvailabilityHealth] [tinyint] NULL,
[ConfigurationHealth] [tinyint] NULL,
[PerformanceHealth] [tinyint] NULL,
[SecurityHealth] [tinyint] NULL,
[WarningOpenAlertCount] [int] NULL,
[CriticalOpenAlertCount] [int] NULL,
[BaseManagedEntityId] [uniqueidentifier] NULL,
[ManagementGroup] [nvarchar](128) NULL,
[DWManagedEntityRowId] [int] NULL
)
CREATE TABLE Host_Load (
[HostLoadId] [int] IDENTITY(1,1) NOT NULL,
[OfficeId] [int] NULL,
[DnsHostName] [nvarchar](255) NULL,
[OU] [nvarchar](max) NULL,
[ADSiteName] [nvarchar](128) NULL,
[BaseManagedEntityId] [uniqueidentifier] NULL,
[ManagementGroup] [nvarchar](128) NULL,
[DWManagedEntityRowId] [int] NULL
)
With the custom hierarchy tables created (and loaded where necessary), we need the list of hosts from SCOM. In this case, we queried host information out of the SCOM DW db by selecting from the vManagedEntity view and joining it with vManagedEntityType, vManagementGroup and vManagedEntityManagementGroup views. In addition, we performed subqueries against vManagedEntityPropertySet and vManagedEntityTypeProperty views to return specific host details like ADSiteName, OU, DnsHostName, etc. It's important to note that our customer has multiple SCOM Management Groups so we joined with the aforementioned ManagementGroup views to pull back Management Group details (namely ManagementGroupDefaultName and ToDateTime). Here is a snippet of that main query:
SELECT
me.ManagedEntityRowId,
me.TopLevelHostManagedEntityRowId,
BaseManagedEntityId = me.ManagedEntityGuid,
ServerName = me.Name,
ManagementGroup = mg.ManagementGroupDefaultName,
ADSiteName =
(SELECT TOP 1
ADSiteName = ps_ADSite.PropertyValue
FROM
vManagedEntity me2 WITH (NOLOCK)
INNER JOIN
vManagedEntityPropertySet ps_ADSite WITH (NOLOCK)
ON ps_ADSite.ManagedEntityRowId = me2.ManagedEntityRowId
INNER JOIN
vManagedEntityTypeProperty tp_ADSite WITH (NOLOCK)
ON tp_ADSite.PropertyGuid = ps_ADSite.PropertyGuid
AND tp_ADSite.PropertySystemName = 'ActiveDirectorySite'
WHERE
me2.ManagedEntityRowId = me.ManagedEntityRowId
AND ps_ADSite.ToDateTime IS NULL),
DomainDNSName =
(SELECT TOP 1
DomainDNSName = ps_DomainDNSName.PropertyValue
FROM
vManagedEntity me2 WITH (NOLOCK)
INNER JOIN
vManagedEntityPropertySet ps_DomainDNSName WITH (NOLOCK)
ON ps_DomainDNSName.ManagedEntityRowId = me2.ManagedEntityRowId
INNER JOIN
vManagedEntityTypeProperty tp_DomainDNSName WITH (NOLOCK)
ON tp_DomainDNSName.PropertyGuid = ps_DomainDNSName.PropertyGuid
AND tp_DomainDNSName.PropertySystemName = 'DomainDNSName'
WHERE
me2.ManagedEntityRowId = me.ManagedEntityRowId
AND ps_DomainDNSName.ToDateTime IS NULL),
OrganizationalUnit =
(SELECT TOP 1
OrganizationalUnit = ps_OrganizationalUnit.PropertyValue
FROM
vManagedEntity me2 WITH (NOLOCK)
INNER JOIN
vManagedEntityPropertySet ps_OrganizationalUnit WITH (NOLOCK)
ON ps_OrganizationalUnit.ManagedEntityRowId = me2.ManagedEntityRowId
INNER JOIN
vManagedEntityTypeProperty tp_OrganizationalUnit WITH (NOLOCK)
ON tp_OrganizationalUnit.PropertyGuid = ps_OrganizationalUnit.PropertyGuid
AND tp_OrganizationalUnit.PropertySystemName = 'OrganizationalUnit'
WHERE
me2.ManagedEntityRowId = me.ManagedEntityRowId
AND ps_OrganizationalUnit.ToDateTime IS NULL)
FROM
vManagedEntity me WITH (NOLOCK)
INNER JOIN
vManagedEntityType met WITH (NOLOCK)
ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
AND met.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
INNER JOIN
vManagementGroup mg WITH (NOLOCK)
ON mg.ManagementGroupRowId = me.ManagementGroupRowId
INNER JOIN
vManagedEntityManagementGroup memg WITH (NOLOCK)
ON memg.ManagedEntityRowId = me.ManagedEntityRowId
WHERE
memg.ToDateTime IS NULL
ORDER BY
me.Name
Using the above query in an SSIS package, we loaded the results into the Host_Load table. Using our customer's host naming convention, we were able to determine which Office each host is assigned to. There's some customer-specific formulas to make those determinations, so in the effort of privacy I'm leaving those details out. However, using those formulas, we were able to update the OfficeId field for each host in the load table. From there we used a MERGE statement to merge the records with the existing host table.
MERGE INTO
Host AS h
USING
Host_Load AS l
ON
h.DnsHostName = l.DnsHostName
WHEN NOT MATCHED BY TARGET THEN
INSERT (OfficeId, DnsHostName, OU, ADSiteName, TimeAdded, BaseManagedEntityId, ManagementGroup, DWManagedEntityRowId)
VALUES (l.OfficeId, l.DnsHostName, l.OrganizationalUnit, l.ADSiteName, GETUTCDATE(), l.BaseManagedEntityId, l.ManagementGroup, l.DWManagedEntityRowId))
WHEN MATCHED THEN
UPDATE SET
h.OfficeId = l.OfficeId,
h.DnsHostName = l.ServerName,
h.OU = l.OrganizationalUnit,
h.ADSiteName = l.ADSiteName,
h.BaseManagedEntityId = l.BaseManagedEntityId,
h.ManagementGroup = l.ManagementGroup,
h.DWManagedEntityRowId = l.DWManagedEntityRowId
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Using these tables and a regularly scheduled load, we've now got a foundation that will support rolling up host aggregates at the Region and Office level. In addition, we can now use these structures to navigate live data in the SCOM DW db in our customer's desired fashion.
Viel spass!
Joseph
UPDATED 5 June 2017 - Part 2 of this topic has been posted and is available here.