Invoke-SQLUpdate : Argument passed in is not serializable. Parameter name: value

[cmorillo21]

Hoping I can get a help with my script that after the update to version to stopped working with the below error and i'm not sure what could be the issue.

Invoke-SQLUpdate : Argument passed in is not serializable.
Parameter name: value

Here it is my script;

do {
    $auditlogsURI = "https://auditservice.dev.azure.com/$($OrganizationName)/_apis/audit/auditlog?batchSize=1000&startTime=$($StartTime)&endTime=$($EndTime)&continuationToken=$continuationToken&api-version=6.0-preview.1"
    $token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($PAT)"))
    $headers = @{authorization = "Basic $token"}
    $auditcall = Invoke-RestMethod -Uri $auditlogsURI -Method GET -ContentType "application/json" -Headers $headers
    $auditlogs= $auditcall.decoratedAuditLogEntries
    $continuationToken=$auditcall.continuationToken

    #Open connection for MySQL Database to enter the data.
    Open-SQLConnection -Server "*************" -Database "*******" -Credential $credential

    #Iterate all date from the api and save them into variables
    Foreach ( $Record in $auditlogs ) {
        $ID = $Record.id
        $CorrelationId = $Record.CorrelationId
        $ActivityId = $Record.ActivityId
        $ActorCUID = $Record.ActorCUID
        $ActorUserId = $Record.ActorUserId
        $ActorUPN = $Record.ActorUPN
        $AuthenticationMechanism = $Record.AuthenticationMechanism
        $Timestamp = [DateTime]$Record.Timestamp
        $ScopeType = $Record.ScopeType
        $ScopeDisplayName = $Record.ScopeDisplayName
        $ScopeId = $Record.ScopeId
        $ProjectId = $Record.ProjectId
        $ProjectName = $Record.ProjectName
        $IpAddress = $Record.IpAddress
        $UserAgent = $Record.UserAgent
        $ActionId = $Record.ActionId
        [string]$Data = ($Record.Data | Out-String -Stream) -ne '' | Select-Object -Skip 2
        $Details = $Record.Details
        $Area = $Record.Area
        $Category = $Record.Category
        $CategoryDisplayName = $Record.CategoryDisplayName
        $ActorDisplayName = $Record.ActorDisplayName

        Invoke-SQLUpdate -query "
        INSERT INTO logs(
            Id, 
            CorrelationId, 
            ActivityId, 
            ActorCUID, 
            ActorUserId, 
            ActorUPN, 
            AuthenticationMechanism, 
            Timestam, 
            ScopeType, 
            ScopeDisplayName, 
            ScopeId, 
            ProjectId,
            ProjectName, 
            IpAddress, 
            UserAgent, 
            ActionId, 
            Dato, 
            Details, 
            Area, 
            Category, 
            CategoryDisplayName, 
            ActorDisplayName
        )VALUES(
            @Id,
            @CorrelationId,
            @ActivityId,
            @ActorCUID,
            @ActorUserId,
            @ActorUPN,
            @AuthenticationMechanism,
            @Timestam,
            @ScopeType,
            @ScopeDisplayName,
            @ScopeId,
            @ProjectId,
            @ProjectName,
            @IpAddress,
            @UserAgent,
            @ActionId,
            @Dato,
            @Details,
            @Area,
            @Category,
            @CategoryDisplayName,
            @ActorDisplayName
        )
        " -Parameters @{
            Id=$ID;
            CorrelationId=$correlationID;
            ActivityId=$ActivityID;
            ActorCUID=$ActorCUID;
            ActorUserId=$ActorUserId;
            ActorUPN=$ActorUPN;
            AuthenticationMechanism=$AuthenticationMechanism;
            Timestam=$Timestamp;
            ScopeType=$ScopeType;
            ScopeDisplayName=$ScopeDisplayName;
            ScopeId=$ScopeId;
            ProjectId=$ProjectId;
            ProjectName=$ProjectName;
            IpAddress=$IpAddress;
            UserAgent=$UserAgent;
            ActionId=$ActionId;
            Dato=$Data;
            Details=$Details;
            Area=$Area;
            Category=$Category;
            CategoryDisplayName=$CategoryDisplayName;
            ActorDisplayName=$ActorDisplayName
        } | Out-Null
  } 
 Close-SQLConnection
  } while ($null -ne $continuationToken)

If you need any details please let me know.

[mithrandyr]

