Server Health Report

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

SCOM Maintenance Mode via Email Message sent from anywhere

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.

 

Powershell script to pull out performance objects, counters and sample count for a specific counter collected by SCOM

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

}

Performance Counter Trending and Forecasting report

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

HTML 5 version of infrastructure monitoring by SCOM

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.

 

Microsoft acquires BlueStripe

BlueStripe offers solutions and add-ons for customers using SCOM for application discovery and visualization.

Annoucement:http://blogs.microsoft.com/blog/2015/06/10/microsoft-acquires-bluestripe-to-help-customers-improve-application-visibility-and-management-across-the-datacenter-and-cloud/

Would be interesting to see what of the functionality will be included in SCOM 2016 slated for release early in 2016.

&amp;#66;&amp;#41;

MS Operations Management Suite: SQL Assessment

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.

Connecting a Server into my Microsoft Operations Management Suite

With Microsoft Operations Management Suite (MOMS) you can add different sources of data to send data to OMS.

1. Manually installing the MMA agent on a server

2. Connecting your SCOM management group.

3. Azure Storage Account

For this post I will discuss option 1 above.

I’ve setup my lab environment with three Windows 2012 servers.

1. SQL 2012 Server

2. Windows 2012 File Server

3. SCVMM 2012 server

Details that is required during the MMA agent installation are

1. The workspace ID

2. Primary Key

This information is obtainable in your MOMS console

There is also a download link for the Windows Agent.

The next step I did was to copy the MMagent install msi across to the 3 servers and double-clicked on the install.

The files are then extracted and I was presented with the following screen

Click Next and then you get

The next screen is then where you specify wether you want to connect this MMA to a SCOM management group or to an Microsoft Azure Operational Insights service

