Spotlight Alarms and Powershell Execution

Spotlight notifies DBAs of performance issues via triggered alarm messages. More importantly, specific actions can be taken based on these alarms, by configuring the Alarm Actions feature of Spotlight. Read our blog about Alarm Actions configuration steps to learn more. Some common actions are sending an email, suppressing the alarm or running a program. Additionally, executing a Powershell script is another available action that can be imitated based on generated alarms.

During script development, keep in mind that you can leverage Spotlight Message Variables as input variables within scripts. These variables can be used both at the command line level and inside the script. These variables include:

Variable Description
{{CONNECTION_NAME}}                  The name of the connection that alarm was raised on.
{{TIME}} The time the alarm was raised on.
{{SEVERITY}} The severity of the raised alarm.
{{ALARM_NAME}} The name of the alarm.
{{MESSAGE}} The error message from the alarm.
{{ALARM_ID}} The internal alarm name and ID number.
{{VALUE}} The value that will trigger alarm. For example in a CPU alarm this would the %CPU that is measured.
{{KEY_VALUE}} The value of the key in collection. Some collections such database backup alarm are multi-valued.
{{TAG_VALUE#name}} Tag name on the connection. Tag names are values are assigned on the Connection Properties dialogue.

In the following example, the Execute Powershell action has been used.  It’s intended to send a high priority level email indicating high CPU Usage alarm also including an attachment file containing the contents of current running processes order by CPU usage.

Configuration Options:

The script used for this action:

get-process | Sort-Object -Property cpu -Descending | Format-Table -AutoSize | out-file c:\temp\process.txt
$smtp = "smtp.server.name"
$from = email@quest.com
$to = email@quest.com
$subject = "Spotlight notification for {{CONNECTION_NAME}}: {{ALARM_NAME}}"
$body = "Spotlight has raised the CPU alarm:
Connection {{CONNECTION_NAME}} on {{TIME}}

{{ALARM_NAME}}: {{MESSAGE}}"

Send-MailMessage -From $from -To $to -SmtpServer $smtp -Subject $subject -Body $body -Priority High -Attachments c:\temp\process.txt

Spotlight alarm action results - generated email:

Tips:

  • Spotlight runs Powershell script remotely from the monitored host machine.
  • The script uses the username/password configured for the monitored Windows server in Spotlight. Refer to the connection properties of monitored Windows server.
  • Ensure that WinRM service is configured at monitored server by using winrm quickconfig command.

Download a Spotlight free trial or drop us a note at our Spotlight User Forum Community.

About the Author
Gita Sharifi
Gita Sharifi has been with Quest since 2006 working as a Senior Technical Support Engineer supporting database performance monitoring tools. Prior to joining Quest Gita has held positions in Oracle PL...