search

Azure / azure-powershell

Microsoft Azure PowerShell
Other
4.22k stars 3.83k forks source link

AzureRM.OperationalInsights: Not supporting Kusto Query Language #4548

Open adactitsla opened 7 years ago

adactitsla commented 7 years ago

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
cormacpayne commented 7 years ago

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

FirestormAngel commented 6 years ago

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 commented 6 years ago

@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.

ghost commented 4 years ago

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @AzmonLogA.