Unable to display Data View result in Power shell

Question

I am new to azure data explorer and Kusto Queries. I am learning from below online sample

https://dataexplorer.azure.com/clusters/help/databases/Samples

Here is the query which i am getting results in Data Explorer but unable to display in power shell

   StormEvents
   | where DamageProperty >0
   | limit 2
   | project StormSummary.TotalDamages

Below is reference link for code which i am trying to run query in Powershell (Example2 in below link page)

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/api/powershell/powershell

I had changed only "$Query" and modified last code line as like below

 $dataView | Format-Table -AutoSize

i am getting output as

  StormSummary_TotalDamages
  -------------------------
 {}

I tried modifying query without "TotalDamages" in "StormSummary.TotalDamages" but resulting dataview which i am unable to get again "TotalDamages" .

 StormSummary                               
  ------------                               
 {TotalDamages, StartTime, EndTime, Details}

Interesting - maybe you either need to do something (not shown in the docs) to materialize the projected fields (or this is simply a bug) — Mathias R. Jessen

PavanKumar GVVS PavanKumar GVVS · Accepted Answer · 2020-04-22T15:14:39

Some one helped me to fix my issue. I am posting it to helps others.

Explanation: Query result is importing to Json string, converting it from json and converting the columns&rows data-layout back into individual PSObjects really helped my issue.

Code: As per the Example 2 mentioned in my question . we are calling

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

After this I removed existing code and modified like below:

Modified Code to get Projected Field data(TotalDamages):

 $json = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToJsonString($reader)
 $data = $json | ConvertFrom-Json

 $columns = @{}
 $count = 0
 foreach ($column in $data.Tables[0].Columns) {
   $columns[$column.ColumnName] = $count
    $count++
 }
$items = foreach ($row in $data.Tables[0].Rows) {
$hash = @{}
foreach ($property in $columns.Keys){
    $hash[$property] = $row[$columns[$property]]
}
[PSCustomObject]$hash
}
foreach($item in $items)
{
   Write-Host "TotalDamages: "$item.StormSummary.TotalDamages
 }

Output:

  TotalDamages:  6200000
  TotalDamages:  2000

Unable to display Data View result in Power shell

1 Answers