TIL: Get-DbaAgentJobHistory

Another blog post, another dbatools command.

Today’s command: Get-DbaAgentJobHistory

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Select-Object * `
    | Out-Gridview 

Basic command, gets all the job history info and pipes it to gridview, because there’s a LOT of info here. Probably a good idea to filter it down to the interesting stuff.

The Duration column behaves a bit weird:
For durations under 1 minute, it’ll say x number of seconds or milliseconds. (Yellow arrow in picture below)
Anything over a minute gets formatted as minutes:seconds:milliseconds. (Red arrow)
Unless it’s over an hour, in which case it’s hours:minutes:seconds, which looks exactly the same. (Blue arrow)

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    | Out-Gridview

Not using Select-Object * cuts down on columns a lot, but we can do better.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-10 `
    | Out-Gridview

Using -StartDate to only return all the job history from the last 3 days, default value is Jan 1, 1900.

I’m like, 90% sure this job doesn’t have any results from the 20th century though.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 1900-01-01 `
    -EndDate 1999-12-31 `
    | Out-Gridview

This doesn’t even pull up a gridview on account of there’s no data, which means my earlier theory was right! On a more useful note, -EndDate lets us exclude results from a given date onwards. Default value is $(Get-Date).

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Out-Gridview

If you don’t care about which steps were successful and which failed, -ExcludeJobSteps cuts it down to reporting the job run attempt as a whole.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object * `
    | where Status `
        -Match Failed `
    | Out-GridView

Filter to just the failure results. Useful if you want to check which jobs failed and when.

Get-DbaAgentJobHistory `
    -SqlInstance PRECISION-M7520\SQL2016 `
    -StartDate 2020-05-01 `
    -ExcludeJobSteps `
    | Select-Object `
        SqlInstance, StartDate, EndDate, `
        Duration, Status, Job, InstanceID, `
        Message, StepName, SqlSeverity, JobName, `
        RunStatus, RunDate, RunDuration, RetriesAttempted `
    | where Status `
        -Match Failed `
    | Export-Csv `
        -Path C:\output.csv `
        -NoTypeInformation

Two things here:
One: Filter out the columns we don’t care about in Select-Object, we don’t need to be doing Select * From * Where * = * here.
Two: Output the data to a csv file so we can play around with it in excel.

Three: There is no three, this is the end of the post. This is also the part where i (b|t) link to my twitter a bunch of times to generate traffic. Also because it’s funny.

Previous Dbatools posts:

Backup-DbaDatabase
Restore-DbaDatabase
Get-DbaLastBackup

Future Dbatools posts:

None yet, unless there are and i forgot to update this part. If that’s the case, go yell at me on twitter!