SCOM – Monitoring SQL Failed Login

111

First create a new collection rule, go to the authoring pane then rules –> create a rule –> collection rule –> NT event log.
The collection rule will only collect, not generate any alerts. Choose the appropriate target, this can be Windows Server 2008 Computer. Also I It could be a good idea to create the rule disable as default. Then override and enable it for a group including ex; some of your SQL servers.

110

Then you can use the following query to build your report or simply download the following RDL file;
https://docs.google.com/open?id=0ByVMhVXdDQn4OW9DS0dGRU5NYlU

SELECT
Event.vEvent.DateTime,
LEFT(SUBSTRING(Event.vEventDetail.RenderedDescription,CHARINDEX(‘.’,Event.vEventDetail.RenderedDescription)+1 ,LEN (Event.vEventDetail.RenderedDescription)),CHARINDEX(‘.’,SUBSTRING (Event.vEventDetail.RenderedDescription,CHARINDEX(‘.’,Event.vEventDetail.RenderedDescription)+1 ,LEN(Event.vEventDetail.RenderedDescription)))-1) AS ‘Reason’,
LEFT(vEventLoggingComputer.ComputerName,CHARINDEX(‘.’, vEventLoggingComputer.ComputerName) – 1) AS ‘SQL Server’,
CASE
WHEN Event.vEventDetail.RenderedDescription LIKE ‘% %’
THEN LEFT(Event.vEventDetail.RenderedDescription, CHARINDEX(‘.’, Event.vEventDetail.RenderedDescription) – 1)
ELSE Event.vEventDetail.RenderedDescription
END AS ‘Failed Login’,

CASE
WHEN Event.vEventDetail.RenderedDescription LIKE ‘% %’ AND Event.vEventDetail.RenderedDescription NOT LIKE ‘%<local%machine>%’
THEN LEFT(SUBSTRING(Event.vEventDetail.RenderedDescription, CHARINDEX(‘1’,Event.vEventDetail.RenderedDescription), LEN(Event.vEventDetail.RenderedDescription)), CHARINDEX(‘]’, SUBSTRING (Event.vEventDetail.RenderedDescription, CHARINDEX(‘1’, Event.vEventDetail.RenderedDescription), LEN(Event.vEventDetail.RenderedDescription))) – 1)
WHEN Event.vEventDetail.RenderedDescription LIKE ‘%%’
THEN LEFT(vEventLoggingComputer.ComputerName, CHARINDEX(‘.’,vEventLoggingComputer.ComputerName) – 1)
ELSE Event.vEventDetail.RenderedDescription
END AS ‘Source Machine’
FROM
Event.vEvent LEFT OUTER JOIN
vEventCategory ON Event.vEvent.EventCategoryRowId = vEventCategory.EventCategoryRowId LEFT OUTER JOIN
vEventPublisher ON Event.vEvent.EventPublisherRowId = vEventPublisher.EventPublisherRowId LEFT OUTER JOIN
vEventLoggingComputer ON Event.vEvent.LoggingComputerRowId = vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN
vEventLevel ON Event.vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN
vEventChannel ON Event.vEvent.EventChannelRowId = vEventChannel.EventChannelRowId LEFT OUTER JOIN
Event.vEventDetail ON Event.vEvent.EventOriginId = Event.vEventDetail.EventOriginId
WHERE
(Event.vEvent.EventDisplayNumber = ‘18456’)

ORDER BY
Event.vEvent.DateTime DESC

Advertisements