windows:powershell:insertinstance
Differences
This shows you the differences between two versions of the page.
|
windows:powershell:insertinstance [2013/10/14 22:13] 127.0.0.1 external edit |
windows:powershell:insertinstance [2018/05/14 22:00] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | Here is a PowerShell script to discover SQL Server instances installed on the server hosts in the Host table. 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 ** | ||
| - | |||
| - | <code powershell 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" | ||
| - | } | ||
| - | } | ||
| - | } | ||
| - | </code> | ||
windows/powershell/insertinstance.txt ยท Last modified: 2018/05/14 22:00 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
