- Powershell
- .NET Programming
- C#
Powershell SQL Insert
Instead of doing a select I want to do an insert instead of a select-Object at the bottom
Code should look a little like this. not sure how piece it together.
$AdnServer = "MyServer"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReport"
$All = @()
foreach ($computer in $ServerList)
{
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} |
ForEach-Object {
$rv = New-Object PSObject | Select-Object Computer,Date, User, Action, Process, Reason, ReasonCode, Comment
$rv.Date = $_.TimeCreated
$rv.Process = $_.Properties[0].Value
$rv.Computer = $_.Properties[1].Value
$rv.Reason = $_.Properties[2].Value
$rv.ReasonCode = $_.Properties[3].Value
$rv.Action = $_.Properties[4].Value
$rv.Comment = $_.Properties[5].Value
$rv.User = $_.Properties[6].Value
} | Select-Object COmputer,Date, Action, Reason, User |select -first 100 | Format-Table -auto #r
}
Code should look a little like this. not sure how piece it together.
$Qry2 = "INSERT INTO NOCTools.dbo.RebootReport(Computer,RebootDate,Uptime,[Action],Reason,ADUser,Process)
VALUES ('$rv.Computer','$rv.Date','',$rv.Comment , $rv.ReasonCode ,$rv.User ,$rv.Process)"
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
What are you actually looking for?
The following code inserts all the information (not just 100) to the SQL Table "RebootReport".
The following code inserts all the information (not just 100) to the SQL Table "RebootReport".
$AdnServer = "MyServer"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReport"
# $All = @()
foreach ($computer in $ServerList)
{
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} |
ForEach-Object {
$rv = New-Object PSObject | Select-Object Computer,Date, User, Action, Process, Reason, ReasonCode, Comment
$rv.Date = $_.TimeCreated
$rv.Process = $_.Properties[0].Value
$rv.Computer = $_.Properties[1].Value
$rv.Reason = $_.Properties[2].Value
$rv.ReasonCode = $_.Properties[3].Value
$rv.Action = $_.Properties[4].Value
$rv.Comment = $_.Properties[5].Value
$rv.User = $_.Properties[6].Value
$Qry2 = @" INSERT INTO NOCTools.dbo.RebootReport(Computer, RebootDate, Uptime, Action], Reason, ADUser, Process) VALUES ('$rv.Computer','$rv.Date','','$rv.Comment' , '$rv.ReasonCode' ,'$rv.User', '$rv.Process') "@
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
}
}
# $all | Select-Object Computer,Date, Action, Reason, User | select -first 100
There seems to be an issue with this line. Qry2 string has bad syntax some where
As for the insert I need it to insert top 1 latest record. Based on what I have I may still have to address that.
At line:58 char:11
+ $Qry2 = @"INSERT INTO NOCTools.dbo.RebootReport(Computer, RebootDate, Uptime, Ac ...
+ ~
No characters are allowed after a here-string header but before the end of the line.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnexpectedCharactersAfterHereStringHeader
As for the insert I need it to insert top 1 latest record. Based on what I have I may still have to address that.
Hi, just chiming in....change the Qry2 line to this:
$Qry2 = @"
INSERT INTO NOCTools.dbo.RebootReport(Computer, RebootDate, Uptime, Action], Reason, ADUser, Process)
VALUES ('$rv.Computer','$rv.Date','','$rv.Comment' , '$rv.ReasonCode' ,'$rv.User', '$rv.Process')
"@
OK so that worked, don't understand why carriage return makes a difference, But yes your code worked.
Here is the new syntax error.
Here is the new syntax error.
Invoke-Sqlcmd : Incorrect syntax near ']'.
At line:65 char:1
+ Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Oh yeah, after the Action field in the query line I just got you to replace, there is a ] character.
The query should be:
The query should be:
$Qry2 = @"
INSERT INTO NOCTools.dbo.RebootReport(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$rv.Computer','$rv.Date','','$rv.Comment' , '$rv.ReasonCode' ,'$rv.User', '$rv.Process')
"@
The table has all columns have 255 and data entered was longer than that. I changed it to 4000 and the code worked but its entered the entire string into one column.
This is what got inserted into 1 column
How do I change it to insert each value.
This is what got inserted into 1 column
@{Computer=Server; Date=02/28/2015 04:39:29; User=NT AUTHORITY\SYSTEM; Action=restart; Process=C:\Windows\CCM\TSManager.exe (Server); Reason=No title for this reason could be found; ReasonCode=0x80030002; Comment=Initiating system reboot}.Date
I N S E R T I N T O N O C T o o l s . d b o . R e b o o t R e p o r t T e m p ( C o m p u t e r , R e b o o t D a t e , U p t i m e , A c t i o n , R e a s o n , A D U s e r , P r o c e s s )
V A L U E S ( ' @ { C o m p u t e r = s e r v e r ; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( s e r v e r ) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . C o m p u t e r ' , ' @ { C o m p u t e r = E 1 S T A 1 W B 0 3 ; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( server ) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . D a t e ' , ' ' , ' @ { C o m p u t e r = s e r v e r ; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( s e r v e r ) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . C o m m e n t ' , ' @ { C o m p u t e r = s e r v e r ; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( s e r v e r ) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . R e a s o n C o d e ' , ' @ { C o m p u t e r = s e r v e r; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( E 1 S T A 1 W B 0 3 ) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . U s e r ' , ' @ { C o m p u t e r = s e r v e r ; D a t e = 0 2 / 2 6 / 2 0 1 5 0 4 : 3 4 : 4 6 ; U s e r = N T A U T H O R I T Y \ S Y S T E M ; A c t i o n = r e s t a r t ; P r o c e s s = C : \ W i n d o w s \ C C M \ T S M a n a g e r . e x e ( s e r v e r) ; R e a s o n = N o t i t l e f o r t h i s r e a s o n c o u l d b e f o u n d ; R e a s o n C o d e = 0 x 8 0 0 3 0 0 0 2 ; C o m m e n t = I n i t i a t i n g s y s t e m r e b o o t } . P r o c e s s ' )
Invoke-Sqlcmd : String or binary data would be truncated.
The statement has been terminated.
At line:65 char:1
+ Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
How do I change it to insert each value.
Ok Update. Not sure this is the best way to do it but it works when I assign value to variable and pass variable to query.
Now I would just like to only enter the last reboot value only.
$computer = $rv.Computer
$boottime = $rv.Date
$comment = $rv.Comment
$Reason = $rv.Reason
$User = $rv.User
$process = $rv.Process
$Qry2 = "
INSERT INTO NOCTools.dbo.RebootReportTemp(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$computer','$boottime','','$comment' , '$Reason' ,'$User', '$process')"
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
Now I would just like to only enter the last reboot value only.
Oh I see. The properties of the object are not being expanded. Enclosing each property in $() will help that. Then you don't need the extra variables.
Regards,
Rob.
$Qry2 = @"
INSERT INTO NOCTools.dbo.RebootReport(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$($rv.Computer)','$($rv.Date)','','$($rv.Comment)','$($rv.ReasonCode)','$($rv.User)','$($rv.Process)')
"@
Regards,
Rob.
For the inserting of the latest record only, you should be able to change the Get-WinEvent line to this:
Rob.
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
Rob.
Error running script and entering blank data
Here is what the code looks like since we have made changes
TimeCreated Id LevelDisplayName Message
----------- -- ---------------- -------
3/1/2015 4:34:26 AM 1074 Information The process C:\Windows\CCM\TSManager.exe (E1STA1WB03) has initiated the restart of computer E1STA1WB03 on behalf o...
Cannot index into a null array.
At line:50 char:5
+ $rv.Process = $_.Properties[0].Value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : NullArray
Here is what the code looks like since we have made changes
CLS
$AdnServer = "E0MADMDB03"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
# $All = @()
foreach ($computer in $ServerList)
{
Get-WinEvent -ComputerName $computer.name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1
ForEach-Object {
$rv = New-Object PSObject | Select-Object Computer,Date, User, Action, Process, Reason, ReasonCode, Comment
$rv.Date = $_.TimeCreated
$rv.Process = $_.Properties[0].Value
$rv.Computer = $_.Properties[1].Value
$rv.Reason = $_.Properties[2].Value
$rv.ReasonCode = $_.Properties[3].Value
$rv.Action = $_.Properties[4].Value
$rv.Comment = $_.Properties[5].Value
$rv.User = $_.Properties[6].Value
$computer = $rv.Computer
$boottime = $rv.Date
$comment = $rv.Comment
$Reason = $rv.Reason
$User = $rv.User
$process = $rv.Process
$Qry2 = "
INSERT INTO NOCTools.dbo.RebootReportTemp(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$computer','$boottime','','$comment' , '$Reason' ,'$User', '$process')"
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
}
}
Is this a cut&waste error? There is a pipe missing at the end of line 13.
And, guys, please don't let me see something like
And, guys, please don't let me see something like
$x.y = 1
$a = $x.y
Yes this was a cut and paste error on my part I have a "|" at the end of line 13.
As for the rv it existing code I am expanding on and it was already there.
Thank you for being the convention police I will make it a point to use better names.
As for the rv it existing code I am expanding on and it was already there.
Thank you for being the convention police I will make it a point to use better names.
If you need $rv, you should use it in the INSERT too, as shown in http:#a40637929. Using intermediate vars just to simplify the INSERT is prone to more errors, and you are not adding to keep the code simple and readable, so there is no value (other than for testing purposes, of course).
I cannot see where
As-is, the error message states that there is no $_.Properties with $_ being the current event object, which is strange.
TimeCreated Id LevelDisplayName Message
----------- -- ---------------- -------
3/1/2015 4:34:26 AM 1074 Information The process C:\Windows\CCM\TSManager.exe (E1STA1WB03) has initiated the restart of computer E1STA1WB03 on behalf o...
As-is, the error message states that there is no $_.Properties with $_ being the current event object, which is strange.
For debugging, I recommend to add a
$_ | select -Expand Properties | write-host
Qlemo is right, the re-assignment of property values is unnecessary, as the Event object properties can be directly used in the query.
Try this.
Try this.
CLS
$AdnServer = "E0MADMDB03"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
Get-WinEvent -ComputerName $computer.name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1
ForEach-Object {
$Qry2 = "
INSERT INTO NOCTools.dbo.RebootReportTemp(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$($_.Properties[1].Value)','$($_.TimeCreated)','','$($_.Properties[5].Value)','$($_.Properties[2].Value)','$($_.Properties[6].Value)','$($_.Properties[0].Value)')"
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
}
}
I have to admit that I would rather use the ADO objects here to create an INSERT with bind variables ...
Hey Qlemo, I'm not really a SQL guy (particularly with Powershell), but I have ran bound parameters with the MySQLi provider in PHP. I'm assuming you mean you could do a similar thing with a Powershell object?
Guys i will be online to try this later tonight thank you for the help. Just wanted to keep you guys in the loop.
Just to show what I mean:
$AdnServer = 'E0MADMDB03'
$constr = "server=$AdnServer; database=NOCTools; Integrated Security=sspi"
$insert = @'
insert into dbo.RebootReportTemp (Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
values @Computer, @RebootDate, @Uptime, @Action, @Reason, @ADUser, @Process)
'@
$cmd = New-Object Data.SqlClient.SqlCommand($insert, (New-Object Data.SqlClient.SqlConnection($constr)))
$cmd.Parameters.Add('@Computer' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@RebootDate', [System.Data.SqlDbType]::Datetime)
$cmd.Parameters.Add('@Uptime' , [System.Data.SqlDbType]::DateTime)
$cmd.Parameters.Add('@Action' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Reason' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@ADUser' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
Get-WinEvent -ComputerName $computer.name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
ForEach-Object {
$cmd.Parameters('@Computer' ).value = $_.Properties[1].value
$cmd.Parameters('@RebootDate').value = $_.TimeCreated
$cmd.Parameters('@Uptime' ).value = ''
$cmd.Parameters('@Action' ).value = $_.Properties[5].value
$cmd.Parameters('@Reason' ).value = $_.Properties[2].value
$cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
$cmd.Parameters('@Process' ).value = $_.Properties[0].value
$cmd.ExecuteNonQuery()
}
}
$Process = $cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
# ...
$Process.value = $_.Properties[0].value
# ...
Wow Qlemo! Thats really impressive! I will be testing in a few hours from now.
What the advantage of creating a sql Connection Object than just using invoke-sqlcmd? Not that I have a problem with your method but I use invoke-sqlcmd alot because it seems easier. What the advantage of the object?
What the advantage of creating a sql Connection Object than just using invoke-sqlcmd? Not that I have a problem with your method but I use invoke-sqlcmd alot because it seems easier. What the advantage of the object?
Using parameters is:
faster. The DBMS is able to keep compiled execution plan in cache, and apply it to the same query but different values. There is some effort made to compare SQLs without using parameters, but rules are more simple and often do not allow to keep the same execution plan.
more secure. In particular if users can enter data, SQL Injection is an issue. If you add a string directly into the query, you can add a command terminator and inject a delete, for example.
less prone to errors. In particular with strings containing ticks or double quotes, it might get really difficult to properly escape the strings inserted. Imagine you have a string var $var = "O'Neill", and insert that into a SQL string like "insert x (y) values('$var')". The result is a SQL syntax error.
There are certainly more advantages ...
That's reasonably similar to the PHP MySQLi methods. Certainly far more secure in a public interface environment, but I guess the only downside is that it's more code ;-)
I forgot one important point: It is type-safe. In particular with dates and numbers that might be important.
Good point. That means it would throw an error on a $cmd.Parameters.Add line, instead of within the attempt to execute the query.
Qlemo.: great points above on why its better to use this way I used they way but my friend talked me out of it and into Invoke-sqlcmd becuase its less code. Thanks for the explanation
There is a syntax error some where here is the code output
Not sure if this is what Sampson was addressing..
There is a syntax error some where here is the code output
Not sure if this is what Sampson was addressing..
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Computer
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @RebootDate
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @Uptime
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Action
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Reason
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @ADUser
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Process
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
Get-WinEvent : There are no more endpoints available from the endpoint mapper
At line:25 char:5
+ Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WinEvent], EventLogException
+ FullyQualifiedErrorId : System.Diagnostics.Eventing.Reader.EventLogException,Microsoft.PowerShell.Commands.GetWinEventCommand
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:27 char:7
+ $cmd.Parameters('@Computer' ).value = $_.Properties[1].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:28 char:7
+ $cmd.Parameters('@RebootDate').value = $_.TimeCreated
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:29 char:7
+ $cmd.Parameters('@Uptime' ).value = ''
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:30 char:7
+ $cmd.Parameters('@Action' ).value = $_.Properties[5].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:31 char:7
+ $cmd.Parameters('@Reason' ).value = $_.Properties[2].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:32 char:7
+ $cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:33 char:7
+ $cmd.Parameters('@Process' ).value = $_.Properties[0].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."
At line:34 char:7
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
PS C:\Users\usgltorres>
Hi, I don't quite understand. You say you were talked into using Invoke-SQLCmd, but the errors you have are for $cmd.Parameters('@Computer
Rob.
Rob.
I used his code exactly as is
CLS
$AdnServer = 'E0MADMDB03'
$constr = "server=$AdnServer; database=NOCTools; Integrated Security=sspi"
$insert = @'
insert into dbo.RebootReportTemp (Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
values @Computer, @RebootDate, @Uptime, @Action, @Reason, @ADUser, @Process)
'@
$cmd = New-Object Data.SqlClient.SqlCommand($insert, (New-Object Data.SqlClient.SqlConnection($constr)))
$cmd.Parameters.Add('@Computer' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@RebootDate', [System.Data.SqlDbType]::Datetime)
$cmd.Parameters.Add('@Uptime' , [System.Data.SqlDbType]::DateTime)
$cmd.Parameters.Add('@Action' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Reason' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@ADUser' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
ForEach-Object {
$cmd.Parameters('@Computer' ).value = $_.Properties[1].value
$cmd.Parameters('@RebootDate').value = $_.TimeCreated
$cmd.Parameters('@Uptime' ).value = ''
$cmd.Parameters('@Action' ).value = $_.Properties[5].value
$cmd.Parameters('@Reason' ).value = $_.Properties[2].value
$cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
$cmd.Parameters('@Process' ).value = $_.Properties[0].value
$cmd.ExecuteNonQuery()
}
}
I said I "used" invoke-sqlcmd. I like Qlemo code better for reasons stated above So i am sticking to his method
OK, I see. My apologies. Are you running the code "As Administrator"? Do you have a firewall that may get in the way of the Remote Procedure Call Locator service?
Rob.
Rob.
I think that error was not very conclusive. Yes I can not execute stuff remotely. But I did update code and the RPC error did not Appear
I did change the code there was a Parentheses missing in the insert statement
New Code
Error
I did change the code there was a Parentheses missing in the insert statement
New Code
CLS
$AdnServer = 'E0MADMDB03'
$constr = "server=$AdnServer; database=NOCTools; Integrated Security=sspi"
$insert = @'
insert into dbo.RebootReportTemp (Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
values (@Computer, @RebootDate, @Uptime, @Action, @Reason, @ADUser, @Process)
'@
$cmd = New-Object Data.SqlClient.SqlCommand($insert, (New-Object Data.SqlClient.SqlConnection($constr)))
$cmd.Parameters.Add('@Computer' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@RebootDate', [System.Data.SqlDbType]::Datetime)
$cmd.Parameters.Add('@Uptime' , [System.Data.SqlDbType]::DateTime)
$cmd.Parameters.Add('@Action' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Reason' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@ADUser' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Select Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
ForEach-Object {
$cmd.Parameters('@Computer' ).value = $_.Properties[1].value
$cmd.Parameters('@RebootDate').value = $_.TimeCreated
$cmd.Parameters('@Uptime' ).value = ''
$cmd.Parameters('@Action' ).value = $_.Properties[5].value
$cmd.Parameters('@Reason' ).value = $_.Properties[2].value
$cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
$cmd.Parameters('@Process' ).value = $_.Properties[0].value
$cmd.ExecuteNonQuery()
}
}
Error
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Computer
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @RebootDate
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @Uptime
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Action
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Reason
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @ADUser
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Process
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
Get-WinEvent : There are no more endpoints available from the endpoint mapper
At line:25 char:5
+ Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WinEvent], EventLogException
+ FullyQualifiedErrorId : System.Diagnostics.Eventing.Reader.EventLogException,Microsoft.PowerShell.Commands.GetWinEventCommand
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:27 char:7
+ $cmd.Parameters('@Computer' ).value = $_.Properties[1].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:28 char:7
+ $cmd.Parameters('@RebootDate').value = $_.TimeCreated
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:29 char:7
+ $cmd.Parameters('@Uptime' ).value = ''
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:30 char:7
+ $cmd.Parameters('@Action' ).value = $_.Properties[5].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:31 char:7
+ $cmd.Parameters('@Reason' ).value = $_.Properties[2].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:32 char:7
+ $cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Method invocation failed because [System.Data.SqlClient.SqlCommand] does not contain a method named 'Parameters'.
At line:33 char:7
+ $cmd.Parameters('@Process' ).value = $_.Properties[0].value
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."
At line:34 char:7
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
PS C:\Users\usgltorres>
Just to make sure, are you running this code directly on the E0MADMDB03 machine, as administrator?
If you need to, you may like to try adding
-Credential (get-credential YourDomain\AdminUser)
to the Get-WMIObject cmdlet.
Rob.
If you need to, you may like to try adding
-Credential (get-credential YourDomain\AdminUser)
to the Get-WMIObject cmdlet.
Rob.
In addition I dont need admin to use Get-WinEvent. I have used it before to get data and put it into txt files or evtx files with my credentials.
You typed the Get-WMIObject I am assuming you meant Get-WinEvent.
FYI this code did insert but it does 130 rows
You typed the Get-WMIObject I am assuming you meant Get-WinEvent.
FYI this code did insert but it does 130 rows
<#
$AdnServer = "E0MADMDB03"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReport"
$All = @()
foreach ($computer in $ServerList)
{
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} |
ForEach-Object {
$rv = New-Object PSObject | Select-Object Computer,Date, User, Action, Process, Reason, ReasonCode, Comment
$rv.Date = $_.TimeCreated
$rv.Process = $_.Properties[0].Value
$rv.Computer = $_.Properties[1].Value
$rv.Reason = $_.Properties[2].Value
$rv.ReasonCode = $_.Properties[3].Value
$rv.Action = $_.Properties[4].Value
$rv.Comment = $_.Properties[5].Value
$rv.User = $_.Properties[6].Value
} | Select-Object COmputer,Date, Action, Reason, User |select -first 100 | Format-Table -auto #r
}
#>
$AdnServer = "E0MADMDB03"
$Qry = "Select Top 2 Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
# $All = @()
foreach ($computer in $ServerList)
{
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074; } |
ForEach-Object {
$rv = New-Object PSObject | Select-Object Computer,Date, User, Action, Process, Reason, ReasonCode, Comment
$rv.Date = $_.TimeCreated
$rv.Process = $_.Properties[0].Value
$rv.Computer = $_.Properties[1].Value
$rv.Reason = $_.Properties[2].Value
$rv.ReasonCode = $_.Properties[3].Value
$rv.Action = $_.Properties[4].Value
$rv.Comment = $_.Properties[5].Value
$rv.User = $_.Properties[6].Value
$computer = $rv.Computer
$boottime = $rv.Date
$comment = $rv.Comment
$Reason = $rv.Reason
$User = $rv.User
$process = $rv.Process
$Qry2 = "
INSERT INTO NOCTools.dbo.RebootReportTemp(Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
VALUES ('$computer','$boottime','','$comment' , '$Reason' ,'$User', '$process')"
Write-Host @Qry2
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query $Qry2
}
}
# $all | Select-Object Computer,Date, Action, Reason, User | select -first 100
You are right. For some reason I thought you were querying the Security log....didn't read it properly. I think the problem is with the $computer.Name expansion for the -ComputerName parameter.
Can you try changing
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
to this
Write-Host "Querying computer $computer"
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
Regards,
Rob.
Can you try changing
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
to this
Write-Host "Querying computer $computer"
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
Regards,
Rob.
aaded your line to logic
got error
I think you have to keep the ".Name"
got error
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Computer
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @RebootDate
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @Uptime
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Action
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Reason
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @ADUser
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Process
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
Querying computer System.Data.DataRow
Get-WinEvent : The RPC server is unavailable
At line:27 char:5
+ Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WinEvent], EventLogException
+ FullyQualifiedErrorId : System.Diagnostics.Eventing.Reader.EventLogException,Microsoft.PowerShell.Commands.GetWinEventCommand
Querying computer System.Data.DataRow
Get-WinEvent : The RPC server is unavailable
At line:27 char:5
+ Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WinEvent], EventLogException
+ FullyQualifiedErrorId : System.Diagnostics.Eventing.Reader.EventLogException,Microsoft.PowerShell.Commands.GetWinEventCommand
PS C:\Users\usgltorres>
I think you have to keep the ".Name"
To confirm that, before this line:
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
please put this
Write-Output "About to query $computer"
and that should show a valid computer name. The new error is that the RPC server is unavailable, which means we should put in a Test-Connection.
Try this.
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
please put this
Write-Output "About to query $computer"
and that should show a valid computer name. The new error is that the RPC server is unavailable, which means we should put in a Test-Connection.
Try this.
CLS
$AdnServer = 'E0MADMDB03'
$constr = "server=$AdnServer; database=NOCTools; Integrated Security=sspi"
$insert = @'
insert into dbo.RebootReportTemp (Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
values (@Computer, @RebootDate, @Uptime, @Action, @Reason, @ADUser, @Process)
'@
$cmd = New-Object Data.SqlClient.SqlCommand($insert, (New-Object Data.SqlClient.SqlConnection($constr)))
$cmd.Parameters.Add('@Computer' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@RebootDate', [System.Data.SqlDbType]::Datetime)
$cmd.Parameters.Add('@Uptime' , [System.Data.SqlDbType]::DateTime)
$cmd.Parameters.Add('@Action' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Reason' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@ADUser' , [System.Data.SqlDbType]::String )
$cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Select Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
Write-Output "About to query $computer"
If (Test-Connection -ComputerName $computer -Quiet -Count 1) {
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
ForEach-Object {
$cmd.Parameters('@Computer' ).value = $_.Properties[1].value
$cmd.Parameters('@RebootDate').value = $_.TimeCreated
$cmd.Parameters('@Uptime' ).value = ''
$cmd.Parameters('@Action' ).value = $_.Properties[5].value
$cmd.Parameters('@Reason' ).value = $_.Properties[2].value
$cmd.Parameters('@ADUser' ).value = $_.Properties[6].value
$cmd.Parameters('@Process' ).value = $_.Properties[0].value
$cmd.ExecuteNonQuery()
}
}
}
Here is the oputput for 2 rows
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Computer
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @RebootDate
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : DateTime
LocaleId : 0
ParameterName : @Uptime
Precision : 0
Scale : 0
SqlDbType : DateTime
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Action
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Reason
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @ADUser
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
CompareInfo : None
XmlSchemaCollectionDatabase :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName :
DbType : String
LocaleId : 0
ParameterName : @Process
Precision : 0
Scale : 0
SqlDbType : NVarChar
SqlValue :
UdtTypeName :
TypeName :
Value :
Direction : Input
IsNullable : False
Offset : 0
Size : 0
SourceColumn :
SourceColumnNullMapping : False
SourceVersion : Current
About to query System.Data.DataRow
About to query System.Data.DataRow
PS C:\Users\usgltorres>
Sorry for the bugs :/, changed too much. This code should work, and not spit out uninteresting info either:
CLS
$AdnServer = 'E0MADMDB03'
$constr = "server=$AdnServer; database=NOCTools; Integrated Security=sspi"
$insert = @'
insert into dbo.RebootReportTemp (Computer, RebootDate, Uptime, Action, Reason, ADUser, Process)
values (@Computer, @RebootDate, @Uptime, @Action, @Reason, @ADUser, @Process)
'@
$conn = New-Object Data.SqlClient.SqlConnection($constr)
$conn.Open()
$cmd = New-Object Data.SqlClient.SqlCommand($insert, $conn)
[void] $cmd.Parameters.Add('@Computer' , [System.Data.SqlDbType]::String )
[void] $cmd.Parameters.Add('@RebootDate', [System.Data.SqlDbType]::Datetime)
[void] $cmd.Parameters.Add('@Uptime' , [System.Data.SqlDbType]::DateTime)
[void] $cmd.Parameters.Add('@Action' , [System.Data.SqlDbType]::String )
[void] $cmd.Parameters.Add('@Reason' , [System.Data.SqlDbType]::String )
[void] $cmd.Parameters.Add('@ADUser' , [System.Data.SqlDbType]::String )
[void] $cmd.Parameters.Add('@Process' , [System.Data.SqlDbType]::String )
$ServerList = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Select Name from NOCTools.dbo.ADComputers Where Left(Name,1) in ('E','A')"
Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $AdnServer -Query "Truncate Table NOCTools.dbo.RebootReportTemp"
foreach ($computer in $ServerList) {
if (Test-Connection -Count 1 -TimeToLive 5 $computer.name -Quiet)
{
Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |
ForEach-Object {
$cmd.Parameters['@Computer' ].value = $_.Properties[1].value
$cmd.Parameters['@RebootDate'].value = $_.TimeCreated
$cmd.Parameters['@Uptime' ].value = ''
$cmd.Parameters['@Action' ].value = $_.Properties[5].value
$cmd.Parameters['@Reason' ].value = $_.Properties[2].value
$cmd.Parameters['@ADUser' ].value = $_.Properties[6].value
$cmd.Parameters['@Process' ].value = $_.Properties[0].value
[void] $cmd.ExecuteNonQuery()
}
}
}
Ok, so since it is outputting System.Data.DataRow it should need the .Name by the look of it. I hate it when can't test a script to get it working. Hopefully Qlemo can get it right for you.
Rob.
Rob.
Yes. the column name is required when using the Invoke-SqlCmd result, which is a DataRow, as stated correctly.
Guys sorry I have been able to reply real busy in the afternoon. I will test this code tonight and provide feed back. Thanks again!
Still erroring out
Get-WinEvent : There are no more endpoints available from the endpoint mapper
At line:27 char:5
+ Get-WinEvent -ComputerName $computer.Name -FilterHashtable @{logname='System ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WinEvent], EventLogException
+ FullyQualifiedErrorId : System.Diagnostics.Eventing.Reader.EventLogException,Microsoft.PowerShell.Commands.GetWinEventCommand
Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a String to a DateTime."
At line:36 char:9
+ [void] $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FormatException
That message is probably caused by W2003 servers - they don't support Get-WinEvent, and there is nothing we can do about that.
Guys thanks for the help. I got it working with the variables. My issue is that it takes for ever to finish going thru 8000 Servers to get the last reboot data. I will close this for now and open a new thread. Thank you sorry about delay!
There are certainly better and faster ways to get that info. Or background jobs, or remote execution, or ...
I will get back to this right now this dropped in Priority. Posting another question here that is more urgent actually.
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28635456.html
Thanks for the help!
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28635456.html
Thanks for the help!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial