SOLVED

Invoke-LogAnalyticsQuery only returns Tables JSON Array and not Results Array

%3CLINGO-SUB%20id%3D%22lingo-sub-144701%22%20slang%3D%22en-US%22%3EInvoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-144701%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20the%20new%20PowerShell%20based%20API%20(Invoke-LogAnalyticsQuery%20-%20see%20below)%20and%20the%20return%20payload%20only%20includes%20the%20Tables%20array%20and%20not%20the%20Results%20array%20as%20documented..%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Eimport-module%3C%2FSPAN%3E%3CSPAN%3E%20.%5CLogAnalyticsQuery.psm1%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EqueryString%3C%2FSPAN%3E%20%3CSPAN%3E%3D%3C%2FSPAN%3E%20%3CSPAN%3E'search%20*%20%7C%20where%20Type%20%3D%3D%20%22Heartbeat%22%20%7C%20take%2010'%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EworkspaceName%3C%2FSPAN%3E%20%3CSPAN%3E%3D%3C%2FSPAN%3E%20%3CSPAN%3E%22xxxx%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EresourceGroupName%3C%2FSPAN%3E%20%3CSPAN%3E%3D%3C%2FSPAN%3E%20%3CSPAN%3E%22xxxx%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EsubscriptionID%3C%2FSPAN%3E%20%3CSPAN%3E%3D%3C%2FSPAN%3E%20%3CSPAN%3E%22xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3Eresponse%3C%2FSPAN%3E%20%3CSPAN%3E%3D%3C%2FSPAN%3E%20%3CSPAN%3Einvoke-loganalyticsquery%3C%2FSPAN%3E%20%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3EWorkspaceName%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EworkspaceName%3C%2FSPAN%3E%20%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3EResourceGroup%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EresourceGroupName%3C%2FSPAN%3E%20%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3EQuery%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EqueryString%3C%2FSPAN%3E%20%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3ESubscriptionId%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3EsubscriptionID%3C%2FSPAN%3E%20%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3EIncludeTabularView%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-144701%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowershell%20and%20Rest%20API%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-163956%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-163956%22%20slang%3D%22en-US%22%3E%3CP%3EFollowing%20up%20on%20this%2C%20we've%20made%20the%20a%20cmdlet%20available%20for%20querying%20as%20part%20of%20the%20Azure%20RM%20cmdlets%20in%20the%20gallery.%20If%20you%20don't%20already%20have%20them%2C%20see%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fazure%2Finstall-azurerm-ps%3Fview%3Dazurermps-5.3.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fazure%2Finstall-azurerm-ps%3Fview%3Dazurermps-5.3.0%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDocumentation%20for%20the%20new%20cmdlet%20is%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fazurerm.operationalinsights%2Finvoke-azurermoperationalinsightsquery%3Fview%3Dazurermps-5.3.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpowershell%2Fmodule%2Fazurerm.operationalinsights%2Finvoke-azurermoperationalinsightsquery%3Fview%3Dazurermps-5.3.0%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20it%20out%20and%20let%20us%20know%20what%20you%20think!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152503%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152503%22%20slang%3D%22en-US%22%3E%3CP%3EThanks.%26nbsp%3B%20I%20did%20verify%20I%20am%20getting%20the%20results%20you%20indicate%20in%20the%20Results%20array%20is%20there.%26nbsp%3B%20The%20documentation%20for%20the%20CMDLET%20is%20a%20bit%20confusing%20(%3CA%20href%3D%22https%3A%2F%2Fdev.loganalytics.io%2Fdocumentation%2FTools%2FPowerShell-Cmdlets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdev.loganalytics.io%2Fdocumentation%2FTools%2FPowerShell-Cmdlets%3C%2FA%3E)%20but%20I%20see%20what's%20going%20on%20now.%26nbsp%3B%20I%20also%20vote%20for%20a%20properly%20signed%20supported%20module%20here.%26nbsp%3B%20Thanks%20a%20lot%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152163%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152163%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20feedback%20Mark.%20We%20are%20working%20to%20provide%20a%20better%20set%20of%20cmdlets%26nbsp%3Bintegrated%20with%20Azure%20Powershell%2C%20which%20will%20be%20available%20from%20the%20PowerShell%20gallery.%20They%20should%20be%20available%20in%20the%20next%20release%20of%20AzureRM%20PowerShell%20module.%20These%20should%20have%20better%20stability%20and%20will%20be%20signed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152158%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152158%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brady%2C%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20returned%20payload%20should%20include%20the%20results%20array.%20Can%20you%20inspect%20the%20payload%20using%20Get-Member%20(as%20shown%20in%20screenshot)%20to%20check%20for%20it%3F%20The%20flag%20-IncludeTabularView%20will%20add%20the%20tabular%20view%2C%20but%20the%20results%20array%20will%20always%20be%20on%20the%20payload.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F27965i394EA0390E22EA49%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ps.png%22%20title%3D%22ps.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152145%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152145%22%20slang%3D%22en-US%22%3E%3CP%3EAnd%20please%20provide%20a%20signed%20version%20of%26nbsp%3B%3CSPAN%3EInvoke-LogAnalyticsQuery.%26nbsp%3B%20Changing%20execution%20policies%20is%20not%20a%20good%20practice.%26nbsp%3B%20This%20command%20is%20fragile%20too%20-%20lots%20of%20bad%20gateways.%26nbsp%3B%20Makes%20it%20challenging%20to%20piece%20together%20data.%26nbsp%3B%20Thank%20you.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-146566%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-146566%22%20slang%3D%22en-US%22%3E%3CP%3EAdding%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F79889%22%20target%3D%22_blank%22%3E%40Chris%20Suich%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145324%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145324%22%20slang%3D%22en-US%22%3E%3CP%3EMeir%2C%20thanks%20for%20the%20pointer%20on%20query%20optimization.%26nbsp%3B%20I%20did%20figure%20out%20that%20I%20am%20getting%20the%20results.%26nbsp%3B%20It%20seems%20the%20shape%20of%20the%20JSON%20result%20you%20get%20back%20from%20Invoke-LogAnalyticsQuery%20doesn't%20match%20what%20is%20documented%20at%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdev.loganalytics.io%2Fdocumentation%2FTools%2FPowerShell-Cmdlets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdev.loganalytics.io%2Fdocumentation%2FTools%2FPowerShell-Cmdlets%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-144936%22%20slang%3D%22en-US%22%3ERe%3A%20Invoke-LogAnalyticsQuery%20only%20returns%20Tables%20JSON%20Array%20and%20not%20Results%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-144936%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brady%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20I%20must%20comment%20on%20your%20query.%20You%20should%20avoid%20queries%20that%20has%20'search%20*%20%7C%20where%20Type%20%3D%3D'.%20Instead%20the%20query%20below%20should%20just%20be%3A%20'Heartbeat%20%7C%20take%2010'.%20Search%20*%20is%20very%20inefficient%20way%20to%20use%20the%20system.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegarding%20the%20results%20array.%20I%20ran%20the%20same%20code%20but%20didn't%20managed%20to%20reproduce.%20I%20got%20both%20tables%20and%20results.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%3C%2FP%3E%0A%3CP%3EMeir%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm trying to use the new PowerShell based API (Invoke-LogAnalyticsQuery - see below) and the return payload only includes the Tables array and not the Results array as documented..  

 

