New SQL management pack

SQL Server Blog – New SQL Management pack released!

 

The blog posting the pack release fell through the cracks.  Released on 10 July, I’ve had some issues getting the updated MSI’s, but they’re live now.   I normally use the SQL Tech Community SQL releases site https://techcommunity.microsoft.com/t5/sql-server-blog/bg-p/SQLServer/label-name/SQLReleases

 

Don’t forget to look for SQL Security updates, (CU) Cumulative Updates, or (SP) Service Pack updates at the SQL releases link!  https://techcommunity.microsoft.com/t5/sql-server-blog/bg-p/SQLServer/label-name/SQLReleases

 

 

New SQL pack released

Microsoft System Center Management Pack for SQL Server enables the discovery and monitoring of SQL Server 2012, 2014, 2016, 2017, 2019, 2022, and upcoming versions.

Download link https://www.microsoft.com/en-us/download/details.aspx/?id=56203

Version:
7.6.5

File Name:
SQLServerMP.Windows.msi

SQLServerMP.CustomMonitoring.msi

SQLServerMP.Linux.msi

SQLServerMPWorkflowList.pdf <missing as of today>

Date Published:
7/10/2024

Functionality https://learn.microsoft.com/en-us/system-center/scom/sql-server-management-pack-changes-history?view=sc-om-2022

https://www.microsoft.com/en-us/download/details.aspx/?id=56203

June 2024 – 7.5.19.0 CTP

What’s New

  • Added new “Table Clustered Index Fragmentation” monitor that targets databases and checks for high fragmentation of clustered indexes
  • Added new “Property Bag” step in the custom monitor setup to extend the alert context with a property from the query result
  • Updated the “Product Version Compliance” monitor with the most recent version of public updates for the SQL Server
  • Reworked the “Long Running Queries” alert rule to improve security
  • Improved accessibility for the Summary Dashboard view and Monitoring Wizard template, including the following major changes:
    • implemented Keyboard Navigation using the A and D buttons on the tiles in the dashboard
    • added the ability for the screen reader to announce buttons and errors in the SQL Server wizard
    • redesigned dashboard list controls for greater accessibility

 

Pretty simple steps

Download and save to your SCOM server, or SCOM console connected machine

Navigate to the Administration tab

Expand Management Packs

Click on Installed Management packs

Click the Add drop-down, select the packs

Verify selections, and click Install button

Importing new SQL v7.6.5.0 packs into the SCOM Console

Click Close after import

v7.6.5.0 management packs are imported into the SCOM console

 

Enjoy!

 

Need to find the command UNIX pack runs for perf counter

Magnifying Glass

 

 

Have you ever needed to find the command UNIX pack runs for perf counter?   Say the processor time value doesn’t match what the Unix admin may be saying SCOM is showing.

 

Many times you can look at the SCOM management pack, and those commands trace back to the UNIX library.

 

Background:  The SCOM management server runs many of the cross-plat/xplat workflows to the UNIX agent through WinRM.

 

Agenda
  1. Unseal SCOM UNIX management pack to obtain URI
  2. Understand command line options from UNIX/Linux side, and how to view the output
  3. Enumerate command line
  4. Test Command line from SCOM MS

 

 

 

Unseal SCOM UNIX management pack

The screenshot below is unsealing the Solaris10 pack to XML, and then viewing/searching to show the processor reference.

Solaris 10 processor rules

NOTE that’s a URI, not a script

 

 

How UNIX admin may supply processor output

Example – Unix admin typically uses vmstat or iostat.

 

The screenshot uses ‘vmstat 2 10‘ – a snapshot every 2 second intervals, 10 times

vmstat output

 

We can discuss the vmstat output, but it shows way more than just processor (ready queue, swap, user, system, and cpu %) to help figure out which operating system component is the problem.

 

 

Enumerate command line test

How do we test the command line syntax, to verify what SCOM pulls when running the rule?

For example, we need to make the URI actionable from the management pack.  What is needed to make a usable command?

 

Grab the URI from the pack

