New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AzureRM.OperationalInsights: Not supporting Kusto Query Language #4548

Open
adactitsla opened this Issue Sep 5, 2017 · 3 comments

Comments

Projects
None yet
6 participants
@adactitsla

adactitsla commented Sep 5, 2017

Cmdlet(s)

Get-AzureRmOperationalInsightsSearchResults
Get-AzureRmOperationalInsightsSavedSearch

PowerShell Version

Name                           Value
----                           -----
PSVersion                      5.1.15063.502
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.15063.502
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

Module Version

ModuleType Version    Name
---------- -------    ----
Script     3.3.1      AzureRM.OperationalInsights

OS Version

BuildVersion 10.0.15063.502

Description

Both Get-AzureRmOperationalInsightsSearchResults and Get-AzureRmOperationalInsightsSavedSearch doesn't support the Kusto query language, even though the OMS/Log Analytics workspace has been upgraded.

I run the following command to pull data from a upgraded OMS workspace:

$queryKusto = "search Type == ""Heartbeat"""
Get-AzureRmOperationalInsightsSearchResults -ResourceGroupName "resourcegroup" -WorkspaceName "workspace" -Query $queryKusto

I receive this message:

Id       :
Metadata : Microsoft.Azure.Commands.OperationalInsights.Models.PSSearchMetadata
Error    : Microsoft.Azure.Commands.OperationalInsights.Models.PSSearchError
Value    :

If I run the same cmdlet with the native query.

$queryNative = "Type=Heartbeat"
Get-AzureRmOperationalInsightsSearchResults -ResourceGroupName "resourcegroup" -WorkspaceName "workspace" -Query $queryNative

I get this result:

Id       : subscriptions/e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX/resourceGroups/workspacename_rg/providers/Microsoft.Operationa
           lInsights/workspaces/workspacename/search/80f791c2-4004-4a1f-80f9-XXXXXXXXXXXX|Shim
