EventLog_Capture - save your event logs, from multiple servers, to a database, excluding the noise

EventLog_Capture.ps1

#Note this is JUST the capture portion; there's a database component as well
# the 4 scripts below are obviously prereqs, from Hey Scripting Guy and PoshCode
clear 
. C:\powershell_scripts\invoke-sqlcmd2.ps1;
. C:\powershell_scripts\split-job.ps1;
. C:\powershell_scripts\write-datatable.ps1;
. C:\powershell_scripts\out-datatable.ps1;

$server_repository = 'yourreposerverhere'  
$database_repository = 'yourrepodbhere'

write-host (get-date)

########################
#Get the list of servers and Event Logs we need to connect.  For now, just App & System (table = EventLogs_To_Get)
# We have to use the tables because the serverlog table's "last" date is when it last ran, not when it was actually done.
########################
$serverlist = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
-query "
SELECT a.Server_Name, 
        a.EventLog, 
        isnull(MAX(TimeGenerated),DATEADD(dd, DATEDIFF(dd, 0, GetDate()-days_back_to_get), 0)) AS max_timegenerated, 
        ISNULL(MAX(RecordNumber),minimum_recordnumber) AS max_recordnumber  
FROM 
(
SELECT Server_Name, EventLog, days_back_to_get, minimum_recordnumber FROM EventLog_Servers 
CROSS JOIN EventLogs_To_Get 
WHERE Is_Active = 1
--ORDER BY Server_Name
) a LEFT OUTER JOIN [EventLog_Details] WITH (NOLOCK) 
ON EventLog_Details.EventLog = a.EventLog
AND EventLog_Details.ComputerName= a.Server_Name
AND TimeGenerated >=GETDATE()-15
GROUP BY a.Server_Name, a.EventLog, days_back_to_get, minimum_recordnumber
ORDER BY a.Server_Name" 

$serverlist


##############################
# Truncate the staging table #
##############################
#have to do it here because we can't have multiples all hitting the same table and truncating it
invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
-query "truncate table EventLog_Staged_PoSH"


###############################
# FOR EACH SERVER (SPLIT-JOB) #
###############################

#now use Split-Job (from POSHcode) to split into a number of Runspaces ("hosted runspaces")
#variables need to be inside the split-job due to scope issues, though there's a parameter (see end) that will import them.
# Need a way to trap for inside errors, since any failure causes an invisible total failure
#$serverlist| %{   #alternate version for testing