import-module .\LogAnalyticsQuery.psm1

$queryString = 'search * | where Type == "Heartbeat" | take 10'
$workspaceName = "xxxx"
$resourceGroupName = "xxxx"
$subscriptionID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

$response = invoke-loganalyticsquery -WorkspaceName $workspaceName -ResourceGroup $resourceGroupName -Query $queryString -SubscriptionId $subscriptionID -IncludeTabularView
8 Replies
Best Response confirmed by Brady Evans (Occasional Contributor)
Solution

Hi Brady,

 

First, I must comment on your query. You should avoid queries that has 'search * | where Type =='. Instead the query below should just be: 'Heartbeat | take 10'. Search * is very inefficient way to use the system.

 

Regarding the results array. I ran the same code but didn't managed to reproduce. I got both tables and results.

 

Sorry

Meir

Meir, thanks for the pointer on query optimization.  I did figure out that I am getting the results.  It seems the shape of the JSON result you get back from Invoke-LogAnalyticsQuery doesn't match what is documented at:

https://dev.loganalytics.io/documentation/Tools/PowerShell-Cmdlets.

 

Thanks again.

 

And please provide a signed version of Invoke-LogAnalyticsQuery.  Changing execution policies is not a good practice.  This command is fragile too - lots of bad gateways.  Makes it challenging to piece together data.  Thank you.

Hi Brady, 

 

The returned payload should include the results array. Can you inspect the payload using Get-Member (as shown in screenshot) to check for it? The flag -IncludeTabularView will add the tabular view, but the results array will always be on the payload. 

 

ps.png

Thanks for the feedback Mark. We are working to provide a better set of cmdlets integrated with Azure Powershell, which will be available from the PowerShell gallery. They should be available in the next release of AzureRM PowerShell module. These should have better stability and will be signed. 

Thanks.  I did verify I am getting the results you indicate in the Results array is there.  The documentation for the CMDLET is a bit confusing (https://dev.loganalytics.io/documentation/Tools/PowerShell-Cmdlets) but I see what's going on now.  I also vote for a properly signed supported module here.  Thanks a lot for the help.

Following up on this, we've made the a cmdlet available for querying as part of the Azure RM cmdlets in the gallery. If you don't already have them, see here: https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-5.3.0

 

Documentation for the new cmdlet is here: https://docs.microsoft.com/en-us/powershell/module/azurerm.operationalinsights/invoke-azurermoperati...

 

Try it out and let us know what you think!