Forum Discussion

debashish2021's avatar
debashish2021
Copper Contributor
Jun 25, 2021

Saving results of a query to be used by other queries

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

 

 

 

azure monitor

4 Replies

  • lukemurraynz's avatar
    lukemurraynz
    Learn Expert
    Jun 25, 2021
    You could potentially look at Azure Automation and a PowerShell runbook to trigger the query, based on your schedule..
    • CliveWatson's avatar
      CliveWatson
      Former Employee
      Jun 28, 2021
      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-azure-sentinel-how-schedule-a-report/ 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
      • debashish2021's avatar
        debashish2021
        Copper Contributor
        Jun 28, 2021

        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

Resources