Ed: SQLDork is a Junior DBA on the team here, learning the DBATools in addition to normal DBA stuff™
Today’s dbatools command is Get-DbaErrorLog, which is very similar to a script we frequently run on client servers to check for errors that need fixing.
Get-DbaErrorLog `
-SqlInstance PRECISION-M7520\SQL2016 `
| Select-Object `
SqlInstance, Source, LogDate, Text `
| Out-Gridview
Basic command usage, using Select-Object to filter out the columns i don’t really care about, then piping the results to Out-Gridview.
Get-DbaErrorLog `
-SqlInstance PRECISION-M7520\SQL2016 `
-LogNumber 1, 0 `
| Select-Object `
SqlInstance, Source, LogDate, Text `
| Out-Gridview
Adding the -LogNumber Parameter to check the archived errorlogs, defaults to current log only.
NOTE: If you want the results ordered by date, the values passed into -LogNumber must be in descending order.
Get-DbaErrorLog `
-SqlInstance PRECISION-M7520\SQL2016 `
-Source spid5s `
| Select-Object `
SqlInstance, Source, LogDate, Text `
| Out-Gridview
The -Source parameter lets us filter out some stuff™. I can’t think of a better way to describe that. The documentation uses -Source Logon as an example, but my sample data didn’t have that so i used a spid.
(Ed: The Source can be a spid, “logon”, “Server”, or anything you see in the errorlog Source column in the SSMS Log Viewer)
Get-DbaErrorLog `
-SqlInstance PRECISION-M7520\SQL2016 `
-LogNumber 5, 4, 3, 2, 1, 0 `
-Text "instance" `
| Select-Object `
SqlInstance, Source, LogDate, Text `
| Out-Gridview
Using -Text as a rudimentary where clause, but this one only supports 1 string. We can do better.
Get-DbaErrorLog `
-SqlInstance PRECISION-M7520\SQL2016 `
-LogNumber 5, 4, 3, 2, 1, 0 `
| Select-Object `
SqlInstance, Source, LogDate, Text `
| Where { `
( `
$_.Text -Like "*CheckDB*" `
-or $_.Text -Like "*Error*" `
-or $_.Text -Like "*UTC*" `
) `
-and ( `
$_.Text -NotLike "*found 0 errors*" `
-and $_.Text -NotLike "*finished without errors*" `
-and $_.Text -NotLike "*informational message*" `
) `
} `
| Out-GridView
This formatting feels very strange to me, but it does a very similar thing to that errorlog query we use that i mentioned earlier.
(Ed: The Where-Object or Where is PowerShell, not DBATools, click here)
Shoutout to Kevin (b|t) for helping me figure out the syntax on this, and also for pointing out that he changed the -SqlInstance parameter to his own machine name when he was testing this. Saved me a bunch of time troubleshooting connection errors.
Speaking of connection errors, follow me (b|t) on twitter, even though i don’t really tweet that much. Unless i retweet pictures of dogs, that’s always a possibility.