Metadata : Microsoft.Azure.Commands.OperationalInsights.Models.PSSearchMetadata
Error    :
Value    : {"TenantId": "214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceSystem": "OpsManager" "TimeGenerated":
           "2017-09-05T06:47:34.72Z" "MG": "00000000-0000-0000-0000-000000000001" "ManagementGroupName":
           "AOI-214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceComputerId": "d1e35af7-cb7b-4325-8f52-XXXXXXXXXXXX"
           "ComputerIP": "13.93.5.48" "Computer": "workspacename-vm1" "Category": "Direct Agent" "OSType": "Windows"
           "OSMajorVersion": "10" "OSMinorVersion": "0" "Version": "8.0.11049.0" "SCAgentChannel": "Direct"
           "IsGatewayInstalled": false "RemoteIPLongitude": 4.94 "RemoteIPLatitude": 52.31 "RemoteIPCountry":
           "Netherlands" "SubscriptionId": "e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX" "ResourceGroup": "workspacename_rg"
           "ResourceProvider": "Microsoft.Compute" "Resource": "workspacename-vm1" "ResourceId": "/subscriptions/e4de540c-9b
           b9-49ab-8e32-XXXXXXXXXXXX/resourceGroups/workspacename_rg/providers/Microsoft.Compute/virtualMachines/workspacename-vm
           1" "ResourceType": "virtualMachines" "ComputerEnvironment": "Azure" "Solutions": "\"updates\",
           \"changeTracking\", \"networkMonitoring\", \"serviceMap\", \"wireData2\"" "Type": "Heartbeat" "id":
           "773fe1ca-5ce0-4672-b623-XXXXXXXXXXXX" "__metadata": {
             "Type": "Heartbeat",
             "TimeGenerated": "2017-09-05T06:47:34.72Z"
           }, "TenantId": "214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceSystem": "OpsManager" "TimeGenerated":
           "2017-09-05T06:46:34.72Z" "MG": "00000000-0000-0000-0000-000000000001" "ManagementGroupName":
           "AOI-214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceComputerId": "d1e35af7-cb7b-4325-8f52-XXXXXXXXXXXX"
           "ComputerIP": "13.93.5.48" "Computer": "workspacename-vm1" "Category": "Direct Agent" "OSType": "Windows"
           "OSMajorVersion": "10" "OSMinorVersion": "0" "Version": "8.0.11049.0" "SCAgentChannel": "Direct"
           "IsGatewayInstalled": false "RemoteIPLongitude": 4.94 "RemoteIPLatitude": 52.31 "RemoteIPCountry":
           "Netherlands" "SubscriptionId": "e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX" "ResourceGroup": "workspacename_rg"
           "ResourceProvider": "Microsoft.Compute" "Resource": "workspacename-vm1" "ResourceId": "/subscriptions/e4de540c-9b
           b9-49ab-8e32-XXXXXXXXXXXX/resourceGroups/workspacename_rg/providers/Microsoft.Compute/virtualMachines/workspacename-vm
           1" "ResourceType": "virtualMachines" "ComputerEnvironment": "Azure" "Solutions": "\"updates\",
           \"changeTracking\", \"networkMonitoring\", \"serviceMap\", \"wireData2\"" "Type": "Heartbeat" "id":
           "0a163761-9c0f-4307-9da9-XXXXXXXXXXXX" "__metadata": {
             "Type": "Heartbeat",
             "TimeGenerated": "2017-09-05T06:46:34.72Z"
           }, "TenantId": "214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceSystem": "OpsManager" "TimeGenerated":
           "2017-09-05T06:45:34.677Z" "MG": "00000000-0000-0000-0000-000000000001" "ManagementGroupName":
           "AOI-214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceComputerId": "d1e35af7-cb7b-4325-8f52-XXXXXXXXXXXX"
           "ComputerIP": "13.93.5.48" "Computer": "workspacename-vm1" "Category": "Direct Agent" "OSType": "Windows"
           "OSMajorVersion": "10" "OSMinorVersion": "0" "Version": "8.0.11049.0" "SCAgentChannel": "Direct"
           "IsGatewayInstalled": false "RemoteIPLongitude": 4.94 "RemoteIPLatitude": 52.31 "RemoteIPCountry":
           "Netherlands" "SubscriptionId": "e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX" "ResourceGroup": "workspacename_rg"
           "ResourceProvider": "Microsoft.Compute" "Resource": "workspacename-vm1" "ResourceId": "/subscriptions/e4de540c-9b
           b9-49ab-8e32-XXXXXXXXXXXX/resourceGroups/workspacename_rg/providers/Microsoft.Compute/virtualMachines/workspacename-vm
           1" "ResourceType": "virtualMachines" "ComputerEnvironment": "Azure" "Solutions": "\"updates\",
           \"changeTracking\", \"networkMonitoring\", \"serviceMap\", \"wireData2\"" "Type": "Heartbeat" "id":
           "2e4c5608-0b24-4a0f-b4e9-XXXXXXXXXXXX" "__metadata": {
             "Type": "Heartbeat",
             "TimeGenerated": "2017-09-05T06:45:34.677Z"
           }, "TenantId": "214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceSystem": "OpsManager" "TimeGenerated":
           "2017-09-04T10:45:50.147Z" "MG": "00000000-0000-0000-0000-000000000001" "ManagementGroupName":
           "AOI-214fc5a7-9a4e-4818-a600-XXXXXXXXXXXX" "SourceComputerId": "d1e35af7-cb7b-4325-8f52-XXXXXXXXXXXX"
           "ComputerIP": "52.178.113.62" "Computer": "workspacename-vm1" "Category": "Direct Agent" "OSType": "Windows"
           "OSMajorVersion": "10" "OSMinorVersion": "0" "Version": "8.0.11049.0" "SCAgentChannel": "Direct"
           "IsGatewayInstalled": false "RemoteIPLongitude": 4.94 "RemoteIPLatitude": 52.31 "RemoteIPCountry":
           "Netherlands" "SubscriptionId": "e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX" "ResourceGroup": "workspacename_rg"
           "ResourceProvider": "Microsoft.Compute" "Resource": "workspacename-vm1" "ResourceId": "/subscriptions/e4de540c-9b
           b9-49ab-8e32-XXXXXXXXXXXX/resourceGroups/workspacename_rg/providers/Microsoft.Compute/virtualMachines/workspacename-vm
           1" "ResourceType": "virtualMachines" "ComputerEnvironment": "Azure" "Solutions": "\"updates\",
           \"changeTracking\", \"networkMonitoring\", \"serviceMap\", \"wireData2\"" "Type": "Heartbeat" "id":
           "a7242335-926e-4116-99ab-XXXXXXXXXXXX" "__metadata": {
             "Type": "Heartbeat",
             "TimeGenerated": "2017-09-04T10:45:50.147Z"
           }...}

