PSWinReporting 1.8 - Split of branches (Legacy vs. New Hope)

By Przemyslaw Klys Active Directory PowerShell Windows 10 marca, 2019

A new branch of PSWinReporting is slowly coming, and I thought it would be the best time to have a final article about it with all configuration options available for those that will want to stay using PSWinReporting from Legacy branch. The idea is that you may have it working in your systems and it's good enough for you. You may not want to change it, and with New Hope, the changes are so big it's a rewrite.

PSWinReporting Information

Please notice this article contains parts of information (still useful) and may not reflect all functionalities of this module. For download, source code and so on you should refer to the dedicated PSWinReporting module page. After reading this one… of course! It contains useful information, examples and know-how.

What the hell is PSWinReporting?

For those that see this first time, PSWinReporting can scan Active Directory Domain Controllers, or Event Log Forwarders and prepare nicely formatted HTML report, send that data to Microsoft Teams, Slack or/and Microsoft SQL.

It has two working modes. One mode is the ability to scan events on demand on hourly, daily, weekly, monthly (and so on) periods. You can treat it as a sort of post-fact report that you send to yourself or your manager or service desk to review what happened and whether it was approved or not. You can track group membership changes, user changes and so on knowing precisely what happened when it happened and who did it. It's able to write data to HTML, Microsoft Excel, CSV, and Microsoft SQL. It's either sending that data with Email or save it to disk for later (with the exception of SQL of course). The second mode is the live mode. It requires Event Forwarding to be set up, but it allows to send events to Microsoft Teams, Slack and Microsoft SQL as events happen.

PSWinReporting - Reporting on Demand

Below is a standard PowerShell Configuration/Script that you would use in your Task Scheduler, executed every day. I usually run this right after midnight to get a report for myself in the morning when I start my work. Make sure to change report paths, email parameters and which periods are you interested in. Please review PSWinReporting Module page for links to other blog posts about this configuration if you have any questions. I've written 7 or so articles about it, and that should cover most of your questions about it.

