API tools faq
paste
Guest User

Untitled

a guest
Aug 30th, 2019
128
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PowerShell 31.00 KB | None | 0 0
raw download clone embed print report
  1. If((Get-Module -ListAvailable -Name dbatools) -eq $null) {
  2.     # try to install dbatools module
  3.     try {
  4.         Install-Module dbatools -ErrorAction Stop
  5.     }
  6.     catch {
  7.         throw [System.ArgumentException] "You need to install the dbatools module, which requires administrator privileges."
  8.     }
  9. }
  10. Import-Module dbatools
  11.  
  12. Add-Type -AssemblyName PresentationCore, PresentationFramework, WindowsBase, System.Windows.Forms, System.Drawing
  13.  
  14. $Global:syncHash = [hashtable]::Synchronized(@{})
  15.  
  16. ## LOAD INVENTORY TO SYNCHASH ###
  17. $syncHash.InventoryFileLocation = $PSScriptRoot
  18. $syncHash.Servers = (Import-CSV -Path "$($syncHash.InventoryFileLocation)\servers.csv" | Sort-Object -Property ServerName)
  19. $syncHash.ServersChanged = $false
  20. $syncHash.InventoryLocked = $false
  21. $syncHash.ParentPID = $PID
  22.  
  23. If(Test-Path "$PSScriptRoot\inv.loc") {
  24.     [System.Windows.MessageBox]::Show("Someone is editing the inventory file!  All modify operations are disabled.", "Information", "OK", "Information")
  25.     $syncHash.InventoryLocked = $true
  26. }
  27. Else {
  28.     New-Item -Path "$PSScriptRoot\inv.loc" | Out-Null
  29. }
  30.  
  31. $newRunspace = [runspacefactory]::CreateRunspace()
  32. $newRunspace.ApartmentState = "STA"
  33. $newRunspace.ThreadOptions = "ReuseThread"
  34. $newRunspace.Open()
  35. $newRunspace.SessionStateProxy.SetVariable("syncHash", $syncHash)
  36.  
  37. $guiCmd = [PowerShell]::Create().AddScript({
  38.     [xml]$mainWindowXAML = @"
  39.    <Window
  40.            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  41.            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  42.            Title="SQL Server Inventory" Height="620" Width="920" MinWidth="920" Background="#FF323242"
  43.             Icon="$($syncHash.InventoryFileLocation)\favicon.ico">
  44.         <Window.Resources>
  45.             <Style TargetType="DataGridCell">
  46.                 <Setter Property="ToolTip" Value="{Binding RelativeSource={RelativeSource Self},Path=Content.Text}" />
  47.                 <Setter Property="ToolTipService.InitialShowDelay" Value="500" />
  48.             </Style>
  49.         </Window.Resources>
  50.         <DockPanel x:Name="dckContainer" VerticalAlignment="Stretch" HorizontalAlignment="Stretch" Margin="10,10,10,10" LastChildFill="True">
  51.             <UniformGrid Rows="1" Columns="4" HorizontalAlignment="Stretch" DockPanel.Dock="Top">
  52.                 <UniformGrid.Resources>
  53.                     <Style TargetType="{x:Type Button}">
  54.                         <Setter Property="FontWeight" Value="Bold"/>
  55.                         <Setter Property="FontSize" Value="16"/>
  56.                         <Setter Property="Width" Value="216"/>
  57.                         <Setter Property="Height" Value="30"/>
  58.                     </Style>
  59.                 </UniformGrid.Resources>
  60.                 <Button x:Name="btnAddServer" Content="Add SQL Server" Width="216" HorizontalAlignment="Left"/>
  61.                 <Button x:Name="btnModifyServer" Content="Modify Selected Server" Width="216" HorizontalAlignment="Center"/>
  62.                 <Button x:Name="btnRemoveServer" Content="Remove Selected Server(s)" Width="216" HorizontalAlignment="Center"/>
  63.                 <Button x:Name="btnRescanServer" Content="Rescan Selected Server(s)" Width="216" HorizontalAlignment="Right"/>
  64.             </UniformGrid>
  65.             <Separator HorizontalAlignment="Stretch" Margin="0,10,0,10" DockPanel.Dock="Top"/>
  66.             <DataGrid x:Name="dgvInventory" RowHeight="20" IsReadOnly="True" HorizontalAlignment="Stretch" DockPanel.Dock="Top" AutoGenerateColumns="False" Background="#FF49495F" ClipToBounds="True" CanUserResizeRows="False" CanUserSortColumns="True" ScrollViewer.VerticalScrollBarVisibility="Visible">
  67.                 <DataGrid.ContextMenu>
  68.                     <ContextMenu>
  69.                         <MenuItem x:Name="mnuAdd" Header="Add Server" />
  70.                         <Separator />
  71.                         <MenuItem x:Name="mnuModify" Header="Modify Server" />
  72.                         <MenuItem x:Name="mnuRescan" Header="Rescan Server(s)" />
  73.                         <MenuItem x:Name="mnuRemove" Header="Remove Server(s)" />
  74.                     </ContextMenu>
  75.                 </DataGrid.ContextMenu>
  76.                 <DataGrid.Columns>
  77.                     <DataGridTextColumn Header="Server Name" Binding="{Binding ServerName}" Width="5*" SortDirection="Ascending" />
  78.                     <DataGridTextColumn Header="IP Address" Binding="{Binding IPAddress}" Width="3*" />
  79.                     <DataGridTextColumn Header="Description" Binding="{Binding Description}" Width="6*" />
  80.                     <DataGridTextColumn Header="Version" Binding="{Binding Version}" Width="3*"/>
  81.                     <DataGridTextColumn Header="SP" Binding="{Binding SP}" Width="1*" />
  82.                     <DataGridTextColumn Header="Edition" Binding="{Binding Edition}" Width="5* "/>
  83.                     <DataGridTextColumn Header="Windows OS" Binding="{Binding WindowsOS}" Width="6*" />
  84.                 </DataGrid.Columns>
  85.             </DataGrid>
  86.         </DockPanel>
  87.     </Window>
  88. "@
  89.  
  90.    $XMLReader = (New-Object System.Xml.XmlNodeReader $mainWindowXAML)
  91.    $syncHash.MainWindow = [Windows.Markup.XamlReader]::Load($XMLReader)
  92.    $syncHash.MainWindowControls = @{}
  93.    $mainWindowXAML.SelectNodes("//*[@*[contains(translate(name(.),'n','N'),'Name')]]") | % {
  94.        $syncHash.MainWindowControls.Add($_.Name, $syncHash.MainWindow.FindName($_.Name))
  95.    }
  96.  
  97.    #region Background runspace to clean up jobs
  98.    $Script:JobCleanup = [hashtable]::Synchronized(@{})
  99.    $Script:Jobs = [System.Collections.ArrayList]::Synchronized((New-Object System.Collections.ArrayList))
  100.  
  101.    $JobCleanup.Flag = $true
  102.    $newRunspace = [runspacefactory]::CreateRunspace()
  103.    $newRunspace.ApartmentState = "STA"
  104.    $newRunspace.ThreadOptions = "ReuseThread"
  105.    $newRunspace.Open()
  106.    $newRunspace.SessionStateProxy.SetVariable("JobCleanup", $JobCleanup)
  107.    $newRunspace.SessionStateProxy.SetVariable("Jobs", $Jobs)
  108.    $JobCleanup.PowerShell = [PowerShell]::Create().AddScript({
  109.        Do {
  110.            Foreach($Runspace in $Jobs) {
  111.                If($Runspace.Runspace.isCompleted) {
  112.                    [void]$Runspace.PowerShell.EndInvoke($Runspace.Runspace)
  113.                    $Runspace.PowerShell.Dispose()
  114.                    $Runspace.Runspace = $null
  115.                    $Runspace.PowerShell = $null
  116.                }
  117.            }
  118.            $temphash = $Jobs.Clone()
  119.            $temphash | ? { $_.Runspace -eq $null } | % { $Jobs.Remove($_) }
  120.            Start-Sleep -Seconds 1
  121.        } While($JobCleanup.Flag)
  122.    })
  123.    $JobCleanup.PowerShell.Runspace = $newRunspace
  124.    $JobCleanup.Thread = $JobCleanup.PowerShell.BeginInvoke()
  125.    #endregion Background runspace to clean up jobs
  126.  
  127.    # Initial load of inventory into GUI
  128.    $syncHash.MainWindowControls.dgvInventory.ItemsSource = $syncHash.Servers
  129.  
  130.    # If locked, disable the menu buttons
  131.    $syncHash.MainWindow.Add_Loaded({
  132.        If($syncHash.InventoryLocked) {
  133.            $syncHash.MainWindowControls.Keys | ? { $_.Substring(0,3) -in ("mnu", "btn") } | % { $syncHash.MainWindowControls.$_.IsEnabled = $false }
  134.        }
  135.    })
  136.  
  137.    # Context menu handlers, just trigger the button events
  138.    $syncHash.MainWindowControls.mnuAdd.Add_Click({
  139.        $syncHash.MainWindowControls.btnAddServer.RaiseEvent((New-Object -TypeName System.Windows.RoutedEventArgs -ArgumentList $([System.Windows.Controls.Button]::ClickEvent)))
  140.    })
  141.  
  142.    $syncHash.MainWindowControls.mnuModify.Add_Click({
  143.        $syncHash.MainWindowControls.btnModifyServer.RaiseEvent((New-Object -TypeName System.Windows.RoutedEventArgs -ArgumentList $([System.Windows.Controls.Button]::ClickEvent)))
  144.    })
  145.  
  146.    $syncHash.MainWindowControls.mnuRescan.Add_Click({
  147.        $syncHash.MainWindowControls.btnRescanServer.RaiseEvent((New-Object -TypeName System.Windows.RoutedEventArgs -ArgumentList $([System.Windows.Controls.Button]::ClickEvent)))
  148.    })
  149.  
  150.    $syncHash.MainWindowControls.mnuRemove.Add_Click({
  151.        $syncHash.MainWindowControls.btnRemoveServer.RaiseEvent((New-Object -TypeName System.Windows.RoutedEventArgs -ArgumentList $([System.Windows.Controls.Button]::ClickEvent)))
  152.    })
  153.        
  154.    # Handles spawning the Add Server window
  155.    $syncHash.MainWindowControls.btnAddServer.Add_Click({
  156.        $newRunspace =[runspacefactory]::CreateRunspace()
  157.        $newRunspace.ApartmentState = "STA"
  158.        $newRunspace.ThreadOptions = "ReuseThread"          
  159.        $newRunspace.Open()
  160.        $newRunspace.SessionStateProxy.SetVariable("SyncHash",$SyncHash)
  161.        $PowerShell = [PowerShell]::Create().AddScript({
  162.            If($syncHash.AddWindow -ne $null) {
  163.                $syncHash.AddWindow.Dispatcher.Invoke(
  164.                    [action]{ $syncHash.AddWindow.Activate() },
  165.                    "Normal"
  166.                )
  167.            }
  168.            Else {
  169.                [xml]$addWindowXAML = @"
  170. <Window
  171.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  172.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  173.         Title="Add SQL Server" Height="220" Width="270" Background="#323242" ResizeMode="NoResize"
  174.         Icon="$($syncHash.InventoryFileLocation)\favicon.ico">
  175.     <Grid Margin="10">
  176.         <Grid.RowDefinitions>
  177.             <RowDefinition Height="Auto"/>
  178.             <RowDefinition Height="Auto"/>
  179.             <RowDefinition Height="*"/>
  180.             <RowDefinition Height="5"/>
  181.             <RowDefinition Height="Auto"/>
  182.         </Grid.RowDefinitions>
  183.         <Grid.ColumnDefinitions>
  184.             <ColumnDefinition Width="100"/>
  185.             <ColumnDefinition Width="*"/>
  186.         </Grid.ColumnDefinitions>
  187.         <Label Grid.Row="0" Grid.Column="0" Content="Server Name:" Foreground="White" FontWeight="Bold" HorizontalContentAlignment="Right"/>
  188.         <Label Grid.Row="1" Grid.Column="0" Content="Instance Name:" Foreground="White" FontWeight="Bold" HorizontalContentAlignment="Right"/>
  189.         <TextBox x:Name="txtServerName" Grid.Row="0" Grid.Column="1" Height="20" HorizontalAlignment="Stretch"/>
  190.         <TextBox x:Name="txtInstanceName" Grid.Row="1" Grid.Column="1" Height="20" HorizontalAlignment="Stretch" Text="MSSQLSERVER" Foreground="Gray"/>
  191.         <TextBox x:Name="txtDescription" Grid.Row="2" Grid.Column="0" Grid.ColumnSpan="2" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Text="Enter description" Foreground="Gray" TextWrapping="Wrap"/>
  192.         <Button x:Name="btnAddServer" Grid.Row="4" Grid.Column="0" Grid.ColumnSpan="2" Content="Add Server"/>
  193.     </Grid>
  194. </Window>
  195. "@
  196.  
  197.                $XMLReader = (New-Object System.Xml.XmlNodeReader $addWindowXAML)
  198.                $syncHash.AddWindow = [Windows.Markup.XamlReader]::Load($XMLReader)
  199.                
  200.                $syncHash.AddWindowControls = @{}
  201.                $addWindowXAML.SelectNodes("//*[@*[contains(translate(name(.),'n','N'),'Name')]]") | % {
  202.                    $syncHash.AddWindowControls.Add($_.Name, $syncHash.AddWindow.FindName($_.Name))
  203.                }
  204.  
  205.                # Handler to clear this window's controls out on close
  206.                $syncHash.AddWindow.Add_Closed({
  207.                    $syncHash.AddWindowControls = $null
  208.                    $syncHash.AddWindow = $null
  209.                })
  210.  
  211.                # Handler to add new server to inventory
  212.                $syncHash.AddWindowControls.btnAddServer.Add_Click({
  213.                    $txtServerName = $syncHash.AddWindowControls.txtServerName
  214.                    $txtInstanceName = $syncHash.AddWindowControls.txtInstanceName
  215.                    $txtDescription = $syncHash.AddWindowControls.txtDescription
  216.                    
  217.                    If($syncHash.AddWindowControls.txtServerName.Text -eq "") {
  218.                        [System.Windows.MessageBox]::Show("Server name cannot be blank!", "Error", "OK", "Error")
  219.                        Return
  220.                    }
  221.                    ElseIf($syncHash.Servers.ServerName -contains $txtServerName.Text) {
  222.                        [System.Windows.MessageBox]::Show("Server already exists in inventory!", "Error", "OK", "Error")
  223.                        Return
  224.                    }
  225.  
  226.                    $syncHash.AddWindowControls.btnAddServer.IsEnabled = $false
  227.  
  228.  
  229.                    If($txtInstanceName.Text -eq "MSSQLSERVER") {
  230.                        $ServerName = $txtServerName.Text
  231.                    }
  232.                    Else {
  233.                        $ServerName = "$($txtServerName.Text)\$($txtInstanceName.Text)"
  234.                    }
  235.  
  236.                    If($txtDescription.Text -eq "Enter description") { $Description = "" } Else { $Description = $txtDescription.Text }
  237.  
  238.                    $ServerData = [PSCustomObject]@{ServerName=$ServerName; IPAddress=""; Description=$Description; Version=""; SP=""; Edition=""; WindowsOS=""}
  239.                    $syncHash.Servers += $ServerData
  240.                    $syncHash.MainWindowControls.dgvInventory.Dispatcher.Invoke(
  241.                        [action]{
  242.                            $SortDesc = $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions[0]
  243.                            If($SortDesc -eq $null) {
  244.                                $SortDesc = New-Object System.ComponentModel.SortDescription("ServerName", "Ascending")
  245.                            }
  246.  
  247.                            $syncHash.MainWindowControls.dgvInventory.ItemsSource = $syncHash.Servers
  248.                            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Clear()
  249.                            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Add($SortDesc)
  250.                            $columnFields = $syncHash.MainWindowControls.dgvInventory.Columns.Binding.Path.Path
  251.                            # This is super hacky but it will freeze if I try to pipe to where-object or foreach...
  252.                            $colIdx = 0
  253.                            while($colIdx -lt $columnFields.Count) {
  254.                                if($columnFields[$colIdx] -eq $SortDesc.PropertyName) {
  255.                                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $SortDesc.Direction
  256.                                }
  257.                                else {
  258.                                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $null
  259.                                }
  260.                            }
  261.                            
  262.                        },
  263.                        "Normal"
  264.                    )
  265.                    $syncHash.ServersChanged = $true
  266.                    $syncHash.AddWindow.Close()
  267.                    [System.Windows.MessageBox]::Show("Added server $($ServerData.ServerName)", "Success", "OK", "Information")
  268.                })
  269.  
  270.                # Handlers for default text on instance name
  271.                $syncHash.AddWindowControls.txtInstanceName.Add_GotFocus({
  272.                    $txtInstanceName = $syncHash.AddWindowControls.txtInstanceName
  273.  
  274.                    If($txtInstanceName.Text -eq "MSSQLSERVER") {
  275.                        $txtInstanceName.Text = ""
  276.                        $txtInstanceName.Foreground = "Black"
  277.                    }
  278.                })
  279.  
  280.                $syncHash.AddWindowControls.txtInstanceName.Add_LostFocus({
  281.                    $txtInstanceName = $syncHash.AddWindowControls.txtInstanceName
  282.  
  283.                    If($txtInstanceName.Text -eq "") {
  284.                        $txtInstanceName.Text = "MSSQLSERVER"
  285.                        $txtInstanceName.Foreground = "Gray"
  286.                    }
  287.                })
  288.                
  289.                # Handlers for default text on description
  290.                $syncHash.AddWindowControls.txtDescription.Add_GotFocus({
  291.                    $txtDescription = $syncHash.AddWindowControls.txtDescription
  292.  
  293.                    If($txtDescription.Text -eq "Enter description") {
  294.                        $txtDescription.Text = ""
  295.                        $txtDescription.Foreground = "Black"
  296.                    }
  297.                })
  298.  
  299.                $syncHash.AddWindowControls.txtDescription.Add_LostFocus({
  300.                    $txtDescription = $syncHash.AddWindowControls.txtDescription
  301.  
  302.                    If($txtDescription.Text -eq "") {
  303.                        $txtDescription.Text = "Enter description"
  304.                        $txtDescription.Foreground = "Gray"
  305.                    }
  306.                })
  307.  
  308.                $syncHash.AddWindow.ShowDialog() | Out-Null
  309.            }
  310.        })
  311.        $PowerShell.Runspace = $newRunspace
  312.        [void]$Jobs.Add((
  313.            [pscustomobject]@{
  314.                PowerShell = $PowerShell
  315.                Runspace = $PowerShell.BeginInvoke()
  316.            }
  317.        ))
  318.    })
  319.  
  320.    $syncHash.MainWindowControls.btnModifyServer.Add_Click({
  321.        $newRunspace =[runspacefactory]::CreateRunspace()
  322.        $newRunspace.ApartmentState = "STA"
  323.        $newRunspace.ThreadOptions = "ReuseThread"          
  324.        $newRunspace.Open()
  325.        $newRunspace.SessionStateProxy.SetVariable("SyncHash",$SyncHash)
  326.        $PowerShell = [PowerShell]::Create().AddScript({
  327.            If($syncHash.ModifyWindow -ne $null) {
  328.                $syncHash.ModifyWindow.Dispatcher.Invoke(
  329.                    [action]{ $syncHash.ModifyWindow.Close() },
  330.                    "Normal"
  331.                )
  332.                $syncHash.ModifyWindow = $null
  333.                $syncHash.ModifyWindowControls = $null
  334.            }
  335.  
  336.            $script:ServerItem = $null
  337.            $syncHash.MainWindow.Dispatcher.Invoke({ $script:ServerItem = $syncHash.MainWindowControls.dgvInventory.SelectedItem },"Normal")
  338.            If($ServerItem -eq $null) {
  339.                [System.Windows.MessageBox]::Show("You must select a server to modify", "Information", "OK", "Information")
  340.                Return
  341.            }
  342.  
  343.            $ServerName = $script:ServerItem.ServerName
  344.            $ServerDesc = $script:ServerItem.Description
  345.            If($ServerDesc -eq "") {
  346.                $ServerDesc = "Enter description"
  347.                $DescColor = "Gray"
  348.            }
  349.            Else {
  350.                $DescColor = "Black"
  351.            }
  352.  
  353.            [xml]$modifyWindowXAML = @"
  354. <Window
  355.     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  356.     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  357.     Title="Modify SQL Server ($ServerName)" Height="220" Width="270" Background="#323242" ResizeMode="NoResize"
  358.     Icon="$($syncHash.InventoryFileLocation)\favicon.ico">
  359. <Grid Margin="10">
  360.     <Grid.RowDefinitions>
  361.         <RowDefinition Height="Auto"/>
  362.         <RowDefinition Height="*"/>
  363.         <RowDefinition Height="5"/>
  364.         <RowDefinition Height="Auto"/>
  365.     </Grid.RowDefinitions>
  366.     <Grid.ColumnDefinitions>
  367.         <ColumnDefinition Width="100"/>
  368.         <ColumnDefinition Width="*"/>
  369.     </Grid.ColumnDefinitions>
  370.     <Label Grid.Row="0" Grid.Column="0" Content="Server Name:" Foreground="White" FontWeight="Bold" HorizontalContentAlignment="Right" />
  371.     <TextBox x:Name="txtServerName" Grid.Row="0" Grid.Column="1" Height="20" HorizontalAlignment="Stretch" IsEnabled="False" Text="$ServerName"/>
  372.     <TextBox x:Name="txtDescription" Grid.Row="1" Grid.Column="0" Grid.ColumnSpan="2" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Foreground="$DescColor" TextWrapping="Wrap" AcceptsReturn="True"/>
  373.     <Button x:Name="btnUpdateServer" Grid.Row="3" Grid.Column="0" Grid.ColumnSpan="2" Content="Update Server $ServerName"/>
  374. </Grid>
  375. </Window>
  376. "@
  377.  
  378.            $XMLReader = (New-Object System.Xml.XmlNodeReader $modifyWindowXAML)
  379.            $syncHash.ModifyWindow = [Windows.Markup.XamlReader]::Load($XMLReader)
  380.                
  381.            $syncHash.ModifyWindowControls = @{}
  382.            $modifyWindowXAML.SelectNodes("//*[@*[contains(translate(name(.),'n','N'),'Name')]]") | % {
  383.                $syncHash.ModifyWindowControls.Add($_.Name, $syncHash.ModifyWindow.FindName($_.Name))
  384.            }
  385.  
  386.            # Handler to load the server description into the textbox
  387.            # Needed to handle the newlines
  388.            $syncHash.ModifyWindow.Add_Loaded({
  389.                $syncHash.ModifyWindowControls.txtDescription.Text = $ServerDesc
  390.            })
  391.  
  392.            # Handler to clear this window's controls out on close
  393.            $syncHash.ModifyWindow.Add_Closed({
  394.                $syncHash.ModifyWindowControls = $null
  395.                $syncHash.ModifyWindow = $null
  396.            })
  397.  
  398.            # Handler for default description text
  399.            $syncHash.ModifyWindowControls.txtDescription.Add_GotFocus({
  400.                $txtDescription = $syncHash.ModifyWindowControls.txtDescription
  401.  
  402.                If($txtDescription.Text -eq "Enter description") {
  403.                    $txtDescription.Text = ""
  404.                    $txtDescription.Foreground = "Black"
  405.                }
  406.            })
  407.  
  408.            $syncHash.ModifyWindowControls.txtDescription.Add_LostFocus({
  409.                $txtDescription = $syncHash.ModifyWindowControls.txtDescription
  410.  
  411.                If($txtDescription.Text -eq "") {
  412.                    $txtDescription.Text = "Enter description"
  413.                    $txtDescription.Foreground = "Gray"
  414.                }
  415.            })
  416.  
  417.            # Handler for update button
  418.            $syncHash.ModifyWindowControls.btnUpdateServer.Add_Click({
  419.                $Description = $syncHash.ModifyWindowControls.txtDescription.Text
  420.                $ServerName = $syncHash.ModifyWindowControls.txtServerName.Text
  421.  
  422.                If($Description -eq "Enter description") { $Description = "" }
  423.                ($syncHash.Servers | ? { $_.ServerName -eq $ServerName }).Description = $Description
  424.  
  425.                $syncHash.MainWindow.Dispatcher.Invoke([action]{
  426.                    $SortDesc = $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions[0]
  427.                    If($SortDesc -eq $null) {
  428.                        $SortDesc = New-Object System.ComponentModel.SortDescription("ServerName", "Ascending")
  429.                    }
  430.  
  431.                    $syncHash.MainWindowControls.dgvInventory.ItemsSource = $syncHash.Servers
  432.                    $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Add($SortDesc)
  433.                    $columnFields = $syncHash.MainWindowControls.dgvInventory.Columns.Binding.Path.Path
  434.                    # This is super hacky but it will freeze if I try to pipe to where-object or foreach... probably some error somewhere that I can't see
  435.                    $colIdx = 0
  436.                    while($colIdx -lt $columnFields.Count) {
  437.                        if($columnFields[$colIdx] -eq $SortDesc.PropertyName) {
  438.                            $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $SortDesc.Direction
  439.                        }
  440.                        else {
  441.                            $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $null
  442.                        }
  443.                    }
  444.                }, "Normal")
  445.  
  446.                $syncHash.ServersChanged = $true
  447.                $syncHash.ModifyWindow.Close()
  448.                [System.Windows.MessageBox]::Show("Successfully updated server $ServerName", "Success", "OK", "Information")
  449.            })
  450.                    
  451.            $syncHash.ModifyWindow.ShowDialog() | Out-Null
  452.        })
  453.        $PowerShell.Runspace = $newRunspace
  454.        [void]$Jobs.Add((
  455.            [pscustomobject]@{
  456.                PowerShell = $PowerShell
  457.                Runspace = $PowerShell.BeginInvoke()
  458.            }
  459.        ))
  460.    })
  461.  
  462.    $syncHash.MainWindowControls.btnRemoveServer.Add_Click({
  463.        $SelectedItems = $syncHash.MainWindowControls.dgvInventory.SelectedItems
  464.        If($SelectedItems.Count -eq 0) {
  465.            [System.Windows.MessageBox]::Show("You must select a server to remove", "Information", "OK", "Information")
  466.            Return
  467.        }
  468.  
  469.        $ServerNames = $SelectedItems.ServerName
  470.        $ConfirmDelete = [System.Windows.MessageBox]::Show("Are you sure you wish to delete the following servers?`n`n$($ServerNames -join "`n")", "Warning", "YesNo", "Warning")
  471.        If($ConfirmDelete -eq "Yes") {
  472.            $SortDesc = $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions[0]
  473.            If($SortDesc -eq $null) {
  474.                $SortDesc = New-Object System.ComponentModel.SortDescription("ServerName", "Ascending")
  475.            }
  476.            $syncHash.Servers = ($syncHash.Servers | ? { $_.ServerName -notin $ServerNames })
  477.            $syncHash.MainWindowControls.dgvInventory.ItemsSource = $syncHash.Servers
  478.            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Clear()
  479.            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Add($SortDesc)
  480.            $columnFields = $syncHash.MainWindowControls.dgvInventory.Columns.Binding.Path.Path
  481.            # This is super hacky but it will freeze if I try to pipe to where-object or foreach... probably some error somewhere that I can't see
  482.            $colIdx = 0
  483.            while($colIdx -lt $columnFields.Count) {
  484.                if($columnFields[$colIdx] -eq $SortDesc.PropertyName) {
  485.                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $SortDesc.Direction
  486.                }
  487.                else {
  488.                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $null
  489.                }
  490.            }
  491.  
  492.            $syncHash.ServersChanged = $true
  493.            [System.Windows.MessageBox]::Show("Successfully removed $($ServerNames.Count) servers", "Success", "OK", "Information")
  494.        }
  495.    })
  496.  
  497.    $syncHash.MainWindowControls.btnRescanServer.Add_Click({
  498.        $SelectedItems = $syncHash.MainWindowControls.dgvInventory.SelectedItems
  499.        If($SelectedItems.Count -eq 0) {
  500.            [System.Windows.MessageBox]::Show("You must select a server to rescan", "Information", "OK", "Information")
  501.            Return
  502.        }
  503.  
  504.        $ServerNames = ($SelectedItems.ServerName | % { ($_ -split "\\")[0] } | Select -Unique)
  505.        $InstanceNames = $SelectedItems.ServerName
  506.        $ConfirmScan = [System.Windows.MessageBox]::Show("Are you sure you wish to rescan the following servers? This could a few minutes and the UI may appear unresponsive!`n`n$($InstanceNames -join "`n")", "Warning", "YesNo", "Warning")
  507.        If($ConfirmScan -eq "Yes") {
  508.            $syncHash.MainWindow.Title += " - Scanning..."
  509.  
  510.            $SqlProperties = ($InstanceNames | % { @{InstanceName=$_} } )
  511.            Get-DbaInstanceProperty -SqlInstance $InstanceNames | ? { $_.Name -in @("ComputerName", "Edition", "VersionString", "ProductLevel") } | % {
  512.                $Instance = $_.ComputerName
  513.                If($_.InstanceName -ne "MSSQLSERVER") { $Instance = "$Instance\$($_.InstanceName)" }
  514.                ($SqlProperties | ? { $_.InstanceName -eq $Instance }).Add($_.Name, $_.Value)
  515.            }
  516.            $OperatingSystems = (Get-DbaOperatingSystem -ComputerName $ServerNames | Select ComputerName, OSVersion)
  517.            $IPAddresses = ($ServerNames | % { @{ComputerName=$_; IPAddress=(Resolve-DnsName $_).IPAddress} })
  518.  
  519.            $InstanceNames | % {
  520.                $ThisInstance = $_
  521.                $ComputerName = ($ThisInstance -split "\\")[0]
  522.                $Server = ($syncHash.Servers | ? { $_.ServerName -eq $ThisInstance })
  523.                $SQLInstanceProperties = ($SqlProperties | ? { $_.InstanceName -eq $ThisInstance })
  524.                $Server.IPAddress = ($IPAddresses | ? { $_.ComputerName -eq $ComputerName }).IPAddress
  525.                $Server.Version = $SQLInstanceProperties.VersionString
  526.                $Server.SP = $SQLInstanceProperties.ProductLevel
  527.                $Server.Edition = $SQLInstanceProperties.Edition
  528.                $Server.WindowsOS = ($OperatingSystems | ? { $_.ComputerName -eq "$ComputerName" -or $_.ComputerName -eq "$ComputerName.domain.local" }).OSVersion.Trim()
  529.            }
  530.            
  531.            $SortDesc = $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions[0]
  532.            If($SortDesc -eq $null) {
  533.                $SortDesc = New-Object System.ComponentModel.SortDescription("ServerName", "Ascending")
  534.            }
  535.            $syncHash.MainWindowControls.dgvInventory.ItemsSource = $syncHash.Servers
  536.            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Clear()
  537.            $syncHash.MainWindowControls.dgvInventory.Items.SortDescriptions.Add($SortDesc)
  538.            $columnFields = $syncHash.MainWindowControls.dgvInventory.Columns.Binding.Path.Path
  539.            # This is super hacky but it will freeze if I try to pipe to where-object or foreach... probably some error somewhere that I can't see
  540.            $colIdx = 0
  541.            while($colIdx -lt $columnFields.Count) {
  542.                if($columnFields[$colIdx] -eq $SortDesc.PropertyName) {
  543.                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $SortDesc.Direction
  544.                }
  545.                else {
  546.                    $syncHash.MainWindowControls.dgvInventory.Columns[$colIdx++].SortDirection = $null
  547.                }
  548.            }
  549.  
  550.            $syncHash.ServersChanged = $true
  551.            $syncHash.MainWindowControls.dgvInventory.Items.Refresh()
  552.            $syncHash.MainWindow.Title = "SQL Server Inventory"
  553.            [System.Windows.MessageBox]::Show("Successfully rescanned servers", "Success", "OK", "Information")
  554.        }
  555.    })
  556.  
  557.  
  558.    # Handler for double-clicking (modifying) inventory item
  559.    $syncHash.MainWindowControls.dgvInventory.Add_MouseDoubleClick({
  560.        If($syncHash.InventoryLocked -eq $false) {
  561.            $syncHash.MainWindowControls.btnModifyServer.RaiseEvent((New-Object -TypeName System.Windows.RoutedEventArgs -ArgumentList $([System.Windows.Controls.Button]::ClickEvent)))
  562.        }
  563.    })
  564.  
  565.  
  566.    $syncHash.MainWindow.Add_Closing({
  567.        If($syncHash.ServersChanged) {
  568.            $Confirm = [System.Windows.MessageBox]::Show("Do you want to save before exiting?", "Warning", "YesNoCancel", "Exclamation")
  569.            If($Confirm -eq "Cancel") {
  570.                $_.Cancel = $true
  571.                Return
  572.            }
  573.            ElseIf($Confirm -eq "Yes") {
  574.                If(Test-Path "$($syncHash.InventoryFileLocation)\servers.csv.bak") { Remove-Item -Path "$($syncHash.InventoryFileLocation)\servers.csv.bak" }
  575.                Rename-Item -Path "$($syncHash.InventoryFileLocation)\servers.csv" -NewName "servers.csv.bak"
  576.                $syncHash.Servers | Export-Csv -Path "$($syncHash.InventoryFileLocation)\servers.csv" -NoTypeInformation
  577.            }
  578.        }
  579.        If($syncHash.InventoryLocked -eq $false) {
  580.            Remove-Item "$($syncHash.InventoryFileLocation)\inv.loc"
  581.        }
  582.  
  583.        $syncHash.Keys | ? { $_.EndsWith("Window") -and $_ -ne "MainWindow" } | % {$Window = $_; $syncHash.$Window.Dispatcher.Invoke({[action]$syncHash.$Window.Close()},"Normal")}
  584.  
  585.        Stop-Process -Id $syncHash.ParentPID
  586.    })
  587.  
  588.    $syncHash.MainWindow.ShowDialog() | Out-Null
  589.    $syncHash.Error = $Error
  590. })
  591.  
  592. $guiCmd.Runspace = $newRunspace
  593. $data = $guiCmd.BeginInvoke()
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!