http://schemas.microsoft.com/wbem/wscim/1/cim-schema/2/SCX_ProcessorStatisticalInformation?__cimnamespace=root/scx

 

Because we know the URI, we now build out the syntax with WinRM

winrm enumerate http://schemas.microsoft.com/wbem/wscim/1/cim-schema/2/SCX_ProcessorStatisticalInformation?__cimnamespace=root/scx -auth:basic -remote:https://<servername>:1270 -username:<scomID, not necessarily root> -skipCACheck -skipCNCheck -skiprevocationcheck –encoding:utf-8

 

 

Test WinRM command from SCOM MS

For instance, we want to test the WinRM command from the MS to the UNIX server

winrm enumerate http://schemas.microsoft.com/wbem/wscim/1/cim-schema/2/SCX_ProcessorStatisticalInformation?__cimnamespace=root/scx -auth:basic -remote:https://ubuntu:1270 -username:scom -skipCACheck -skipCNCheck -skiprevocationcheck –encoding:utf-8

 

Example output

SCX_ProcessorStatisticalInformation
InstanceID = null
Caption = Processor information
Description = CPU usage statistics
ElementName = null
Name = 0
IsAggregate = FALSE
PercentIdleTime = 99
PercentUserTime = 0
PercentNiceTime = 0
PercentPrivilegedTime = 0
PercentInterruptTime = 0
PercentDPCTime = 0
PercentProcessorTime = 1
PercentIOWaitTime = 0

SCX_ProcessorStatisticalInformation
InstanceID = null
Caption = Processor information
Description = CPU usage statistics
ElementName = null
Name = _Total
IsAggregate = TRUE
PercentIdleTime = 99
PercentUserTime = 0
PercentNiceTime = 0
PercentPrivilegedTime = 0
PercentInterruptTime = 0
PercentDPCTime = 0
PercentProcessorTime = 1
PercentIOWaitTime = 0

 

Additional references for WinRM syntax and troubleshooting

Warren’s blog

Docs site

Use Unix MP’s for shell commands

 

Build FluentD conf file

Build trust one block at a time

Ready to build out a FluentD conf file?

 

Let’s build a FluentD conf file.  We can use the docs site for another example.  And now, let’s build a simple FluentD configuration file. Paste the XML code below, and save as <yourlogfile>.conf

Create custom log file to test

cd /etc/opt/microsoft/omsagent/scom/conf/omsagent.d/
# vi <yourlogfile>.conf

vi mylog.conf

# Example conf file

<source>
# Specifies input plugin. Tail is a fluentd input plugin – http://docs.fluentd.org/v0.12/articles/in_tail
type tail
# Specify the log file path. Supports wild cards.
path /var/log/mylog
# Recommended so that Fluentd will record the position it last read into this file.
pos_file /home/omsagent/fluent-logging/mylog.pos

# Used to correlate the directives.
tag scom.log.mylog

format /(?<message>.*)/
</source>

<filter scom.log.mylog>
type filter_scom_simple_match
regexp1 message 911
event_id1 911
</filter>

<match scom.log.mylog>
#Disable mutual Auth
enable_server_auth false

# Output plugin to use
type out_scom
log_level trace
num_threads 5

# Size of the buffer chunk. If the top chunk exceeds this limit or the time limit flush_interval, a new empty chunk is pushed to the top of the
queue and bottom chunk is written out.
buffer_chunk_limit 5m
flush_interval 15s
# Specifies the buffer plugin to use.
buffer_type file
# Specifies the file path for buffer. Fluentd must have write access to this directory.
buffer_path /var/opt/microsoft/omsagent/scom/state/out_scom_common*.buffer
# If queue length exceeds the specified limit, events are rejected.
buffer_queue_limit 10
# Control the buffer behavior when the queue becomes full: exception, block, drop_oldest_chunk
buffer_queue_full_action drop_oldest_chunk
# Number of times Fluentd will attempt to write the chunk if it fails.
retry_limit 10
# If the bottom chunk fails to be written out, it will remain in the queue and Fluentd will retry after waiting retry_wait seconds
retry_wait 30s
# The retry wait time doubles each time until max_retry_wait.
max_retry_wait 9m
</match>

