PSWinReporting - Forwarders, Microsoft Teams, Slack, Microsoft SQL and more

By Przemyslaw Klys Active Directory PowerShell Windows 16 września, 2018

It's been a while since PSWinReporting has been updated, or rather since I've written a blog post about it since it's always work in progress. This release is just a wrap up for multiple new features that were added and tested last few weeks, months.

What's new?

Where do we start….

Support for Event Forwarding – monitoring one event log instead of scanning all domain controllers

Support for Microsoft Teams – Sending events as they happen to Microsoft Teams (only supported when forwarders are in use)

Support for Slack – Sending events as they happen to Slack (only supported when forwarders are in use)

Support for Microsoft SQL – Sending events directly to SQL (some people prefer it that way)

Support for backing up old archived logs (moves logs from Domain Controllers into chosen place)

Support for re-scanning logs from files – a way to recheck your logs for missing information

Each new feature brings something to the table. Just to add that not everything is enabled everywhere. It's not possible or rather it doesn't make much sense to use Microsoft Teams / Slack notifications in the old method of scanning event logs. Largely because it would required 30+ minutes per scan to finish depending on how large environment you have. Instead those methods rely on using Windows Event Forwarding where all domain controllers report additionally to one central place and store Event Logs in there. Of course only the one you want, request it to. Therefore it's possible to have your Domain Controllers log everything that happen and essentially have 40GB of Logs each month and still have 100MB of essential data that this module is able to parse.

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.

Windows Event Forwarding - How do I use it?

Windows Event Forwarding is a bit of pain to setup and get it run correctly. This module has some functions built-in that are supposed to help you set it up correctly from A to Y. The last Z step you may need to do yourself. And that are permissions. Keep in mind that you can either choose totally separate machine for this task (but as secure as your DC) or even use your DC if you have small enough domain. I'm encouraging you to start using Forwarding for a simple reason.. scanning 2,5,10 domain controllers for events that often store 99% more information is both time consuming and hard to maintain. By enabling Windows Event Forwarding you get few new features and old features work in seconds/minutes instead of minutes/hours.

To get it up and running you need 4 things:

You need to create Subscription for LOGS on your Windows Log Collector so that it can PULL data from Domain Controllers

You need a script (RunMe-TriggerOnEvents.ps1) that will be executed anytime new event log is added on Windows Log Collector

You need to attach that script to Task Scheduler so that when new event is pulled from Domain Controllers it's fed to the script. This can be setup by hand or by using RunMe-CreateTasks.ps1 that is part of this package.

You need to have permissions to set this up (which is a bit out of scope of this module for now)

Keep in mind that this script sets up things on defaults. It doesn't ask you for credentials, it doesn't set up any permissions. This means that if new Task Schedule is created it works under SYSTEM account. Same goes for Subscriptions. If it's setup it uses Machine Account. You can and should change that to match your domain.

Import-Module PSWinReporting -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'
            }
        }
    }
}

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

Script above sets up Event Log Subscriptions. When it's run it should create 1 or more Subscriptions that will cover your whole domain and what you've set up in configuration. Depending on what you've chosen in config it can be 1,2 or 3 new subscriptions. When you rerun the script it will create all necessary providers and will use built-in template that comes with PowerShell Module. It's encouraged to use this module directly from PowerShellGallery. Keep in mind that script when rerun will delete it's own providers and recreate them on per need basis. This means you can enable some things now, and simply add things at later date.

And when you check Event Viewer you should have something similar to the one below

When you right click on each of subscriptions you can check Run time status. It's very important step and you should spend time on getting this right. It's a matter of permissions or WinRM to get this run properly. Don't be discouraged. It will work 😉

If everything went OK, you should start seeing events you chosen (and couple of default ones) in your Forwarded Events log

Next step is to copy script below and putting it in place that will be used for Task Scheduler to start it.