Debug Output

DEBUG: 08:55:44 - GetAzureOperationalInsightsSearchResultsCommand begin processing with ParameterSet
'__AllParameterSets'.
DEBUG: 08:55:44 - using account id 'user@tenant.onmicrosoft.com'...
DEBUG: [Common.Authentication]: Authenticating using Account: 'user@tenant.onmicrosoft.com', environment:
'AzureCloud', tenant: '910ae351-0839-4d63-a3ef-XXXXXXXXXXXX'
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - AcquireTokenHandlerBase: === Token Acquisition
started:
 Authority: https://login.microsoftonline.com/910ae351-0839-4d63-a3ef-XXXXXXXXXXXX/
 Resource: https://management.core.windows.net/
 ClientId: 1950a258-227b-4e31-a9cf-XXXXXXXXXXXX
 CacheType: Microsoft.Azure.Commands.Common.Authentication.ProtectedFileTokenCache (2 items)
 Authentication Target: User

DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44:  - TokenCache: Deserialized 2 items to token cache.
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Verbose: 1 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - TokenCache: Looking up cache for a token...
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - TokenCache: An item matching the requested resource
was found in the cache
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Verbose: 1 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - TokenCache: 46.178268625 minutes left until token in
 cache expires
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - TokenCache: A matching item (access token or refresh
 token or both) was found in the cache
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: aeccb7e5-7f46-46bd-afa2-XXXXXXXXXXXX - AcquireTokenHandlerBase: === Token Acquisition
finished successfully. An access token was retuned:
 Access Token Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 Refresh Token Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 Expiration Time: 09/05/2017 07:41:55 +00:00
 User Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - AcquireTokenHandlerBase: === Token Acquisition
started:
 Authority: https://login.microsoftonline.com/910ae351-0839-4d63-a3ef-XXXXXXXXXXXX/
 Resource: https://management.core.windows.net/
 ClientId: 1950a258-227b-4e31-a9cf-XXXXXXXXXXXX
 CacheType: Microsoft.Azure.Commands.Common.Authentication.ProtectedFileTokenCache (2 items)
 Authentication Target: User

DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44:  - TokenCache: Deserialized 2 items to token cache.
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Verbose: 1 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - TokenCache: Looking up cache for a token...
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - TokenCache: An item matching the requested resource
was found in the cache
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Verbose: 1 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - TokenCache: 46.1781018916667 minutes left until
token in cache expires
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - TokenCache: A matching item (access token or refresh
 token or both) was found in the cache
DEBUG: Microsoft.IdentityModel.Clients.ActiveDirectory Information: 2 :
DEBUG: 09/05/2017 06:55:44: 32605171-b015-44d4-a261-XXXXXXXXXXXX - AcquireTokenHandlerBase: === Token Acquisition
finished successfully. An access token was retuned:
 Access Token Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 Refresh Token Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 Expiration Time: 09/05/2017 07:41:55 +00:00
 User Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