Save (:wq!)

 

# Restart Agent

/opt/microsoft/omsagent/bin/service_control restart

# Check for errors – see blog

grep -i error /var/opt/microsoft/omsagent/scom/log/omsagent.log

# Test strings into your logfile

# Options

echo test >> /var/log/mylog

echo 911 error >> /var/log/mylog

# mimic syslog or messages syntax

echo `date +”%b %e %H:%M:%S”` MYLOG 911 test string. Call 911 >> /var/log/mylog

 

Please stay tuned for more management pack options to alert on the strings.  Refer to the part1/2 blogs for more details on unit testing for alerts.

OMSAgent FluentD debunked – Configure Linux FluentD – part2

Are you stoked and fired up to Configure Linux FluentD - part2 !?

Now to begin – OMSAgent FluentD debunked

Configure Linux FluentD – part2 –> see part one (1) here)

 

 

First, my thanks to Mike Johnston@Microsoft (CSS SEE SME) to help validate my steps and testing, to configure Linux FluentD on an Ubuntu server!  Are you ready to bust a myth – OMSAgent FluentD debunked

 

If you’re starting fresh, or just joining, start with Part 1.  And Part 1 configures packs and assumes SCOM agent is installed and working.  Because it’s time to use the feature, we need to get the agent configured and tested.

 

Part one (1) quick summary

    • Verify pre-reqs – SCOM Linux Management packs for Linux/Universal Linux (2019 @ 10.19.1082.0), UNIX/Linux Log File monitoring (2019 @ 10.19.1008.0)
    • Linux server has SCOM Agent installed, configured, and updated (sudoers configured) – GUI blog here
    • Use docs.microsoft.com article

 

Load Sample Log monitoring pack

This piece is missing in the doc, but the content development team has this covered in a subsequent docs article.  We need to load a sample log monitoring pack to the SCOM management group, so we can test functionality.

Configure FluentD part 2 - This is a picture of the SCOM console GUI showing the OMED pack installed from the Admin tab > Management Packs > Installed Management Packs > with omed in the 'look for:' bar

Grab the file here, otherwise you can copy/paste from the docs article pretty easily.

 

 

Verify OMED service running on Management Server

It’s now time to enable the OMED service on the management server, and we can start with the docs subsection

Navigation steps from SCOM console (GUI)

    1. From the Operations console, go to Monitoring>Operations Manager>Management Server>Management Servers State.
    2. Select the management server in the Management Servers state.
    3. From Tasks, select Health Service Tasks>Enable System Center OMED Server

 

Steps to set/start service PowerShell (as admin)

# Verify service startup type is automatic

get-Service OMED | select -property Name,Starttype

# Example output

PS C:\Users\admin> Get-Service OMED | select -property name,starttype
Name StartType
—- ———
OMED Automatic

# Set startup type

 

# Start OMED service on SCOM management server (MS)

start-service OMED

 

Now we’re ready to test the UNIX agent!

 

 

Configure SCOM/OMSagent on Linux server

And now it’s time to switch to the agent side.  I’m assuming that you’ve already configured the SCOM agent on the Linux server.  So it’s time to verify the SCOM and OMSAgent is configured and working.  Let’s go back to the docs subsection for our sanity check, because we need to create folders, and set ownership, etc.

 

Create files and set permissions
mkdir /etc/opt/microsoft/omsagent/scom/conf/omsagent.d
mkdir /etc/opt/microsoft/omsagent/scom/certs
mkdir /var/opt/microsoft/omsagent/scom/log
mkdir /var/opt/microsoft/omsagent/scom/run
mkdir /var/opt/microsoft/omsagent/scom/state
mkdir /var/opt/microsoft/omsagent/scom/tmp
mkdir /home/omsagent/fluent-logging
# NOTE – This location is flexible for the path to use for log file position files
chown omsagent:omiusers state
chown omsagent:omiusers run
chown omsagent:omiusers log
chown omsagent:omiusers tmp
chown omsagent:omiusers /home/omsagent/fluent-logging