Click on the first option (“Connect the agent to Microsoft Azure Operational Insights” and then click on next you get the following screen

The here you specify the ID and Key as it appears on your website.

For my environment and lab I had to specify the HTPP proxy URL, username and password, I click on the “Advanced” button and specified the details in the next screen.

Filling in all the details on this screen and click Next and Next on the main screen will cause the MMA agent installation to verify details with the MOMS site.

The log file for this installation is in %temp%monitoringagent.log

This log file will contain the installation steps and any issues that occurred during the installation.

When the installation was successful in it’s verification you will get the following screen.

Clicking on Next and then Install will install the MMA agent.

Successful installed

The agent will then communicate with the MOMS service and all management packs for the MOMS Solutions will then be pulled from the MOMS site and monitoring will commence.

Events in the OperationsManager Eventlog like the following will tell you that there is communication between the MOMS service and your MMA agent installed on your server

In the next post I will chat more about the different MOMS solutions and also connect my SCOM management group in my Lab with the MOMS services for alert management etc.

GrapeVine SMS and SCOM

GrapeVine has several methods for message delivery to be sent as a SMS.

1. Email to grapevine mailbox from there SMS messages is sent

2. Post

3. XML

The below powershell script is one example of consuming the HTTP Post method.

The powershell script is configured in SCOM as a Command Line notification with the SCOM alert description and CellPhoneNumbers passed to the script as parameters.

The script takes the comma-seperated list of CellPhonenumbers and one for one send the information to GrapeVine web url.

You will need the following information from GrapeVine for your clients’ account

1. Originator ID

2. AffiliateCode

3. AuthenticationCode.

These 3 items are required and only obtainable from Grapevine, you need to get in touch with the GrapeVine SP to get this information.

The script also consumes the return code information from the post attempt and saves these returned information into a text file for future reference.

The script also when the # lines are removed to create actual event log entries into the Application event log. You can use these event logging capabilities and monitor for these events either by using SCOM or some Monitor of Monitors.

Param($Message=$Args[1], $cellnumbers=$Args[2])

$aaa = $cellnumbers.split(“,”)

write-host $cellnumbers

foreach ($cellnumber in $aaa)

{

write-host $Message

write-host $cellnumber

$http_request = New-Object -ComObject Msxml2.XMLHTTP

$http_request.open(“POST”,”http://grapevineurlreceivedfromGrapeVine/httpInputhandler/ApplinkUpload”,$false)

$http_request.setRequestHeader(“Content-type”,”text/xml”)

$http_request.send(“origidaffcodeauthocode2015-03-11T12:40:00text$Message$cellnumber”)

write-host $http_request.responseText

$ResultOutPut = $http_request.responseText

$DateNow = Get-Date

add-content d:\grapevinesms\gvsmslog.txt “$DateNow :: $Message – $cellnumber returned the following response – $ResultOutPut”

add-content d:\grapevinesms\gvsmslog.txt “

Reading SQL Database and Table and create SCOM Alerts

Reading data from a SQL database table and based on the returned recordset one can easily created SCOM performance counters or SCOM events.

The below VB-Script does just that. It reads a SQL table and for each record it builds up the event message with VBCRLF (Carriage Return Line Feed aka Enter key J).

Dim oAPI

Set oAPI = CreateObject(“MOM.ScriptAPI”)

Set conn = CreateObject(“ADODB.Connection”)’

conn.Open “Provider=SQLOLEDB;Data Source=SQLSERVER;Initial Catalog=Database;Persist Security Info=False;Integrated Security=SSPI”

‘Call oAPI.LogScriptEvent(“ReadSQLTable”, 100, 2, “running”)

conn.commandtimeout = 500

set recset = createobject(“adodb.recordset”)

sql = “SQLSelectStatement”

recset.open sql, conn

Call oAPI.LogScriptEvent(“readsqltable.vbs”, 100, 2, “connected and running”)

recfound = 0

while not recset.eof

eventmsg = eventmsg & vbcrlf & recset.fields(0).value & ” ” & recset.fields(1).value & ” ” & recset.fields(2) & ” ” & recset.fields(3) & ” ” & recset.fields(4)

recset.movenext

recfound = recfound +1

Set oBagAgentVer = oAPI.CreateTypedPropertyBag(1)

call oBagAgentVer.AddValue(“EventID”,912)

call oBagAgentVer.AddValue(“EventSource” ,”ReadSQLTable“)

call oBagAgentVer.AddValue(“EventMessage”,eventmsg)

oAPI.AddItem(oBagAgentVer)

Call oAPI.ReturnItems

Call oAPI.LogScriptEvent(“readsqltable”, 101, 2, eventmsg)

recset.movenext

wend

recset.close

The above script is then put into a Time Command > Execute a Script SCOM rule

For this rule and the access to the particular database I needed to specify a Run-As account

This runas account has a domain user specified with read access to the particular SQL table.

The Scheduler is set to every 1 minute

Because the above script is creating a SCOM event, you then need to configure an Alert Generating Event rule that monitors for the event source and Event Id as specified in the script.

In this case the eventsource is ReadSQLTable and the eventid is 912.

Below is a sample script for reading records out of an Access Database and creating SCOM alerts for the returned recordset

The bold italics parts of the script is what you must substitute the relevant values for your environment.

Dim oAPI

Set oAPI = CreateObject(“MOM.ScriptAPI”)

Set conn = CreateObject(“ADODB.Connection”)

‘Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, “Run script”)

sconn = “Driver={Microsoft Access Driver (*.mdb)}; DBQ=DirectoryandAccessMBDfilelocation;”

conn.Open sconn

set recset = createobject(“adodb.recordset”)

sql = “AccessSQLSelectQuery

set rs = conn.execute(sql)

‘Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, rs.recordcount)

evt_msg = “”

while not rs.eof

sss = RS(“Message“)

Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, sss)

msg = “‘” & sss & “‘”

evt_msg = “Date of error: ” & RS(“fielname”) & ” ” & RS(“time”) & vbcrlf & ” Type: ” & RS(“type”) & vbcrlf & ” Message: ” & msg & vbcrlf

‘ Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, evt_msg)

Set oBagAgentVer = oAPI.CreateTypedPropertyBag(1)

call oBagAgentVer.AddValue(“EventID”,99101)

call oBagAgentVer.AddValue(“EventSource” ,”EventSource“)

call oBagAgentVer.AddValue(“EventMessage”,evt_msg)

oAPI.AddItem(oBagAgentVer)

rs.movenext

wend

Call oAPI.ReturnItems