Wednesday, July 25, 2018

Audit basic events in Active Directory with Windows 2012.

I want to share a way to audit events in Active Directory with domain controllers on Windows 2012 and younger.
For servers with OS Windows 2012 and younger, a new method of collecting events "ForwardedEvents" appeared. First, configure the collection of events on the dedicated server:
1. Create the policy "AuditFrw" and apply to the OU "Domain Controllers"
Computer Configuration - Administrative Templates - Windows Components - Event Forwarding - Server=http://scr-server.blogspot.com:5985/wsman/SubscriptionManager/WEC,Refresh=120

Computer Configuration - Administrative Templates - Windows Components - Windows Remote Management (WinRM) - WinRMService - Allow remote server managment through WinRM - 1.1.1.0-1.1.1.50

Computer Configuration - Preferences - Control Panel Settings - Services - Service:WinRM - Start/Automatic(Delayed Start)

2. On the audit server scr-server.blogspot.com we create a subscription
Event Viewer - Subscriptions - Create Subscription
- Name:Base-Audit-AD
- Type: Source computer initiated
- Computers: "Domain Controllers"
- Events: 4728,4732,4756,4729,4733,4757,4720,4726,4727,4731,4754,4730,4734,4758,4741,4725,4740




3. Create a task on a schedule, for example: 7 am, every day, repeat every 12:00
and specify the script

Download Script

$hf = "D:\Scripts\Audit"
cd $hf

$s_date = (Get-date).AddDays(-0.5)

function ResolveSID ($sid) {
    $objSID = New-Object System.Security.Principal.SecurityIdentifier($sid)
    $objUser = $objSID.Translate([System.Security.Principal.NTAccount])
    Return $objUser.Value
}