Verify SCOM certificate

Configuring FluentD requires the SCOM management server (MS) has signed the certificate on the UNIX server.  The docs article tells you to generate a new certificate for FluentD, which requires the management server.

Overview

Sign the certs on the agent > copy to MS > sign > copy back to agent

Step by step instructions
    1. Generate certs

/opt/microsoft/scx/bin/tools/scxsslconfig -c -g /etc/opt/microsoft/omsagent/scom/certs/

2. Rename certificates

cp -p omi-host-server.domain.pem to scom-cert.pem

cp -p omikey.pem to scom-key.pem

 

3. Copy certs to MS (sftp/ssh via WinSCP, or your app of choice)

 

4. Sign certs on MS via scxcertconfig -sign

 

Open PowerShell (as admin)

Go to your SCOM management server directory (hopefully d:)

cd ‘D:\Program Files\Microsoft System Center\Operations Manager\Server’

scxcertconfig -sign scom-cert.pem

scxcertconfig -sign scom-key.pem

 

5. Copy certs back to agent from MS (sftp/ssh via WinSCP, or your app of choice)

 

6. Verify the SCOM certificate shows your Management Server (MS) in the DC= line in the certificate

openssl x509 -in scom-cert.pem -noout -text

Verify SSL certificate - openssl syntax, verify the DC= portion is from the SCOM management server (MS)

 

7. Restart omsagent

As the ALLINONE server is one of my 2019 SCOM labs, I can verify that my cert is now signed by the management server (MS).  Time to load the certificate, and then restart the agent to see if we have any errors

# Restart Agent

/opt/microsoft/omsagent/bin/service_control restart

 

 

Verify omsagent.log errors

Verify any errors from the omsagent.log

Depending on where you are with your UNIX/Linux commands, this may help provide some context or use case examples.

My example –

First error after restart was ‘permission denied’.   FluentD runs under the omsagent ID, and needs to have access to whatever log – at least read (4).  For the syslog example, I made omsagent the owner, and omiusers the group.   The smarter, security hat on, choice is to leave as root and make it read capable, or add omsagent to the root group

Configure FluentD part 2 - fluentd permission denied alerts on /var/log/syslog

 

Search /var/opt/microsoft/omsagent/scom/log/omsagent.log for errors.  Commands build on another, from simpler to more complex.  Don’t worry if UNIX/Linux is new, I’m all about examples, so hope that helps bridge the gap!

 

# Tail omsagent.log for progress

# Option 1 Continual output updates from file

tail -f /var/opt/microsoft/omsagent/scom/log/omsagent.log

# Option 2 – get last 10 lines

tail /var/opt/microsoft/omsagent/scom/log/omsagent.log

 

# Option 3 – get last 100 lines

tail -100 /var/opt/microsoft/omsagent/scom/log/omsagent.log

# Option 4 – Get a little fancier – search for a string

grep string /var/opt/microsoft/omsagent/scom/log/omsagent.log

# Option 5 – Specific example = error, case insensitive (-i)

grep -i error /var/opt/microsoft/omsagent/scom/log/omsagent.log

 

# Option 6 – egrep strings and -v to exclude what you don’t want to see

grep -i error /var/opt/Microsoft/omsagent/scom/log/omsagent.log |egrep -v “Permission denied|stacktrace”

 

Verify FluentD config files

Verify FluentD conf files and omsagent.conf has INCLUDE line

The INCLUDE lines allows a directory for a ‘Gold depot’ to control what log files are monitored on destination linux servers.  The goal is a standard repository (gold depot ) to simply copy the conf file you want for logfile/app/daemon, restart agent, and you’re off to the races monitoring that log file.

 

Verify omsagent.conf includes directory

grep -i include /etc/opt/Microsoft/omsagent/scom/conf/omsagent.conf

# If there’s output, make sure that omsagent.d path exists

# Verify permissions show omsagent:omiusers

ls -al /etc/opt/Microsoft/omsagent/scom/conf | grep omsagent

 

