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.
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 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 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
