CODEWITHSUNDEEP
powershell
JM1
JM1

Reputation: 1715

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

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: enter image description here

Thanks for the help.

Upvotes: 2

Views: 1110

Answers (2)

JM1
JM1

Reputation: 1715

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 ' ')))
      }
    }

Upvotes: 0

mklement0
mklement0

Reputation: 438283

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%

Upvotes: 3

Related Questions