SQL Sentry PowerShell Module

Getting Started

PowerShell Requirements

The following versions of Windows PowerShell are compatible with the SentryOne PowerShell module:

  • 3.0
  • 4.0
  • 5.0
  • 5.1

Note:  PowerShell Core is not supported.

The SentryOne installation package includes a PowerShell module that can be used to manage your SentryOne environment through PowerShell. This topic includes a walkthrough of that functionality.

Importing the module

You'll find the PowerShell module in the SentryOne program directory (which varies depending on the installed <Version>):

C:\Program Files\SentryOne\<Version>\Intercerve.SQLSentry.Powershell.psd1

Import the SentryOne PowerShell module by using the Import-Module command. 

Import-Module command example for version 19.x:

Import-Module "C:\Program Files\SentryOne\19.0\Intercerve.SQLSentry.Powershell.psd1"

Import-Module command example for version 20.x:

Import-Module "C:\Program Files\SentryOne\20.0\Intercerve.SQLSentry.Powershell.psd1"

SentryOne PowerShell Import Module command exampleImport-Module Example with version 2020.0

Note:  When using the Enhanced Platform Installer (EPI), the module location is as follows (if the machine has both a client and monitoring service installed, the module will appear in both locations):

Monitoring Service

On a machine with the monitoring service installed:

C:\ProgramData\SentryOne\monitoringservice\bin\Intercerve.SQLSentry.Powershell.psd1

Client

On a machine with the client installed:

C:\Users\<USERNAME>\Documents\sentryone\client\<SENTRYONE_SQLSERVER_INSTANCE>\<SENTRYONE_DATABASE_NAME>\bin\Intercerve.SQLSentry.Powershell.psd1

For example:

C:\Users\mconnors\Documents\sentryone\client\QA-SRV-1\SentryOne\bin\Intercerve.SQLSentry.Powershell.psd1

Verify that the module imported correctly by running the Get-Module command. You should see a listing for Intercerve.SentryOne.Powershell

SentryOne PowerShell Get Module Command exampleGet-Module Example showing the Intercerve.SentryOne.PowerShell

Additional Information: For more information about Get-Module commands, see Microsoft Get Module Commands.

Available Commands

See the Command Examples section for more information on using available commands.

CommandDescription
Connect-SQLSentry

Allows you to connect to a specific SentryOne installation that's required before any other actions are performed. This command is useful for navigating between repositories, and is used in environments with more than one SentryOne database.

Available parameters
  • Database
  • Login
  • Password
  • ServerName
  • UseIntegratedSecurity
Disable-User

Disable a SQL Sentry user account.
Disconnect-SQLSentry

Allows you to disconnect from a specific SentryOne installation. Useful for environments with more than one SentryOne database.
Enable-User

Enable a SQL Sentry user account.
Get-ComputerAllows you to view computers in your environment.

Available Parameters
  • ComputerType
    • {Windows | VMwareHost | ApsAppliance | SqlDataWarehouse | AzureSqlDatabase | AzureElasticPool}
  • ID
  • Name
Information returned
  • Name
  • HostName
  • DomainName
  • ObjectID
  • ID
  • ComputerType
  • AccessLevel
Get-Connection

Allows you to view connections in your environment.

Available parameters
  • ConnectionType
    • {SqlServer | SqlServerAnalysisServices | RDSSqlServer}
  • ID
  • Name
  • ObjectID
Information returned
  • Name
  • ServerName
  • InstanceName
  • ObjectID
  • ID
  • ConnectionType
  • WatchedBy
Get-GroupAllows you to view groups in your environment.

Available Parameters
  • Name
  • Username
Information returned
  • Name
  • Description
  • IsEnabled
  • ObjectID
  • ID
Get-SiteAllows you to view sites in your environment.

Note:  This includes both sites and groups in the return.

Available Parameters
  • ID
  • Name
Get-SQLSentryConfiguration

