How to Insert EventViewer records to SQL Server Table

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  February 9, 2020 at 7:28 pm

  #3724236

  Hello,

  I developed the below code to scan through cluster error issues happening in the last 48 hrs. But if I need to insert the reocrds to a table, how to achieve the need?

  Get-EventLog -LogName "system" -after (((get-date).AddDays(-2)).date) | where {$_.InstanceId -eq 1069 -or $_.InstanceId -eq 1045 -or $_.InstanceId -eq 1205 -or $_.InstanceId -eq 1254} |select-Object Timegenerated, EventId, EntryType, Source, Message | Format-Table -autosize -Wrap;

  $servername = $env:COMPUTERNAME

  $sObj = Get-WmiObject -Class Win32_SystemServices -ComputerName $ServerName

  if ($sObj | select PartComponent | where {$_ -like "*ClusSvc*"})

  {

  Write-Output "$ServerName is Clustered"

  $Event = Get-EventLog -LogName "system" -after (((get-date).AddDays(-15)).date) | where {$_.InstanceId -eq 1069 -or $_.InstanceId -eq 1045 -or $_.InstanceId -eq 1205 -or $_.InstanceId -eq 1254} |

  select-Object Timegenerated, EventId, EntryType, Source, Message | Format-Table -autosize -Wrap;

  foreach($Events in $Event)

  {

  $TimeGen=$Event.Timegenerated

  $EventId=$Event.EventId

  $EntryType=$Event.EntryType

  $Source=$Event.Source

  $Message=$Event.Message

  $insertquery="

  INSERT INTO [dbo].[LogTable]

  ([TimeGenerated]

  ,[EventId]

  ,[EntryType]

  ,[Source]

  ,[Message] )

  VALUES

  ('$TimeGen'

  ,'$EventId'

  ,'$EntryType'

  ,'$Source'

  , '$Message')

  GO

  "

  Invoke-SQLcmd -ServerInstance $servername -Database [XYZ] -Query $insertquery -U XX -Password XX

  }

  }

  else

  {

  Write-Output "$ServerName is Not clustered"

  }

   

  Thanks.

• frederico_fonseca

  SSCoach

  Points: 16154

  More actions

  February 9, 2020 at 8:15 pm

  #3724239

  google for "winevents load to sql server" and you will find a few examples of how to do it