0

I have a RunKQLQuery.psm1 file where I follow all the steps from the Microsoft documentation: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/api/powershell/powershell?tabs=user

In here, I authenticate as an application and run the query and display the results:

$reader = $queryProvider.ExecuteQuery($query, $crp)

$dataTable = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader).Tables[0]
$dataView = New-Object System.Data.DataView($dataTable)
$dataView | Sort StartTime -Descending | Format-Table -AutoSize

return $dataView (or $datatable)

This part works fine.

enter image description here

Next, I have an Azure Function that calls the above .psm1 file and runs the query by doing:

$query = 'SignInLogs
    | take 5
    | evaluate bag_unpack(Location)
    | project 
        CreatedDateTime,
        IPAddress,
        UserPrincipalName,
        ResourceDisplayName,
        CountryOrRegion,
        ResourceTenantId
    $data = Run-KQLQuery -query $query -tenantId $TenantId

Unfortunately, I am unable to use the $data output since I can't get it formatted in a nice way.

foreach ($row in $data){
        Write-Output "Row content: $($row)"
    }

enter image description here

When I check the data type of $dataView before being returned, I can see that it's: System.Data.DataTable

And when checking the type in my Azure Function, it's System.Object[].

I experimented with the suggested solution of converting the results to a JSON string, but it appears to break the output I receive in my Azure Function. Details of this approach I attempted: Unable to display Data View result in Power shell

When printing $data after applying the solution from the post I get the following output:

Kusto.Data, Version=12.2.0.0, Culture=neutral, PublicKeyToken=829a00acde12d551 Kusto.Data.Data.Impl.Impl.KustoJsonDataStreamReader Data type after conversion: Kusto.Data.Data.Impl.Impl.KustoJsonDataStreamReader Number of rows in table: 0

In addition, I tried the solution from How to return specific data type from PowerShell function i.e. DataTable where return is replaced with write-output -noenumerate. However, the output still seems unusable in my Azure Function.

My objective is to execute the query using the following command:

$data = Run-KQLQuery -query $query -tenantId $TenantId

In the module file, it will handle the authentication and execute the query. Subsequently, in my Azure Function, I intend to retrieve the data and utilize it for the remainder of my code to trigger an event with some fields like IP/User/Date. What would be the best approach for this?

2
  • 1
    Remove this line from your first sample - $dataView | Sort StartTime -Descending | Format-Table -AutoSize - it’s converting $dataview into internal “display formatting instructions” which get rendered to the console when you run it interactively, but are being returned along with the output from return $dataView in your live environment. These formatting instructions are causing the …Format.FormatStartData etc lines in your logs… Commented Apr 26, 2024 at 10:10
  • @mclayton I really appreciate the response. I have tried this, but unfortunately, it doesn't really change anything in the output for me. Commented Apr 26, 2024 at 11:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.