Allows you to view basic information about your SentryOne configuration.

Information returned
  • SQLServer
  • Database
  • UseIntegratedSecurity
  • DatabaseLogin
  • DatabaseVersion
  • ApplicationDatabaseVersion
  • ApplicationPath
  • ApplicationVersion
  • ServerTime
  • ServerTimeUtc
Get-UserAllows you to view information about users in your environment.

Note:  See examples in the code section below for piping with Add-GroupUser and Remove-GroupUser to manager group memberships for users.

Available Parameters
  • FirstName
  • Name
  • EmailAddress
  • ID
  • Group
Information returned
  • FirstName
  • LastName
  • EmailAddress
  • PagerAddress
  • Description
  • IsEnabled
  • ObjectID
  • ID
Invoke-UnwatchConnectionAllows you to stop watching a connection with SentryOne.

Available Parameters
  • Connection
Invoke-WatchComputerAllows you to watch a computer with SentryOne.

Available Parameters
  • Computer
  • LicenseMode
Invoke-WatchConnectionAllows you to watch a connection with SentryOne.

Available Parameters
  • Connection
Register-ComputerAllows you to register a computer to be watched with SentryOne.

Available Parameters
  • ComputerType
  • Name
  • AccessLevel
  • DatabaseName
  • Login
  • Password
  • Port
  • TargetSite
  • UseIntegratedSecurity
  • AllowAzureRemoteObjectInstallation
Note:  AllowAzureRemoteObjectInstallation is only valid for Azure SQL Database targets.

Additional Information: For more information about adding servers, see the Automating adding servers to SentryOne article.
Register-ConnectionAllows you to register a connection to be watched with SentryOne.

Available Parameters
  • ConnectionType
  • Name
  • Login
  • Password
  • Port
  • TargetSite
  • UseIntegratedSecurity
Register-GroupAdds a new user group.
Register-UserAdds a new SQL Sentry user.
Unregister-GroupRemoves an existing user group.
Unregister-UserRemoves an existing SQL Sentry user.

Advisory Conditions & Configurations

Note:  The advisory condition and configuration commands in the following tables are only available in SQL Sentry versions 2021.1.13 or later.

Advisory Conditions

The advisory condition commands use the same logic that is found in the individual import and export features on the SQL Sentry client menu, but they allow for bulk importing and exporting of .condition files.

CommandDescription
Export-AdvisoryCondition

Returns one or more advisory condition file items.

Note:  You can filter advisory conditions by using ConvertFrom-Json and then operating on any advisory condition object properties.

Required
  • Must use Out-File -FilePath to write to file system.
    • Import-AdvisoryCondition command can only use files.
  • Must use foreach to write out multiple files.
Parameters
  • Name
    • Advisory condition name search pattern which can include wildcards (%).
Import-AdvisoryConditionImports one or more advisory conditions.

Parameters
  • Path
    • File or directory name.
  • SearchPattern
    • File name pattern to use with directory can which can include wildcards (%).
      • Default is *.condition
Remove-AdvisoryConditionDeletes one or more advisory conditions.

Parameters
  • Name
    • Advisory condition name search pattern which can include wildcards (%).

Configurations

The configuration file is stored in a JSON document and may be edited as needed.

The configuration commands include the following in the import/export:

  • Advisory Conditions
  • Conditions/Actions
    • Rulesets, Windows, Filters, etc.
  • Users and Groups (assigned)
  • All Global (All Targets) Settings
CommandDescription
Export-EnvironmentConfigurationExports a SQL Sentry system configuration in JSON format. The export may be written to a variable or file.

Switches
  • ExcludeAdvisoryConditions
  • ExcludeContacts
  • ExcludeConditionActions
  • ExcludeGlobalSettings
Import-EnvironmentConfigurationImports and applies a SQL Sentry system configuration from a variable or file in a JSON format.

Parameters
  • Config
    • Variable containing the JSON configuration.
  • Path
    • Path to the JSON configuration file.