$EmailParameters = @{
    EmailFrom                   = "[email protected]"
    EmailTo                     = "[email protected], [email protected]"
    EmailCC                     = ""
    EmailBCC                    = ""
    EmailReplyTo                = ""
    EmailServer                 = "smtp.office365.com"
    EmailServerPassword         = "YourPassword"
    EmailServerPasswordAsSecure = $false
    EmailServerPasswordFromFile = $false
    EmailServerPort             = "587"
    EmailServerLogin            = "[email protected]"
    EmailServerEnableSSL        = 1
    EmailEncoding               = "Unicode"
    EmailSubject                = "[Reporting] Event Changes for period <<DateFrom>> to <<DateTo>>"
    EmailPriority               = "Low" # Normal, High
}
$FormattingParameters = @{
    CompanyBranding        = @{
        Logo   = 'https://evotec.xyz/wp-content/uploads/2015/05/Logo-evotec-012.png'
        Width  = '200'
        Height = ''
        Link   = 'https://evotec.xyz'
        Inline = $false
    }
    FontFamily             = 'Calibri Light'
    FontSize               = '9pt'
    FontHeadingFamily      = 'Calibri Light'
    FontHeadingSize        = '12pt'

    FontTableHeadingFamily = 'Calibri Light'
    FontTableHeadingSize   = '9pt'

    FontTableDataFamily    = 'Calibri Light'
    FontTableDataSize      = '9pt'

    Colors                 = @{
        # case sensitive
        Red   = 'removed', 'deleted', 'locked out', 'lockouts', 'disabled', 'Domain Admins', 'was cleared'
        Blue  = 'changed', 'changes', 'change', 'reset'
        Green = 'added', 'enabled', 'unlocked', 'created'
    }
    Styles                 = @{
        # case sensitive
        B = 'status', 'Domain Admins', 'Enterprise Admins', 'Schema Admins', 'was cleared', 'lockouts' # BOLD
        I = '' # Italian
        U = 'status'# Underline
    }
    Links                  = @{

    }
}
$ReportOptions = @{
    JustTestPrerequisite  = $false # runs testing without actually running script

    AsExcel               = $false # attaches Excel to email with all events, required ImportExcel module
    AsCSV                 = $false # attaches CSV to email with all events,
    AsHTML                = $true # puts exported data into email directly with all events
    SendMail              = $false
    SendMailOnlyOnEvents = $false
    OpenAsFile            = $true
    KeepReports           = $true # keeps files after reports are sent (only if AssExcel/AsCSV are in use)
    KeepReportsPath       = "C:\Support\Reports\ExportedEvents" # if empty, temp path is used
    FilePattern           = "Evotec-<currentdate>.<extension>"
    FilePatternDateFormat = "yyyy-MM-dd-HH_mm_ss"
    RemoveDuplicates      = $true #

    AsSql                 = @{
        Use                   = $true
        SqlServer             = 'EVOWIN'
        SqlDatabase           = 'SSAE18'
        SqlTable              = 'dbo.[Events]'
        # Left side is data in PSWinReporting. Right Side is ColumnName in SQL
        # Changing makes sense only for right side...
        SqlTableCreate        = $true
        SqlTableAlterIfNeeded = $false # if table mapping is defined doesn't do anything
        SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column name


        SqlTableMapping       = [ordered] @{
            'Event ID'               = 'EventID,[int]'
            'Who'                    = 'EventWho'
            'When'                   = 'EventWhen,[datetime]'
            'Record ID'              = 'EventRecordID,[bigint]'
            'Domain Controller'      = 'DomainController'
            'Action'                 = 'Action'
            'Group Name'             = 'GroupName'
            'User Affected'          = 'UserAffected'
            'Member Name'            = 'MemberName'
            'Computer Lockout On'    = 'ComputerLockoutOn'
            'Reported By'            = 'ReportedBy'
            'SamAccountName'         = 'SamAccountName'
            'Display Name'           = 'DisplayName'
            'UserPrincipalName'      = 'UserPrincipalName'
            'Home Directory'         = 'HomeDirectory'
            'Home Path'              = 'HomePath'
            'Script Path'            = 'ScriptPath'
            'Profile Path'           = 'ProfilePath'
            'User Workstation'       = 'UserWorkstation'
            'Password Last Set'      = 'PasswordLastSet,[datetime]'
            'Account Expires'        = 'AccountExpires,[datetime]'
            'Primary Group Id'       = 'PrimaryGroupId'
            'Allowed To Delegate To' = 'AllowedToDelegateTo'
            'Old Uac Value'          = 'OldUacValue'
            'New Uac Value'          = 'NewUacValue'
            'User Account Control'   = 'UserAccountControl'
            'User Parameters'        = 'UserParameters'
            'Sid History'            = 'SidHistory'
            'Logon Hours'            = 'LogonHours'
            'OperationType'          = 'OperationType'
            'Message'                = 'Message'
            'Backup Path'            = 'BackupPath'
            'Log Type'               = 'LogType'
            'AddedWhen'              = 'EventAdded,[datetime],null' # ColumnsToTrack when it was added to database and by who / not part of event
            'AddedWho'               = 'EventAddedWho'  # ColumnsToTrack when it was added to database and by who / not part of event
            'Gathered From'          = 'GatheredFrom'
            'Gathered LogName'       = 'GatheredLogName'
        }
    }


    DisplayConsole        = @{
        ShowTime   = $true
        LogFile    = "$Env:USERPROFILE\Desktop\PSWinReporting-Manual.log"
        TimeFormat = "yyyy-MM-dd HH:mm:ss"
    }
    Debug                 = @{
        DisplayTemplateHTML = $false
        Verbose             = $false
    }
}
$ReportTimes = @{
    # Report Per Hour
    PastHour             = $false # if it's 23:22 it will report 22:00 till 23:00
    CurrentHour          = $false # if it's 23:22 it will report 23:00 till 00:00
    # Report Per Day
    PastDay              = $false # if it's 1.04.2018 it will report 31.03.2018 00:00:00 till 01.04.2018 00:00:00
    CurrentDay           = $false # if it's 1.04.2018 05:22 it will report 1.04.2018 00:00:00 till 01.04.2018 00:00:00
    # Report Per Week
    OnDay                = @{
        Enabled = $false
        Days    = 'Monday'#, 'Tuesday'
    }
    # Report Per Month
    PastMonth            = @{
        Enabled = $false # checks for 1st day of the month - won't run on any other day unless used force
        Force   = $false  # if true - runs always ...
    }
    CurrentMonth         = $true

    # Report Per Quarter
    PastQuarter          = @{
        Enabled = $false # checks for 1st day fo the quarter - won't run on any other day
        Force   = $false
    }
    CurrentQuarter       = $false
    # Report Custom
    CurrentDayMinusDayX  = @{
        Enabled = $false
        Days    = 7    # goes back X days and shows just 1 day
    }
    CurrentDayMinuxDaysX = @{
        Enabled = $false
        Days    = 3 # goes back X days and shows X number of days till Today
    }
    CustomDate           = @{
        Enabled  = $false
        DateFrom = get-date -Year 2018 -Month 03 -Day 19
        DateTo   = get-date -Year 2018 -Month 03 -Day 23
    }
    Everything           = $false
}
$ReportDefinitions = @{
    TimeToGenerate = $false

    ReportsAD      = @{
        Servers           = @{
            UseForwarders   = $true # if $true skips Automatic/OnlyPDC/DC for reading logs. However it uses Automatic to deliver size of logs so keep Automatic to $true
            ForwardServer   = $ENV:COMPUTERNAME
            ForwardEventLog = 'ForwardedEvents'

            UseDirectScan   = $true
            Automatic       = $true
            OnlyPDC         = $false
            DC              = ''
        }
        ArchiveProcessing = @{
            Use         = $false
            Directories = [ordered] @{
                Use      = $false
                MyEvents = 'E:\EventLogs' #
                #MyOtherEvent = 'C:\MyEvent1'
            }
            Files       = [ordered] @{
                Use = $false
                #File1 = 'E:\EventLogs\Archive-Security-2018-09-14-22-13-07-710.evtx'
            }
        }
        EventBased        = @{
            UserChanges            = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4720, 4738
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            UserStatus             = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4722, 4725, 4767, 4723, 4724, 4726
                LogName          = 'Security'
                IgnoreWords      = @{}
                ExportToSql      = @{
                    Use                   = $true
                    SqlServer             = 'EVOWIN'
                    SqlDatabase           = 'SSAE18'
                    SqlTable              = 'dbo.[EventsUserStatus]'
                    # Left side is data in PSWinReporting. Right Side is ColumnName in SQL
                    # Changing makes sense only for right side...
                    SqlTableCreate        = $true
                    SqlTableAlterIfNeeded = $false # if table mapping is defined doesn't do anything
                    SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column name
                    SqlTableMapping       = [ordered] @{
                        'Event ID'               = 'EventID,[int]'
                        'Who'                    = 'EventWho'
                        'When'                   = 'EventWhen,[datetime]'
                        'Record ID'              = 'EventRecordID,[bigint]'
                        'Domain Controller'      = 'DomainController'
                        'Action'                 = 'Action'
                        'Group Name'             = 'GroupName'
                        'User Affected'          = 'UserAffected'
                        'Member Name'            = 'MemberName'
                        'Computer Lockout On'    = 'ComputerLockoutOn'
                        'Reported By'            = 'ReportedBy'
                        'SamAccountName'         = 'SamAccountName'
                        'Display Name'           = 'DisplayName'
                        'UserPrincipalName'      = 'UserPrincipalName'
                        'Home Directory'         = 'HomeDirectory'
                        'Home Path'              = 'HomePath'
                        'Script Path'            = 'ScriptPath'
                        'Profile Path'           = 'ProfilePath'
                        'User Workstation'       = 'UserWorkstation'
                        'Password Last Set'      = 'PasswordLastSet,[datetime]'
                        'Account Expires'        = 'AccountExpires,[datetime]'
                        'Primary Group Id'       = 'PrimaryGroupId'
                        'Allowed To Delegate To' = 'AllowedToDelegateTo'
                        'Old Uac Value'          = 'OldUacValue'
                        'New Uac Value'          = 'NewUacValue'
                        'User Account Control'   = 'UserAccountControl'
                        'User Parameters'        = 'UserParameters'
                        'Sid History'            = 'SidHistory'
                        'Logon Hours'            = 'LogonHours'
                        'OperationType'          = 'OperationType'
                        'Message'                = 'Message'
                        'Backup Path'            = 'BackupPath'
                        'Log Type'               = 'LogType'
                        'AddedWhen'              = 'EventAdded,[datetime],null' # ColumnsToTrack when it was added to database and by who / not part of event
                        'AddedWho'               = 'EventAddedWho'  # ColumnsToTrack when it was added to database and by who / not part of event
                        #   'Gathered From'          = 'GatheredFrom'
                        #   'Gathered LogName'       = 'GatheredLogName'
                    }
                }
            }
            UserLockouts           = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4740
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            UserLogon              = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 4624
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            GroupMembershipChanges = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4728, 4729, 4732, 4733, 4756, 4757, 4761, 4762
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            GroupCreateDelete      = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4727, 4730, 4731, 4734, 4759, 4760, 4754, 4758
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            GroupPolicyChanges     = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 5136, 5137, 5141
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            LogsClearedSecurity    = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 1102
                LogName          = 'Security'
                IgnoreWords      = @{}
                ExportToSql      = @{
                    Use                   = $false
                    SqlServer             = 'EVO1'
                    SqlDatabase           = 'SSAE18'
                    SqlTable              = 'dbo.[EventsLogsClearedSecurity]'
                    SqlTableCreate        = $true
                    SqlTableAlterIfNeeded = $false # if table mapping is defined doesn't do anything
                    SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column nameg
                }
            }
            LogsClearedOther       = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 104
                LogName          = 'System' # Source: EventLog, Task: 'Log clear'
                IgnoreWords      = @{}
            }
            EventsReboots          = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 1001, 1018, 1, 12, 13, 42, 41, 109, 1, 6005, 6006, 6008, 6013
                LogName          = 'System'
                IgnoreWords      = @{}
            }
            ComputerCreatedChanged = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4741, 4742 # created, changed
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
            ComputerDeleted        = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4743 # deleted
                LogName          = 'Security'
                IgnoreWords      = @{}
            }
        }
        Custom            = @{
            EventLogSize = @{
                Enabled          = $true
                EnabledSqlGlobal = $false
                Logs             = 'Security', 'Application', 'System'
                SortBy           = ''
            }
            ServersData  = @{
                Enabled          = $true
                EnabledSqlGlobal = $false
            }
            FilesData    = @{
                Enabled = $true
            }
        }
    }
}

