Log Analytics: KQL saved Queries, how to find and run them in a Workbook

18/06/2020

Summary

Log Analytics has a option called Query Explorer (note, this is due to be updated, so this example is applicable for a short period of time). If like me you have 100’s of saved queries, managing them can be a challenge (my #1 challenge!), lets fix that with a Azure Monitor Workbook…

One of the ways Query Explorer is used, is to save your KQL queries in a Category, with a Name – to help you find them again. So I may have saved a query in Category:Demo and with a Name: “This is a demo query”. If I wanted to use this query again, I’d open Query Explorer, search for the name and re-run it. However the challenge is, the search only looks at the “name”. So for example if I had some KQL using the “externaldata” operator, unless I had that in the name as well, I couldn’t find it (without opening all my files), which is only ok, if you have a few saves. It’s a reason I started to store more in Github, as that has a keyword search.

John Gardner a Principal Software Engineer in the Azure Monitor Workbooks team recently shared an example of using an api within a Workbook, similar to what I did here: https://techcommunity.microsoft.com/t5/azure-sentinel/using-the-sentinel-api-to-view-data-in-a-workbook/ba-p/1386436 John has kindly let me share his example, the workbook he produced retrieved the ‘Saved Searches’ from the Log Analytics api, displayed them, then if you clicked one, shows the KQL and tries to run it in a workspace.

Solution

This was great, but whilst having a conversation about this, I thought why can’t it be used to solve my #1 challenge; how to find a keyword or command within a saved KQL query. A light bulb moment. Fortunately it was easy to make a few simple changes to the code from John. Now from the search control in this Workbook you can type and find any text.

You can see in the next screenshot, we can search on a string, that can be the category, name or content/key word in the code. This is a great time saver for me – just today I wanted a “regex” example and had to open 10+files to find it, with this workbook, I only needed one go!

You can see here, I looked for the word ‘extend‘ which was found in the Demo category, in a file called services-running – prior to this Workbook…would I have remembered it was in a file with that name, probably not?

key word search