Switches
  • ExcludeAdvisoryConditions
  • ExcludeContacts
  • ExcludeConditionActions
  • ExcludeGlobalSettings

Get-Help

Use Get-Help followed by a command to get information about the available commands in the table above. For example, to learn more about Get-Connection:

Get-Help Get-Connection

SentryOne PowerShell Get Help Get Connection command exampleGet-Help Get-Connection Example

Command Examples

You can get a file of all these command examples from GitHub in the SentryOne PowerShell Module Commands.ps1 file.

Import the SentryOne PowerShell Module

<# Import the SentryOne PowerShell Module #>
Import-Module "C:\Program Files\SentryOne\2020.0\Intercerve.SQLSentry.Powershell.psd1"

Connect to SentryOne Installation

<# Connect to a specific SentryOne Installation #>
Connect-SQLSentry -ServerName server.domain.com -DatabaseName SentryOne

Get SentryOne Installation Information

<# Get Information about your SentryOne Installation #>
Get-SQLSentryConfiguration 

Get SentryOne Information for Sites

<# Get Information about the Sites in your SentryOne Installation -use parameters to find information for a specific site #>
Get-Site 

Get SentryOne Information for Instances

<# Get Information about the Connections (Instances) in your SentryOne Installation -use parameters to find information for a specific connection #>
Get-Connection 

Get SentryOne Information for Targets

<# Get Information about the Computers (Targets) in your SentryOne Installation -use parameters to find information for a specific connection #>
Get-Computer 

Get SentryOne Information for Specific Target

<# Get Information about the Connections in your SentryOne Installation -use parameters to find information for a specific connection #>
Get-Connection 

Register a Target

<# Register Computers (Targets), so that they can be watched in your environment #>
Register-Computer -ComputerType Windows -Name server.domain.com -AccessLevel Full Register-Computer -ComputerType Windows - Name server.domain.com -AccessLevel Limited 

Register a Target (non Windows)

<# Register a Target that cannot utilize Windows Authentication (e.g., Azure SQL Database) #>
Register-Computer -ComputerType AzureSqlDatabase -Name example.database.windows.net -DatabaseName dbName -Login username -Password password -AccessLevel Full -UseIntegratedSecurity 0 

Register Instances

<# Register Connections (Instances), so that they can be watched in your environment #>
Register-Connection -ConnectionType SqlServerAnalysisServices -Name server.domain.com Register-Connection -ConnectionType SqlServer -Name server.domain.com 

Watch a Windows Target

<# Watch Windows Computer (Target) with Performance Analysis and Event Calendar | -Pipe in the Computer #>
Get-Computer -Name server.domain.com -NamedServerComputerType Windows | Invoke-WatchComputer 

Watch a Hyper-V Host Target

<# Watch Hyper-V Host (Target) with Performance Analysis and Event Calendar (core-based licensing) | -Pipe in the Computer #>
Get-Computer -Name server.domain.com -NamedServerComputerType Windows | Invoke-WatchComputer -LicenseMode CoreBased 

Watch a SQL Server Target

<# Watch SQLServer Connection (Instance) with Performance Analysis and Event Calendar | -Pipe in the Connection #>
Get-Connection -Name server.domain.com -NamedServerConnectionType SqlServer | Invoke-WatchConnection 

Watch a SSAS Target

<# Watch SSAS Connection (Instance) with Performance Analysis and Event Calendar | -Pipe in the Connection #>
Get-Connection -Name server.domain.com -NamedServerConnectionType SqlServerAnalysisServices | Invoke-WatchConnection 

Unwatch Target

<# Unwatch Windows computer (Target) #>
Get-Computer -Name server.domain.com -NamedServerComputerType Windows | Invoke-UnwatchComputer

Unwatch SSAS Connection

<# Unwatch SSAS connection #>
Get-Connection -Name server.domain.com -NamedServerConnectionType SqlServerAnalysisServices | Invoke-UnwatchConnection

