Powershell apply filter to date hours mins filter

user_3657339 0
Viewed 120

Below command runs fine, and give results- using powershell

Invoke-DbaWhoIsActive -SqlInstance prod  | Select-object 'dd hh:mm:ss.mss',  login_name, wait_info, host_name, sql_text

dd hh:mm:ss.mss : 00 02:36:54.540
login_name      : sa
wait_info       : 
host_name       : Prod
sql_text        : <?query --
                  begin tran
                  --?>

How to apply filter to dd hh:mm:ss.mss - to show results only greater than 15 mins.

powershell
3 Answers

You can parse the dd hh:mm:ss.mss property values as [timespan] (System.TimeSpan) instances, whose .TotalMinutes property you can then filter by (via the Where-Object cmdlet):

Invoke-DbaWhoIsActive -SqlInstance prod | Where-Object { 
  ([timespan] ($_.'dd hh:mm:ss.mss' -replace ' ', '.')).TotalMinutes -gt 15  
} | Select-Object 'dd hh:mm:ss.mss',  login_name, wait_info, host_name, sql_text

The above replaces the space in strings such as 00 02:36:54.540 with a period (.), which results in a string that you can cast directly to [timespan].

user_453750

Try this:

Invoke-DbaWhoIsActive -SqlInstance prod  | Select-object @{n='dd hh:mm:ss.mss';e={if(($_.'dd hh:mm:ss.mss'.tostring().split(" ")[1]).split(":")[0])-gt15){$_.'dd hh:mm:ss.mss'}}},login_name, wait_info, host_name, sql_text
user_122698570

What a horrible name for a column header. That said, this should achieve your goal.

Invoke-DbaWhoIsActive -SqlInstance prod |
    Where-Object {($_.'dd hh:mm:ss.mss' -split ':')[1] -gt 15} |
        Select-object 'dd hh:mm:ss.mss',  login_name, wait_info, host_name, sql_text

Simply splitting the property 'dd hh:mm:ss.mss' at the colon :, grabbing the second element [1], and if it's greater than 15 it will be passed onto your select-object statement.

Here's a little test you can run

$var = @'
dd hh:mm:ss.mss
00 02:36:54.540
00 02:12:54.540
00 02:44:54.540
00 02:15:54.540
'@ | convertfrom-csv

$var | Where-Object {($_.'dd hh:mm:ss.mss' -split ':')[1] -gt 15}
user_40853310
Related