windows:powershell:insertinstance
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
windows:powershell:insertinstance [2013/10/15 15:00] haidong Added a line to tell reader that a SQL Server Agent job is needed. |
windows:powershell:insertinstance [2018/05/14 22:00] (current) |
||
|---|---|---|---|
| Line 6: | Line 6: | ||
| <code powershell insertInstance.ps1> | <code powershell insertInstance.ps1> | ||
| - | function getSqlInstanceName($ComputerName) | + | function getSqlInstanceName($ComputerName) { |
| - | { | + | $SqlInstances = Get-Service -ComputerName $ComputerName | where {($_.Name -like |
| - | $SqlInstances = Get-Service -ComputerName $ComputerName | where {($_.Name -like | + | |
| 'mssql$*') -or ($_.Name -eq 'mssqlserver')} | 'mssql$*') -or ($_.Name -eq 'mssqlserver')} | ||
| - | $instanceNameArray = New-Object System.Collections.ArrayList | + | $instanceArray = @() |
| if ($SqlInstances -ne $null) { | if ($SqlInstances -ne $null) { | ||
| $SqlInstances | foreach { | $SqlInstances | foreach { | ||
| - | if ($_.Name -eq 'mssqlserver') { | + | $sqlName = $_.Name |
| - | [void] | + | $service = Get-WmiObject win32_service -ComputerName $ComputerName | where {$_.Name -eq $sqlName} |
| - | $instanceNameArray.add(@{InstanceName=$ComputerName;Status=$_.Status}) | + | if ($sqlName -eq 'mssqlserver') { |
| - | } | + | $instanceArray = $instanceArray + @(@{InstanceName=$ComputerName; StartupAcct=$service.StartName; Status=$_.Status})} |
| else { | else { | ||
| - | [void] | + | $instanceArray = $instanceArray + @(@{InstanceName=$ComputerName + "\" + |
| - | $instanceNameArray.add(@{InstanceName=$ComputerName + "\" + | + | $sqlName.split("$")[1];StartupAcct=$service.StartName; Status=$_.Status})}}} |
| - | $_.Name.split("$")[1];Status=$_.Status}) | + | return $instanceArray} |
| - | } | + | |
| - | } | + | function insertInstanceSQL($i, $HostID) { |
| - | } | + | $InstanceName = $i.InstanceName |
| - | $instanceNameArray | + | $StartupAcct = $i.StartupAcct |
| - | } | + | if ($InstanceName -ne $null) { |
| + | if ($i.Status -eq 'running') { | ||
| + | $IsActive = "Y"} | ||
| + | else {$IsActive = "N"} | ||
| + | $sql = "EXEC Windows.Instance_Insert $HostID, '$InstanceName', '$StartupAcct', '$IsActive'" | ||
| + | return $sql}} | ||
| $HostList = Invoke-Sqlcmd -ServerInstance "sql1" -Query "EXEC | $HostList = Invoke-Sqlcmd -ServerInstance "sql1" -Query "EXEC | ||
| - | Windows.Host_Select_HostID_HostName" -Database "SysMetrics" | + | Windows.Host_Select_HostID_HostName" -Database "JiMetrics" |
| $HostList | foreach { | $HostList | foreach { | ||
| $HostID = $_.HostID | $HostID = $_.HostID | ||
| - | Try { | + | Try {$SqlInstances = getSqlInstanceName($_.HostName)} |
| - | $SqlInstances = getSqlInstanceName($_.HostName) } | + | Catch {Return} |
| - | Catch { | + | |
| - | Return } | + | |
| $SqlInstances | foreach { | $SqlInstances | foreach { | ||
| - | $InstanceName = $_.InstanceName | + | $sql = insertInstanceSQL $_ $HostID |
| - | if ($InstanceName -ne $null) { | + | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"}} |
| - | 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> | </code> | ||
windows/powershell/insertinstance.1381867210.txt.gz · Last modified: 2018/05/14 21:58 (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
