May 17, 2017 at 9:27 am
John Mitchell-245523 - Wednesday, May 17, 2017 8:42 AMOK, so there are 16 different types of login failure message, and you have 36 instances of login failure messages in your errorlog. That makes sense to me. It sounds like you've got what you need, even if you think you haven't.By the way, to avoid fiddling about with spreadsheets, do this:
EXEC master.dbo.xp_readerrorlog 0, 1, 'Login failed for user'John
John Mitchell-245523 - Wednesday, May 17, 2017 8:42 AMOK, so there are 16 different types of login failure message, and you have 36 instances of login failure messages in your errorlog. That makes sense to me. It sounds like you've got what you need, even if you think you haven't.By the way, to avoid fiddling about with spreadsheets, do this:
EXEC master.dbo.xp_readerrorlog 0, 1, 'Login failed for user'John
I do indeed, but not quite for the above reason, as the Select count wasn't distinct. You're right that XP_ReadErrorLog is the way forward, so here's what I've done (and while I was in Excel, used the drag-drop to increment the Severity level):
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 16, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 17, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 18, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 19, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 20, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 21, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 22, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 23, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 24, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, ", Severity: 25, State:"
EXEC master.dbo.xp_readerrorlog 0 , 1, "Msg 823,"
EXEC master.dbo.xp_readerrorlog 0 , 1, "Msg 824,"
EXEC master.dbo.xp_readerrorlog 0 , 1, "Msg 825,"
So you get the Panatella after all 🙂
May 17, 2017 at 9:39 am
Please can I exchange it for a nice single malt?
The xp_readerrorlog proc takes up to two search parameters, so you can make your code more efficient, something like this. You may find that if you cut it down this brutally, you'll capture stuff that you're not interested in. But give it a try, and tweak if necessary.EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 16, State:', ', Severity: 17, State:'
EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 18, State:', ', Severity: 19, State:'
EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 2', 'Msg 82'
John
May 18, 2017 at 3:56 am
John Mitchell-245523 - Wednesday, May 17, 2017 9:39 AMPlease can I exchange it for a nice single malt?The xp_readerrorlog proc takes up to two search parameters, so you can make your code more efficient, something like this. You may find that if you cut it down this brutally, you'll capture stuff that you're not interested in. But give it a try, and tweak if necessary.
EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 16, State:', ', Severity: 17, State:'
EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 18, State:', ', Severity: 19, State:'
EXEC master.dbo.xp_readerrorlog 0 , 1, ', Severity: 2', 'Msg 82'John
Hmmmm...I honestly feel this is one of those rare occasions when more is not necessarily better, for precisely the reason you cite above. While we're currently woefully under-alerting, over-alerting brings it's own issues, notably alert fatigue. While I'm at it, we are so far alerting on just whether a server responds, PLE, Buffer Cache, (and the usual Windowsy CPU/Memory/etc). Pretty poor list. If you have any ideas to improve, these will be warmly welcomed.
I forgot to add, I found that you do NOT need to enable the Error Log to use Severity Levels for the entries generates, as I saw when poring through one of the logs on a machine with no alerts enabled, the Severity Levels are built into SQL Server and enabled at default, the only thing you may want to actively enable is if you want alerts pushed out to you (which I don't, as Nagios pulls alerts in).
You may indeed swap the cigar for a whiskey, but did I mention I have some Cohibas too? But yes, here you go, a bottle of Lagavulin to you, good man - Special Edition!! 🙂
May 18, 2017 at 4:19 am
Nah, I've never smoked. But thanks for the offer!
I wasn't necessarily suggesting that you increase the scope of your alerting. I was just showing how you can reduce the number of xp_readerrorlog calls that you issue, in order to make the capturing process more efficient, but pointing out that in doing so, you may end up capturing other stuff. For example, if there's such a thing as Msg 826, you'd capture that as well. Since you ask, though, you might want to capture deadlocks, errors in DBBC checks, "memory paged out" warnings and so on, if what we've talked about so far doesn't already cover those things.
John
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply