Combile filter criteria DueDateTime and FirstResponseDueDateTime

[pbundschuh]

Hi, currently I have two commands for getting a list of escalating tickets:

$DueFrom = Get-Date
$DueTo = $DueFrom.AddHours(20)

$tickets_followup = Get-AtwsTicket -Filter {Status -ne '5' -and Priority -ne '3' -and Priority -ne '5' -and QueueID -ne '29682833' -and (DueDateTime -gt $DueFrom -and DueDateTime -lt $DueTo) } | Sort-Object -Property DueDateTime

$tickets_firstresponse = Get-AtwsTicket -Filter {Status -ne '5' -and Priority -ne '3' -and Priority -ne '5' -and QueueID -ne '29682833' -and (FirstResponseDueDateTime -gt $DueFrom -and FirstResponseDueDateTime -lt $DueTo) } | Sort-Object -Property FirstResponseDueDateTime

So I get two results, but I need them in one. How can I combine the filter-Arguments, so that I have only one result?

[bjowol]

we got the power of powershell to help us with this.

Create a collection/array if you hvae a small set of tickets to work with. Else, use $myDotNetList = system.collections.generic.list[autotask.ticket]::new.

then do $tickets followup | % { $myDotNetList. add($. _) and same for the first response tickets.

the result is an array of tickets as it would be from the module.

I did not study your filter clauses, but the parameter sets might give you more options. The filter clauses Is not something I use at all, just parameters and splatting.

[klemmestad]

Working with collections in place of arrays like bjowol suggests will usually speed up the rest of your code, in particular if your queries return a lot of objects. To solve your problem at the query level you can combine the two filters by adding another level of parenthesis like so:

((DueDateTime -gt $DueFrom -and DueDateTime -lt $DueTo) -or (FirstResponseDueDateTime -gt $DueFrom -and FirstResponseDueDateTime -lt $DueTo))

Your full query would look something like:

$DueFrom = Get-Date
$DueTo = $DueFrom.AddHours(20)

$tickets_combined = Get-AtwsTicket -Filter {Status -ne '5' -and Priority -ne '3' -and Priority -ne '5' -and QueueID -ne '29682833' -and ((DueDateTime -gt $DueFrom -and DueDateTime -lt $DueTo) -or (FirstResponseDueDateTime -gt $DueFrom -and FirstResponseDueDateTime -lt $DueTo)) }

This kind of combined query is only possible with the -Filter clause. Any of your two queries could have been specified using parameters, but not when you need to combine -and with -or. You will get a performance boost from saving a query to the API and have the SQL server at their side do the combining, but this usually will not be very noticeable while running batch jobs.

All this said, the easiest would probably be to just add the two arrays together using powershell as it is usually easier to debug queries when they are split up. Do whatever fits your needs and best suits the rest of your code 😉

$tickets_combined = $tickets_followup + $tickets_firstresponse