Differences

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

--- windows:powershell:insertinstance [2013/11/05 03:20] – Change SysMetrics to JiMetrics haidong
+++ windows:powershell:insertinstance [2018/05/15 03:00] (current) – external edit 127.0.0.1
@@ Line 6: / Line 6: @@
 <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')}
-    $instanceNameArray = New-Object System.Collections.ArrayList
+    $instanceArray = @()
     if ($SqlInstances -ne $null) {
         $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 {
-                [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
 Windows.Host_Select_HostID_HostName" -Database "JiMetrics"
 $HostList | foreach {
     $HostID = $_.HostID
-    Try {
+    Try {$SqlInstances = getSqlInstanceName($_.HostName)}
-        $SqlInstances = getSqlInstanceName($_.HostName) }
+    Catch {Return}
-    Catch {
-        Return }
     $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>