Get a list of SCOM Management Servers and Gateway Servers from SQL

I recently received a question asking about getting some of the information from a table in my health check script directly from SQL. So in response to that, here’s some SQL code that will pull out the Management Servers and the Gateways from the SCOM Database, along with some performance collection data about each server.

/*
Get a list of SCOM Management Servers and Gateway Servers

– Server Name
– Operating System Version
– Agent Version
– List if it’s a Management Server or Gateway Server
– Added in some other info…
– Workflows (avg over 24 hours)
– CPU (avg over 24 hours)
– Disk Transfer (avg over 24 hours)
– Memory (avg over 24 hours)

*/

DECLARE @ComputerName as nvarchar(50)
DECLARE @WorkFlow as int
DECLARE @CPU as float
DECLARE @Memory as float
DECLARE @DiskTransfer as float
DECLARE @ManagementServers TABLE
([Server Name] nvarchar(50),
[Operating System] nvarchar(100),
[Agent Version] nvarchar(50),
[Management Server] nvarchar(3),
[Gateway] nvarchar(3),
[Workflow] int,
CPU float,
Memory float,
DiskTransfer float

)

Use [OperationsManager]

INSERT INTO @ManagementServers ([Server Name], [Operating System], [Agent Version], [Management Server], [Gateway])
(
SELECT
MTV_Microsoft$Windows$OperatingSystem.PrincipalName AS [Server Name],
MTV_Microsoft$Windows$OperatingSystem.DisplayName AS [Operating System],
CS.Agent.AgentVersion AS [Agent Version],

— Find out the Management Servers from the Gateway Servers
CASE
— Match on if the ManagementServerInd is 1 and the GatewayInd is 0. This will define a Gateway Server
WHEN CS.Agent.ManagementServerInd = 1 AND CS.Agent.GatewayInd = 0 THEN ‘Yes’
— Match on if the ManagementServerInd is 1 and the GatewayInd is 1. This will define a Management Server
WHEN CS.Agent.ManagementServerInd = 1 AND CS.Agent.GatewayInd = 1 THEN ‘No’
END AS [Management Server],

Case CS.Agent.GatewayInd
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS [Gateway]

FROM BaseManagedEntity INNER JOIN
MTV_Microsoft$Windows$OperatingSystem ON BaseManagedEntity.BaseManagedEntityId = MTV_Microsoft$Windows$OperatingSystem.BaseManagedEntityId INNER JOIN
CS.Agent ON BaseManagedEntity.Path = CS.Agent.NetworkName
WHERE CS.Agent.ManagementServerInd = 1
)

DECLARE WComputer CURSOR FOR
SELECT DISTINCT [Server Name] FROM @ManagementServers
OPEN WComputer FETCH NEXT FROM WComputer INTO @ComputerName
WHILE @@FETCH_STATUS = 0
BEGIN

Use [OperationsManagerDW]

— Grab the last 24 results of the Workflow Count Performance Counter from the Hourly Table.
SELECT @Workflow = avg(AverageValue) from (
SELECT top 24 Perf.vPerfHourly.DateTime, Perf.vPerfHourly.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfHourly ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = ‘Workflow Count’) AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfHourly.DateTime Desc
) tmp
group by Path

— Grab the last 24 results of the % Processor Time Performance Counter from the Hourly Table.
SELECT @CPU = avg(AverageValue) from (
SELECT Top 24 Perf.vPerfHourly.DateTime, Perf.vPerfHourly.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfHourly ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = ‘% Processor Time’) AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfHourly.DateTime Desc
) tmp
group by Path

— Grab the last 24 results of the PercentMemoryUsed Performance Counter from the Hourly Table.
SELECT @Memory = avg(AverageValue) from (
SELECT Top 24 Perf.vPerfHourly.DateTime, Perf.vPerfHourly.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfHourly ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = ‘PercentMemoryUsed’) AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfHourly.DateTime Desc
) tmp
group by Path

— Grab the last 24 results of the Avg. Disk sec/Transfer Performance Counter from the Hourly Table.
SELECT @DiskTransfer = avg(AverageValue) from (
SELECT Top 24 Perf.vPerfHourly.DateTime, Perf.vPerfHourly.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfHourly ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = ‘Avg. Disk sec/Transfer’) AND(vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfHourly.DateTime Desc
) tmp
group by Path

— Update the Management Servers Table with the additional columns from the Operations Manager DW.
— Show workflows as whole number. Round CPU and Memory to 2 decimal places. Round Disk to 5 decimal places.

UPDATE @ManagementServers SET
[Workflow] = @Workflow, CPU = round(@CPU,2), Memory = round(@Memory,2), DiskTransfer = ROUND(@DiskTransfer,5)
WHERE [Server Name] = @ComputerName

FETCH NEXT FROM WComputer INTO @ComputerName
END
CLOSE WComputer;
DEALLOCATE WComputer;

SELECT * from @ManagementServers
ORDER BY [Management Server] DESC, [Server Name]

 

 

 

 
Comments

No comments yet.

Leave a Reply