How to use custom command alert actions in ApexSQL Monitor

Applies to
ApexSQL Monitor

Summary
This article shows how to utilize the Create command functionality, how to include the action tags within a command line and perform command testing in ApexSQL Monitor.

Description

The Create command functionality, as part of ApexSQL Monitor’s alert actions feature, allows executing custom commands including Windows Shell, PowerShell, .bat and .ps1 files etc., when alerting events occur.

  1. How to create a custom command line alert action

    For utilizing the Create command alert action, select a particular instance of the SQL server, and navigate through the Metrics view, choose the Metrics tab and select the Manage alert action profile option, to create a new profile:

    Select all system, SQL server, Database and other groups of metrics, with their respective baselines and thresholds, except where is stated differently.

    By clicking the New button, create a new profile. We’ll title it “My custom command:

    The command line execution panel overview

    As can be seen, this panel is similar to the SQL alert panel. The command line execution panel has two elements: the command line input field e.g. “Execute command” and the action tags e.g. Machine beneath.

    In the Create SQL script alert action functionality, action tags can be included in the command line composition as parameters, which provide actual information from the alerting event.

    Testing the command script execution alert action

    When the customized command line (with optionally included action tag or tags) is completed, it can be validated and tested. To proceed testing the particular command input, press Test button.

    After successful testing attempt and corresponding message,

    a command can be included in alert action profile and saved, used as the preset alert action.

    Quick tip icon

    Quick tip:

    If the whole command line or its part are not valid, ApexSQL Monitor will raise specific error message and provide the information, related to the command input.

  2. Command line examples for utilizing in the Create command alert action

    Using action tags in command line

    In the provided example, the command will include all of the action tags and set output file, in this case a text file:

    (
      echo 
      % Machine % 
      % SERVER % 
      % DATABASE % 
      % ServerFullName % 
      % User % 
      % Password % 
      % AlertSeverity % 
      % AlertTime % 
      % AlertValue % 
      % MetricName % 
      % DefaultPath %
    ) >
    
    c: \alert_status_command.txt
    

    When the command input field is hovered over, the results will show in the tooltip, like shown below:

    Execute PowerShell script

    Commands are useful if some metric and corresponding alert indicates the need to start or restart a related service.

    To start or restart the particular service within ApexSQL Monitor, simply execute this command with set parameter, like shown below:

    powershell.exe "Start-Service defragsvc"
    

    This PowerShell command will restart the network adapter, in case that System availability metric exceeds the threshold:

    powershell.exe "Restart-NetAdapter"
    

    If this alert action profile (name it Restart network adapter) is set to the mentioned metric, it will look like this:

    The following PowerShell command will generate a text file with the list of the processes which consume the most memory:

    powershell.exe "Get-Process | Sort-Object -Descending WS | Out-File c:\which_process.txt"
    

    To immediately calculate the present physical memory size, and generate the corresponding text file, use this PowerShell command:

    powershell.exe "(get-wmiobject -class " win32_physicalmemory " -namespace " root\CIMV2 ").Capacity | Out-File c:\physical_memory_size.txt"
    
    Quick tip icon

    Quick tip:

    In order to avoid the syntax error messages, quotation marks and/or apostrophes must be used: if there is a space character in the command input (like in the case with PowerShell commands) or title of the file within the path. That also applies for executing the batch or PowerShell script files.

    Examples:
    powershell.exe ‘c:\”process List”.ps1’ (for executing PS scripts)
    tasklist /svc >”c:\process list.txt” (it lists all active processes in one text file)

    FAQs

    Q: Does ApexSQL Monitor support executing multiple commands at the same time?

    A: It does. Just create specified number of custom command alert actions in one alert action profile:

    Like SQL scripts, command lines also execute in the order they appear in the grid.

    Q: Can I execute an existing/external batch file/PowerShell script within custom command input in ApexSQL Monitor?

    A: Yes. Although the Windows Shell and PowerShell commands can be executed directly from ApexSQL Monitor, batch files (.bat) and PowerShell scripts (.ps1) can be also used within custom command alert actions. These are the examples of usage, both for batch file:

    "<path_of_the_batch_file>.bat"
    

    and PowerShell script:

    powershell.exe "<path_of_the_powershell_script>.ps1"
    
    Quick tip icon

    Quick tip:

    The full path of the batch file or PowerShell script can be fetched with the right-click on the file, and choosing the option “Copy as path”. The file will be automatically enclosed with quotation marks and ready to use in the command script.