Import-Module PSWinReporting -Force

### Starts Module (Requires config above)
$startADReportingSplat = @{
    ReportDefinitions    = $ReportDefinitions
    ReportTimes          = $ReportTimes
    FormattingParameters = $FormattingParameters
    ReportOptions        = $ReportOptions
    EmailParameters      = $EmailParameters
}
Start-ADReporting @startADReportingSplat

In this article, I wanted to focus mostly on the SQL part of the configuration as it was neglected in the last article I wrote and was half ready. It works in two ways. You can use it at a global level and local level. In the global level, it puts all event types in a single SQL Table. But there's also a way to send each event type to a different table. You can use both of them at the same time. I've highlighted those sections in code, and hopefully, it will be apparent to you on how to use it properly. You have to define SqlTableMapping or don't. If you define, SqlTableMapping PSWinReporting will try to match fields reported to the one in the table. If the SQL Table doesn't exist it will create it based on that. If you don't define SQLTableMapping it will take defaults and build it for you. Feel free to experiment.

PSWinReporting Information

PSWinReporting - Reporting Live - Sending to Teams, Slack and SQL

The other mode is for monitoring events live as they happen. I won't bore you here on how to set it up because it has already been described before – just go to PSWinReporting PowerShell module page and read the links, information. Again the focus here is to have last, fully working configuration to send events to SQL.

