User Tools

Site Tools


windows:powershell:insertinstance

This is an old revision of the document!


Here is a PowerShell script to discover SQL Server instances installed on the server hosts in the Host table. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

This information should be fairly static. Depending on your situation, scheduling this to run weekly, monthly, or quarterly, or whatever. You can always manually kick off this job if needed.

Remember to change “sql1” to your host instance name

insertInstance.ps1
function getSqlInstanceName($ComputerName)
{
$SqlInstances = Get-Service -ComputerName $ComputerName | where {($_.Name -like
    'mssql$*') -or ($_.Name -eq 'mssqlserver')}
    $instanceNameArray = New-Object System.Collections.ArrayList
    if ($SqlInstances -ne $null) {
        $SqlInstances | foreach {
            if ($_.Name -eq 'mssqlserver') {
                [void]
                $instanceNameArray.add(@{InstanceName=$ComputerName;Status=$_.Status})
            }
            else {
                [void]
                $instanceNameArray.add(@{InstanceName=$ComputerName + "\" +
                $_.Name.split("$")[1];Status=$_.Status})
            }
        }
    }
    $instanceNameArray
}
$HostList = Invoke-Sqlcmd -ServerInstance "sql1" -Query "EXEC
Windows.Host_Select_HostID_HostName" -Database "SysMetrics"
$HostList | foreach {
    $HostID = $_.HostID
    Try {
        $SqlInstances = getSqlInstanceName($_.HostName) }
    Catch {
        Return }
    $SqlInstances | foreach {
        $InstanceName = $_.InstanceName
        if ($InstanceName -ne $null) {
            if ($_.Status -eq 'running') {
                $IsActive = "Y"}
            else {
                $IsActive = "N"}
            $sql = "EXEC Windows.Instance_Insert $HostID, '$InstanceName',
'$IsActive'"
            Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database `
            "SysMetrics"
        }
    }
}
windows/powershell/insertinstance.1381867210.txt.gz · Last modified: 2018/05/14 21:58 (external edit)