$serverlist|Split-Job {%{ 
    #because the scope is separate in here, you need to re-dot-source and reinit your parameters.
    . C:\powershell_scripts\invoke-sqlcmd2.ps1;
    . C:\powershell_scripts\write-datatable.ps1;
    . C:\powershell_scripts\out-datatable.ps1;
    #setting these here because the datatypes are screwy if passed directly. (due to the datatable)
    $computername = $_.server_name
    $eventlog = $_.EventLog
    $recordnumber = $_.max_recordnumber

try
{

    ############################
    # Clear the staging table ##
    ############################
    #Doing it again in case we somehow dupe.  
    invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
    -query "delete from EventLog_Staged_PoSH where computername = '$computername' and eventlog = '$eventlog'"


    #################################################
    # get OS version so we know which method to use #
    #################################################
    # not currently used; GWMI is fastest of the methods, in my testing

    # http://stackoverflow.com/questions/27513886/substring-to-get-text-after-second-period-in-powershell
    #get version of OS via WMI - lets us know if we can use get-winevent, get-eventlog, etc.  Annoyingly, GWMI is currently fastest.
    [version]$full_version = (get-wmiobject -class win32_operatingsystem -ComputerName $computername|select version).version

    #now set up a string we can compare against
    [string]$usable_version = "$($full_version.Major).$($full_version.Minor)"

    ####################################################
    ## Windows Server 5 (Windows Server 2003, 2003R2) ##
    ####################################################

    #Unsure if we event _want_ get-winevent for now - in testing, using filterxpath on a 6.0 box took 31 seconds, but 9 using gwmi
    #even if I swapped the order to ensure evenness, gwmi is simply faster, at least with 6.0, which can't use hashtables.
    if(($full_version.Major -eq "5") -or ($full_version.Major -eq "6")) #doing it this way until I can get the get-winevent added
    {

    #for this path, which includes WS 2003, we HAVE to go old-school.  Specifically, GWMI, which in our testing was much faster than get-eventlog
    $time = [System.Management.ManagementDateTimeConverter]::ToDmtfDateTime($_.max_timegenerated)

    #pull across the min number of fields you need
    #Does filtering on RecordNumber speed things up? No, but doesn't seem to slow it down either.  Needs more testing!
    $EventQuery = @"
    select
      Logfile, RecordNumber, Timegenerated, TimeWritten, EventCode, EventType, Type, Category, CategoryString, SourceName, InsertionStrings, ComputerName, User, Message
    from Win32_NTlogEvent
    where timewritten >='$time'
    and LogFile = '$eventlog'
    and RecordNumber > $recordnumber
"@ #this line must be un-recessed

$EventQuery

    #logging the time the eventlog pull starts
    $computername + ", " + $eventlog + " pull beginning at " + (get-date)
    $LogResults = gwmi -computername $computername -Query $EventQuery |
    select @{Label='EventLog';Expression={$_.Logfile}}, `
    RecordNumber, `
    @{LABEL="TimeGenerated"; EXPRESSION = {$_.convertToDateTime($_.TimeGenerated)}}, `
    @{LABEL="TimeWritten"; EXPRESSION = {$_.convertToDateTime($_.TimeWritten)}}, `
    @{Label='EventID';Expression={$_.EventCode}}, `
    #eventType DOES NOT MATCH existing!  One has diff values than other. is 4 = information, 2= warning, 1 = error, 5 = audit failure 
    EventType, `
    @{Label='EventTypeName';Expression={$_.Type}}, `  
    @{Label='EventCategory';Expression={$_.Category}}, `  
    @{Label='EventCategoryName';Expression={$_.CategoryString}}, `  #"None" in old, blank here
    SourceName, `                                                   #"Perflib" in old, Microsoft-Windows-Perflib here
    @{name='Strings';Expression={ $_.InsertionStrings -join '|'}}, ` #Appears to match!
    @{name='ComputerName';Expression={$_.ComputerName -replace "\..*",""}}, ` #remove the domain name (.blah.com)
    @{Label='SID';Expression={$_.User}}, `  
    @{Label='Message';Expression={$_.Message}} |out-datatable

#17s/18s  with just filter on timewritten and logfile  (two tests, mem-pr-repl-01)
#18s/18s   including recordnumber                      (two tests, mem-pr-repl-01)

    #Why no results on the console?
    #It's due to runspace (aka split-job)

    #logging the time the eventlog pull ends
    $computername + ", " + $eventlog + " pull ending at " + (get-date)
    }
        
    #Write to the table, so that we can act upon it using the SP        
    Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName EventLog_Staged_PoSH_insert -Data $LogResults

    #logging the time the eventlog write finishes
    $computername + ", " + $eventlog + " write to staged ending at " + (get-date)
    invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
    -query "EXEC Eventlog_Blacklist_Removal @ComputerName = '$computername', @EventLog = '$eventlog'"

    #logging the time the blacklist SP ends
    $computername + ", " + $eventlog + " blacklist removal ending at " + (get-date)
}    
 catch
        {
        #throw $_.Exception.Message #doesn't remove enough crap
        Write-Error $_.Exception.Message #-ErrorAction Stop  I dont want to stop it, just throw an error.
        }
	
    #Now, run a (new for this posh version) stored procedure to get it into EventLog_YYYYMM	
	
    }
 } -MaxDuration 3200 -MaxPipelines 8 -Variable server_repository, database_repository -NoProgress
 #the variable above allows the split-job to read from outside the scope and put it inside
 #max of 10 minutes because some will actually take that long; may need to set to 15-20!.
 #as you run it initially, it will take the full MaxDuration to get everything downloaded.  After several times, will settle down.
 #Perms needed – add whatever credential you’re using to “Distributed COM users group”
 # and add user to permissions on the wmi namespace “CIMV2” on each server with permissions “Allow” on “Remote Enabled”

write-host (get-date)