Powershell String to csv

Question

I am working on a script to collect netstat information and store in sql server table. For that, I am using below command to collect netstats.

$data = Invoke-Command -Computername LocalPC -ScriptBlock {netstat -ano}
$data| ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""}

However, The result i am getting is below.

LocalPC,def60bb5-4a27-4d6c-ac13-a08ef0f9b7da,True,76 LocalPC,def60bb5-4a27-4d6c-ac13-a08ef0f9b7da,True,76 LocalPC,def60bb5-4a27-4d6c-ac13-a08ef0f9b7da,True,76 LocalPC,def60bb5-4a27-4d6c-ac13-a08ef0f9b7da,True,76 LocalPC,def60bb5-4a27-4d6c-ac13-a08ef0f9b7da,True,76

I want to get the result as below.

LocalPC,TCP,0.0.0.0:22,0.0.0.0:0,LISTENING,3080 LocalPC,TCP,0.0.0.0:135,0.0.0.0:0,LISTENING,976 LocalPC,TCP,0.0.0.0:445,0.0.0.0:0,LISTENING,4

Does this answer your question? [Get specific value from \`netstat\` command in windows](https://stackoverflow.com/questions/45371228/get-specific-value-from-netstat-command-in-windows) — iRon, Dec 17 '20 at 19:19
"Get-NetTCPConnection -State Listen" Is it equivalent to "netstat -ano" ? — user3115055, Dec 17 '20 at 19:27
[Get-NetTCPConnection](https://learn.microsoft.com/en-us/powershell/module/nettcpip/test-netconnection) has a lot of similar parameters but where it differs most is that it is a native PowerShell cmdlet which outputs objects rather than a text stream. — iRon, Dec 17 '20 at 19:32
But if you really want to use the external NetStat command, have a look at [Is there a way to convert tables of text into a PowerShell Object](https://stackoverflow.com/a/60889277/1701026). e.g. `NetStat -ano | Select -Skip 2 |` [`ConvertFrom-SourceTable`](https://www.powershellgallery.com/packages/ConvertFrom-SourceTable) — iRon, Dec 17 '20 at 19:48

score 0 · Answer 1 · answered Dec 17 '20 at 22:21

Here is a little script that can turn the output of netstat -ano into objects

switch -Regex (netstat -ano){
    'TCP' {
        , -split $_ | ForEach-Object {
            $localaddr,$localport = -split ($_[1] -replace '(^.+):(.+$)','$1 $2')
            $remoteaddr,$remoteport = -split ($_[2] -replace '(^.+):(.+$)','$1 $2')
            [PSCustomObject]@{
                Protocol      = $_[0]
                LocalAddress  = $localaddr
                LocalPort     = $localport
                RemoteAddress = $remoteaddr
                RemotePort    = $remoteport
                ProcessID     = $_[4]
                State         = $_[3]
            }
        }
    }
    'UDP' {
        , -split $_ | ForEach-Object {
            $localaddr,$localport = -split ($_[1] -replace '(^.+):(.+$)','$1 $2')
            $remoteaddr,$remoteport = -split ($_[2] -replace '(^.+):(.+$)','$1 $2')
            [PSCustomObject]@{
                Protocol      = $_[0]
                LocalAddress  = $localaddr
                LocalPort     = $localport
                RemoteAddress = $remoteaddr
                RemotePort    = $remoteport
                ProcessID     = $_[3]
                State         = 'Stateless'
            }
        }
    }
}

You can assign the output of it to a variable and then convert/export that variable to Csv, or in the case of running on a remote machine like you've shown here using Invoke-Command you can use the following

$data = Invoke-Command -ComputerName Win10-Admin -Scriptblock {
    switch -Regex (netstat -ano){
        'TCP' {
            , -split $_ | ForEach-Object {
                $localaddr,$localport = -split ($_[1] -replace '(^.+):(.+$)','$1 $2')
                $remoteaddr,$remoteport = -split ($_[2] -replace '(^.+):(.+$)','$1 $2')
                [PSCustomObject]@{
                    Protocol      = $_[0]
                    LocalAddress  = $localaddr
                    LocalPort     = $localport
                    RemoteAddress = $remoteaddr
                    RemotePort    = $remoteport
                    ProcessID     = $_[4]
                    State         = $_[3]
                }
            }
        }
        'UDP' {
            , -split $_ | ForEach-Object {
                $localaddr,$localport = -split ($_[1] -replace '(^.+):(.+$)','$1 $2')
                $remoteaddr,$remoteport = -split ($_[2] -replace '(^.+):(.+$)','$1 $2')
                [PSCustomObject]@{
                    Protocol      = $_[0]
                    LocalAddress  = $localaddr
                    LocalPort     = $localport
                    RemoteAddress = $remoteaddr
                    RemotePort    = $remoteport
                    ProcessID     = $_[3]
                    State         = 'Stateless'
                }
            }
        }
    }
} -HideComputerName | Select-Object -Property * -ExcludeProperty RunSpaceId

$data | ConvertTo-Csv -NoTypeInformation

Powershell String to csv

1 Answers1