CODEWITHSUNDEEP
excelazureazure-log-analytics
Ruben Verschueren
Ruben Verschueren

Reputation: 842

how to use a stored log analytics query as a datasource in excel?

In our project we created several useful queries on log analytics that we deploy as a "savedSearch" (Microsoft.OperationalInsights/workspaces/savedSearches@2020-08-01). Now when we load the query in the editor we can export it to excel, which can be nicely refreshed to view current data.

However this link is created to the query that is in the editor and not the stored/deployed query. The alternative is to export to Power Bi (M query) which generates a script that you can then use in excel.

In both cases the query itself seems to be in the connection, so it does not get updated when we deploy a new version. Does anyone know of a way to make this connection to a stored/deployed query?

I feel like this should be as straightforward as a connection to resource so that not only the data, but also the query itself gets updated.... I must be missing something

Upvotes: 0

Views: 226

Answers (1)

Bhargavi Annadevara
Bhargavi Annadevara

Reputation: 5512

One way I can think of is to leverage Functions in log queries.

You can first save your query as a function, then export it to excel that would create a connection but execute the function, instead of the raw query.

You can tweak your query later if needed and save/overwrite to the same function, and the refresh should still be able to pull in the latest results since the changes are now neatly abstracted away via the function. :)

Upvotes: 1

Related Questions