PowerShell - How do you do math on two columns in a Select-Object Expression?

Question

I'm trying to come up with a percent column by dividing two columns in a select-object. This is what I'm using:

Get-DbaDbLogSpace -SqlInstance serverName |
  Select-Object ComputerName, InstanceName, SqlInstance, Database, LogSize, 
                LogSpaceUsed, LogSpaceUsedPercent,
                @{Name="PercentFree"; Expression={($_.LogSpaceUsed / $_.LogSize)}} | 
    Format-Table

This returns an 'OB' on the expression column (see pic below). How do I do math with two columns in a Select-Object expression please?

If doing this a different way outside of a Select-Object would be better, I'm open to it.

This is what the data looks like for the above code:

Thanks for the help.

mklement0 · Accepted Answer · 2021-09-24T19:29:47.367

The operands of your calculation appear to be strings with byte multipliers (e.g. 38.99 MB), so you'll have to transform them to numbers in order to perform division on them.

Here's a simplified example:

Note: I'm using Invoke-Expression to transform the strings to numbers, relying on PowerShell's support for byte-multiplier suffixes such as mb in number literals (e.g., 1mb - note that there must be no space before the suffix). While Invoke-Expression (iex) should generally be avoided, it is safe to use if you trust that the relevant property values only ever contain strings such as '38.99 MB'.

[pscustomobject] @{
  Database     = 'db1'
  LogSize      = '11 MB'
  LogSpaceUsed = '3 MB'
} | 
  Format-Table Database, LogSize, LogSpaceUsed, 
    @{
      Name       = "PercentFree"
      Expression = { 
        '{0:P2}' -f ((Invoke-Expression ($_.LogSpaceUsed -replace ' ')) / 
                    (Invoke-Expression ($_.LogSize -replace ' ')))
      }
    }

^{Note that I'm passing the properties, including the calculated one directly to Format-Table - no need for an intermediary Select-Object call. Note that the calculated property outputs the percentage as a formatted string, using the -f operator, so that the number of decimal places can be controlled.}

Output:

Database LogSize LogSpaceUsed PercentFree
-------- ------- ------------ -----------
db1      11 MB   3 MB         27.27%

score 0 · Answer 2 · answered Oct 07 '21 at 11:51

Thanks to @mklement0, this is what works.

  Get-DbaDbLogSpace -SQLInstance servername | Format-Table Database, LogSize, LogSpaceUsed, 
    @{
      Name       = "PercentFree"
      Expression = { 
        '{0:P2}' -f ((Invoke-Expression ($_.LogSpaceUsed -replace ' ')) / 
                    (Invoke-Expression ($_.LogSize -replace ' ')))
      }
    }

PowerShell - How do you do math on two columns in a Select-Object Expression?

2 Answers2