# Collects all named paramters (all others end up in $Args)
param(
    $eventid = 4729,
    $eventRecordID = 7488468, # 425358 ,
    $eventChannel,
    $eventSeverity
)

$ReportOptions = @{
    JustTestPrerequisite  = $false # runs testing without actually running script

    AsExcel               = $false # attaches Excel to email with all events, required ImportExcel module
    AsCSV                 = $false # attaches CSV to email with all events,
    AsHTML                = $true # puts exported data into email directly with all events
    SendMail              = $false
    OpenAsFile            = $true # requires AsHTML set to $true
    KeepReports           = $true # keeps files after reports are sent (only if AssExcel/AsCSV are in use)
    KeepReportsPath       = 'C:\Support\Reports\ExportedEvents' # if empty, temp path is used
    FilePattern           = 'Evotec-ADMonitoredEvents-<currentdate>.<extension>'
    FilePatternDateFormat = 'yyyy-MM-dd-HH_mm_ss'

    DisplayConsole        = @{
        ShowTime   = $true
        LogFile    = ''
        TimeFormat = 'yyyy-MM-dd HH:mm:ss'
    }
    Debug                 = @{
        DisplayTemplateHTML = $false
        Verbose             = $true
    }
    Notifications         = @{
        MicrosoftTeams = @{
            Use     = $true
            TeamsID = 'https://outlook.office.com/webhook/f0a1728bf5-4.....8'
        }
        Slack          = @{
            Use     = $false
            Channel = '#general'
            Uri     = ""
        }
        MSSQL          = @{
            Use                   = $true
            SqlServer             = 'EVOWIN'
            SqlDatabase           = 'SSAE18'
            SqlTable              = 'dbo.[Events]'
            # Left side is data in PSWinReporting. Right Side is ColumnName in SQL
            # Changing makes sense only for right side...
            SqlTableCreate        = $true
            SqlTableAlterIfNeeded = $true
            SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column name
            
            SqlTableMapping       = [ordered] @{
                'Event ID'               = 'EventID,[int]'
                'Who'                    = 'EventWho'
                'When'                   = 'EventWhen,[datetime]'
                'Record ID'              = 'EventRecordID,[bigint]'
                'Domain Controller'      = 'DomainController'
                'Action'                 = 'Action'
                'Group Name'             = 'GroupName'
                'User Affected'          = 'UserAffected'
                'Member Name'            = 'MemberName'
                'Computer Lockout On'    = 'ComputerLockoutOn'
                'Reported By'            = 'ReportedBy'
                'SamAccountName'         = 'SamAccountName'
                'Display Name'           = 'DisplayName'
                'UserPrincipalName'      = 'UserPrincipalName'
                'Home Directory'         = 'HomeDirectory'
                'Home Path'              = 'HomePath'
                'Script Path'            = 'ScriptPath'
                'Profile Path'           = 'ProfilePath'
                'User Workstation'       = 'UserWorkstation'
                'Password Last Set'      = 'PasswordLastSet,[datetime]'
                'Account Expires'        = 'AccountExpires,[datetime]'
                'Primary Group Id'       = 'PrimaryGroupId'
                'Allowed To Delegate To' = 'AllowedToDelegateTo'
                'Old Uac Value'          = 'OldUacValue'
                'New Uac Value'          = 'NewUacValue'
                'User Account Control'   = 'UserAccountControl'
                'User Parameters'        = 'UserParameters'
                'Sid History'            = 'SidHistory'
                'Logon Hours'            = 'LogonHours'
                'OperationType'          = 'OperationType'
                'Message'                = 'Message'
                'Backup Path'            = 'BackupPath'
                'Log Type'               = 'LogType'
                'AddedWhen'              = 'EventAdded,[datetime],null' # ColumnsToTrack when it was added to database and by who / not part of event
                'AddedWho'               = 'EventAddedWho'  # ColumnsToTrack when it was added to database and by who / not part of event
                'Gathered From'          = 'GatheredFrom'
                'Gathered LogName'       = 'GatheredLogName'
            }
        }
    }
    Backup                = @{
        Use             = $false
        DestinationPath = 'E:\EventLogs'
    }
}
$ReportDefinitions = @{
    TimeToGenerate = $false

    ReportsAD      = @{
        Servers    = @{
            ForwardServer   = $env:COMPUTERNAME
            ForwardEventLog = 'ForwardedEvents'
        }
        EventBased = @{
            UserChanges            = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4720, 4738
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            UserStatus             = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4722, 4725, 4767, 4723, 4724, 4726
                LogName          = 'Security'
                IgnoreWords      = @{
                    'Domain Controller' = ''
                    'Action'            = ''
                    'User Affected'     = 'Win-*', '*AD1$*'
                    'Who'               = ''
                    'When'              = ''
                    'Event ID'          = ''
                    'Record ID'         = ''
                }
                ExportToSql      = @{
                    # per Event Category / Global SQL is above
                    Use                   = $true
                    SqlServer             = 'EVOWIN'
                    SqlDatabase           = 'SSAE18'
                    SqlTable              = 'dbo.[EventsUserStatus]'
                    # Left side is data in PSWinReporting. Right Side is ColumnName in SQL
                    # Changing makes sense only for right side...
                    SqlTableCreate        = $true
                    SqlTableAlterIfNeeded = $false # if table mapping is defined doesn't do anything
                    SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column name
                    SqlTableMapping       = [ordered] @{
                        'Event ID'               = 'EventID,[int]'
                        'Who'                    = 'EventWho'
                        'When'                   = 'EventWhen,[datetime]'
                        'Record ID'              = 'EventRecordID,[bigint]'
                        'Domain Controller'      = 'DomainController'
                        'Action'                 = 'Action'
                        'Group Name'             = 'GroupName'
                        'User Affected'          = 'UserAffected'
                        'Member Name'            = 'MemberName'
                        'Computer Lockout On'    = 'ComputerLockoutOn'
                        'Reported By'            = 'ReportedBy'
                        'SamAccountName'         = 'SamAccountName'
                        'Display Name'           = 'DisplayName'
                        'UserPrincipalName'      = 'UserPrincipalName'
                        'Home Directory'         = 'HomeDirectory'
                        'Home Path'              = 'HomePath'
                        'Script Path'            = 'ScriptPath'
                        'Profile Path'           = 'ProfilePath'
                        'User Workstation'       = 'UserWorkstation'
                        'Password Last Set'      = 'PasswordLastSet,[datetime]'
                        'Account Expires'        = 'AccountExpires,[datetime]'
                        'Primary Group Id'       = 'PrimaryGroupId'
                        'Allowed To Delegate To' = 'AllowedToDelegateTo'
                        'Old Uac Value'          = 'OldUacValue'
                        'New Uac Value'          = 'NewUacValue'
                        'User Account Control'   = 'UserAccountControl'
                        'User Parameters'        = 'UserParameters'
                        'Sid History'            = 'SidHistory'
                        'Logon Hours'            = 'LogonHours'
                        'OperationType'          = 'OperationType'
                        'Message'                = 'Message'
                        'Backup Path'            = 'BackupPath'
                        'Log Type'               = 'LogType'
                        'AddedWhen'              = 'EventAdded,[datetime],null' # ColumnsToTrack when it was added to database and by who / not part of event
                        'AddedWho'               = 'EventAddedWho'  # ColumnsToTrack when it was added to database and by who / not part of event
                        #   'Gathered From'          = 'GatheredFrom'
                        #   'Gathered LogName'       = 'GatheredLogName'
                    }
                }
            }
            UserLockouts           = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4740
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            UserLogon              = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 4624
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            UserLogonKerberos      = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 4768
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            GroupMembershipChanges = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4728, 4729, 4732, 4733, 4756, 4757, 4761, 4762
                LogName          = 'Security'
                IgnoreWords      = @{
                    'Who' = '*ANONYMOUS*'
                }
                ExportToSql      = @{
                    # per Event Category / Global SQL is above
                    Use                   = $true
                    SqlServer             = 'EVOWIN'
                    SqlDatabase           = 'SSAE18'
                    SqlTable              = 'dbo.[EventsGroupMembershipChanges]'
                    # Left side is data in PSWinReporting. Right Side is ColumnName in SQL
                    # Changing makes sense only for right side...
                    SqlTableCreate        = $true
                    SqlTableAlterIfNeeded = $false # if table mapping is defined doesn't do anything
                    SqlCheckBeforeInsert  = 'EventRecordID', 'DomainController' # Based on column name
                }
            }
            GroupCreateDelete      = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4727, 4730, 4731, 4734, 4759, 4760, 4754, 4758
                LogName          = 'Security'
                IgnoreWords      = @{
                    'Who' = '*ANONYMOUS*'
                }
            }
            GroupPolicyChanges     = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 5136, 5137, 5141
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            LogsClearedSecurity    = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 1102
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            LogsClearedOther       = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 104
                LogName          = 'System'
                IgnoreWords      = ''
            }
            EventsReboots          = @{
                Enabled          = $false
                EnabledSqlGlobal = $true
                Events           = 1001, 1018, 1, 12, 13, 42, 41, 109, 1, 6005, 6006, 6008, 6013
                LogName          = 'System'
                IgnoreWords      = ''
            }
            ComputerCreatedChanged = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4741, 4742 # created, changed
                LogName          = 'Security'
                IgnoreWords      = ''
            }
            ComputerDeleted        = @{
                Enabled          = $true
                EnabledSqlGlobal = $true
                Events           = 4743 # deleted
                LogName          = 'Security'
                IgnoreWords      = ''
            }
        }
    }
}

