How do I use psobject to export to csv?

PowerShell Help
1

I have the following script that displays the connections of each database in the server

I want to create and export to csv the “server”, “database”, and “connection info” column headers, and basically output the server name,DBName,and connection information to corresponding column.

I looked into PSCustomObject but i am not sure how to implement this exactly in my script. I have so far defined the pscustomobjects but not sure how they get exported to csv

write-host "`r`n            Server            |               DataBase               |       Connection info      `r`n" -foregroundcolor white -backgroundcolor darkyellow

Import-Module SqlServer

$oldAS = New-Object Microsoft.AnalysisServices.Server
$oldAS.connect("server1")

foreach ($db in $oldAS.Databases){
    $compatibility_level = $db.CompatibilityLevel
    if($compatibility_level -lt 1200)
    {
      $OLDdbName = $db.Name
      Write-Host $OLDdbName -Fore green
[PSCustomObject]@{
    DataBase = $OLDdbName
}
      $db.DataSources | ForEach-Object{write-host "$($_.ConnectionString)";
    [PSCustomObject]@{
        "Connection Info" = $_.ConnectionString
              }
}
    }
    else
    {
        $AS = new-Object Microsoft.AnalysisServices.Tabular.Server
        $AS.Connect("server1")

        foreach ($dbt in $AS.Databases | Where-Object{$compatibility_level -ge 1200} ){
          $dbName = $dbt.Name
          Write-Host $dbName
   [PSCustomObject]@{
        DataBase = $dbName
    }
          if(($dbt.model.datasources[0]).GetType().Name -match "ProviderDataSource") 
          {
            write-host "$($dbt.model.datasources[0].ConnectionString)"
            }
          else {
                #$dbt.model.datasources[0].ConnectionDetails.ToString(); #ToJson
                write-host "$($dbt.model.datasources[0].Credential.ToString())"
   [PSCustomObject]@{
        "Connection Info" = "$($dbt.model.datasources[0].Credential.ToString())"
    }
            }
        }
    }
} | Export-Csv -Path .\CONNECTIONS_LOG.csv -NoTypeInformation -Append
2

I don’t have an SQL server to test and I don’t even have experience with SQL servers and databases unfortunately but I’ll try to give you an example how to use a custom object in Powershell

$ServerList = @(

$env:COMPUTERNAME,

[System.Net.Dns]::GetHostName()

& {hostname}

)


foreach ($ComputerName in $ServerList) {

$Session    = New-CimSession -ComputerName $ComputerName

$System     = Get-CimInstance -CimSession $Session -ClassName CIM_ComputerSystem

$OS         = Get-CimInstance -CimSession $Session -ClassName CIM_OperatingSystem

$SystemDisk = Get-CimInstance -CimSession $Session -ClassName CIM_LogicalDisk | Where-Object -Property DeviceID -EQ -Value ‘C:’

[PSCustomObject]@{

ComputerName          = $System.Name

RAM                   = $System.TotalPhysicalMemory

Manufacturer          = $System.Manufacturer

OS                    = $OS.Caption

OSVersion             = $OS.Version

LastBootUpTime        = $OS.LastBootUpTime

SystemVolumeName      = $SystemDisk.VolumeName

SystemVolumeSize      = $SystemDisk.Size

SystemVolumeFreeSpace = $SystemDisk.FreeSpace

}

}

Usualy you use a [PSCustomObject] to combine two or more sources of information in one output. In this case I use 3 different cim queries and combine them as you can see.
Does this make it a little more clear for you? And of course you can pipe the output to whatever further step you want or you collect the results in a variable by placing a variable assignment in front of the foreach loop.