# Collects all named paramters (all others end up in $Args)
param(
    $eventid = 1105,
    $eventRecordID = 1854610, # 425358 ,
    $eventChannel,
    $eventSeverity
)
<#
Update-Module PSTeams
Update-Module PSEventViewer
Update-Module PSWinReporting
Update-Module PSWriteColor
Update-Module PSWriteExcel
Update-Module PSSlack
Update-Module DBATools
#>
Import-Module PSTeams
Import-Module PSEventViewer
Import-Module PSWinReporting -Force
Import-Module PSWriteColor
Import-Module PSSlack
Import-Module DBATools
Import-Module PSSharedGoods #-Force

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

    AsExcel               = $true # attaches Excel to email with all events, required PSWriteExcel 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   = $false
        LogFile    = 'C:\testing.log'
        TimeFormat = 'yyyy-MM-dd HH:mm:ss'
    }
    Debug                 = @{
        DisplayTemplateHTML = $false
        Verbose             = $false
    }
    Notifications         = @{
        MicrosoftTeams = @{
            Use     = $false
            TeamsID = ''
        }
        Slack          = @{
            Use     = $false
            Channel = '#general'
            Uri     = ""
        }
        MSSQL          = @{
            Use                   = $false
            SqlServer             = 'EVO1'
            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
            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
            }
        }
    }
    Backup                = @{
        Use             = $true
        DestinationPath = 'C:\MyEvents\'
    }
}
$ReportDefinitions = @{
    TimeToGenerate = $false
    TeamsID        = ''

    ReportsAD      = @{
        Servers    = @{
            ForwardServer   = $env:COMPUTERNAME
            ForwardEventLog = 'ForwardedEvents'
        }
        EventBased = @{
            UserChanges            = @{
                Enabled     = $true
                Events      = 4720, 4738
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserStatus             = @{
                Enabled     = $true
                Events      = 4722, 4725, 4767, 4723, 4724, 4726
                LogName     = 'Security'
                IgnoreWords = @{
                    'Domain Controller' = ''
                    'Action'            = ''
                    'User Affected'     = 'Win-*', '*AD1$*'
                    'Who'               = ''
                    'When'              = ''
                    'Event ID'          = ''
                    'Record ID'         = ''
                }
            }
            UserLockouts           = @{
                Enabled     = $true
                Events      = 4740
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserLogon              = @{
                Enabled     = $false
                Events      = 4624
                LogName     = 'Security'
                IgnoreWords = ''
            }
            ComputerCreatedChanged = @{
                Enabled     = $true
                Events      = 4741, 4742 # created, changed
                LogName     = 'Security'
                IgnoreWords = ''
            }
            ComputerDeleted        = @{
                Enabled     = $true
                Events      = 4743 # deleted
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserLogonKerberos      = @{
                Enabled     = $false
                Events      = 4768
                LogName     = 'Security'
                IgnoreWords = ''
            }
            GroupMembershipChanges = @{
                Enabled     = $true
                Events      = 4728, 4729, 4732, 4733, 4756, 4757, 4761, 4762
                LogName     = 'Security'
                IgnoreWords = @{
                    'Who' = '*ANONYMOUS*'
                }
            }
            GroupCreateDelete      = @{
                Enabled     = $true
                Events      = 4727, 4730, 4731, 4734, 4759, 4760, 4754, 4758
                LogName     = 'Security'
                IgnoreWords = @{
                    'Who' = '*ANONYMOUS*'
                }
            }
            GroupPolicyChanges     = @{
                Enabled     = $false
                Events      = 5136, 5137, 5141
                LogName     = 'Security'
                IgnoreWords = ''
            }
            LogsClearedSecurity    = @{
                Enabled     = $true
                Events      = 1102, 1105
                LogName     = 'Security'
                IgnoreWords = ''
            }
            LogsClearedOther       = @{
                Enabled     = $true
                Events      = 104
                LogName     = 'System'
                IgnoreWords = ''
            }
            EventsReboots          = @{
                Enabled     = $false
                Events      = 1001, 1018, 1, 12, 13, 42, 41, 109, 1, 6005, 6006, 6008, 6013
                LogName     = 'System'
                IgnoreWords = ''
            }
        }
    }
}

Start-Notifications -ReportDefinitions $ReportDefinitions -ReportOptions $ReportOptions -EventID $EventID -EventRecordID $EventRecordID -EventChannel $EventChannel #-Verbose

Keep in mind this script when started will do nothing by itself. On top of it there are 4 parameters with 2 filled in (EventID and EventRecordID). If you want to use this by hand for verification you can find and Event in your Forwarded Events log and use that as a testing exercise to see if you're getting any data to MS Teams, Slack or SQL.

Finally there is one last script that has to be run (or you can set it up manually). Getting the script above to be executed by Task Scheduler when event happens. This script below will create new Task and attach it to ForwardedEvents logs.

Import-Module PSWinReporting -Force
Import-Module PSSharedGoods -Force

$TaskName = 'ForwardedEvents'
$TaskPath = '\Event Viewer Tasks\'
$Author = 'EVOTEC'
$URI = '\Event Viewer Tasks\ForwardedEvents'
$Command = 'powershell.exe'
$Argument = @('-windowstyle hidden', 'C:\Support\GitHub\PSWinReporting\Examples\RunMe-TriggerOnEvents.ps1', '-EventID $(eventID) -eventRecordID $(eventRecordID) -eventChannel $(eventChannel) -eventSeverity $(eventSeverity)')

Remove-TaskScheduledForwarder -TaskPath $TaskPath -TaskName $TaskName
Add-TaskScheduledForwarder -TaskPath $TaskPath -TaskName $TaskName -Author $Author -URI $Uri -Command $Command -Argument $Argument

Output should be something similar to below. Keep in mind that we again use Template that comes in with this PowerShell Module. You should verify created Task whether it has proper permissions and proper setup.

After all those 3 scripts are setup you really need to test this out. Make sure that if you run the task manually that you get notifications you expect (that's why the “default” test data comes useful in the defined parameters). It may take a while before you get this right. It's very important step to get other things running properly.

PSWinReporting Information

Main script changes

There were some subtle config changes, additions to RunMe-Report.ps1. It should still work the old way but new way of using forwarders is encouraged. Also in this new version Get-ADForest is used to get all Domain Controllers. Not just Get-ADDomain. Therefore you may get some new DC's showing up if you have more then one domain.

### Starts Module (Requires config above)
Clear-Host
Import-Module PSWinReporting #-Force
Import-Module PSSharedGoods #-Force

$EmailParameters = @{
    EmailFrom            = "[email protected]"
    EmailTo              = "[email protected], [email protected]"
    EmailCC              = ""
    EmailBCC             = ""
    EmailReplyTo         = ""
    EmailServer          = "smtp.office365.com"
    EmailServerPassword  = "YourPassword"
    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'
    }
    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               = $true # attaches Excel to email with all events, required PSWriteExcel 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             = $false
    }
    RemoveDuplicates = $false
}
$ReportTimes = @{
    # Report Per Hour
    PastHour             = $true # 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   = $false # if $true skips Automatic/OnlyPDC/DC for reading logs. However it uses Automatic to deliver size of logs so keep Automatic to $true
            ForwardServer   = 'EVO1'
            ForwardEventLog = 'ForwardedEvents'
            UseDirectScan = $true
            Automatic       = $true # will use all DCs for a forest
            OnlyPDC         = $false # will use PDC of current domain returned by Get-ADDomain
            DC              = ''
        }
        EventBased = @{
            UserChanges            = @{
                Enabled     = $true
                Events      = 4720, 4738
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserStatus             = @{
                Enabled     = $true
                Events      = 4722, 4725, 4767, 4723, 4724, 4726
                LogName     = 'Security'
                IgnoreWords = @{
                    'Domain Controller' = ''
                    'Action'            = ''
                    'User Affected'     = 'Win-*', '*AD1$*'
                    'Who'               = ''
                    'When'              = ''
                    'Event ID'          = ''
                    'Record ID'         = ''
                }
            }
            UserLockouts           = @{
                Enabled     = $true
                Events      = 4740
                LogName     = 'Security'
                IgnoreWords = ''
            }
            ComputerCreatedChanged = @{
                Enabled     = $true
                Events      = 4741, 4742 # created, changed
                LogName     = 'Security'
                IgnoreWords = ''
            }
            ComputerDeleted        = @{
                Enabled     = $true
                Events      = 4743 # deleted
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserLogon              = @{
                Enabled     = $false
                Events      = 4624
                LogName     = 'Security'
                IgnoreWords = ''
            }
            UserLogonKerberos      = @{
                Enabled     = $false
                Events      = 4768
                LogName     = 'Security'
                IgnoreWords = ''
            }
            GroupMembershipChanges = @{
                Enabled     = $true
                Events      = 4728, 4729, 4732, 4733, 4756, 4757, 4761, 4762
                LogName     = 'Security'
                IgnoreWords = @{
                    'Who' = '*ANONYMOUS*'
                }
            }
            GroupCreateDelete      = @{
                Enabled     = $true
                Events      = 4727, 4730, 4731, 4734, 4759, 4760, 4754, 4758
                LogName     = 'Security'
                IgnoreWords = @{
                    'Who' = '*ANONYMOUS*'
                }
            }
            GroupPolicyChanges     = @{
                Enabled     = $true
                Events      = 5136, 5137, 5141
                LogName     = 'Security'
                IgnoreWords = ''
            }
            LogsClearedSecurity    = @{
                Enabled     = $true
                Events      = 1102, 1105
                LogName     = 'Security'
                IgnoreWords = ''
            }
            LogsClearedOther       = @{
                Enabled     = $true
                Events      = 104
                LogName     = 'System'
                IgnoreWords = ''
            }
            EventsReboots          = @{
                Enabled     = $false
                Events      = 1001, 1018, 1, 12, 13, 42, 41, 109, 1, 6005, 6006, 6008, 6013
                LogName     = 'System'
                IgnoreWords = ''
            }
        }
        Custom     = @{
            EventLogSize = @{
                Enabled = $true
                Logs    = 'Security', 'Application', 'System'
                SortBy  = ''
            }
            ServersData  = @{
                Enabled = $true
            }
        }
    }
}

Start-ADReporting -EmailParameters $EmailParameters -FormattingParameters $FormattingParameters -ReportOptions $ReportOptions -ReportTimes $ReportTimes -ReportDefinitions $ReportDefinitions

I've highlighted few, important changes above. But if you've been using very old version it's best to start over and use new config file instead.

PSWinReporting Information

Windows Event Forwarding - Sending to Slack

As you can see below PSWinReporting with help of PSSlack module from RamblingCookieMonster is able to deliver almost instant notifications to your Slack channel as things happen. Of course there will be some delay due to forwarding of events but the message should show up within 1 minute since things happend.

Or it can be something like this

This is only available on RunMe-TriggerOnEvents.ps1 where you simply define parameters in section as shown

        Slack          = @{
            Use     = $false
            Channel = '#general'
            Uri     = ""
        }

Windows Event Forwarding - Sending to Microsoft Teams

Just like with Slack you ‘re able to send any notification straight to Microsoft Teams.

Settings for Microsoft Teams are similar to Slack. If you need to know how to set it up you can follow setup procedure on the Microsoft Teams module page. Other than that.. it's just matter of 2 parameters. Channel name and TeamsID (kind of API key). Keep in mind it's impossible to send notification to Chat.

        MicrosoftTeams = @{
            Use     = $false
            TeamsID = ''
        }

PSWinReporting Information

Sending to Microsoft SQL

This feature is working but a bit unfinished and will get some love in next release. Basically you define different parameters but the way it's written now assumes you want all Event Logs to be combined into one big table which may not be required by everyone. Couple of things to know before using this:

SqlTableMapping – if you define the mapping of SQL columns script will work with that. Meaning if you forget, remove or change name it will be like you wanted. Data types for columns only matter if you're using SqlTableCreate below
SqlTableCreate – means script will create a table if needed based on SqlTableMapping. However if no SqlTableMapping is available script will create table anyways based on fields and data in columns. It will also try to guess the type of data in columns.
SqlTableAlter – when that is set to true and there is no SqlTableMapping any time new row is added it will check for missing columns and alter the table. It only works if there is no SqlTableMapping.

        MSSQL          = @{
            Use                   = $false
            SqlServer             = 'EVO1'
            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
            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
            }
        }

You can see SqlTableAlter/SqlTableCreate in action below

Which gives us both table creation and insert of 1 row.

What happens if we have SqlTableAlter enabled and new event comes along but with different fields…

As seen above Alter Table SQL was created and was run before Insert was run. Lets see how it looks on the MS SQL side

If AlterTable would be dissallowed it would simply fill in what is defined

In few screens above you can also see one more thing. Copy of Event Log file on Event. It's a feature I've added but not tested much. Probably needs some more love as well.

As you can see above the file was skipped because it didn't exists. It didn't exists since I've already copied it multiple times. But if it was there and permissions would work… it would move it to defined place.

PSWinReporting Information

To finalize this blog… please keep ind mind I've tested this on limited number of systems, resources and it may not be supporting everything you need. Feel free to report bugs / features requests and if time allows me to fix them I will. Also please notice there has been few additions/removals to this module. I've extracted some functions that almost all my projects share to separate module PSSharedGoods. It's heavy work in progress as in I'm slowly removing things from my modules and putting in there, things that are common to most of my projects or are in other ways useful to me. For installation instructions you should refresh the main PSWinReporting page! Keep in mind that if you want to use all those features you have to install few new modules. But if you don't use Teams, Slack or SQL you don't need those additional modules.

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 – Forwarders, Microsoft Teams, Slack, Microsoft SQL and more

Posty powiązane