Powershell SQL Insert

Instead of doing a select I want to do an insert instead of a select-Object at the bottom

$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 


  }

Open in new window



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

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
What are you actually looking for?
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 

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
There seems to be an issue with this line. Qry2 string has bad syntax some where
                                       
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

Open in new window



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.
RobSampsonCommented:
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')
"@

Open in new window

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Leo TorresSQL DeveloperAuthor Commented:
OK so that worked, don't understand why carriage return makes a difference, But yes your code worked.

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

Open in new window

RobSampsonCommented:
Oh yeah, after the Action field in the query line I just got you to replace, there is a ] character.

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')
"@

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
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
@{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

Open in new window


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

Open in new window


How do I change it to insert each value.
Leo TorresSQL DeveloperAuthor Commented:
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.

$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

Open in new window



Now I would just like to only enter the last reboot value only.
RobSampsonCommented:
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.

$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)')
"@

Open in new window


Regards,

Rob.
RobSampsonCommented:
For the inserting of the latest record only, you should be able to change the Get-WinEvent line to this:
Get-WinEvent -ComputerName $computer -FilterHashtable @{logname='System'; id=1074} | Sort-Object TimeCreated -Descending | Select-Object -First 1 |

Open in new window


Rob.
Leo TorresSQL DeveloperAuthor Commented:
Error running script and entering blank data
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

Open in new window



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

        }
 } 

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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
   $x.y = 1
   $a = $x.y

Open in new window

Set only the vars finally used. The whole $rv stuff should be removed, if taking that approach with simple vars (which is a valid solution).
Leo TorresSQL DeveloperAuthor Commented:
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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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).
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I cannot see where
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...

Open in new window

is coming from, and that could be important. With the pasted code that would not be seen on screen.
As-is, the error message states that there is no $_.Properties with $_ being the current event object, which is strange.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
For debugging, I recommend to add a
  $_ | select -Expand Properties | write-host

Open in new window

before you access the properties.
RobSampsonCommented:
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.

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
    }
} 

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I have to admit that I would rather use the ADO objects here to create an INSERT with bind variables ...
RobSampsonCommented:
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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That's nothing special for PowerShell, it's a .Net way to access databases.
Leo TorresSQL DeveloperAuthor Commented:
Guys i will be online to try this later tonight thank you for the help. Just wanted to keep you guys in the loop.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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()      
    }
} 

Open in new window

This could be simplified further, e.g. by using vars for the bind parameter definitions, i.e.
$Process = $cmd.Parameters.Add('@Process'   , [System.Data.SqlDbType]::String  )
# ...
$Process.value = $_.Properties[0].value
# ...

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 ...
RobSampsonCommented:
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 ;-)
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I forgot one important point: It is type-safe. In particular with dates and numbers that might be important.
RobSampsonCommented:
Good point.  That means it would throw an error on a $cmd.Parameters.Add line, instead of within the attempt to execute the query.
Leo TorresSQL DeveloperAuthor Commented:
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..
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> 

Open in new window

RobSampsonCommented:
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'  ).value which means you are using Qlemo's code.  Can you show us the code you are using?

Rob.
Leo TorresSQL DeveloperAuthor Commented:
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()      
    }
} 
                                          

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
I said I "used" invoke-sqlcmd. I like Qlemo code better for reasons stated above So i am sticking to his method
RobSampsonCommented:
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.
Leo TorresSQL DeveloperAuthor Commented:
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
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()      
    }
} 
                                          

Open in new window


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> 

Open in new window

RobSampsonCommented:
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.
Leo TorresSQL DeveloperAuthor Commented:
Yes I am running On that server but as myself. I dont believe I have admin right though.
Leo TorresSQL DeveloperAuthor Commented:
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
<#
$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 
                                          

Open in new window

RobSampsonCommented:
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.
Leo TorresSQL DeveloperAuthor Commented:
aaded your line to logic

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> 

Open in new window


I think you have to keep the ".Name"
RobSampsonCommented:
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.

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()      
        }
    }
} 

Open in new window

Leo TorresSQL DeveloperAuthor Commented:
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> 

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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()      
      }
  }
} 

Open in new window

Note: I've added code to test if the remote machine is available.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobSampsonCommented:
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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes. the column name is required when using the Invoke-SqlCmd result, which is a DataRow, as stated correctly.
Leo TorresSQL DeveloperAuthor Commented:
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!
Leo TorresSQL DeveloperAuthor Commented:
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

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That message is probably caused by W2003 servers - they don't support Get-WinEvent, and there is nothing we can do about that.
RobSampsonCommented:
Can you use Get-WMIObject -Class Win32_NTLogEvent?
Leo TorresSQL DeveloperAuthor Commented:
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!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There are certainly better and faster ways to get that info. Or background jobs, or remote execution, or ...
Leo TorresSQL DeveloperAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.