Active Directory User Auditing – Simple

For my Audit report, I had to create an audit list about creating,deleting,disabling,enabling,locking, unlocking my AD users.

Since we haven’t had a SCOM on our premises, I hade to come up with something that will trigger an e-mail to me with an event.
First of all you have to enable user auditing on Default GPO.

image

After that, I connected to my primary domain controller and created an Powershell script (with a little help from http://powershell.com/cs/) which sends an e-mail in HTML form to me, with some parameters.
First of all, it creates HTML file with a table, which then populates from Security Event under Event ID 4740. After it populates HTML file, this table sets as an body, and sends it to email addresses.

   1: #$DC = "DCServerName" 
   2: $Report= "C:\Admin\lockedaccount\locked.html" 

   3: $HTML=@" 

   4: <title>Account locked out Report</title> 

   5: <style> 

   6: BODY{background-color :#FFFFF} 

   7: TABLE{Border-width:thin;border-style: solid;border-color:Black;border-collapse: collapse;} 

   8: TH{border-width: 1px;padding: 1px;border-style: solid;border-color: black;background-color: ThreeDShadow} 

   9: TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color: Transparent} 

  10: H2{color: #457dcf;font-family: Arial, Helvetica, sans-serif;font-size: medium; margin-left: 40px; 

  11: </style> 

  12: "@ 

  13: $Account_Name = @{n='Account name';e={$_.ReplacementStrings[-1]}} 

  14: $Account_domain = @{n='Account Domain';e={$_.ReplacementStrings[-2]}} 

  15: $Caller_Computer_Name = @{n='Caller Computer Name';e={$_.ReplacementStrings[-1]}} 

  16: $event= Get-EventLog -LogName Security -ComputerName $DC -InstanceId 4740 -Newest 1 | 

  17:    Select TimeGenerated,ReplacementStrings,"Account name","Account Domain","Caller Computer Name" | 

  18:    % { 

  19:      New-Object PSObject -Property @{ 

  20:       "Account name" = $_.ReplacementStrings[-7] 

  21:       "Account Domain" = $_.ReplacementStrings[5] 

  22:       "Caller Computer Name" = $_.ReplacementStrings[1] 

  23:       Date = $_.TimeGenerated 

  24:     } 

  25:    } 

  26:   $event | ConvertTo-Html -Property "Account name","Account Domain","Caller Computer Name",Date -head $HTML -body  "<H2> User is locked in the Active Directory</H2>"| 

  27:      Out-File $Report -Append 

  28: $MailBody= Get-Content $Report 

  29: $MailSubject= "User Account locked out" 

  30: $SmtpClient = New-Object system.net.mail.smtpClient 

  31: $SmtpClient.host = "mail.uniqa.hr" 

  32: $MailMessage = New-Object system.net.mail.mailmessage 

  33: $MailMessage.from = “AccountLockout@test.com” 

  34: $MailMessage.To.add(“itsupport@test.com) 

  35: $MailMessage.Subject = $MailSubject 

  36: $MailMessage.IsBodyHtml = 1 

  37: $MailMessage.Body = $MailBody 

  38: $SmtpClient.Send($MailMessage) 

  39: del C:\Admin\lockedaccount\locked.html 

After creating this powershell script, the next step is to create an Event Trigger which will send this e-mail.
This is done through Task Scheduler.

image

image
This trigger works when Event with 4740 ID is generated in Security Event Viewer.

image

-command "& 'C:\Admin\lockedaccount\account_locked_out.ps1' "

The final result is this:

image

image

Now, you can do this with Unlock account 4767, or Disable account 4725 or deleted 4726etc.
I found out this site with lists of Event IDs : link

Good Luck

Mail merge from SQL Database mail

Few months ago, I was given a task to send some notifications via circular letter. The best thing is to send it by MS WORD.
image
Unfortunately, after sending this, the attachment could not be read on the recipients side. Me and my colleague could not understand, is it because WORD or attachment or in the end mail server. Because we where on tight schedule, I came up with idea to send circular letter through SQL Database mail.
After importing the table of recipients, I have created an simple WHILE loop to call sp_send_dbmail.

It can be sent in plain text or HTML.
For this I have create a three users me, John Doe, Jane Doe.

This is T-SQL for loop plain text
/*****************************************PLAIN TEXT********************************************************/
use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
”+@Title+’
‘+@Name+’

We must inform you that you have signed a contract with TESTFirm.

‘+@option+’

Best Regards

Somebody’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
–@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************PLAIN TEXT********************************************************/

/*****************************************HTMLTEXT********************************************************/

use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
‘<font size="3" face="Arial" color="black">
<p><big>’+@Title+'</p>
<p>’+@Name+'</big></p>
<p> </p>
<p> </p>
<p>We must inform you that you have signed a contract with TESTFirm.</p>
<p> </p>
<p>’+@option+'</p>
<BR>&nbsp;<BR>
<p></p>
<p>Best Regards</p>
<p></p>
<p>Somebody</p> </font>’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************HTMLTEXT********************************************************/

I received three mails:
image

The result of plain text mail:
image 

The result of HTML mail:
image

So, that’s about it.

Good Luck