az monitor app-insights query does not return anything compared to query in portal

[vmachacek]

Describe the bug

When I do where clause in the query it does return 1 row while running same query in the web ui returns ~20

Related command

traces | where customDimensions.OriginalFormat == 'Startup' and timestamp > ago(30d)

Errors

no error message recieved

Issue script & Debug output

cli.knack.cli: Command arguments: ['monitor', 'app-insights', 'query', '--app', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx', '--analytics-query', "traces | where customDimensions.OriginalFormat == 'Startup' and timestamp > ago(30d)", '--debug']
cli.knack.cli: __init__ debug log:
Enable color in terminal.
Init colorama.
cli.knack.cli: Event: Cli.PreExecute []
cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at 0x01DA4148>, <function OutputProducer.on_global_arguments at 0x01E11FA0>, <function CLIQuery.on_global_arguments at 0x01F37BB0>]
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
cli.azure.cli.core: Modules found from index for 'monitor': ['azure.cli.command_modules.monitor', 'azext_applicationinsights']
cli.azure.cli.core: Loading command modules:
cli.azure.cli.core: Name                  Load Time    Groups  Commands
cli.azure.cli.core: monitor                   0.007        32       133
cli.azure.cli.core: Total (1)                 0.007        32       133
cli.azure.cli.core: These extensions are not installed and will be skipped: ['azext_ai_examples', 'azext_next']
cli.azure.cli.core: Loading extensions:
cli.azure.cli.core: Name                  Load Time    Groups  Commands  Directory
cli.azure.cli.core: application-insights      0.004        10        30  C:\Users\vmach\.azure\cliextensions\application-insights
cli.azure.cli.core: Total (1)                 0.004        10        30
cli.azure.cli.core: Loaded 41 groups, 163 commands.
cli.azure.cli.core: Found a match in the command table.
cli.azure.cli.core: Raw command  : monitor app-insights query
cli.azure.cli.core: Command table: monitor app-insights query
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableTruncate [<function AzCliLogging.init_command_file_logging at 0x03A830B8>]
cli.azure.cli.core.azlogging: metadata file logging enabled - writing logs to 'C:\Users\vmach\.azure\commands\2023-12-19.18-24-36.monitor_app-insights_query.34268.log'.
az_command_data_logger: command args: monitor app-insights query --app {} --analytics-query {} --debug
cli.knack.cli: Event: CommandInvoker.OnPreArgumentLoad [<function register_global_subscription_argument.<locals>.add_subscription_parameter at 0x03AC8148>, <function register_global_query_examples_argument.<locals>.register_query_examples at 0x03ADA850>]
cli.knack.cli: Event: CommandInvoker.OnPostArgumentLoad []
cli.knack.cli: Event: CommandInvoker.OnPostCommandTableCreate [<function register_ids_argument.<locals>.add_ids_arguments at 0x03ADA898>, <function register_cache_arguments.<locals>.add_cache_arguments at 0x03ADA928>]
cli.knack.cli: Event: CommandInvoker.OnCommandTableLoaded []
cli.knack.cli: Event: CommandInvoker.OnPreParseArgs []
cli.knack.cli: Event: CommandInvoker.OnPostParseArgs [<function OutputProducer.handle_output_argument at 0x01E21028>, <function CLIQuery.handle_query_parameter at 0x01F37BF8>, <function register_global_query_examples_argument.<locals>.handle_example_parameter at 0x03ADA808>, <function register_ids_argument.<locals>.parse_ids_arguments at 0x03ADA8E0>]
az_command_data_logger: extension name: application-insights
az_command_data_logger: extension version: 0.1.17
msrest.universal_http.requests: Configuring retry: max_retries=3, backoff_factor=0.8, max_backoff=90
cli.azure.cli.core.adal_authentication: AdalAuthentication.signed_session invoked by Track 1 SDK
cli.azure.cli.core._profile: Retrieving token from ADAL for resource 'https://api.applicationinsights.io'
cli.azure.cli.core.util: attempting to read file C:\Users\vmach\.azure\accessTokens.json as utf-8-sig
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - Authority:Performing instance discovery: ...
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - Authority:Performing static instance discovery
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - Authority:Authority validated via static instance discovery
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - TokenRequest:Getting token from cache with refresh if necessary.
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - CacheDriver:finding with query keys: {'_clientId': '...', 'userId': '...'}
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - CacheDriver:Looking for potential cache entries: {'_clientId': '...', 'userId': '...'}
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - CacheDriver:Found 4 potential entries.
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - CacheDriver:Resource specific token found.
adal-python: 121f50c7-1595-4d4f-92e8-1a093f010254 - CacheDriver:Returning token from cache lookup, AccessTokenId: b'uc7AGtsoNrl6RuaXt3Hqmj/Y9SO96toK1kAEoISy0bQ=', RefreshTokenId: b'f/KDQesRC01DsAI3NcpZ8GBfhfUokB+Bv7S9EJb60Kw='
msrest.universal_http: Configuring redirects: allow=True, max=30
msrest.universal_http: Configuring request: timeout=100, verify=True, cert=None
msrest.universal_http: Configuring proxies: ''
msrest.universal_http: Evaluate proxies against ENV settings: True
urllib3.connectionpool: Starting new HTTPS connection (1): api.applicationinsights.io:443

