How to alert on multiple component conditions combined into a total count? Detect Account Lockout Storm

Question

I am at a total loss here and sure would appreciate any help that the community could offer. I have a unique situation and am not sure how to address it. I need to build an alert based on the total number of locked out user accounts in our environment at any given time. I have an Iframe (widget) that displays the total count using a basic SQL query but I can't find a way to get that query into an alert so that it will work. It seems that being forced to select the tables in advance from the dropdown are what are causing me issues. Here is the query that works.

SELECT COUNT(*) FROM [dbo].[APM_WindowsEvent_Detail]

WHERE EventCode = 4740

AND [TimeGeneratedUtc] > DATEADD(hour,-1,GETUTCDATE())

This displays the total number of event logs that match the ID 4740 which gives me a good idea of the total number of locked accounts. Is there any way to get this query into an alert so that I can set some logic to alert anytime I see more than 50 locked accounts within a 30 minute period? Is there possibly another way to do this? I do have the Windows AD templates in place on all domain controllers counting the various account status details. Included is locked out accounts but it seems to be based on the event logs for the specific server that the template is assigned to (as you would expect it to be). I need to get a total count of all locked out accounts and generate an alert / email off that stat rather than what each server sees individually.

This is a real hot issue in our NOC currently so any help or suggestions would be most welcomed.

ChrystalT · Accepted Answer

So, because it forces you to select a predetermined SQL statement (as you stated) you have to join it in as a subquery. You would have to do something like the below. It would be better for your SQL query if you have something to join on. So, if you can join it on ComponentID or something like the second example:

Example 1:

SELECT APM_AlertsAndReportsData.ComponentName, APM_AlertsAndReportsData.ComponentID FROM APM_AlertsAndReportsData (precreated)

JOIN (SELECT COUNT(*) as NumOfEvents FROM [dbo].[APM_WindowsEvent_Detail]

WHERE EventCode = 4740

AND [TimeGeneratedUtc] > DATEADD(hour,-1,GETUTCDATE())) as tbl on 1 = 1

group by APM_AlertsAndReportsData.ComponentName, APM_AlertsAndReportsData.ComponentID, tbl.NumOfEvents

having tbl.NumOfEvents > 1

Example 2:

SELECT APM_AlertsAndReportsData.ComponentName, APM_AlertsAndReportsData.ComponentID, tbl.NumOfEvents FROM APM_AlertsAndReportsData (precreated)

JOIN (SELECT COUNT(*) as NumOfEvents, ComponentID FROM [dbo].[APM_WindowsEvent_Detail]

WHERE EventCode = 4740

AND [TimeGeneratedUtc] > DATEADD(hour,-1,GETUTCDATE())

group by ComponentID) as tbl on tbl.ComponentID = APM_AlertsAndReportsData.ComponentID

group by APM_AlertsAndReportsData.ComponentName, APM_AlertsAndReportsData.ComponentID, tbl.NumOfEvents, tbl.ComponentID

having tbl.NumOfEvents > 1

Try that out. I don't have a similar component set up in my lab for those events so I am working off of theory. I would try it out in database manager or SSMS before you throw it into an alert, and if/when you do throw it into an alert make sure you only copy from the JOIN onward.

-ChrystalT

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

How to alert on multiple component conditions combined into a total count? Detect Account Lockout Storm

Top Replies