Unwatch SQL Server Connection

<# Unwatch SQLServer connection #>
Get-Connection -Name server.domain.com -NamedServerConnectionType SqlServer| Invoke-UnwatchConnection

User cmdlets

<# User cmdlets #>

<# Add a user #>
Register-User -FirstName Test -LastName User -Email tuser@test.net -PagerAddress tuser@testPager.net -Description Tester -Login domain\username

<# View a user by first name #>
Get-User -FirstName Test

<# View a user by name #>
Get-User -Name "Test User"

<# Disable a user by name #>
Disable-User -Name "Test User"

<# Enable a user by name #>
Enable-User -Name "Test User"

<# Remove a user by name #>
Unregister-User -Name "Test User"

Group cmdlets

<# Group cmdlets #>

<# Create a new group #>
Register-Group -Name "Test Group" -Description "A Group" -Login Domain\TestGroup

<# View group information #>
Get-Group -Name "Test Group"

<# Disable a group #>
Disable-Group -Name "Test Group"

<# Enable a group #>
Enable-Group -Name "Test Group"

<# Remove a group #>
Unregister-Group -Name "Test Group"

User and Group cmdlets

<# User = and - to the Group cmdlets #>

<# Add a user to a group #>
Get-User -Name "Test User" | Add-GroupUser -GroupName "Test Group" 

<# Remove a user from a group #>
Get-User -Name "Test User" | Remove-GroupUser -GroupName "Test Group"

Important:  For performance considerations, when using scripts to bulk add targets, we recommend limiting the text file list to batches of 50-100 targets at a time.

PowerShell Sample Scripts

The following code samples are available in the sentryone-samples / powershell repository on GitHub.

Export/Import/Remove Advisory Conditions

$exportdirectory = "C:\Users\<UserName>\Documents\conditions"
 $count = 0
foreach ($ac in Export-AdvisoryCondition)
{    
    $acj = $ac | ConvertFrom-Json
    # Use .* for all ACs; separate multiple tags with vertical pipes (|).
    $tags = '.*'
    if ($acj.Tags -match $tags)
    {
        # Replace invalid file name chars
        $filename = ($acj.Name -replace '[/<>]', '_') + ".condition"
        $ac | Out-File -FilePath "$exportdirectory\$filename"
        Write-Host "Exported: $filename"
        $count++
    } 
}
Write-Host "$count conditions exported." 

Import-AdvisoryCondition $exportdirectory # import all ACs
Import-AdvisoryCondition "$exportdirectory\High Compiles.condition"
Import-AdvisoryCondition -Path $exportdirectory -SearchPattern "Service Broker*“

Remove-AdvisoryCondition -Name "High Compiles"
Remove-AdvisoryCondition -Name "Service Broker%"
Remove-AdvisoryCondition -Name "%" # will not cascade delete dependent ACs

Export/Import Configurations

Using a configuration file

# Using config file
$exportdirectory = "C:\Users\<UserName>\Documents\conditions"

Connect-SQLSentry -ServerName "server-name-us" -DatabaseName "SQLSentry-US"
Export-EnvironmentConfiguration -ExcludeAdvisoryConditions | Out-File -FilePath "$exportdirectory\env-config.json"
Disconnect-SQLSentry

Connect-SQLSentry -ServerName "server-name-uk" -DatabaseName "SQLSentry-UK"
Import-EnvironmentConfiguration -Path "$exportdirectory\env-config.json"
Disconnect-SQLSentry

Using a variable

# Using variable
Connect-SQLSentry -ServerName "server-name-us" -DatabaseName "SQLSentry-US"
$s1config = Export-EnvironmentConfiguration
Disconnect-SQLSentry

Connect-SQLSentry -ServerName "server-name-uk" -DatabaseName "SQLSentry-UK"
Import-EnvironmentConfiguration -Config $s1config
Disconnect-SQLSentry