Create Excel Matrix showing Blackmagic VideoHub routing

Get-VideoHubRouting.ps1

#Get video hub properties and add to Excel file
# Author: Ian Morrish
# Website: https://ianmorrish.wordpress.com
# tested on Windows 10 running in ISE with Excel 2016
# update the IP address and path to included send-tcprequest.ps1 scrit as required

$VideoHupIP = "127.0.0.1"
$hubcommand = @"
        VIDEO OUTPUT ROUTING:
        `r`n
"@
$result =  ..\VideoHub\send-tcprequest.ps1 -remoteHost $VideoHupIP -port 9990  -inputObject $hubcommand

$result = @($result -split '[\r\n]+')
  # Name
  $Name_line = $result | Select-String -Pattern 'Model Name'
  $Name = ($Name_line -split ":")[-1].Trim()
  # Video Inputs
  $VideoInputs_line = $result | Select-String -Pattern 'Video Inputs'
  $VideoInputs = ($VideoInputs_line -split ":")[-1].Trim()
  # Video Outputs
  $VideoOutputs_line = $result | Select-String -Pattern 'Video Outputs'
  $VideoOutputs = ($VideoOutputs_line -split ":")[-1].Trim()
  # Video Monitoring Outputs
  $VideoMonitoringOutputs_line = $result | Select-String -Pattern 'Video Monitoring Outputs'
  $VideoMonitoringOutputs = ($VideoMonitoringOutputs_line -split ":")[-1].Trim()
  # Serial Ports
  $SerialPorts_line = $result | Select-String -Pattern 'Serial Ports'
  $SerialPorts = ($SerialPorts_line -split ":")[-1].Trim()
  #Input Labels
  $InputLabels = @{}
  $InputLabels_Line = $result | Select-String -Pattern 'INPUT LABELS:'
  for($i=0; $i -lt $VideoInputs; $i++){
    $InputLabels_Item = $result[$InputLabels_Line.LineNumber + $i].Split(" ",2)
    $InputLabels.Add([convert]::ToInt32($InputLabels_Item[0])+1,$InputLabels_Item[1])
  }
  #Output Labels
  $OutputLabels = @{}
  $OutputLabels_Line = $result | Select-String -Pattern 'OUTPUT LABELS:'
  for($i=0; $i -lt $VideoOutputs; $i++){
    $OutputLabels_Item = $result[$OutputLabels_Line[0].LineNumber + $i].Split(" ",2)
    $OutputLabels.Add([convert]::ToInt32($OutputLabels_Item[0])+1,$OutputLabels_Item[1])
  }
  #Monitoring Output Labels
  $MonitoringOutputLabels = @{}
  $MonitoringOutputLabels_Line = $result | Select-String -Pattern 'MONITORING OUTPUT LABELS:'
  if($MonitoringOutputLabels_Line.Count > 1){
    for($i=0; $i -lt $VideoMonitoringOutputs; $i++){
      $MonitoringOutputLabels_Item = $result[$MonitoringOutputLabels_Line[1].LineNumber + $i].Split(" ",2)
      $MonitoringOutputLabels.Add([convert]::ToInt32($MonitoringOutputLabels_Item[0])+1,$MonitoringOutputLabels_Item[1])
    }
  }
  #Video Output Routing
$VideoOutputRouting = @{}
  $VideoOutputRouting_Line = $result | Select-String -Pattern 'VIDEO OUTPUT ROUTING:'
  for($i=0; $i -lt $VideoInputs; $i++){
    $VideoOutputRouting_Item = $result[$VideoOutputRouting_Line[0].LineNumber + $i].Split(" ",2)
    $VideoOutputRouting.Add($i,[pscustomobject]@{
      OutputPort = [convert]::ToInt32($VideoOutputRouting_Item[0])+1
      OutputLabel = $OutputLabels.get_item($i+1)
      InputPort = [convert]::ToInt32($VideoOutputRouting_Item[1])+1
      InputLabel = $InputLabels.get_item($i+1)
    })
  }

#Create Excel file
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true

