Get-DbaAgentHistory - filter by outcome types

[zikato]

Summary of new feature

Allow filtering by agent JobHistory Outcome type (Cancelled, Failed, InProgress, Retry, Succeeded or Unknown) in Get-DbaAgentHistory.

Proposed technical details (if applicable)

@andreasjordan wrote on slack: The command uses Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter to filter on the server. This class has a property OutcomeTypes of type Microsoft.SqlServer.Management.Smo.Agent.CompletionResult, which can be Cancelled, Failed, InProgress, Retry, Succeeded or Unknown. So filtering for one of then would be very easy. The command Get-SqlAgentJobHistory has a paramater -OutcomesType: https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlagentjobhistory?view=sqlserver-ps

[wsmelton]

This is already doable piping to where-object is it not?

[zikato]

It is, but I want to filter server-side. Let's say I want to get failed jobs and steps, I have for example 2 million rows with status success and 10 rows with status failed. I want to get only those 10 rows from the server - not 2 million which I'll pipe into where-object.

[andreasjordan]

I started coding - that's the easy part...

But:

The filter property is named "OutcomeTypes" (singular outcome, plural type):

   TypeName: Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter

Name               MemberType Definition                                                                       
----               ---------- ----------                                                                       
Equals             Method     bool Equals(System.Object obj)                                                   
GetHashCode        Method     int GetHashCode()                                                                
GetType            Method     type GetType()                                                                   
ToString           Method     string ToString()                                                                
EndRunDate         Property   datetime EndRunDate {get;set;}                                                   
JobID              Property   guid JobID {get;set;}                                                            
JobName            Property   string JobName {get;set;}                                                        
MinimumRetries     Property   int MinimumRetries {get;set;}                                                    
MinimumRunDuration Property   int MinimumRunDuration {get;set;}                                                
OldestFirst        Property   bool OldestFirst {get;set;}                                                      
OutcomeTypes       Property   Microsoft.SqlServer.Management.Smo.Agent.CompletionResult OutcomeTypes {get;set;}
SqlMessageID       Property   int SqlMessageID {get;set;}                                                      
SqlSeverity        Property   int SqlSeverity {get;set;}                                                       
StartRunDate       Property   datetime StartRunDate {get;set;}    

The parameter of Get-SqlAgentJobHistory is named "OutcomesType" (plural outcome, singular type).

And the parameter is only a scalar type, so no array is possible.

So my suggestions (ordered by priority): OutcomeType (because it's just a single one and this would be grammatically correct) OutcomeTypes (because that's the name of the object parameter we set - but maybe someone accidentally passes in an array) OutcomesType (because that's the name in Get-SqlAgentJobHistory - but do we have lots of people changing vom SqlServer to dbatools?)

And the documentation of Get-SqlAgentJobHistory is:

Specifies a job filter constraint that restricts the values returned to jobs that have the specified outcome at completion. The acceptable values for this parameter are: -- Failed -- Succeeded -- Retry -- Cancelled -- InProgress -- Unknown

Do we just copy that 1:1? Or do we want to reword it?

[wsmelton]

I want to get only those 10 rows from the server - not 2 million which I'll pipe into where-object.

SMO works by creating a DataTable for the output. The performance will be dependent upon where SMO decides to apply the filter. If it does not do that until the 2 million rows are pulled out (which is my guess) then there is no real difference than you using Where-Object.

As well for it to create the DataTable output that has to have the whole dataset before it can do that, so where it filters is a key indicator of what performance you would get compared to just piping to Where-Object.

[andreasjordan]

@zikato Can you test the difference?

$Server = Connect-DbaInstance -SqlInstance <put instance here>
$filter = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter
$filter.StartRunDate = (Get-Date).AddDays(-1)
$filter.EndRunDate = (Get-Date)
# activate the following line to get just failed jobs
# $filter.OutcomeTypes = [Microsoft.SqlServer.Management.Smo.Agent.CompletionResult]::Failed
$startDate = Get-Date
$result = $server.JobServer.EnumJobHistory($filter)
$result.Rows.Count
$duration = (Get-Date) - $startDate
$duration

[andreasjordan]

If have coded the change an pushed it to my repo. You can see the diff here: https://github.com/sqlcollaborative/dbatools/compare/development...andreasjordan:GetDbaAgentJobHistory_AddOutcomesType

[zikato]

@andreasjordan Without filter Days : 0 Hours : 0 Minutes : 0 Seconds : 4 Milliseconds : 462 Ticks : 44628477 TotalDays : 5.16533298611111E-05 TotalHours : 0.00123967991666667 TotalMinutes : 0.074380795 TotalSeconds : 4.4628477 TotalMilliseconds : 4462.8477

With filter Days : 0 Hours : 0 Minutes : 0 Seconds : 2 Milliseconds : 855 Ticks : 28558662 TotalDays : 3.30540069444444E-05 TotalHours : 0.000793296166666667 TotalMinutes : 0.04759777 TotalSeconds : 2.8558662 TotalMilliseconds : 2855.8662

[andreasjordan]

@zikato Thanks. So this would improve performance for you. Then I will open a pull request and we will see...