Expected behavior

the returned number of rows should match the one returned from web ui

Environment Summary

azure-cli                         2.29.0 *

core                              2.29.0 *
telemetry                          1.0.6 *

Extensions:
application-insights              0.1.17
azure-devops                      0.21.0

Additional context

No response

[yonzhan]

Thank you for opening this issue, we will look into it.

[AllyW]

Hi @vmachacek , can you provide the full debug log of this cmd's execution? Request api and body and service response are needed for us to determine what's lost here.

[vmachacek]

@AllyW here

az monitor app-insights query --app xxxxxxxxxxxxxxxxxxxxxxxxxxxx --analytics-query "customMetrics | where name == 'XXXXXX' and timestamp > ago(1d) | where value == 0 | project tostring(customDimensions.serialNumber) | distinct customDimensions_serialNumber" --debug
cli.knack.cli: Command arguments: ['monitor', 'app-insights', 'query', '--app', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx', '--analytics-query', "customMetrics | where name == 'XXXXXX' and timestamp > ago(1d) | where value == 0 | project tostring(customDimensions.serialNumber) | distinct customDimensions_serialNumber", '--debug']
cli.knack.cli: __init__ debug log:
Enable color in terminal.
Init colorama.
cli.knack.cli: Event: Cli.PreExecute []
cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at 0x022F5148>, <function OutputProducer.on_global_arguments at 0x02361FA0>, <function CLIQuery.on_global_arguments at 0x02487BB0>]
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
cli.azure.cli.core: Modules found from index for 'monitor': ['azure.cli.command_modules.monitor', 'azext_applicationinsights']
cli.azure.cli.core: Loading command modules:
cli.azure.cli.core: Name                  Load Time    Groups  Commands
cli.azure.cli.core: monitor                   0.006        32       133
cli.azure.cli.core: Total (1)                 0.006        32       133
cli.azure.cli.core: These extensions are not installed and will be skipped: ['azext_ai_examples', 'azext_next']
cli.azure.cli.core: Loading extensions:
cli.azure.cli.core: Name                  Load Time    Groups  Commands  Directory
cli.azure.cli.core: application-insights      0.006        10        30  C:\Users\vmach\.azure\cliextensions\application-insights
cli.azure.cli.core: Total (1)                 0.006        10        30
cli.azure.cli.core: Loaded 41 groups, 163 commands.
cli.azure.cli.core: Found a match in the command table.
cli.azure.cli.core: Raw command  : monitor app-insights query
cli.azure.cli.core: Command table: monitor app-insights query
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableTruncate [<function AzCliLogging.init_command_file_logging at 0x040030B8>]
cli.azure.cli.core.azlogging: metadata file logging enabled - writing logs to 'C:\Users\vmach\.azure\commands\2023-12-20.18-42-32.monitor_app-insights_query.67964.log'.
az_command_data_logger: command args: monitor app-insights query --app {} --analytics-query {} --debug
cli.knack.cli: Event: CommandInvoker.OnPreArgumentLoad [<function register_global_subscription_argument.<locals>.add_subscription_parameter at 0x04049148>, <function register_global_query_examples_argument.<locals>.register_query_examples at 0x04059850>]
cli.knack.cli: Event: CommandInvoker.OnPostArgumentLoad []
cli.knack.cli: Event: CommandInvoker.OnPostCommandTableCreate [<function register_ids_argument.<locals>.add_ids_arguments at 0x04059898>, <function register_cache_arguments.<locals>.add_cache_arguments at 0x04059928>]
cli.knack.cli: Event: CommandInvoker.OnCommandTableLoaded []
cli.knack.cli: Event: CommandInvoker.OnPreParseArgs []
cli.knack.cli: Event: CommandInvoker.OnPostParseArgs [<function OutputProducer.handle_output_argument at 0x02371028>, <function CLIQuery.handle_query_parameter at 0x02487BF8>, <function register_global_query_examples_argument.<locals>.handle_example_parameter at 0x04059808>, <function register_ids_argument.<locals>.parse_ids_arguments at 0x040598E0>]
az_command_data_logger: extension name: application-insights
az_command_data_logger: extension version: 0.1.17
msrest.universal_http.requests: Configuring retry: max_retries=3, backoff_factor=0.8, max_backoff=90
cli.azure.cli.core.adal_authentication: AdalAuthentication.signed_session invoked by Track 1 SDK
cli.azure.cli.core._profile: Retrieving token from ADAL for resource 'https://api.applicationinsights.io'
cli.azure.cli.core.util: attempting to read file C:\Users\vmach\.azure\accessTokens.json as utf-8-sig
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - Authority:Performing instance discovery: ...
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - Authority:Performing static instance discovery
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - Authority:Authority validated via static instance discovery
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - TokenRequest:Getting token from cache with refresh if necessary.
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - CacheDriver:finding with query keys: {'_clientId': '...', 'userId': '...'}
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - CacheDriver:Looking for potential cache entries: {'_clientId': '...', 'userId': '...'}
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - CacheDriver:Found 4 potential entries.
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - CacheDriver:Resource specific token found.
adal-python: c03f7f0f-25e7-4a8b-b87a-48380f94b3d5 - CacheDriver:Returning token from cache lookup, AccessTokenId: b'dqTckmATF/p2SlOKB6nNMS29baKGk8r/UbxOKo2YZTI=', RefreshTokenId: b'+W1oy3Fk5Wwpq7T69Vjor5Qc8C9R0zJyU7B82Ik/AGo='
msrest.universal_http: Configuring redirects: allow=True, max=30
msrest.universal_http: Configuring request: timeout=100, verify=True, cert=None
msrest.universal_http: Configuring proxies: ''
msrest.universal_http: Evaluate proxies against ENV settings: True
urllib3.connectionpool: Starting new HTTPS connection (1): api.applicationinsights.io:443
urllib3.connectionpool: https://api.applicationinsights.io:443 "POST /v1/apps/xxxxxxxxxxxxxxxxxxxxxxxxxxxx/query HTTP/1.1" 200 141
cli.knack.cli: Event: CommandInvoker.OnTransformResult [<function _resource_group_transform at 0x04039B68>, <function _x509_from_base64_to_hex_transform at 0x04039BB0>]
cli.knack.cli: Event: CommandInvoker.OnFilterResult []
{
  "tables": [
    {
      "columns": [
        {
          "name": "customDimensions_serialNumber",
          "type": "string"
        }
      ],
      "name": "PrimaryResult",
      "rows": [
        [
          "XXXXXXXX"
        ],
        [
          "XXXXXXXX"
        ]
      ]
    }
  ]
}
cli.knack.cli: Event: Cli.SuccessfulExecute []
cli.knack.cli: Event: Cli.PostExecute [<function AzCliLogging.deinit_cmd_metadata_logging at 0x040031D8>]
az_command_data_logger: exit code: 0
cli.__main__: Command ran in 1.334 seconds (init: 0.128, invoke: 1.206)
telemetry.save: Save telemetry record of length 3033 in cache
telemetry.check: Negative: The C:\Users\vmach\.azure\telemetry.txt was modified at 2023-12-20 18:42:27.209530, which in less than 600.000000 s

when doint exact same query it returns 4 distinct values and in cli it returns 2

[AllyW]

@vmachacek can you have a check what's the query condition in portal for your app?
BTW, what is the request body in az monitor app-insights query --app in your cli cmd execution debug log?

[microsoft-github-policy-service]

Hi, we're sending this friendly reminder because we haven't heard back from you in a while. We need more information about this issue to help address it. Please be sure to give us your input within the next 7 days. If we don't hear back from you within 14 days of this comment the issue will be automatically closed. Thank you!