An array with index [-1]

Question

I have a function giving me some results of Get-WinEvent which works just fine. Using it as an input for another function and trying to iterate the array I notice, that the array is built like:

-1
   ProviderName: Microsoft-Windows-FailoverClustering

TimeCreated                     Id LevelDisplayName Message
-----------                     -- ---------------- -------
07.08.2019 01:14:23           1538 Information      The backup operation for the cluster configuration data completed successfully. The snapshot ID is '47cda23b-c446-43dc-9b5d-dfdbaca2be8c'.

Obviously I don't want to have the "-1" and "ProviderName: Microsoft-Windows-FailoverClustering".

How to get it right?

function Get-ClusterLogEntries {
    Param(
        [Parameter(Position=0, Mandatory=$true)]
        [int] $server_id,
        [Parameter(Position=1, Mandatory=$true)]
        [string] $clusterName
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=xxxx\yyyy;Database=zzzz;Integrated Security=True"
    $conn.Open() | Out-Null
    $cmd = New-Object System.Data.SqlClient.SqlCommand("extern.xxxx", $conn)

    $cmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $cmd.Parameters.Add("@server_id", [System.Data.SqlDbType]::int) | Out-Null
    $cmd.Parameters['@server_id'].Direction = [System.Data.ParameterDirection]::Input
    $cmd.Parameters['@server_id'].Value = $server_id #local server

    $cmd.Parameters.Add("@clusterName", [System.Data.SqlDbType]::VarChar, 10) | Out-Null
    $cmd.Parameters['@clusterName'].Direction = [System.Data.ParameterDirection]::Input
    $cmd.Parameters['@clusterName'].Value = $clusterName

    $cmd.Parameters.Add("@timeCreated", [System.Data.SqlDbType]::DateTime) | Out-Null
    $cmd.Parameters['@timeCreated'].Direction = [System.Data.ParameterDirection]::Output

    $cmd.ExecuteNonQuery()
    $conn.Close()
    $conn.Dispose()

    $timeCreated = $cmd.Parameters["@timeCreated"].Value
    $startdatum = $timeCreated
    return [array]$clusterLogEntries = Get-WinEvent -FilterHashTable @{
        logname   = 'Microsoft-Windows-FailoverClustering/Operational';
        starttime = $startdatum
    }
}

and the consuming function:

function Write-ClusterLog {
    Param(
        [Parameter(Position=0, Mandatory=$true)]
        [int] $server_id,
        [Parameter(Position=1, Mandatory=$true)]
        [string] $computer,
        [Parameter(Position=2, Mandatory=$true)]
        [string] $clusterName,
        [Parameter(Position=3, Mandatory=$true)]
        [array] $clusterLogs
    )

    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Server=xxxx;Database=yyyyy;Integrated Security=True"
    $conn.Open() | Out-Null
    $cmd = New-Object System.Data.SqlClient.SqlCommand("extern.zzzzzz", $conn)

    $cmd.CommandType = [System.Data.CommandType]::StoredProcedure

    foreach ($clusterLog in $clusterLogs) {
        $cmd.Parameters.Add("@server_id", [System.Data.SqlDbType]::int) | Out-Null
        $cmd.Parameters['@server_id'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@server_id'].Value = $server_id

        $cmd.Parameters.Add("@servername", [System.Data.SqlDbType]::VarChar, 10) | Out-Null
        $cmd.Parameters['@servername'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@servername'].Value = $computer #local server

        $cmd.Parameters.Add("@clustername", [System.Data.SqlDbType]::VarChar, 10) | Out-Null
        $cmd.Parameters['@clustername'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@clustername'].Value = $clusterName

        $cmd.Parameters.Add("@timecreated", [System.Data.SqlDbType]::DateTime) | Out-Null
        $cmd.Parameters['@timecreated'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@timecreated'].Value = $clusterLog.TimeCreated

        $cmd.Parameters.Add("@eventID", [System.Data.SqlDbType]::int) | Out-Null
        $cmd.Parameters['@eventID'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@eventID'].Value = $clusterLog.ID

        $cmd.Parameters.Add("@levelDisplayName", [System.Data.SqlDbType]::varchar, 50) | Out-Null
        $cmd.Parameters['@levelDisplayName'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@levelDisplayName'].Value = $clusterLog.LevelDisplayName

        $cmd.Parameters.Add("@message", [System.Data.SqlDbType]::VarChar, 4000) | Out-Null
        $cmd.Parameters['@message'].Direction = [System.Data.ParameterDirection]::Input
        $cmd.Parameters['@message'].Value = $clusterLog.Message

        $cmd.ExecuteNonQuery()
        $cmd.Parameters.Clear()
    }
    $conn.Close()
    $conn.Dispose()
}

Answer 1

@MathiasR.Jessen's answer is correct for where the -1 came from.

The "ProviderName: Microsoft-Windows-FailoverClustering" is coming from the default output formatting for objects of the type that Get-WinEvent returns (EventLogRecord). It's the same reason you see "Directory: X:\foo" when you output Get-ChildItem.

To fix it, you need to select the fields rather than returning the entire object:

Get-WinEvent -FilterHashTable @{
        logname   = 'Microsoft-Windows-FailoverClustering/Operational';
        starttime = $startdatum
} | Select-Object TimeCreated, Id, LevelDisplayName, Message

If you need all the properties, you can specify Select-Object * instead, but you'll get a list output due to the number of properties to display.

If you need methods on the EventLogRecord class, then you can't do this because Select-Object with the -Property argument changes the object type to a PSCustomObject. You're unrolling the object from it's base class. If you need methods and you need to not show the provider name, you'd have to modify the Types.ps1xml file describing formatting for objects of type EventLogRecord. You almost certainly don't want to do this. See Get-Help about_Types.ps1xml.

Answer 2

This statement:

$cmd.ExecuteNonQuery()

will return an integer indicating the number of rows affected by an INSERT or UPDATE operations - or for non-modifying operations, it'll return -1!

Change it to:

[void]$cmd.ExecuteNonQuery()
# or
$cmd.ExecuteNonQuery() |Out-Null