User Tools

Site Tools


windows:powershell:insertinstance

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
windows:powershell:insertinstance [2013/10/14 22:13]
127.0.0.1 external edit
windows:powershell:insertinstance [2018/05/14 22:00] (current)
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.+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. 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.
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.1381806784.txt.gz · Last modified: 2018/05/14 21:58 (external edit)