#add to gr
$evs_add_to_gr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4728,4732,4756} -ea 0
if ($evs_add_to_gr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\AddUserToGorup\AddUserToGorup_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,Group,NewMember,AddedBy"
    $evs_add_to_gr = $evs_add_to_gr | sort TimeCreated | select TimeCreated,
    @{n="Group";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="NewMember";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "MemberSid"} | %{$_.’#text’}}},
    @{n="AddedBy";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_add_to_gr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.Group)
        $e_nm = ResolveSID($e.NewMember)
        $e_ab = ResolveSID($e.AddedBy)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_nm,$e_ab"
    }
}
#del from gr
$evs_del_from_gr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4729,4733,4757} -ea 0
if($evs_del_from_gr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\DelUserFromGroup\DelUserFromGroup_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,Group,DelMember,DelBy"
    $evs_del_from_gr = $evs_del_from_gr | sort TimeCreated | select TimeCreated,
    @{n="Group";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="DelMember";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "MemberSid"} | %{$_.’#text’}}},
    @{n="DelBy";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_del_from_gr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.Group)
        $e_nm = ResolveSID($e.DelMember)
        $e_ab = ResolveSID($e.DelBy)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_nm,$e_ab"
    }
}
#new gr
$evs_new_gr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4727,4731,4754} -ea 0
if ($evs_new_gr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\CreateGroup\CreateGroup_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,GroupName,AdminLogin"
    $evs_new_gr = $evs_new_gr | sort TimeCreated | select TimeCreated,
    @{n="Group";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="GroupName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_new_gr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.Group)
        if (!$e_gr) {$e_gr = $e.GroupName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}
#del gr
$evs_del_gr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4730,4734,4758} -ea 0
if ($evs_del_gr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\DelGroup\DelGroup_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,GroupName,AdminLogin"
    $evs_del_gr = $evs_del_gr | sort TimeCreated | select TimeCreated,
    @{n="Group";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="GroupName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_del_gr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.Group)
        if (!$e_gr) {$e_gr = $e.GroupName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}
#new usr
$evs_new_usr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4720} -ea 0
if ($evs_new_usr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\CreateUser\CreateUser_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,UserName,AdminLogin"
    $evs_new_usr = $evs_new_usr | sort TimeCreated | select TimeCreated,
    @{n="User";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="UserName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_new_usr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.User)
        if (!$e_gr) {$e_gr = $e.UserName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}
#del usr
$evs_del_usr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4726} -ea 0
if ($evs_del_usr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\DelUser\DelUser_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,UserName,AdminLogin"
    $evs_del_usr = $evs_del_usr | sort TimeCreated | select TimeCreated,
    @{n="User";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="UserName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_del_usr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.User)
        if (!$e_gr) {$e_gr = $e.UserName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}
#dis usr
$evs_dis_usr = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4725} -ea 0
if ($evs_dis_usr.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\DisableUser\DisableUser_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,UserName,AdminLogin"
    $evs_dis_usr = $evs_dis_usr | sort TimeCreated | select TimeCreated,
    @{n="User";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="UserName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_dis_usr) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.User)
        if (!$e_gr) {$e_gr = $e.UserName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}
#new pc
$evs_new_pc = Get-WinEvent -FilterHashTable @{LogName='ForwardedEvents'; StartTime=$s_date; ID=4741} -ea 0
if ($evs_new_pc.count -gt 0) {
    $LogTime = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $LogFile = ".\Logs\CreatePC\CreatePC_" + $LogTime + ".csv"
    add-content -path $LogFile -value "TimeCreated,PCName,AdminLogin"
    $evs_new_pc = $evs_new_pc | sort TimeCreated | select TimeCreated,
    @{n="User";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetSid"} | %{$_.’#text’}}},
    @{n="UserName";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "TargetUserName"} | %{$_.’#text’}}},
    @{n="AdminLogin";e={([xml]$_.ToXml()).Event.EventData.Data | ? {$_.Name -eq "SubjectUserSid"} | %{$_.’#text’}}}
    foreach ($e in $evs_new_pc) {
        $e_gr = $e_nm = $e_ab = @()
        $e_gr = ResolveSID($e.User)
        if (!$e_gr) {$e_gr = $e.UserName}
        $e_ab = ResolveSID($e.AdminLogin)
        add-content -path $LogFile -value "$($e.TimeCreated),$e_gr,$e_ab"
    }
}









Friday, April 6, 2018

How to reduce IPAM Database

Unexpectedly, IPAM database can occupy a significant place on the server. Here's a way to reduce the size of the database:
1. Use powershell commands:

$Clean_Date = (Get-Date).AddDays(-100)
Remove-IpamIpAddressAuditEvent -EndDate $Clean_Date

2. Next, install and open SQL Management Studio.
Connect to \\.\pipe\Microsoft##WID\tsql\query
Recreate indexes for IP_AUDIT and shrink database

USE [IPAM]
EXEC sp_updatestats
GO
ALTER INDEX [IP_AUDIT_IX_CLIENT_ID_COMBINATION] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO
ALTER INDEX [IP_AUDIT_IX_HOST_NAME_COMBINATION] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO
ALTER INDEX [IP_AUDIT_IX_IP_ADDRESS_COMBINATION] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO
ALTER INDEX [IP_AUDIT_IX_TIMEOFEVENT_EVENTTYPE] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO
ALTER INDEX [IP_AUDIT_IX_USER_NAME_COMBINATION] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO
ALTER INDEX [IP_AUDIT_PK_TIME_OF_EVENT_IP_AUDIT_ID] ON [dbo].[IP_AUDIT] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC SHRINKDATABASE(N'IPAM' )
GO

Wednesday, July 19, 2017

TransportAgentFactory type must be the Microsoft .NET class type of the transport agent factory.

The error correct in two ways :)

1. Install last CU for Exchange
2. Close and open "Exchange Management Shell"

How to log the names and size of attachments in messages (Exchange 2013/2016)

I want to share with you "Exchange TransportAgent" which log the names and size of attachments.
I test this agent on Exchange 2013 CU17 (15.00.1320.004) and Exchange 2016 CU6 (15.01.1034.026).

Download "AttachmentLog TransportAgent for Exchange 2013 CU17"

Download "AttachmentLog TransportAgent for Exchange 2016 CU6"

The location of the folder with logs can be controlled from the file "AttachmentLog.Config.xml"

Example of install:

001
002
003
004
005
$EXDIR="C:\MyAgents\AttachmentLog"
Net Stop MSExchangeTransport
Install-TransportAgent -Name "MyAttachmentLog" -AssemblyPath $EXDIR\AttachmentLog.dll -TransportAgentFactory MyAttachmentLog.Exchange.Agents.AttachmentLog.AttachmentLogFactory
Enable-TransportAgent -Identity "MyAttachmentLog"
Net Start MSExchangeTransport

Example of uninstall:

001
002
Uninstall-TransportAgent -Name "MyAttachmentLog"
Restart-Service MSExchangeTransport

Check Exchange version:

001
Get-Command Exsetup.exe | ForEach{$_.FileVersionInfo}

Example of results: