May 17, 2017 at 8:59 am
JaybeeSQL - Wednesday, May 17, 2017 8:22 AMIt was a good try, but no cigar ;(I ran :
Select COUNT(*)
FROM sys.messages M
Where M.text like '%Login failed for user%'
This gave me 16 results.
I copied the output of EXEC master.dbo.xp_readerrorlog 0, 1 and pasted it into a s/sheet, filtered A-Z and deleted the rows north and south of 'Login failed for user', this counted 36 rows remaining.
Thanks anyway 😉
You actually do have everything you need at this point - I'd give John the cigar. There are different types of login failed messages. As John already noted, you need to check the is_event_logged column in sys.messages to see if it gets logged to the error log. And if you need to change the behavior, it's in the link John provided earlier for logging messages. So you can have whatever messages you want logged to the SQL Server error log but you need to look at the is_event_logged column in sys.messages and need to read the article on changing this behavior if needed with sp_altermessage. It's all there to get the behavior of what you are asking for.
Sue
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 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply