Unclear: how to start SQL export

[rraschke]

Hi! I have no clue how to start the SQL export after the reporting has finished. "Start-AdReporting" generates my Excel sheet and sends me an email, but how do fill the event data to my SQL db? I have added

` Notifications = @{ MicrosoftTeams = @{ Use = $false TeamsID = '' } Slack = @{ Use = $false Channel = '#general' Uri = "" } MSSQL = @{ Use = $true SqlServer = 'DBSERVERNAME' SqlDatabase = 'SLM-Test' 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 } } }`

to my script, but how do I trigger this?

Regards Robert

[PrzemyslawKlys]

Hi,

Sorry for the long delay but as I noticed I've neglected SQL description on my last article and even worse I've changed things around how they work and how you're supposed to use it.

https://evotec.xyz/pswinreporting-1-8-split-of-branches-legacy-vs-new-hope/

Here's the blog post about it. Hope it will be clear for you. If it's still not clear how to use it, let me know!

[rraschke]

Hi,

thanks for the response and don't worry about delaying... this is "open source" at last. ;-)

Unfortunately, it still does not work. First, I needed to install and import "Invoke-Sqlcmd2", I did not see or read this anywhere until I found the error output in the console. Ok, thats imported and ready, now there is this error in the console output:

MS SQL Output: Error occured (Send-SqlInsert): A parameter cannot be found that matches parameter name 'SqlInstance'.

In the "$ReportOptions", I have this information:

AsSql                 = @{
        Use                   = $true
        SqlServer             = 'MySQLServerName'
        SqlDatabase           = 'SLM-Test'
        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
[...]

The SQLServer instance name is the default (MSSQLSERVER). Any ideas?

Kind regards Robert

[PrzemyslawKlys]

No. Invoke was renamed in last dbatools.

Use Install-Module PSwinReporting -force it will update pssharedgoods which was updated to support new version.

[PrzemyslawKlys]

Its called now Invoke-DbaQuery i think and you need to have dbatools and pssharedgoods up to date.

[PrzemyslawKlys]

In other words, you need:

• PSWinReporting
• DbaTools
• PSSharedGoods

Those 3 modules are responsible for SQL part. You probably have outdated PSSharedGoods and new DbaTools. It used to be called Invoke-SqlCmd2 but DBATools renamed it. Since there is another module called Invoke-SqlCmd2 it was conflicting and I guess that's why DBATools renamed it. That's why you're getting errors because you're using the wrong module. Simply uninstall that module, and install all 3 modules above with -Force parameter.

[rraschke]

Sorry for the delay...

I have updated all three modules you mentioned. Unfortunately there is still something wrong... I have attached a PDF with my AsSql-Configuration and the console output of the "Start-ADReporting" with the "SqlTableCreate" option set to "true" and "false".

SQL server name is correct, the database name is correct and the table has been created by the script during the first run. Still it tells me, the table does not exist...

[PrzemyslawKlys]

Well, the part of config looks ok with one small hint being - in a name of your SQL DB. Could you test it without (maybe another DB)? Maybe I have a bug on how I handle that.

[PrzemyslawKlys]

Ye, it's for sure - in the DB name. You can use

AsSql                 = @{
        Use                   = $true
        SqlServer             = 'MySQLServerName'
        SqlDatabase           = '[SLM-Test]'
        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

I can try to fix this.

[PrzemyslawKlys]

Hrmms, actually you can't use [SLM-TEST]. But it works partially. It saves the data to table, but in my case it fails on other place. I'll fix this up. You can use name without - in the meantime.

[PrzemyslawKlys]

Update-Module PSsharedGoods

And test it. Should work in both cases now.

[rraschke]

Yes, now it works, all data are transferred to the database. Thanks a lot!

[rraschke]

Sorry, to come back again...

"User changes" and "Computer Status Changes" result in the follwing error output (not always, I already have some computer status changes in the DB):

Sending User Changes to SQL at Global level
WARNING: [09:51:14][Invoke-DbaQuery] [MyDBServerName] Failed during execution | The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

Sending Computer Status Changes to SQL at Global level
WARNING: [09:51:18][Invoke-DbaQuery] [MyDBServerName] Failed during execution | The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

[PrzemyslawKlys]

Ye, it's because Create-Table created a table based on table mapping. If there was a date in it created a DateTime field, in DB. Most likely subsequent entries have mismatched data and so it's not able to add it properly to DB.

Verify your DB Schema for fields of DateTime type and decided to convert them to varchar.

[PrzemyslawKlys]

I believe you need to modify AccountExpires or PasswordLastSet to varchar. I've wrongly added DateTime in there but the format isn't actual datetime.

[rraschke]

That was it. After changing the data type in the database, all events are imported.

Thanks again!