Combining arrays

PowerShell Help
1

I’ve looked through the questions asked on this topic, including one I asked before, but none help me with this particular issue.
I have two arrays, one has database properties, the other has data file properties. Some databases have more than one datafile. How can I combine the two arrays allowing for occasions when a database has an unknown amount of datafiles?
I’m exporting this to an excel spreadsheet, and I’m hoping that each database has 1 line…there maybe columns that have null values for other databases, that’s ok. The common value in the two arrays is database name.
Here’s my two arrays.

        $Db = $_
        $Databases += [PSCustomObject][ORDERED]@{
        Name = $Db.Name
        CreateDate = $Db.CreateDate
        Owner = $Db.Owner 
        RecoveryModel = $Db.RecoveryModel
        EncryptionEnabled = $Db.EncryptionEnabled
        BrokerEnabled = $Db.DatabaseOptions.BrokerEnabled
        PageVerify = $Db.DatabaseOptions.PageVerify
        TargetRecoveryTime  = $Db.TargetRecoveryTime 
        DatabaseSize = [math]::Round($Db.Size,0)
        DataSpaceUsage = [math]::Round(($Db.DataSpaceUsage * 1024) / 1MB,0)
        IndexSpaceUsage = [math]::Round(($Db.IndexSpaceUsage * 1024) / 1MB,0)
        SpaceAvailable = [math]::Round(($Db.SpaceAvailable * 1024) / 1MB,0)
        LastBackupDate = $Db.LastBackupDate
        LastGoodCheckDbTime  = $Db.LastGoodCheckDbTime 
        PrimaryFilePath  = $Db.PrimaryFilePath 
        LogFileName = $Db.LogFiles.Name
        LogFilePath = $Db.LogFiles.FileName
        LogFileSize = [math]::Round(($Db.LogFiles.Size * 1024) / 1MB,0)
        LogFileUsedSpace = [math]::Round(($Db.LogFiles.UsedSpace * 1024) / 1MB,0)
        LogVolumeFreeSpace = [math]::Round(($Db.LogFiles.VolumeFreeSpace * 1024) / 1MB,0)
        LogFileGrowth = $Db.LogFiles.Growth
        LogFileGrowthType  = $Db.LogFiles.GrowthType 
        }

…and

        $DataFiles = @()
        $Db.FileGroups | %{
            $_.Files | %{ 
                $DataFiles += [PSCustomObject][ORDERED]@{
                Name = $_.Name
                FileName = $_.FileName
                Size = $_.Size
                Growth = $_.Growth
                GrowthType = $_.GrowthType
                MaxSize = $_.MaxSize
                UsedSpace = $_.UsedSpace
                VolumeFreeSpace = $_.VolumeFreeSpace
                FileGroup = $_.Parent.Name
                }
            }
        }
2

The very limited sample code you shared is only useful to you. If you want to export consistently where some have one and others have more than one, csv is not the structure you want. I would recommend JSON or XML. Please provide some sample data/code that we can play with that illustrates what you have and are trying to do. Otherwise I think we won’t be able to provide you with much useful help.

3

If it’s just such a simple case of nesting you could flatten the data structure by simply repeating the properties of the 1st level for every element of the 2nd level.

Of course you have to decide if that’s appropriate for your use case and - depending on the amount of properties you collect - that could have a lot of redundant information in it. :man_shrugging:t3:

Let’s say you have a simple structure like this:

│
├───Database_01
│       DatabaseFile_01
└───Database_02
        DatabaseFile_01
        DatabaseFile_02
        DatabaseFile_03

Then you could use one outer loop iterating over the databases and a nested loop iterating over all database files of each database …

! ! ! ! Pseudo code: ! ! ! !

$DatabaseList = Get-DataBase
$Result =
foreach ($Database in $DatabaseList) {
    $DatabaseFileList = Get-DatabaseFile -Database $Database.Name
    foreach ($DatabaseFile in $DatabaseFileList) {
        [PSCustomObject]@{
            DatabaseName       = $Database.Name
            DatabaseCreateDate = $Database.CreateDate
            DatabaseFileName   = $DatabaseFile.Name
            DatabaseFilePath   = $DatabaseFile.Path
            DatabaseFileSize   = $DatabaseFile.Size
        }
    }
}

Now you could output this to the console …

$Result |
    Format-Table -AutoSize

… or pipe it to whatever next step you need … :wink:

5

Thanks Olaf, I’d tried similar but couldn’t get it right. I’ll go try again, looking at your suggestion I think I was doing it wrong.
btw I’m piping out to excel.