Here is another post about a flavour of a Server Health report.
The report shows top 10 Memory and CPU utilization of the estate monitored by SCOM.
It also shows the health of the servers, up time, maintenance mode at the time the report was executed.
This report will work nicely as a “Start of Day” report showing the health/performance etc of the monitored estate.
If you need more details leave a comment on this post
Enabling Maintenance Mode for a group of servers late on a Friday afternoon of urgently because the admins forgot to let the SCOM admins know has always been a little bit of a problem. In step the SCOM SDK, Exchange EWS 2.0 and some very awesome Vb.net coding skills.
The challenge of doing something like this is the control ie: who are allowed to send emails to request maintenance mode, what if the group does not exist in SCOM etc etc.
The utility makes use of an encrypted file to store email addresses (white-list) and also the groups that each email address are allow to put into maintenance mode.
The configuration file also contains the following
1. Email address for the mailbox to receive the emails for maintenance mode
2. User Account and password (encrypted) to access the mailbox
3. SCOM management server, user id and password to connect to the SCOM server
All the information in the configuration file is encrypted and unreadable.
The admin will send an email to the maibox in the following format for starting of maintenance mode
SCOMGroup: (Groupname)
StartTime: (Startime of maintenance mode)
Duration: (Duration in minutes)
Comment: (Comments to include in the maintenance mode details)
Action: (Start for starting of maintenance mode)
Other values for the Action includes: Stop, Extend and Query
Email that will be received on a “Query” action looks like below
Group is already in maintenance mode, see details below, maybe use ‘Action: Extend’ to extend the current maintenance mode
Group: <Group Name> Current Maintenance mode details
| Comments | Start Time | Scheduled End Time | End Time | Reason |
| Maintenance Mode requested by : Admin.EV on 12/1/2016 10:32:40 AM Comment provided: Testing the utility | 12/1/2016 10:31:00 AM | 12/1/2016 11:01:00 AM | UnplannedApplicationMaintenance |
Confirmation emails are also sent confirming maintenance mode stopped, started and extended.
Example of the confirmation email below:
The Maintenance mode info for the SCOM objects are also updated with the details, when right clicking on any object in the particular group in the SCOM console and viewing the maintenance mode details, a screen similar to below appear.
The utility itself will be executed as a scheduled task on a server. The Interval can be set to any value. The utility will process all the emails with a subject of “SCOM Maintenance Mode” and action maintenance per group in the email according to the action etc.
Currently it’s one email for one group, no multiple groups per single email will work.
If any of the values required is not contained in the message body an “invalid request” email will be sent back with clear instructions on what is required.
Please leave a comment here if you are interested in receiving a copy of this utility.
The below powershell script extracts the number of samples of a specific performance object and counter in the last 24 hours.
This information can be used to check if the performance data is being collected and can also be input into a run-book in SCORCH to further troubleshoot the counts that comes out to 0.
For this script the objectname for the performance data is ‘Cluster Shared Volume’ and the counter is ‘Free space / Percent’
$managementServer=”management server name here”
import-module OperationsManager
try {$mg = New-Object Microsoft.EnterpriseManagement.ManagementGroup($managementServer)}
catch {
“Could not connect to management server $managementServer”
exit(0)
}
$starttime = [datetime]::today.addhours(-24)
$endtime = [datetime]::today.adddays(1)
$mpdc = “ObjectName = ‘Cluster Shared Volume’ and CounterName = ‘Free space / Percent'”
$mpds = $mg.GetMonitoringPerformanceDataReader($mpdc)
while ($mpds.read())
{
$perfdata = $mpds.getmonitoringperformancedata()
#write-host $perfdata.count
$valuereader = $perfdata.getvalues($starttime, $endtime)
$displayname = $perfdata.monitoringobjectdisplayname.tostring()
$path = $perfdata.monitoringobjectpath.tostring()
$count = $valuereader.count.tostring()
write-host “$path, $displayname, $count”
# write-host $perfdata.monitoringobjectpath
}
Capacity and Trending reports are one of the shortcomings of the SCOM product in my opinion. I’ve created a report that does forecasting using Linear Regression.
Example of the output of the report
Linear Regression is a mathematical forumula that works out the y value by using the historical values and a constant
More information on the formula can be found here: https://en.wikipedia.org/wiki/Simple_linear_regression
Parameter Block used by the report
For the performance objects, counter and instances I’ve populate only the Processor, Memory and Disk Counters. You obviously can add more if you choose to.
Below is the SQL statement used in the report
declare @startdate datetime
Declare @enddate datetime
declare @forecasting int
set @startdate = @FromDate
set @enddate = @Todate
set @forecasting = @forecastingdays
if object_id(‘tempdb..#alldiskdata’) IS NOT NULL
DROP TABLE #alldiskdata
create table #alldiskdata
(idt int identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float)
insert into #alldiskdata
SELECT distinct x.path, dateadd(hour, 2, a.DateTime) as datetime, y.instancename, z.objectname, z.countername,a.averagevalue
FROM Perf.vperfdaily AS a with (nolock) INNER JOIN
vManagedEntity AS x with (nolock) ON a.ManagedEntityRowId = x.ManagedEntityRowId INNER JOIN
vPerformanceRuleInstance AS y with (nolock) ON a.PerformanceRuleInstanceRowId = y.PerformanceRuleInstanceRowId INNER JOIN
vPerformanceRule AS z with (nolock) ON y.RuleRowId = z.RuleRowId
wHERE dateadd(hour, 2, a.DateTime) >= @startdate
and z.ObjectName in (@Object) AND z.CounterName in (@Counter) and x.path in (@Servers)
and y.instancename in (@Instance)
order by x.path, z.objectname, z.countername, y.instancename, dateadd(hour, 2, a.DateTime)
if object_id(‘tempdb..#servernames’) IS NOT NULL
DROP TABLE #servernames
create table #servernames
(idt int identity(1,1),
servername varchar(max),
objectname varchar(max),
countername varchar(max),
instancename varchar(max)
)
insert into #servernames
select distinct servername, perf_object, perf_counter, instancename from #alldiskdata
–select * from #servernames
DECLARE @MaxRownum int
SET @MaxRownum = (SELECT MAX(idt) FROM #servernames)
DECLARE @Iter int
SET @Iter = (SELECT MIN(idt) FROM #servernames)
–select @maxrownum, @iter
if object_id(‘tempdb..#serverdiskdatatrend’) IS NOT NULL
DROP TABLE #serverdiskdatatrend
create table #serverdiskdatatrend
(idt bigint identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float,
forecastvalue float)
WHILE @Iter <= @MaxRownum
BEGIN
if object_id(‘tempdb..#serverdiskdata’) IS NOT NULL
DROP TABLE #serverdiskdata
create table #serverdiskdata
(idt int identity(1,1),
servername varchar(max),
sampledate datetime,
instancename varchar(max),
perf_object varchar(max),
perf_counter varchar(max),
samplevalue float)
declare @servername varchar(max)
select @servername = servername from #servernames where idt=@iter
declare @objectname varchar(max)
select @objectname = objectname from #servernames where idt=@iter
declare @countername varchar(max)
select @countername = countername from #servernames where idt=@iter
declare @instancename varchar(max)
select @instancename = instancename from #servernames where idt=@iter
insert into #serverdiskdata
select servername, sampledate, instancename, perf_object, perf_counter, samplevalue from #alldiskdata where servername=@servername and perf_object=@objectname
and perf_counter=@countername and instancename=@instancename
declare @n bigint
select @n=count(*) from #serverdiskdata
declare @m float
declare @b float
declare @y float
declare @xx float
–select @n
–select @n
–@m=slope
select @m =
((@n * sum(idt*samplevalue)) – (sum(idt)*sum(samplevalue)))/
((@n * sum(Power(idt,2)))-Power(Sum(idt),2)),
–@y is intercept
@y =
avg(samplevalue) -Avg(idt) *
((@n * Sum(idt*samplevalue)) – (Sum(idt) * Sum(samplevalue)))/
((@n * Sum(idt*idt)) – (Sum(idt) * Sum(idt))),
@xx=
avg(samplevalue) – ((@n * sum(idt*samplevalue)) – (sum(idt)*sum(samplevalue)))/((@n * sum(idt^2))-sum(idt)^2)* avg(idt)
from #serverdiskdata
declare @x int
declare @forestartdate datetime
declare @forestartvalue float
declare @forestartservername nvarchar(max)
declare @forestartinstancename nvarchar(max)
declare @foreobject nvarchar(max)
declare @forecounter nvarchar(max)
select @x = min(idt) from #serverdiskdata
while @x <= @n
begin
insert into #serverdiskdatatrend select servername, sampledate,instancename,perf_object, perf_counter, samplevalue,@m*@x+@y as yvalue from #serverdiskdata where idt=@x
select @forestartdate = sampledate from #serverdiskdata where idt=@x
select @forestartvalue = samplevalue from #serverdiskdata where idt=@x
select @forestartservername = servername from #serverdiskdata where idt=@x
select @forestartinstancename = instancename from #serverdiskdata where idt=@x
select @foreobject = perf_object from #serverdiskdata where idt=@x
select @forecounter = perf_counter from #serverdiskdata where idt=@x
set @x = @x+1
end
select @x = max(idt) from #serverdiskdata
declare @forecast int
set @forecast = @forecasting+@x
while @x <= @forecast
begin
set @forestartdate = dateadd(“day”, 1, @forestartdate)
insert into #serverdiskdatatrend values (@forestartservername, @forestartdate, @forestartinstancename, @foreobject, @forecounter, 0,@m*@x+@y)
set @x = @x+1
end
set @iter = @iter+1
end
select * from #serverdiskdatatrend
In part I’ve created almost all the “dashboards” using SSRS, these dashboards did not work so well, they experience timeouts, the placement of the objects was difficult etc etc.
In the last couple of weeks I taken one of the big dashboards that I’ve created and redid everything using HTML 5 and all the goodies with it.
End result:
This dashboard shows the Green/Amber/Red (RAG) of the SCOM distributed applications. I’ve decided to use the SCOM DA objects as it’s easier to change the DA’s than changing and performing health rollup of doing it purely in code.
I’m making use of the SCOM SDK in .Net to get the states. This webpage/dashboard does not have a single SQL Select statement. Everything is done with the SCOM SDK and the objects/classes.
Below is a snippet of .net code that gets the DA health objects.
There is 2 drill down options for each Distributed Application
1. View the alerts
2. View the actual Distributed Application
Viewing Alerts (“Launches Alerts”)
The alert view is a HTML 5 table showing the user the server name, date/time of the alert and the actual alert details. Information is sorted in descending order according to the date/time.
This alert view is opened in a separate tab in the browser.
Code snippet to get the alerts
As you can see, I’m actually creating a piece of HTML code for each alert and also have a counter called “Exchange2010alerts” all this is used on the DA health block for the showing the number of alerts and also the alert details in the drill-down.
Viewing the Distributed Application (“More Info”).
Nothing special here, simply showing the Distributed Application in the web console.
The SCOM web console also launches in a separate tab in the browser.
Leave a comment on this article if you require more information. Comments does not show automatically.
BlueStripe offers solutions and add-ons for customers using SCOM for application discovery and visualization.
Would be interesting to see what of the functionality will be included in SCOM 2016 slated for release early in 2016.
I’ve manually added in a SQL 2012 server into my MSOMS service.
The assessment data was not uploaded because of permissions problems with the NT AuthoritySystem Account not having access into the SQL databases to run the assessment queries.
The log file that was used in troubleshooting is called “SironaLog_Advisor_20150527_103752” and it’s located in the These file name and locations might change with later versions.
I’ve giving this NT AuthoringSystem account read access on the different DBs as this is a lab environment, no harm no foul.
In a production environment with the SCOM management group connected you will make use of the run-as account for the SQL assessment collection
I’ve had to set the date on the SQL server forward by 7 days so that the assessment and collection will occur again.
Details when clicking on the above tile
Clicking on the “Availability and Business Continuity” above shows more in-depth information.
For each of the findings there is a Prioritization and also a Recommendation section with affected objects, context and actions on how to fix each finding.
