Saving results of a query to be used by other queries

[debashish2021]

Hi,

  I am new to KQL and trying to explore the possibility of being able to save the results of a query in Azure Log Analytics . A scheduling will be done on this query to be run once a day to update the results and hence update the saved data.

The results saved from the above query will be used by other queries that will do a lookup into this saved table to suss out important details when returning results. So for example.

I have Query A that returns column A , Column B , Column C  that gets saved in Table A.

This Query A is run say every day so that Table A is always refreshed with latest and greatest data.

Now I have Query B that has access to the values in Column A of Table A . It uses this value of ColumnA to query Table A to retrieve data for Column B , Column C and enrich the results that are returned .

Since Query B is run very frequenty I don't really want to do the full query A ( which is fairly static) since it will become overly inefficient.

Is this something that is possible with KQL and Log Analytics ?

Thanks

[#2]

You could potentially look at Azure Automation and a PowerShell runbook to trigger the query, based on your schedule..

[#3]

As Luke says, Log Analytics queries cant create a Table, so you can use his idea or look at a Logic App on a recurrence trigger to fire the query at a specific time of day, the logic app can then run the KQL query to populate a custom log table <table name_CL>. You can then "join" your other query to that table with your aggregated results. This example sends an email, https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2020/06/17/log-analytics-or-azur... but you can use another control "send data" to populate a Table (custom logs are billable data sources)

Example "join" https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/joinoperator?pivots=azuremonitor

ServiceMapProcess_CL
| project Computer // computer
| join
(
Heartbeat
| distinct Computer // computer 1
) on Computer
| project customComputerName = Computer, HeartBeatComputerName = Computer1

[#4]

Hey .. thanks for the reply. I am currently stuck up at creation of step 2 'Run Query and visualize results'. It keeps giving the attached error when I try to sign in. It seems issue with 3rd party cookies that are disabled and I don't have admin control on my browser to change that. Is there a way to get around that ?

Also is there any other way of achieving the above results . We do have all the static data already saved in our azure table storage  so instead of getting the details from the logs we can as well query our table storage to get the static data. Is there a way of doing that in Azure analytics ?

Thanks

[#5]

There are some ideas here: https://stackoverflow.com/questions/62232990/azure-logic-apps-failed-with-error-the-browser-is-close...

Also, you cant easily use Table storage, Blob however would work

https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/externaldata-operator?pivots=azurem...

https://docs.microsoft.com/en-us/azure/azure-monitor/logs/logicapp-flow-connector