I do not understand how to use SQL

[sysop200]

How to use sql?
I created a database, but it doesn’t work out how to create the tables correctly. "Alter" does not help.
Where do the column names come from and what types to use?
Help me please.
PSWinReporting-Manual.log

[Erleshofer]

hi evotech

please help with PSWinReporting writing to SQL, so far i love your module all is working except:

i have the same issue as the other in this thread and cannot get PSWinReporting write to SQL
what did i do: configured event forwarding to just dig one log
created a remote sql db and made a user with permissions
basically i am using your config from here: https://evotec.xyz/hub/scripts/pswinreporting-powershell-module/
just replacing SqlServer and SqlDatabase with my SQL hostname, DB name and switching verbose/debug to true

i get alot of these in the verbose log (i replaced actual names with X)
[2019-12-27 08:15:27][i] Sending User Changes to SQL at Global level
[2019-12-27 08:21:50][i] MS SQL Output: IF NOT EXISTS ( SELECT 1 FROM dbo.[Events] WHERE [DomainController] = 'XXXXXXXXXX' AND [EventRecordID] = '6871291722' ) BEGIN INSERT INTO dbo.[Events] ( [DomainController],[Action],[UserAffected],[SamAccountName],[DisplayName],[UserPrincipalName],[HomeDirectory],[HomePath],[ScriptPath],[ProfilePath],[PasswordLastSet],[AccountExpires],[PrimaryGroupId],[AllowedToDelegateTo],[OldUacValue],[NewUacValue],[UserAccountControl],[UserParameters],[SidHistory],[LogonHours],[EventWho],[EventWhen],[EventID],[EventRecordID],[GatheredFrom],[GatheredLogName],[EventAdded],[EventAddedWho] ) VALUES ( 'XXXXXXX',NULL,'XXX\xxx',NULL,NULL,NULL,'%%1793','%%1793','%%1793','%%1793',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%%1792',NULL,NULL,'XXX\XXXXXXXX','2019-12-27 07:00:22','4738','6871291722','XXXXXX','ForwardedEvents','2019-12-27 08:15:34','xxxxx' ) END

[2019-12-27 08:41:05][i] Sending User Lockouts to SQL at Global level
[2019-12-27 08:41:07][i] MS SQL Output: IF NOT EXISTS ( SELECT 1 FROM dbo.[Events] WHE[2019-12-27 08:15:27][i] Sending User Changes to SQL at Global level
[2019-12-27 08:21:50][i] MS SQL Output: IF NOT EXISTS ( SELECT 1 FROM dbo.[Events] WHERE [DomainController] = 'XXXXXXXXXX' AND [EventRecordID] = '6871291722' ) BEGIN INSERT INTO dbo.[Events] ( [DomainController],[Action],[UserAffected],[SamAccountName],[DisplayName],[UserPrincipalName],[HomeDirectory],[HomePath],[ScriptPath],[ProfilePath],[PasswordLastSet],[AccountExpires],[PrimaryGroupId],[AllowedToDelegateTo],[OldUacValue],[NewUacValue],[UserAccountControl],[UserParameters],[SidHistory],[LogonHours],[EventWho],[EventWhen],[EventID],[EventRecordID],[GatheredFrom],[GatheredLogName],[EventAdded],[EventAddedWho] ) VALUES ( 'XXXXXXX',NULL,'XXX\xxx',NULL,NULL,NULL,'%%1793','%%1793','%%1793','%%1793',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'%%1792',NULL,NULL,'XXX\XXXXXXXX','2019-12-27 07:00:22','4738','6871291722','XXXXXX','ForwardedEvents','2019-12-27 08:15:34','xxxxx' ) END

[2019-12-27 08:41:05][i] Sending User Lockouts to SQL at Global levelRE [DomainController] = 'XXXXXXX' AND [EventRecordID] = '6871380450' ) BEGIN INSERT INTO dbo.[Events] ( [DomainController],[Action],[ComputerLockoutOn],[UserAffected],[ReportedBy],[EventWhen],[EventID],[EventRecordID],[GatheredFrom],[GatheredLogName],[EventAdded],[EventAddedWho] ) VALUES ( 'XXXX',NULL,'XXXXXXXX','xxx','XXX\XXXXX$','2019-12-27 07:07:05','4740','6871380450','XXXXXXXX','ForwardedEvents','2019-12-27 08:41:05','XXXXX' ) END

i get this in the console
[2019-12-27 08:41:05][i] Sending User Lockouts to SQL at Global level
VERBOSE: Send-SqlInsert - Sql Table exists, Alter is allowed, but SqlTableMapping is already defined
WARNING: [08:41:05][Invoke-DbaQuery] [XXXXXX] Failed during execution | Invalid object name 'dbo.Events'.

and this

[2019-12-27 08:15:27][i] Sending User Changes to SQL at Global level
VERBOSE: Send-SqlInsert - Sql Table exists, Alter is allowed, but SqlTableMapping is already defined
WARNING: [08:15:51][Invoke-DbaQuery] [XXXXXXX] Failed during execution | Invalid object name 'dbo.Events'.

i see that the user is logged on to the DB with SQL Activity-Monitor when this happens
also stored-procedure sp_who tells me that the user "finds" to the SQL server and the DB
when i try manually connecting with dbaTools to Connect-DbaInstance it works flawlessly

how can i further debug this?

[PrzemyslawKlys]

get-module -listavailable dbatools

Show me that please?

[Erleshofer]

PS C:\Windows\system32> get-module -listavailable dbatools


    Directory: C:\Program Files\WindowsPowerShell\Modules


ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Script     1.0.77     dbatools                            {Select-DbaObject, Set-DbatoolsConfig, Start-DbaMigration,...
Script     1.0.61     dbatools                            {Select-DbaObject, Set-DbatoolsConfig, Start-DbaMigration,...

[PrzemyslawKlys]

I can reproduce. Will try to update the module to fix those issues.

[PrzemyslawKlys]

I was able to fix it. The issue comes from Invoke-SqlQuery which at some point changed parameter name. I fixed it once but only for sending queries, but not for creating/verifying table exists.

Update-Module PSSharedGoods

This should fix it after restarting of PowerShell (or just force import it). Maybe I'll release update to PSWinReporting with merged PSsharedgoods functions but for now that's simpler.

Give this a go and let me know

[Erleshofer]

Update-Module PSSharedGoods
did the trick
many thanks for locating the problem!