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/11/04 21:20] haidong Change SysMetrics to JiMetrics |
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 "JiMetrics" | 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 ` | + | |
- | "JiMetrics" | + | |
- | } | + | |
- | } | + | |
- | } | + | |
</code> | </code> |