Searching OMS Using the New Search Language (Kusto) REST API in PowerShell

Currently Microsoft is in the process of upgrading all OMS Log Analytics workspaces to the new query language (named Kusto). Once your workspace has been upgraded, you will no longer able to invoke search queries using the Get-AzureRmOperationalInsightsSearchResults cmdlet from the AzureRM.OperationalInsights PowerShell module. Kusto comes with a new set of REST APIs, you can find the documentation site here: https://dev.int.loganalytics.io.

According to the documentation, this REST API has the following limitations:

  • Queries cannot return more than 500,000 rows
  • Queries cannot return more than 64,000,000 bytes (~61 MiB total data)
  • Quries cannot run longer than 10 minutes by default.

From the documentation site, you can also find a sample PowerShell module which allows you to invoke Kusto search queries via the ARM REST API: https://dev.int.loganalytics.io/documentation/Tools/PowerShell-Cmdlets

I have contacted the OMS product group and I have been advised that since the sample PowerShell module offered from the documentation site invokes searches via ARM REST API (as opposed to via the direct Kusto API), the limitation for ARM REST API also applies, which means the query cannot return more than 8MB payload – which is significantly smaller than the direct Kusto API.

Previously with the old language, we also had similar limitations, and I have blogged ways to overcome the throttling limitations using ‘skip’ command. You can find my previous blog post here: https://blog.tyang.org/2017/04/25/programmatically-performing-oms-log-search-against-a-large-result-set/. However, the new Kusto language does not have a ‘skip’ or equivalent command so it was not possible to use the same method when querying against a large result set. Luckily with the help from the OMS product group, I managed to get it working using the row_number() function, and developed a script directly invoking the new Log Analytics search REST API (instead of going through ARM).

Here’s the PowerShell script I developed, in order to run it, in addition to the AzureRM.Profile and AzureRM.Resources module, you will also need the AzureServicePrincipalAccount PowerShell module v1.2.0 or above (developed by myself) :

This script searches your workspace using Kusto API and exports results to one or more files. you will need to specify the following parameters:

  • -AzureCredential: a PSCredential object for an Azure AD account that has access to your workspace
  • -TenantId: the GUID for your AAD Tenant ID
  • -WorkspaceId: the GUID for your Log Analytics workspace ID
  • -SearchQuery: the Kusto search query you wish to perform
  • -StartUTCTime: the start (earliest) time in UTC for the search operation. Optional, if not specified, the default value is 1 day ago
  • -EndUTCTime: the end(latest) time in UTC for the search operation. Optional, if not specified, the default value is now
  • -Timeout: the HTTP Rest time out for the Log Analytics REST API. optional, default value is 180 (seconds)
  • -OutputDir: the directory where you want the search results to be saved. optional, default value is the script root folder.
  • -OutputFileNamePrefix: the prefix for the output file name. Optional, default value is ‘OMSSearchResult’
  • -OutputFormat: the format for the output files. you can choose between CSV and JSON. this parameter is optional, default is CSV
  • -MaximumRowPerFile: the maximum number of rows for each output file. optional, default is 5000

I’ve added many verbose messages in the script. so if you run it with –Verbose switch, you’ll see more details while the script is running:

image

Note: If you perform search within a large time window, the script will take a long time to run depending on number of rows returned from the search result.

Lastly, please feel free to contact me if you have issues or suggestions.

Leave a Reply

%d bloggers like this: