Jump to content
Flobberknock

Internal Monitor for over X amount of Failed Logins in a time period

Recommended Posts

Hi All, 

Spent a lot of time working this one out but believe I have it, in case anyone else wants it. 

The below Internal Monitor has to be RAWSQL and it will return a list of all computers that have had over 500 'Failed Logins' (defined by the event ID's of the pre-built Internal Monitor) in a 1 day period

Create Temporary Table IF NOT EXISTS Tcomp (INDEX (Computerid)) SELECT computerid FROM computers WHERE ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=4114); Select DISTINCT computers.computerid as TestValue,eventlogs.Message as IdentityField,computers.computerid,Computers.Name as computername,locations.locationid,locations.name as locationname,clients.Clientid,clients.name as clientname,agentcomputerdata.NoAlerts,AgentComputerData.UpTimeStart,AgentComputerData.UpTimeEnd FROM ((Computers LEFT JOIN Locations ON Locations.LocationID=Computers.Locationid) LEFT JOIN Clients ON Clients.ClientID=Computers.clientid) JOIN AgentComputerData on Computers.ComputerID=AgentComputerData.ComputerID INNER JOIN eventlogs ON Computers.ComputerID = eventlogs.ComputerID WHERE eventlogs.EventID IN (529, 644, 681, 4625) and eventlogs.timegen > DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) GROUP BY Computers.ComputerID, Computers.Name, eventlogs.EventID HAVING COUNT(EventID) > 500 AND Computers.ComputerID IN (Select ComputerID From Tcomp)

I hope this saves someone many hours!

Cheers,

James

Share this post


Link to post
Share on other sites

Of a similar ilk, here is a similar monitor (also RAWSQL) that focuses specifically on Interactive, RemoteInteractive and Console Logons:

Everything that has ever triggered on this for me has indicated a serious issue.

 

SELECT 	COUNT(*)	AS TestValue,  
        c.name 		AS IDentityField, 
        c.Computerid 	AS ComputerID, 
        acd.NoAlerts, 
        acd.UpTimeStart, 
        acd.UpTimeEnd
FROM computers c 
JOIN eventlogs e
  ON (e.computerid = c.`ComputerID`)
LEFT JOIN AgentComputerData acd 
  ON (c.computerid = acd.computerid)
WHERE e.EventID IN (529,644,681,4625) AND (e.Message LIKE '%Logon Type:2%' OR e.Message LIKE '%Logon Type:7%' OR e.Message LIKE '%Logon Type:10%')
AND TimeGen > (NOW() - INTERVAL 1 HOUR)
GROUP BY c.`ComputerID`
HAVING TestValue > 8

 

  • Like 1

Share this post


Link to post
Share on other sites

Hey Gavsto,

This just errors out on me when I attempt to check the Query Results tab after simply pasting the code in to the configuration. Any idea what I'm missing?

Share this post


Link to post
Share on other sites
On 1/15/2018 at 5:55 PM, thoughtcoder said:

This just errors out on me when I attempt to check the Query Results tab after simply pasting the code in to the configuration. Any idea what I'm missing?

Did you strip out all the formatting text? New lines and tabs (i.e. anything that makes the query functionally readable) have to be removed. It's a weird quirk, but might be the issue if you only copy and pasted the code.

- Zach

Share this post


Link to post
Share on other sites
On 1/19/2018 at 5:51 AM, ZSmith said:

Did you strip out all the formatting text? New lines and tabs (i.e. anything that makes the query functionally readable) have to be removed. It's a weird quirk, but might be the issue if you only copy and pasted the code.

- Zach

I didn't strip formatting, but I actually started to get results - it's just SUPER slow - maybe I'll reformat the RAWSQL and see if that helps.

So far, every tripped instance has been a legit attack or other bad circumstance - so I'm really pleased with this. The only thing I'm interested in is how everyone else is alerting? Are you running it hourly or have you tweaked it? Did you leave it at 8 or change it?

Share this post


Link to post
Share on other sites

I'm planning on using this for our hosted terminal servers, can someone just confirm I've got the config correct? When I build the query I get no results but I'm not sure if this is because we are super secure or I've configured the monitor wrong?

image.thumb.png.b1740f6419dc0ea00fb67d2f7cd5f366.png

Share this post


Link to post
Share on other sites

Couple things here.  First, the obligatory plugin pitch.  Install Third Wall and use the UI from the location screen.

Second, you may want to consider adding %Logon Type:3%' to your monitor along with types 2, 7 and 10.  This will reveal any network services which are trying to logon to your machines.  I've seen environments where an old and forgotten service was running overtime with outdated credentials to the domain machines and no one knew there were literally thousands of failures a day.

 

image.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...