0

I am trying to use below code with date range as Where-Object filter but that is slowing down my output speed as well as unable to export to csv. 

$Prids = get-content -Path C:\Temp\sqltest.txt
foreach ($prid in $prids){
$filterDate = [datetime]::Today.AddDays(-22)
Get-CdPac2000Problems  -PId $Prid  | Where-Object {$_.ClosedDate.Date -ge $filterDate} |ft PID,ClosedDate,ClosedByELID,ResponsibleGroup,ReferredDate -autosize
}

How can I change Where-Object to parameter that looks something like -closedate $variable like I did with -PID? The biggest struggle for me is to creating a datetime variable.

Oxycash
  • 167
  • 12
  • 1
    Depending on what you mean by `slowing down my output speed` that problem is you are using `Format-Table` which has to wait for all objects before they get displayed. That also breaks the objects and prevents `Export-CSV` from working. – Matt Jun 21 '19 at 16:09
  • 1
    About `Format-table`: https://stackoverflow.com/questions/36358047/how-can-i-store-output-from-format-table-for-later-use/36358921#36358921 – Matt Jun 21 '19 at 16:11
  • To achieve exactly what you want, you would need to add that parameter (`-closedate`) to the `Get-CdPac2000Problems` function definition. – AdminOfThings Jun 21 '19 at 16:17

1 Answers1

-1

First of, you should move the $filterDateout of the loop, second you should NEVER use Format-Table, and ABSOLUTELY NEVER in a loop.

Try this:

$filterDate = [datetime]::Today.AddDays(-22)
$Prids = get-content -Path C:\Temp\sqltest.txt
$Result = foreach ($prid in $prids){
    Get-CdPac2000Problems  -PId $Prid  | Where-Object {(Get-Date $_.ClosedDate.Date) -ge $filterDate}
}
$Result | Format-Table PID,ClosedDate,ClosedByELID,ResponsibleGroup,ReferredDate -autosize

One of the most beautyfull things in PowerShell is, that you can interfere directly with all objects. All methods and properties are preserved, when passing the objects to a variable or to the next command in a pipe. Format-Table converts objects to a table - something human readable and something stripped from methods and life.

Axel Andersen
  • 954
  • 1
  • 6
  • 18
  • 3
    If you caution its use you need to at least explain why – Matt Jun 21 '19 at 16:10
  • Done :-) BTW HDYK? – Axel Andersen Jun 21 '19 at 16:29
  • I am going mad why it doesnt show any error or output. – Oxycash Jun 21 '19 at 17:36
  • How do I know what? – Matt Jun 21 '19 at 17:43
  • starting to think there is some problem with connection. will try asap it is up and let you know :) – Oxycash Jun 21 '19 at 18:48
  • It all depends on, what is in your ClosedDate.Date parameter. If it is a string, the current answer is good, if it's a datetime objece, you need to ditch ```(getdate``` and ```)``` – Axel Andersen Jun 21 '19 at 19:05
  • I think it is a datetime object. [DateTime]::Today.AddDays(-1) works fine for me except I believe it kind of slows down my output time. The DB has the date column format as "DD-MM-YYYY 00:00:00.000" (thats the format that worked for me when running db query from SQL Server Management STudio) – Oxycash Jun 21 '19 at 20:40
  • How many prids do you have, and what does the ```Get-CdPac2000Problems``` command do? Maybe you need to look for your time consuming task in the ```Get-CdPac2000Problems``` command. – Axel Andersen Jun 21 '19 at 20:45