How to "Add-Member" in Function "Format-Table" to get new "MemberType" in Powershell?

Question

I can't choose values from a merged table. I'm doing two Oracle queries. Then I put both tables together and calculate new columns. Then I can not choose the calculated values.

I suspect that has something to do with the "MemberType". The newly calculated columns are not shown as "NoteProperty".

Maybe someone knows a good solution?

$tablespace_raw = Invoke-SqlQuery -Query 'SELECT TABLESPACE_NAME, BLOCK_SIZE, CONTENTS from DBA_TABLESPACES' -Stream
Clear-SqlMessage
$tablespace_raw | Format-Table

TABLESPACE_NAME BLOCK_SIZE CONTENTS 
--------------- ---------- -------- 
SYSTEM                8192 PERMANENT
UNDOTBS1              8192 UNDO     
SYSAUX                8192 PERMANENT
TEMP                  8192 TEMPORARY
USERS                 8192 PERMANENT
BMS                   8192 PERMANENT

$tablespace_metrics_raw = Invoke-SqlQuery -Query 'SELECT TABLESPACE_NAME, USED_SPACE, TABLESPACE_SIZE from DBA_TABLESPACE_USAGE_METRICS' -Stream
Clear-SqlMessage
$tablespace_metrics_raw | Format-Table

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE
--------------- ---------- ---------------
BMS                 538952         1024000
SYSAUX               55464         4194302
SYSTEM               65984         4194302
TEMP                     0          128000
UNDOTBS1              1440         4194302
USERS                    8           64000

$tablespace_raw | InnerJoin $tablespace_metrics_raw -On TABLESPACE_NAME | Format-Table -Property *, @{Name="USED_SPACE_Bytes";Expression={[double](($_.USED_SPACE) * ($_.BLOCK_SIZE))}}, 
                         @{Name="TABLESPACE_SIZE_Bytes";Expression={[double](($_.TABLESPACE_SIZE) * ($_.BLOCK_SIZE))}},
                         @{Name="TABLESPACE_Free_Percent";Expression={[int](($_.USED_SPACE) / ($_.TABLESPACE_SIZE) * 100)}}

TABLESPACE_NAME BLOCK_SIZE CONTENTS  USED_SPACE TABLESPACE_SIZE USED_SPACE_Bytes TABLESPACE_SIZE_Bytes TABLESPACE_Free_Percent
--------------- ---------- --------  ---------- --------------- ---------------- --------------------- -----------------------
SYSTEM                8192 PERMANENT      65984         4194302        540540928           34359721984                       2
UNDOTBS1              8192 UNDO            1440         4194302         11796480           34359721984                       0
SYSAUX                8192 PERMANENT      55464         4194302        454361088           34359721984                       1
TEMP                  8192 TEMPORARY          0          128000                0            1048576000                       0
USERS                 8192 PERMANENT          8           64000            65536             524288000                       0
BMS                   8192 PERMANENT     538952         1024000       4415094784            8388608000                      53

$tablespace_raw | Get-Member

   TypeName: System.Management.Automation.PSCustomObject

Name            MemberType   Definition                    
----            ----------   ----------                    
Equals          Method       bool Equals(System.Object obj)
GetHashCode     Method       int GetHashCode()             
GetType         Method       type GetType()                
ToString        Method       string ToString()             
BLOCK_SIZE      NoteProperty decimal BLOCK_SIZE=8192       
CONTENTS        NoteProperty string CONTENTS=PERMANENT     
TABLESPACE_NAME NoteProperty string TABLESPACE_NAME=SYSTEM 


for($i=0;$i-le $tablespace_raw.length-1;$i++){
    $tablespace_raw[$i].TABLESPACE_NAME
    $tablespace_raw[$i].CONTENTS
    $tablespace_raw[$i].BLOCK_SIZE
    $tablespace_raw[$i].USED_SPACE_Bytes   -->> NOTHING!
    $i
}

Answer 1

I don't think I understand the motivation behind doing SQL joins on the client side in PowerShell code. Maybe you need to add some reasoning for that.

But knowing nothing else about the circumstances, would it not be much easier to let the DB server calculate these values?

$sql = '
SELECT
    ts.TABLESPACE_NAME,
    ts.BLOCK_SIZE,
    ts.CONTENTS,
    tsm.USED_SPACE,
    tsm.TABLESPACE_SIZE,
    tsm.USED_SPACE * ts.BLOCK_SIZE USED_SPACE_Bytes,
    tsm.TABLESPACE_SIZE * ts.BLOCK_SIZE TABLESPACE_SIZE_Bytes,
    /* casting to INT, otherwise Invoke-SqlQuery is unhappy */
    CAST(ROUND(tsm.USED_SPACE / tsm.TABLESPACE_SIZE * 100) AS INT) TABLESPACE_Free_Percent
FROM
    DBA_TABLESPACES ts
    INNER JOIN DBA_TABLESPACE_USAGE_METRICS tsm ON tsm.TABLESPACE_NAME = ts.TABLESPACE_NAME
'

$tablespace_info = Invoke-SqlQuery -Query $sql -Stream
foreach ($row in $tablespace_info) {
    $row.TABLESPACE_NAME
    $row.CONTENTS
    $row.BLOCK_SIZE
    $row.USED_SPACE_Bytes
}