Powershell: Collect information about locked AD Accounts in SQL Database

Small background: Working with ~ 20 domain controllers and thousands of users makes DC Security Log really short in time. I mean – there are thousands of security logs, but all I can browse is only last ~ 50 minutes, older logs are archived / removed. Sometimes, it makes simple things impossible. For example, I can’t find information about computer account that was used to lockout user account. That’s why I decided to store this specific information (Event ID 4740) in SQL Database. It’s easy to filter, manage and possibly present in a web interface.

I had an idea to write small powershell script that would insert a record to SQL database for every Event ID 4740 occurrence on any  Domain Controller (as account may be locked on any of them).

Below you can find example database structure with it’s content:

sql-view

From the left:

  • id;
  • Source (event log source, security auditing);
  • MachineName – Domain Controller that a user was authenticating against;
  • Message – complete log message;
  • UserName – locked out user name;
  • CallerName – Workstation that was used during authentication;
  • TimeGenerated;
  • TimeWritten

Of course it’s only example, you may modify this table for you needs. This one was fitting my needs; Below you can copy CREATE TABLE script:

Having this table created, we can start with powershell scripting. First, script preferences (so you would not get any errors on screen), then SQL connection:

Then, you should define Event ID that you’re looking for and LogName that contains such events. In this case, I’m going to browse Security Log for Event ID 4740 which occurs only when user account is being locked out on Domain Controller.

As you can see above, each required information is stored in it’s own variable. We are going to use them inside SQL Insert query now:

Above code will result in SQL row being added into table. You can retrieve some information from SQL to confirm it was actually done properly, but I kept it simply as it runs in background on each DC.

At the end of such script, you should end SQL connection:

At this point, you have a script that will get only one, last Event with ID 4740 from your security log. In the next step you should create a scheduled task on your DC to run this powershell script on every EventID 4740 occurrence:

event-viewer

Of course, you can do that manually on your DC; but if you have more than one domain controller, I recommend using Group Policies for creating a scheduled tasks in your DCs OU. In that case you could also use a UNC path for .ps1 script (fileserver, sysvol share) or just copy this file with GPO on local DC disk. It’s up to you.

Below you can copy complete ps script:

 

Leave a Reply