User Tools

Site Tools


Here is a PowerShell script to gather SQL Server instance configuration information. It comes from sys.configurations view. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to have proper access to the instances in question and be able to insert data into the JiMetrics database.

Remember to change “sql1” to your host instance name

function getInstanceConfig($ServerInstance) {
    $InstanceConfigQuery = @"
       SELECT  [configuration_id]
                , [name]
                , [value]
                , [value_in_use]
        FROM    [sys].[configurations];
    try {
    $InstanceConfigList = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $InstanceConfigQuery -Database "master"
    catch {}
    $dataIndexArray = New-Object System.Collections.ArrayList
    $InstanceConfigList | foreach {
        $myHashtable = @{Configuration_Id = $_.configuration_id;  Name =
        $; Value = $_.value; ValueInUse = $_.value_in_use}
        [void] $dataIndexArray.add($myHashtable)}
function insertInstanceConfigSQL($instanceConfig, $instanceID) {
	$ConfigurationId, $Name, $Value, $ValueInUse =
        $instanceConfig.Configuration_Id, $instanceConfig.Name, $instanceConfig.Value, $instanceConfig.ValueInUse
	$sql = "EXEC Windows.InstanceConfig_Insert $InstanceID, $ConfigurationId, '$Name', '$Value', '$ValueInUse'"
    return $sql}
$InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics"
$InstanceList | ForEach-Object {
    $InstanceName = $_.InstanceName
    $InstanceID = $_.InstanceID
    Try {
           $InstanceConfigArray = getInstanceConfig($InstanceName)
           $InstanceConfigArray | ForEach-Object {
               $sql = insertInstanceConfigSQL $_ $InstanceID
               Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"}}
    Catch [Exception] { Continue }}
windows/powershell/insertinstanceconfig.txt · Last modified: 2018/05/14 22:00 (external edit)