locked
Export-CSV Does not output Server names RRS feed

  • Question

  • The below code does output a CSV file, and it does display all the properties, except for the $Servers Column. It is only displaying the "first" server on the list. Total on the list is about 70 Servers.

    BTW, this is not my code, it was borrowed from T-SQL

    The script does work if I just output the data to the screen, but I cant seem to get all the data to a CSV file correctly. Any help is appreciated, what am I missing? I was hoping my $servers variable will pass all Server names to the CSV list. Am I possibly missing another foreach statement, if so; where exactly? Just trying to have it output ALL of the servers for each result.

        $list = gc C:\Scripts\SQLServerList.txt
        # Load the assemblies
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
        $output = foreach ($Servers in $list)
        # Connect to the instance using SMO
        { $m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "$Servers"
        # Return the protocols and whether or not they're enabled.
        $m.ClientProtocols | select $Servers,DisplayName, IsEnabled } 
        $output | Export-Csv -Path C:\Scripts\SQL.csv  -NoTypeInformation

    Wednesday, March 9, 2016 2:16 PM

Answers

  • [reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | Out-Null
    
    Get-Content C:\Scripts\SQLServerList.txt |
    	ForEach-Object{
    		$m = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($_)
    		$m.ClientProtocols |
    		ForEach-Object{
    			[PSCustomObject]@{
    				ServerName = $m.Name
    				DisplayName = $_.DisplayName
    				IsEnabled=$_.IsEnabled
    			}
    		}
    } #|Export-Csv -Path C:\Scripts\SQL.csv -NoTypeInformation


    \_(ツ)_/



    • Edited by jrv Wednesday, March 9, 2016 3:41 PM
    • Marked as answer by prisoner107 Wednesday, March 9, 2016 3:49 PM
    Wednesday, March 9, 2016 2:51 PM

All replies

  • It doesn't work like that.  Those properties do not exist.  You cannot use $servers in a select statement.

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
    
    Get-Content C:\Scripts\SQLServerList.txt |
    	ForEach-Object{
    		$m = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($server)
    		$m.ClientProtocols
    	} |
    	Format-List


    \_(ツ)_/


    • Edited by jrv Wednesday, March 9, 2016 2:41 PM
    Wednesday, March 9, 2016 2:41 PM
  • [reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | Out-Null
    
    Get-Content C:\Scripts\SQLServerList.txt |
    	ForEach-Object{
    		$m = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($_)
    		$m.ClientProtocols |
    		ForEach-Object{
    			[PSCustomObject]@{
    				ServerName = $m.Name
    				DisplayName = $_.DisplayName
    				IsEnabled=$_.IsEnabled
    			}
    		}
    } #|Export-Csv -Path C:\Scripts\SQL.csv -NoTypeInformation


    \_(ツ)_/



    • Edited by jrv Wednesday, March 9, 2016 3:41 PM
    • Marked as answer by prisoner107 Wednesday, March 9, 2016 3:49 PM
    Wednesday, March 9, 2016 2:51 PM
  • I understand that now and this is great for outputting the server names, but it only gets the desired results for the Single Server im running it against.

    Basically, the end result is outputting all of my servers from the list and the last 3 output lines gives me the Servername,DisplayName,IsEnabled for a single Server, not ALL of my servers.

    Wednesday, March 9, 2016 3:26 PM
  • Sorry - copy it again.  When I paste code here characters disappear. The ones at the end of a line most frequently.  This time it was a | that got lost.


    \_(ツ)_/

    Wednesday, March 9, 2016 3:42 PM
  • That was it. Will study up on what was learned today. 

    this post has been answered. Thank you!

    Wednesday, March 9, 2016 3:49 PM