@cmorillo21 can you provide the full error message and stack trace? That will help narrow down where exactly it failed and perhaps indicate why.

On an aside -- you can simplify your PS by either directly setting the values in the hashtable (without using intermediate variables) or by creating a hashtable variable and passing that in.

[cmorillo21]

Please try checking with the table - column I passed and try to insert the json data to that column so maybe to can discover whats the issue.

[mithrandyr]

I can't reproduce the issue using SQLServer -- if you are using MySql, then i'm getting an error with the create table statement. If you can provide a complete script -- I can try again. Meanwhile, I'm looking into how to solve for the issue during error handling.

[cmorillo21]

I'm using MSSQL server and thats the script just to create the table and insert the values that im getting issue with, you can use one empty DB.

Yes, if you solve the error handling maybe we can clarify the issue with this data.

[mithrandyr]

I've made a code change to hopefully account for this scenario and published a minor update to the PowerShellGallery (v2.0.1)

[cmorillo21]

Error handling looks a lot better, but still i can not insert json data that I was able to before the update.

Here the new error now;

PSMessageDetails      : 
Exception             : System.InvalidCastException: Unable to cast object of 
                        type 'Microsoft.Data.SqlClient.SqlParameterCollection' 
                        to type 'System.Collections.Hashtable'.
                           at SimplySql.Engine.ProviderBase.Update(IDbCommand 
                        cmd)
                           at SimplySql.Engine.ProviderBase.Update(String 
                        query, Int32 timeout, Hashtable params)
                           at SimplySql.Cmdlets.InvokeSqlUpdate.ProcessRecord()
TargetObject          : default
CategoryInfo          : InvalidOperation: (default:String) [Invoke-SqlUpdate], 
                        InvalidCastException
FullyQualifiedErrorId : Invoke-SqlUpdate,SimplySql.Cmdlets.InvokeSqlUpdate
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at GetAuditLogs, 
                        C:\__w\1\s\PowerShell\AuditLogs\AuditLogs.ps1: line 119
                        at <ScriptBlock>, 
                        C:\__w\_temp\12cb8122-7e48-4b74-beee-fabbad2e460c.ps1: 
                        line 11
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {0, 1, 0}

Please let me know if you can help me with this?

[mithrandyr]

@cmorillo21 -- the message is indicating an invalid cast exception -- what is the exact line of Powershell that is throwing this error? (i.e. line 119 of your auditlogs.ps1. Based upon the message, it indicates that you are passing a SqlParameterCollection when the type expected is hashtable. If you are trying to pass parameters to my cmdlets, you pass them as a hashtable.

[cmorillo21]

This is what i'm running right now;

Foreach ( $Record in $auditlogs ) {
$Data =$Record.Data | ConvertTo-Json -Depth 100

        Invoke-SQLUpdate -query "
        INSERT INTO logs(
            dato)values(@dato)" -Parameters @{dato=$data} | Out-Null
        }

The error line is the "Invoke-SQLUpdate -query" line, as you can see it is a json variable inside of the hash, seems like the error is because it is a json as I have mentioned before this was working fine before the update.

PS C:\Users\cmorillo\Documents\Repo\Ops> @{dato=$data}

Name                           Value
----                           -----
dato                           {...


PS C:\Users\cmorillo\Documents\Repo\Ops> $test=@{dato=$data}
PS C:\Users\cmorillo\Documents\Repo\Ops> $test.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Hashtable                                System.Object

[mithrandyr]

Hrm, ok i was able to reproduce -- and the workaround/fix is easy.

-Parameters @{dato=$data} becomes -Parameters @{dato=$data.tostring()}
and then the query goes through --something weird is happening where $data isn't really a string object and thus is causing problems. I'll dig deeper for a fix later.

[mithrandyr]

I've created an Issue for this - but it may not be what either of us think. Once I get the problem, then any invocation of Invoke-SqlUpdate throws this weird error. I'll dig into it and resolve it.

[mithrandyr]

@cmorillo21 -- please try the latest version 2.0.2. Resolved both the original issue and one I accidentally introduced in v2.0.1

[mithrandyr]

@cmorillo21 Two things. (1) my first fix, introduced another problem that has now been fixed, will release shortly
(2) The real problem, when you use ConvertTo-Json you are actually getting a PSObject back. However, when you inspect the type, its revealing itself as a string.

[cmorillo21]

@mithrandyr Thanks a lot for the fixing, all have been working perfectly!!