Writting to SQL Example 1

This example builds on the idea in Advanced Example 2 (Advanced Examples), but instead writes the information to SQL. The example makes use of an additional module ObjectToSQL that has been provided in the Downloads section.

The additional module ObjectToSQL will compare the property names of an object to the column names in a SQL table. If the name matches, the corresponding information will be written to the table. For example if the object contains (TimeCreated, ID, MachineName) properties and the SQL table contains (TimeCreated, ID, MachineName), then that information will be INSERTED into the SQL table.

For more information on what properties would be available to export to SQL, it would be advised to first test logging the information to CSV or another format. An alternative is to open the specific Event ID in either Windows 7 or Windows 2008, and then click on the "Details" tab. From there, click on the "XML View" radio button. Example property names and values can be found under the "EventData" node. Once the schema of the specific EventID(s) is understood, an appropriate SQL table can be created.

Below is an example SQL Table layout for Security Event ID 4740 or "UserAccount_Locked" as shown in some of the code examples. Using the ObjectToSQL method would add the data for all of the properties that match the custom $EventObj used in the example below. If you do not wish to have certain properties recorded, then do not include them in your SQL Table.
AccountLockouts.jpg
Note: The example is storing the entire Event XML data into SQL, so that it is available for reference at any later point in time. The additional columns that have been extracted from the XML (TargetUserName,TargetDomainName, etc.), are included to allow for direct and fast indexing of the information.

ObjectToSQL Module Information

The ObjectToSQL module includes the following mandatory parameters:
  • InputObject - The object to be output to SQL. In the case of the example, a special custom $EventObj is built.
  • WriteConnectionString - The SQL connection string for the location you will be writting to.
  • WriteTableName - The name of the Table for the data to be written.

...along with the following optional parameters:
  • ColumnPrefix - Allows for a prefix when matching property names to SQL Column names. For this example "EVNT_" is used. So for the Event object property of "MachineName" would have a corresponding SQL Column name of "EVNT_MachineName"
  • ReadConnectionString - Allows for the column names to be read from a different location in the case of mirroring, etc. This value defaults to the corresponding write value.
  • ReadTableName - Allows for the column names to be read from a different location in the case of mirroring, etc. This value defaults to the corresponding write value.

import-module ObjectToSQL
import-module EventLogWatcher

$BookmarkStreamPath = "C:\Eventlogwatchers\EventsToSQL.stream"

$BookmarkToStartFrom = Get-BookmarkToStartFrom $BookmarkStreamPath

$EventLogQuery = New-EventLogQuery "ForwardedEvents"

$EventLogWatcher = New-EventLogWatcher $EventLogQuery $BookmarkToStartFrom 

$Action = {        
            
        
            $EventObj = New-Object psobject
            $EventObj | Add-Member noteproperty TimeCreated $EventRecord.TimeCreated
            $EventObj | Add-Member noteproperty ID $EventRecord.ID
            $EventObj | Add-Member noteproperty MachineName $EventRecord.MachineName
            
            $EventRecordXML.Event.EventData.Data | Where-Object {$_.name -ne $Null} | ForEach-Object {$EventObj | Add-Member noteproperty $_.name $_."#text"}
          
            $EventObj | Add-Member noteproperty EventRecordXML $EventRecord.ToXML()
          
                 switch ($EventObj.ID) 
            { 
                # User Account
                4720 {$TableName = "UserAccount_Created"}
                4722 {$TableName = "UserAccount_Enabled"}
                4723 {$TableName = "UserAccount_PWChange"}
                4724 {$TableName = "UserAccount_PWReset"}
                4725 {$TableName = "UserAccount_Disabled"}
                4726 {$TableName = "UserAccount_Deleted"}
                4738 {$TableName = "UserAccount_Changed"}
                4740 {$TableName = "UserAccount_Locked"}
                4767 {$TableName = "UserAccount_Unlocked"}   
                        
                # Global Security Groups     
                4727 {$TableName = "SecurityGroup_Created"}           
                4728 {$TableName = "SecurityGroup_MemberAdded"}     
                4729 {$TableName = "SecurityGroup_MemberRemoved"}  
                4730 {$TableName = "SecurityGroup_Deleted"}
                4737 {$TableName = "SecurityGroup_Changed"}        
                
                # Universal Security Groups     
                4754 {$TableName = "SecurityGroup_Created"}           
                4756 {$TableName = "SecurityGroup_MemberAdded"}     
                4757 {$TableName = "SecurityGroup_MemberRemoved"}  
                4758 {$TableName = "SecurityGroup_Deleted"}   
                4755 {$TableName = "SecurityGroup_Changed"}
                
                # Local Security Groups     
                4731 {$TableName = "SecurityGroup_Created"}           
                4732 {$TableName = "SecurityGroup_MemberAdded"}     
                4733 {$TableName = "SecurityGroup_MemberRemoved"}  
                4734 {$TableName = "SecurityGroup_Deleted"}
                4735 {$TableName = "SecurityGroup_Changed"}  
                
                # Domain Policy
                4739 {$TableName = "DomainPolicy_Changed"}
                
                # Computer Accounts
                4741 {$TableName = "ComputerAccount_Created"}
                4742 {$TableName = "ComputerAccount_Changed"}
                4743 {$TableName = "ComputerAccount_Deleted"}
                                
                default {$TableName = $Null}
            }       
            
            [string]$DataSource = "SeverName\InstanceName"     # Change for your Environment.  Could be changed to a parameter for a script
            [string]$Database = "DataBaseName"                 # Change for your Environment.  Could be changed to a parameter for a script
            
            [string]$connectionString = "Data Source=$dataSource; Initial Catalog=$database; Integrated Security=SSPI;"
     
            If ($TableName -ne $Null)
            {
                Write-ObjectToSQL $EventObj $connectionString $TableName -ColumnPrefix "EVNT_"
            }
            
          } 
          
Register-EventRecordWrittenEvent $EventLogWatcher $BookmarkStreamPath -action $action -SourceIdentifier "EventsToSQL"

$EventLogWatcher.Enabled = $True 

Last edited Jun 24, 2011 at 9:52 PM by sgrinker, version 11

Comments

No comments yet.