Import-Module PSWinReporting -Force
Import-Module DBATools
Import-Module PSSharedGoods
Import-Module PSSlack
Import-Module PSTeams

$startNotificationsSplat = @{
    EventChannel      = $EventChannel
    EventID           = $EventID
    ReportDefinitions = $ReportDefinitions
    ReportOptions     = $ReportOptions
    EventRecordID     = $EventRecordID
}
Start-Notifications @startNotificationsSplat

PSWinReporting Information

The significant bit is to make sure you only use Import-Module for those you want to load. Each module is about 250ms to 1-2 seconds of load time which during massive hours can add up. If you're not sending stuff to SQL or Slack, make sure to disable that functionality and remove Import-Module for the related module. Some of those modules you need to get separately from PowerShellGallery.

PSWinReporting - Setting up event forwarding

There is one more thing to this module. There is function helper which is able to set up forwarding for you. It adds all events that are defined to all Domain Controllers found in Forest. It requires COMPUTER which is Event Log Server to be added to Event Log Readers in Domain for it to work properly.

Import-Module PSWinReporting.psd1 -Force
Import-Module PSSharedGoods #-Force

$ReportDefinitions = @{
    ReportsAD = @{
        Servers    = @{
            Automatic = $true
            OnlyPDC   = $false
            DC        = ''
        }
        EventBased = @{
            UserChanges            = @{
                Enabled = $true
                Events  = 4720, 4738
                LogName = 'Security'
            }
            UserStatus             = @{
                Enabled = $true
                Events  = 4722, 4725, 4767, 4723, 4724, 4726
                LogName = 'Security'
            }
            ComputerCreatedChanged = @{
                Enabled     = $true
                Events      = 4741, 4742 # created, changed
                LogName     = 'Security'
                IgnoreWords = ''
            }
            ComputerDeleted        = @{
                Enabled     = $true
                Events      = 4743 # deleted
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserLockouts           = @{
                Enabled = $true
                Events  = 4740
                LogName = 'Security'
            }
            GroupMembershipChanges = @{
                Enabled = $true
                Events  = 4728, 4729, 4732, 4733, 4756, 4757, 4761, 4762
                LogName = 'Security'
            }
            GroupCreateDelete      = @{
                Enabled = $true
                Events  = 4727, 4730, 4731, 4734, 4759, 4760, 4754, 4758
                LogName = 'Security'
            }
            GroupPolicyChanges     = @{
                Enabled = $true
                Events  = 5136, 5137, 5141
                LogName = 'Security'
            }
            LogsClearedSecurity    = @{
                Enabled = $true
                Events  = 1102, 1105
                LogName = 'Security'
            }
            LogsClearedOther       = @{
                Enabled = $true
                Events  = 104
                LogName = 'System'
            }
        }
    }
}

# This is required if script is not run as admin. It will open up this script as Admin
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) { 
    Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs
    exit 
}

