none
Powershell: Быстрая обработка журналов с импортом в SQL RRS feed

  • Вопрос

  • $Evtxs = (Get-ChildItem -Path "D:\Evtx_Inbox\*.evtx" | Where {$_.LastWriteTime -lt (Date).AddHours(-4)} | Sort-Object LastWriteTime).FullName

    $XmlFilter = @'
    <QueryList>
      <Query Id="0">
        <Select>*[System[(EventID=4688 or EventID=4689 or EventID=4647 or EventID=4648)]]</Select>
        <Suppress>
          *[EventData[(Data[@Name='SubjectUserSid']='S-1-0-0') or (Data[@Name='SubjectUserSid']='S-1-5-18') or (Data[@Name='SubjectUserSid']='S-1-5-19') or (Data[@Name='SubjectUserSid']='S-1-5-20') or (Data[@Name='SubjectUserSid']='S-1-5

    -90-0-1') or (Data[@Name='SubjectUserSid']='S-1-5-90-0-2') or (Data[@Name='SubjectUserSid']='S-1-5-90-0-3') or (Data[@Name='SubjectUserSid']='S-1-5-90-0-4') or (Data[@Name='SubjectUserSid']='S-1-5-90-0-5')]]
        </Suppress>
      </Query>
      <Query Id="1">
        <Select>*[System[(EventID=1100 or EventID=4608 or EventID=4609 or EventID=4778 or EventID=4779 or EventID=4800 or EventID=4801 or EventID=4802 or EventID=4803)]]</Select>
      </Query>
      <Query Id="2">
        <Select>*[System[(EventID=4624 or EventID=4634)]]</Select>
        <Suppress>
          *[EventData[(Data[@Name='TargetUserSid']='S-1-0-0') or (Data[@Name='TargetUserSid']='S-1-5-7') or (Data[@Name='TargetUserSid']='S-1-5-18') or (Data[@Name='TargetUserSid']='S-1-5-19') or (Data[@Name='TargetUserSid']='S-1-5-20') or

    (Data[@Name='TargetUserSid']='S-1-5-90-0-1') or (Data[@Name='TargetUserSid']='S-1-5-90-0-2') or (Data[@Name='TargetUserSid']='S-1-5-90-0-3') or (Data[@Name='TargetUserSid']='S-1-5-90-0-4') or (Data[@Name='TargetUserSid']='S-1-5-90-0-

    5')]]
        </Suppress>
      </Query>
    </QueryList>
    '@

    $MaxThreads = 10

    $Events = @()
    $EventsAccess = @()


    Function Get-Type {
       Param($Type)

       $Types = @(
          'System.Boolean',
          'System.Byte[]',
          'System.Byte',
          'System.Char',
          'System.Datetime',
          'System.Decimal',
          'System.Double',
          'System.Guid',
          'System.Int16',
          'System.Int32',
          'System.Int64',
          'System.Single',
          'System.UInt16',
          'System.UInt32',
          'System.UInt64')
       If ( $Types -contains $Type ) {
          Write-Output "$Type"
       }
       Else {
          Write-Output 'System.String'
       }
    } # Get-Type

    Function Out-DataTable {
       [CmdletBinding()]
       Param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)

       Begin {
          $DT = new-object Data.datatable 
          $First = $True
       }
       Process {
          ForEach ($object in $InputObject) {
             $DR = $DT.NewRow() 
             ForEach($property in $object.PsObject.get_properties()) { 
                If ($First) { 
                   $Col = New-Object Data.DataColumn 
                   $Col.ColumnName = $property.Name.ToString() 
                   If ($property.value) {
                      If ($property.value -isnot [System.DBNull]) {
                         $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")
                      }
                   }
                   $DT.Columns.Add($Col)
                } 
                If ($property.Gettype().IsArray) {
                   $DR.Item($property.Name) = $property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1
                } 
                Else {
                   $DR.Item($property.Name) = $property.value
                }
             } 
             $DT.Rows.Add($DR) 
             $First = $false
          }
       } # Process
       End {
          Write-Output @(,($dt))
       }
    } # Out-DataTable


    $ScriptBlock = {
       Param($Log, $XmlFilter)

       Get-WinEvent -Path $Log -FilterXPath $XmlFilter |`
          Select RecordID, TimeCreated, MachineName, ID,
             @{n="TargetUserSid";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetUserSid"} | %{$_.'#text'}}},
             @{n="TargetUserName";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetUserName"} | %{$_.'#text'}}},
             @{n="TargetDomainName";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetDomainName"} | %{$_.'#text'}}},
             @{n="TargetLogonId";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetLogonId"} | %{$_.'#text'}}},
             @{n="LogonType";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "LogonType"} | %{$_.'#text'}}},
             @{n="AccountName";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "AccountName"} | %{$_.'#text'}}},
             @{n="AccountDomain";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "AccountDomain"} | %{$_.'#text'}}},
             @{n="LogonID";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "LogonID"} | %{$_.'#text'}}},
             @{n="ClientName";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "ClientName"} | %{$_.'#text'}}},
             @{n="ClientAddress";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "ClientAddress"} | %{$_.'#text'}}},
             @{n="TargetServerName";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetServerName"} | %{$_.'#text'}}},
             @{n="TargetInfo";e={([xml]$_.ToXml()).Event.EventData.Data | ?{$_.Name -eq "TargetInfo"} | %{$_.'#text'}}} | ?{($_.TargetUserName -notlike "*`$") -or !($_.TargetUserName) }

    } #ScriptBlock

    $RunspaceCollection = @()
    $RunspacePool = [RunspaceFactory]::CreateRunspacePool(1, $MaxThreads)
    $RunspacePool.Open()

    $Pool = [System.Collections.Generic.List[System.Object]]::new()
    $Count = 0

    ForEach ($Evtx in $Evtxs) {
       $Count++
       $Pool.Add($Evtx)
       If($Count -eq $MaxThreads) {
          Foreach ($Log in $Pool) {
             $Powershell = [PowerShell]::Create().AddScript($ScriptBlock).AddArgument($Log).AddArgument($XmlFilter)
             $Powershell.RunspacePool = $RunspacePool
             [Collections.Arraylist]$RunspaceCollection += New-Object -TypeName PSObject -Property @{
                Runspace = $PowerShell.BeginInvoke()
                PowerShell = $PowerShell
             }
          }
          While($RunspaceCollection) {
             Foreach ($Runspace in $RunspaceCollection.ToArray()) {
                If ($Runspace.Runspace.IsCompleted) {
                   $Events = $Events + $Runspace.PowerShell.EndInvoke($Runspace.Runspace)
                   $Runspace.PowerShell.Dispose()
                   $RunspaceCollection.Remove($Runspace)
                }
             }
          } # While
          $Events = $Events | Out-DataTable
          If($Events) {
             $EventsAccess = $Events | ?{($_.Id -ne '4688') -and ($_.Id -ne '4689')}
             $ConnectionString = "Data Source=SQL01;Database=Events; Trusted_Connection=True;"
             $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
             $bulkCopy.DestinationTableName = "AccessEventsTest"
             $bulkCopy.WriteToServer($EventsAccess)
             $bulkCopy.Close()
             $bulkCopy.Dispose()
          }
          [GC]::Collect()
          Start-Sleep -Milliseconds 1000
          Move-Item -Path $Pool -Destination "d:\Evtx_Archive" -Force
          $Count = 0
          $Pool.Clear()
          $Events = @()
          $EventsAccess = @()
       } # If $Count -eq $MaxThreads
    } # ForEach $Evtx in $Evtxs

    5 апреля 2019 г. 11:44

Все ответы

  • а вопрос в чем?

    The opinion expressed by me is not an official position of Microsoft

    5 апреля 2019 г. 11:52
    Модератор
  • а вопрос в чем?

    The opinion expressed by me is not an official position of Microsoft

    Не работает скрипт что-то. А в чём ошибаюсь не пойму (я раньше с Runspace-ы никогда не использовал).

    Смущает даже то, что в при некоторых запусках скрипта в resmon-е видно обращение к файлам лога, а в других случаях скрипт бесконечно висит. - Но, к сожалению, в обоих случаях в базу данных ничего не падает.

    P.S: Powershell использую последний (5.1).

    5 апреля 2019 г. 12:11