$workbook = $xl.Workbooks.Add()

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Name = $Name 
$serverInfoSheet.Activate() | Out-Null
$serverInfoSheet.Cells.Item(1,1)= "outputs\inputs"
# set columns to input
$row = 1
for($i=1; $i -lt [convert]::ToInt32($VideoInputs)+1; $i++){
  $serverInfoSheet.Cells.Item($row,$i+1)= $InputLabels.get_item($i)
  $serverInfoSheet.Cells.Item($row,$i+1).Orientation = 90
}
# Fill in each row with output
for($r=2; $r -lt [convert]::ToInt32($VideoOutputs)+2; $r++){
  $serverInfoSheet.Cells.Item($r,1)= $OutputLabels.get_item($r-1)
  for($i=1; $i -lt [convert]::ToInt32($VideoInputs)+1; $i++){
    if($VideoOutputRouting[$r-2].InputPort -eq $i){
      $serverInfoSheet.Cells.Item($r,$i+1)="x"
    }
  }
}
$serverInfoSheet.columns.AutoFit()

Send-TcpRequest.ps1

##############################################################################
## Send-TcpRequest.ps1
##
## From Windows PowerShell Cookbook (O'Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Send a TCP request to a remote computer, and return the response.
## If you do not supply input to this script (via either the pipeline, or the
## -InputObject parameter,) the script operates in interactive mode.
##
## Example:
##
## $http = @"
## GET / HTTP/1.1
## Host:search.msn.com
## `n`n
## "@
## 
## $http | Send-TcpRequest search.msn.com 80
##############################################################################
param(
        [string] $remoteHost = "localhost",
        [int] $port = 9990,
        [switch] $UseSSL,
        [string] $inputObject,
        [int] $commandDelay = 10
     )[string] $output = ""

## Store the input into an array that we can scan over. If there was no input,
## then we will be in interactive mode.
$currentInput = $inputObject
if(-not $currentInput)
{
    $SCRIPT:currentInput = @($input)
}
$scriptedMode = [bool] $currentInput
function Main
{
    ## Open the socket, and connect to the computer on the specified port
    if(-not $scriptedMode)
    {
        write-host "Connecting to $remoteHost on port $port"
    }
    trap { Write-Error "Could not connect to remote computer: $_"; exit }
    $socket = new-object System.Net.Sockets.TcpClient($remoteHost, $port)
    if(-not $scriptedMode)
    {
        write-host "Connected. Press ^D followed by [ENTER] to exit.`n"
    }
    $stream = $socket.GetStream()
    
    if($UseSSL)
    {
        $sslStream = New-Object System.Net.Security.SslStream $stream,$false
        $sslStream.AuthenticateAsClient($remoteHost)
        $stream = $sslStream
    }
    $writer = new-object System.IO.StreamWriter $stream
    while($true)
    {
        ## Receive the output that has buffered so far
        $SCRIPT:output += GetOutput
        ## If we're in scripted mode, send the commands,
        ## receive the output, and exit.
        if($scriptedMode)
        {
            foreach($line in $currentInput)
            {
                $writer.WriteLine($line)
                $writer.Flush()
                Start-Sleep -m $commandDelay
                $SCRIPT:output += GetOutput
            }
            break
        }
        ## If we're in interactive mode, write the buffered
        ## output, and respond to input.
        else
        {
            if($output) 
            {
                foreach($line in $output.Split("`n"))
                {
                    write-host $line
                }
                $SCRIPT:output = ""
            }
            ## Read the user's command, quitting if they hit ^D
            $command = read-host
            if($command -eq ([char] 4)) { break; }
            ## Otherwise, Write their command to the remote host 
            $writer.WriteLine($command)
            $writer.Flush()
        }
    }
    ## Close the streams
    $writer.Close()
    $stream.Close()
    ## If we're in scripted mode, return the output
    if($scriptedMode)
    {
        $output
    }
}
## Read output from a remote host
function GetOutput
{
    ## Create a buffer to receive the response
    $buffer = new-object System.Byte[] 1024
    $encoding = new-object System.Text.AsciiEncoding
    $outputBuffer = ""
    $foundMore = $false
    ## Read all the data available from the stream, writing it to the
    ## output buffer when done.
    do
    {
        ## Allow data to buffer for a bit
        start-sleep -m 100
        ## Read what data is available
        $foundmore = $false
        $stream.ReadTimeout = 100
        do
        {
            try
            {
                $read = $stream.Read($buffer, 0, 1024)
                if($read -gt 0)
                {
                    $foundmore = $true
                    $outputBuffer += ($encoding.GetString($buffer, 0, $read))
                }
            } catch { $foundMore = $false; $read = 0 }
        } while($read -gt 0)
    } while($foundmore)
    $outputBuffer
}
. Main