Start-SubscriptionService
$Providers = New-SubscriptionTemplates -ReportDefinitions $ReportDefinitions 
Set-SubscriptionTemplates -ListTemplates $Providers -DeleteOwn

#
Read-Host -Prompt "Press any key to continue"

Hope this little script will help you set up forwarding. Of course, you can do this manually, but it does save some effort.

Final Words

PSWinReporting 2.X (New Hope) is slowly coming. It currently lives as a preview in PowerShellGallery and on GitHub, there are examples on how to use it. It has some bugs, and some things don't work as I expect it to, but if you're feeling adventurous feel free to explore. It's important to know that when New Hope reaches it's time and will land in the stable channel, there will be no more possible for me to apply fixes to Legacy Edition on PSGallery as it will only have New Hope. If I release any fixes (if needed), those will be applied to Legacy branch on GitHub. It's a fair warning. If you use auto-update on PSGallery modules, you should disable it, or it will break your existing setup once New Hope becomes stable. To give you a little preview of what new version will bring…

Console Command – command that has similar functionality as the main module but now it allows you to play with it interactively

Dynamic HTML – you already saw it in Statusimo and it's using PSWriteHTML. It can nicely resize tables and is even readable on a phone. Supports most major browsers. It's able to generate Excel, CSV, and PDF directly from the HTML part. I may add some Charts to it to make it pretty. Just for you 😉

Finally, it's no longer Domain Controller monitoring only, but you will be able to use it for ADConnect, Hyper-V, whatever. You will be able to define your reports, or I can help you do so with your help (I am not Hyper-V expert so knowing which event matter and what should be monitored needs to be provided to me). You will also be able to translate or rename any fields. As you see on screenshots, every single column from those reports can now be changed to something else, removed or otherwise mangled. And that's just part of what's coming. You will have to wait a bit longer, but there were significant changes done to it. Ever wanted to send events to different events to different teams, slack channels? Send an email in the next 5 seconds if someone is added to Domain Admins groups? Well, that's coming!

Przemyslaw Klys / About Author

System Architect with over 14 years of experience in the IT field. Skilled, among others, in Active Directory, Microsoft Exchange and Office 365. Profoundly interested in PowerShell. Software geek.

PSWinReporting 1.8 – Split of branches (Legacy vs. New Hope)

Posty powiązane