Get-SqlAssessmentItem

Gets SQL Assessment best practice checks available for a chosen SQL Server object.

Syntax

Get-SqlAssessmentItem
   [-Check <String[]>]
   [[-InputObject] <PSObject>]
   [-Configuration <PSObject>]
   [-MinSeverity <SeverityLevel>]
   [<CommonParameters>]

Description

The Get-SqlAssessmentItem cmdlet finds all available best practice checks for each input object. Learn more about the SQL Assessment API on its Microsoft Docs page.

This cmdlet accepts the following input types:

  • Microsoft.SqlServer.Management.Smo.Server
  • Microsoft.SqlServer.Management.Smo.Database
  • Microsoft.SqlServer.Management.Smo.AvailabilityGroup
  • Microsoft.SqlServer.Management.Smo.FileGroup
  • String containing path to any object of the above types
  • Collection of objects

You can get input objects with SqlServer cmdlets like Get-SqlInstance and Get-SqlDatabase or basic PowerShell cmdlets like Get-Item and Get-ChildItem. Also, the cmdlet supports the SQL Server PowerShell provider, so it can obtain an object from its path. The path can be passed explicitly, otherwise the current path will be used.

Availability of a check for a chosen object varies on SQL Server version, platform, object type, and name. And besides, you can additionally filter checks with the parameters -MinSeverity and -Check.

Custom configurations can be applied with the -Configuration parameter. Customization examples are available on Github.

Examples

Example 1: Get checks for local default instance

PS:> Get-SqlInstance -ServerInstance 'localhost' | Get-SqlAssessmentItem

 Target: [LOCAL]

Id                                               Enabled DisplayName
--                                               ------- -----------
SqlServer.Server.TraceFlag.634                   True    TF 634 Disables Background Columnstore Compression
SqlServer.Server.NoDefaultTraceFoundIsNotActive  True    No default trace was found or is not active
SqlServer.Server.HintsStatistics                 True    Hints are being used
SqlServer.Server.PlansUseRatio                   True    Amount of single use plans in cache is high
...

This example gets all checks available for the default instance of SQL Server running on the current machine.

Example 2: Get checks with Get-Item cmdlet

PS:> Get-Item SQLSERVER:\SQL\localhost\default | Get-SqlAssessmentItem

This example gets all checks available for the default instance of SQL Server running on the current machine.

Example 3: Get checks with path to target object

PS:> Get-SqlAssessmentItem SQLSERVER:\SQL\localhost\default

This example gets all checks available for the default instance of SQL Server running on the current machine.

Example 4: Get checks with applied custom configuration

PS:> cd SQLSERVER:\SQL\localhost
PS:> Get-Item default | Get-SqlAssessmentItem -Configuration C:\SqlServerAssessment\profileA.json, C:\SqlServerAssessment\profileB.json

This example gets all available checks with applied custom configuration obtained from specified JSON files. Visit SQL Assessment samples folder on Github to find out how to make customization.

Example 5: Get checks for all instances on localhost

PS:> dir SQLSERVER:\SQL\localhost | Get-SqlAssessmentItem

This example shows Get-SqlAssessmentItem cmdlet accepting a set of SQL Server instances via pipeline.

Example 6: Get checks for all instances with names ending with numbers

PS:> dir SQLSERVER:\SQL\localhost | Where { $_.Name -Match '.*\d+' } | Get-SqlAssessmentItem

This example shows Get-SqlAssessmentItem cmdlet accepting a set of SQL Server instances via pipeline. The set is filtered with the standard PowerShell facilities.

Example 7: Get checks for a database by path

PS:> Get-SqlAssessmentItem SQLSERVER:\SQL\localhost\default\Databases\master

This example shows Get-SqlAssessmentItem cmdlet accepting a path to a SQL Server database.

Example 8: Get critical checks for a database

PS:> cd SQLSERVER:\SQL\localhost\default\Databases\master
PS:> Get-SqlAssessmentItem -MinSeverity Critical

This example shows Get-SqlAssessmentItem returning available checks with critical severity for the master database. It accepts the current location as the target.

Example 9: Get checks for all databases on local instance

PS:> Get-SqlDatabase -ServerInstance 'localhost\instance1' | Get-SqlAssessmentItem

This example shows obtaining a list of available checks for all databases returned by Get-SqlDatabase cmdlet.

Example 10: Get checks by tag

PS:> Get-SqlInstance -ServerInstance . | Get-SqlAssessmentItem -Check Backup

This example shows Get-SqlAssessmentItem cmdlet returning all backup-related checks for every SQL Server instance on the local server.

Example 11: Get recommendations for interactively selected checks

PS:> $serverInstance = Get-SqlInstance -ServerInstance '(local)'
PS:> $checks = Get-SqlAssessmentItem $serverInstance | Select Id, Description | Out-GridView -PassThru
PS:> Invoke-SqlAssessment $serverInstance -Check $checks

The second line of this example shows obtaining checks for a $serverInstance, and selecting some of them interactively. Selected items are stored in an array variable, which then can be used as input for Invoke-SqlAssessment cmdlet. In this case, only picked checks will run during the assessment process.

Parameters

-Check

One or more checks, check IDs, or tags. For every check object, Get-SqlAssessmentItem returns that check if it supports input object. For every check ID, Get-SqlAssessmentItem returns the corresponding check if it supports input object. For tags, Get-SqlAssessmentItem returns checks with any of those tags.

Type:String[]
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Configuration

Specifies paths to files containing custom configuration. Customization files will be applied to default configuration in specified order. The scope is limited to this cmdlet invocation only.

Type:PSObject
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-InputObject

Specifies a SQL Server object or a path to such an object. The cmdlet returns appropriate checks for this object. When this parameter is omitted, current location is used as input object. If current location is not a supported SQL Server object, the cmdlet signals an error.

Type:PSObject
Aliases:Target
Position:10
Default value:None
Accept pipeline input:True (ByValue)
Accept wildcard characters:False
-MinSeverity

Specifies minimum severity level for checks to be found.

For example, checks of Warning or Information levels will not be returned when -MinSeverity=Critical.

Possible values: Information, Warning, Critical

Type:SeverityLevel
Aliases:Severity
Accepted values:None, Information, Warning, Critical
Position:Named
Default value:Information
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.String[]

Microsoft.SqlServer.Management.Smo.SqlSmoObject[]

Outputs

Microsoft.SqlServer.Management.Assessment.ICheck