10. Back to step 8’s problem, to fix the FluentD conf files, so we can test!  Step 9 verified that FluentD is configured via the omsagent.conf, and also for specific configuration files (.conf) in omsagent.d directory.

ls -al output list of the omsagent.d directory and oms config specific files for various log files

Next, we need to restart the agent to verify configuration, and any errors are seen on the FluentD side.

My error for ‘out_scom’ plugin was already used by some other test conf files.

grep -i error /var/opt/Microsoft/omsagent/scom/log/omsagent.log |grep “Permission denied” |tail

 

Example of omsagent.log where we have traced an event for our mylog

OMSAgent FluentD debunked - omsagent.log permission denied opening logfile errors for /var/log/syslog

Mike explained that my error was due to having multiple FluentD conf files using the same buffer path for ‘out_scom’.  I searched the conf files to see who had ‘out_scom’ and removed one of my old test files from months back when I was testing the feature.

# Example of errors in the omsagent.log

Tail of the omsagent.log where we want to look for errors

 

Don’t forget to restart the omsagent for reading in the new file changes

# Restart Agent

/opt/microsoft/omsagent/bin/service_control restart

 

 

I’ll cover building a fluentd conf file in another blog post for brevity.

 

 

Time to test for alerts!

Time to test our FluentD conf file and append entries into the log file!

Starting simple again

# Options

echo test >> /var/log/mylog

echo 911 error >> /var/log/mylog

# Echo entries into test logfile to mimic syslog or messages

echo `date +”%b %e %H:%M:%S”` MYLOG 911 test string. Call 911

# Verify

tail /var/log/mylog

Switch over to SCOM management server, and look for alerts

Navigate to the Monitoring Tab > Active alerts

OMSAgent FluentD debunked - scom console alerts for fluentd test patterns

 

 

References for more information

In case you need a refresher on all the date options… Found CyberCiti FAQ helpful

Configure FluentD part 2 - output of the date command formatting like syslog or messages

All because the goal is to make the echo statement better for testing closer test/UAT examples on string matches, etc.

echo `date +”%b %e %H:%M:%S”` MYLOG 911 test string. Call 911

And what does it look like?

OMSAgent FluentD debunked - tail of created /var/log/mylog that shows various echo options

 

MMA Agent, cross platform, and Azure

Things that make you go hmmm….

 

 

Ran across a scenario where we were trying to connect Azure Cross-platform (Linux) VM’s and MMA/SCOM agents to SCOM management group.

 

Management group was 2012R2, discovery wizard from SCOM console, failed to install agent, certificate errors.

 

Researching, found this article first

Windows Azure VM monitoring blog

There’s a version history for the Azure Monitor VM extension here

Applies:

SCOM2012R2 after UR12 or SCOM 2016 UR2+ deprecated the SHA1 certificate

 

Deprecating SHA1 certificates
Tech Community blog

 

Product team nicely published a TechNet gallery script to help!

Gallery download – Script to update SHA1 certificates to SHA256 on cross-platform agents – SCOM

TechNet Gallery Download
https://gallery.technet.microsoft.com/scriptcenter/Script-to-update-SHA1-8a30c5ef

 

 

New Unix MP’s for 2016 and 2012R2

 

 

If you didn’t catch this (I didn’t), the 2016 Universal Linux Monitoring MP is missing, but is in the 2012R2 bundle

Until the bundle is fixed, don’t forget to grab the Universal Linux Monitoring MP from the 2012R2 bundle

unixmpdownloadforscom2012r2

Export the 2012R2 bundle, grab the MP

unixmp_export

Import MP into SCOM

easybutton

 

If that’s just a tad bit annoying, remember Microsoft wants feedback.

Feedback can be about problems, product specific feature requests, and functionality.

 

Use the UserVoice website for SCOM (System Center Operations Manager) https://systemcenterom.uservoice.com/

There are a lot of good features and feedback on the site.   If you weren’t aware, the product team uses this to prioritize updates to the product.

Search, vote up feedback for what’s most near and dear to your heart

ivoted

Fix 2016 Universal Linux Monitoring MP

Universal Linux MP guide needs updating