DEBUG: ============================ HTTP REQUEST ============================

HTTP Method:
POST

Absolute Uri:
https://management.azure.com/subscriptions/e4de540c-9bb9-49ab-8e32-XXXXXXXXXXXX/resourcegroups/workspacename_rg/providers/Mi
crosoft.OperationalInsights/workspaces/workspacename/search?api-version=2015-03-20

Headers:
x-ms-client-request-id        : 277cefa1-c216-4c67-b043-XXXXXXXXXXXX
accept-language               : en-US

Body:
{
  "top": 10,
  "highlight": {},
  "query": "search Type == \"Heartbeat\"",
  "end": "2017-09-05T06:55:44.5898809Z"
}

DEBUG: ============================ HTTP RESPONSE ============================

Status Code:
OK

Headers:
Pragma                        : no-cache
X-RateLimit-Remaining         : 99
X-RateLimit-Limit             : 100
Strict-Transport-Security     : max-age=31536000; includeSubDomains
X-Content-Type-Options        : nosniff
x-ms-ratelimit-remaining-subscription-resource-requests: 149999
x-ms-request-id               : da105eae-57eb-477e-b8c2-XXXXXXXXXXXX
x-ms-correlation-request-id   : da105eae-57eb-477e-b8c2-XXXXXXXXXXXX
x-ms-routing-request-id       : WESTEUROPE:20170905T065545Z:da105eae-57eb-477e-b8c2-XXXXXXXXXXXX
Cache-Control                 : no-cache
Date                          : Tue, 05 Sep 2017 06:55:44 GMT
Server                        : Microsoft-IIS/8.5
X-Powered-By                  : ASP.NET

Body:
{
  "__metadata": {
    "resultType": "error"
  },
  "error": {
    "type": "QuerySyntax",
    "message": "Invalid syntax.",
    "line": 1,
    "column": 13,
    "text": "="
  }
}

Script/Steps for Reproduction

$queryKusto = "search Type == ""Heartbeat"""
Get-AzureRmOperationalInsightsSearchResults -ResourceGroupName "resourcegroup" -WorkspaceName "workspace" -Query $queryKusto

@adactitsla adactitsla changed the title from AzureRM.OperationalInsights: Not supporting to AzureRM.OperationalInsights: Not supporting Kusto Query Language Sep 5, 2017

@cormacpayne cormacpayne assigned haitch and unassigned cormacpayne Sep 5, 2017

@cormacpayne

This comment has been minimized.

Member

cormacpayne commented Sep 5, 2017

@haitch Hey Haitao, would you mind taking a look at this issue?

@FirestormAngel

This comment has been minimized.

FirestormAngel commented Apr 19, 2018

What is the status on this feature request. I'm having exactly the same problem with our Splunk Microsoft OMS application. Making the REST API query, I'll get the same body

Request:
Update | where Type=="Update"

Request body:
{'top': '1000', 'query': "Update | where Type=='Update' ", 'start': '2018-04-18T15:28:27', 'end': '2018-04-19T10:40:29'}

Response body:
{ "__metadata": { "resultType": "error" }, "error": { "type": "QuerySyntax", "message": "Invalid syntax.", "line": 1, "column": 36, "text": "=" } }

Note: I tried a couple of variants with only one "=" sign and \" and \' and so on. same result. What am I missing ? The rest api doesn't like "==" and "sort by ..." and "order by ..."

NOTE: solved it by writing
Type="Update" | sort TimeGenerated asc

@alexeldeib

This comment has been minimized.

Contributor

alexeldeib commented Apr 27, 2018

@FirestormAngel if you're not specifically targeting saved searches, you can use Invoke-AzureRmOperationalInsightsQuery to use Kusto query language. Otherwise, probably the REST API for saved search is currently your best bet.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment