search

EvotecIT / PSWinReporting

This PowerShell Module has multiple functionalities, but one of the signature features of this module is the ability to parse Security logs on Domain Controllers providing easy to use access to AD Events.
MIT License
701 stars 69 forks source link

I do not understand how to use SQL #58

Closed sysop200 closed 4 years ago

sysop200 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago 
get-module -listavailable dbatools

Show me that please?

Erleshofer commented 4 years ago 
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 commented 4 years ago

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

PrzemyslawKlys commented 4 years ago

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 commented 4 years ago

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