This might be move useful to test this issue:
Here is an example of the xml I want to insert:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-a5ba-3e3b0328c30d}"/>
<EventID>4625</EventID>
<Version>0</Version>
<Level>0</Level>
<Task>12544</Task>
<Opcode>0</Opcode>
<Keywords>0x8010000000000000</Keywords>
<TimeCreated SystemTime="2013-07-19T19:27:14.515Z"/>
<EventRecordID>39751424</EventRecordID>
<Correlation/>
<Execution ProcessID="568" ThreadID="1608"/>
<Channel>Security</Channel>
<Computer>myserver.mydom.com</Computer>
<Security/>
</System>
<EventData>
<Data Name="SubjectUserSid">mysid</Data>
<Data Name="SubjectUserName">myuser</Data>
<Data Name="SubjectDomainName">mydom</Data>
<Data Name="SubjectLogonId">0x5ca73f</Data>
<Data Name="TargetUserSid">S-1-0-0</Data>
<Data Name="TargetUserName">myuser2</Data>
<Data Name="TargetDomainName">mydom</Data>
<Data Name="Status">0xc000006d</Data>
<Data Name="FailureReason">%%2313</Data>
<Data Name="SubStatus">0xc000006a</Data>
<Data Name="LogonType">2</Data>
<Data Name="LogonProcessName">seclogo</Data>
<Data Name="AuthenticationPackageName">Negotiate</Data>
<Data Name="WorkstationName">myserver</Data>
<Data Name="TransmittedServices">-</Data>
<Data Name="LmPackageName">-</Data>
<Data Name="KeyLength">0</Data>
<Data Name="ProcessId">0x3e8</Data>
<Data Name="ProcessName">C:\Windows\System32\svchost.exe</Data>
<Data Name="IpAddress">::1</Data>
<Data Name="IpPort">0</Data>
</EventData>
</Event>
I get the following error when trying to insert data into sql via the following script:
$SQLDBName = 'test'
$SQLServer = 'mysrv,1433'
#Open connection to sql DB
$connectionString = "Server = $SQLServer;Integrated Security=true;Initial Catalog=$SQLDBName;"
$SQLConnection = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$SQLConnection.DestinationTableName = "ForwardedEvents"
#create columns for datatable and set their types
$columns = @()
$columns += #Order MUST match that of the DB
(New-Object System.Data.DataColumn -ArgumentList @("SystemTime" , [System.string] )),
(New-Object System.Data.DataColumn -ArgumentList @("EventLogXML" , [System.Xml.XmlNode] ))
#build datatable for bulk insert
$dt = New-Object System.Data.DataTable
$columns | %{$dt.Columns.add($_) | Out-Null}
$row = $dt.NewRow() #Create row
$row.Item("SystemTime") = "This string get inserted fine"
$row.Item("EventLogXML") = [xml](cat C:\test\4625_forForums.xml)
$dt.Rows.Add($row) #add row to table
#insert into DB and close connection
$SQLConnection.WriteToServer($dt)
$SQLConnection.close()
Error: Exception calling "WriteToServer" with "1" argument(s): "Specified cast is not valid."
Really appreciate any assistance, thanks in advance.
No comments:
Post a Comment