Import Azure Monitor log data into Azure Data Factory

Question

Is it possible to connect Azure Data Factory to the Azure Monitor logs to extract the data? You can connect from Power BI as described here: https://learn.microsoft.com/en-us/azure/azure-monitor/platform/powerbi

But I want to be able to connect from Data Factory to the log.

score 1 · Answer 1 · answered Nov 26 '19 at 06:49

1

Per my knowledge,there is no direct way which is similar to the PB way in the link you mentioned in your question in the ADF. Based on this document and ADF portal UI, we could store the log in three residences:

Azure Storage Account.
Event Hub.
Log Analytics.

For storage account ,you could access them in copy activity.
For Event hub, maybe you could use Event Hub REST API in REST dataset and ADF Web Activity or you could get an idea of Azure Stream Analytics.
For Log Analytics, you could use Log Analytics REST API in REST dataset and ADF Web Activity：

POST https://api.loganalytics.io/v1/workspaces/DEMO_WORKSPACE/query
```
X-Api-Key: DEMO_KEY
Content-Type: application/json

{
    "query": "AzureActivity | summarize count() by Category"
}
```

answered Nov 26 '19 at 06:49

Jay Gong

23,163
2
27
32

Thanks Jay, I'll have a go tomorrow and let you know what worked – GM3 Nov 26 '19 at 07:46
@GM3 Sure,take your time. – Jay Gong Nov 26 '19 at 07:47
I've been using ADF and able to create the linked service to the log analytics api using the standard url: https://api.loganalytics.io/v1/workspaces//query But where I'm getting stuck is using the RelativeURL when creating the dataset. I'm not sure how to put that together and struggling to find a good example. l and don't show how to put together the right relative url and authentication. Any good example which shows 1. Linked Service REST connection 2. How they create the ADF Dataset 3. How they then use the Copy Activity to load from REST API to SQL Table ........... – GM3 Nov 28 '19 at 10:29
Or is there a good example someone has of the RequestBody, in the Copy Activity when using REST to copy Azure Log Analytics – GM3 Nov 28 '19 at 20:56
Does anyone have a good example on this of how they would also get the bearer tocken? – GM3 Nov 29 '19 at 21:12

score 1 · Answer 2 · edited Dec 16 '19 at 13:11

Got is all working. I have done the following:

Create a Pipeline which contains 2 Web Activities, 1 For Each Loop & Call to stored procedure to insert the data
First Web Activity gets the bearer token
Second Web Activity calls the REST API GET and has a Header name Authorization which brings in the access_token for the first web activity Bearer {access_token}
Then A For Each Loop which I pass the output for the second Web Activity
Stored procedure Activity which passes in all my fields into an insert stored procedure

Finally that all worked. I had a lot of trouble using the Copy Activity so resorted to the For Each Loop and stored procedure call to insert each record from the output of the REST API call in the web activity.

I will post more detailed info once I get some sleep!

It would be great if you could share more info around what authentication method you uses. — Aaron C, Dec 23 '21 at 03:30

Aaron C · Answer 3 · 2023-05-02T01:07:56.123

There are two methods witch depend on the method of authentication to the API.

The first is with a service principle, High level steps described by GM3. This Blog on the topic is also useful: https://datasavvy.me/2020/12/24/retrieving-log-analytics-data-with-data-factory/comment-page-1/#comment-28467

Second is with Managed Identity:

first give ADF access to Log Analytics using IAM How can I use this API in Azure Data Factory
Then connect to Log Analytic API with Web activity or a copy activity (these are the two i got working).

Web Activity

URL: https://api.loganalytics.io/v1/workspaces/[Workspace ID]/query

Body: {"query":"search '*'| where TimeGenerated >= datetime(@{pipeline().parameters.it_startDate}) and TimeGenerated < datetime(@{pipeline().parameters.it_endDate}) | distinct $table "}

Copy Activity

First the linked service.

ADF Datasets:

Base URL: URL: https://api.loganalytics.io/v1/workspaces/[Workspace ID]/

Copy Source:

Body: { "query": "@{item()[0]} | where TimeGenerated >= datetime(@{pipeline().parameters.it_startDate}) and TimeGenerated < datetime(@{pipeline().parameters.it_endDate})" }

Additional: The body code above, gets a list of the table names in log analytics using the web activity. Which I then pass to the Copy Activity to exports copy of the data for each table.

thanks a lot Aaron, your comment helped me a lot !!! just one question did you manage to save the results to a csv ? I tried but the file is always blank, but if I record it in json the structure appears. Do you know how I can save the results in CSV? once again thanks a lot ! — coding, Apr 21 '23 at 13:18
Hi @coding, I don't have the ADF in front of me or access at the moment. What I can say I do remember is there should be a couple of ways to select the data from the json output. One method was something like "output.data" or "output[data]". Where the words "output" and "data" fit the variables. — Aaron C, May 02 '23 at 03:27

score 0 · Answer 4 · answered Dec 03 '19 at 06:47

0

Partial Answer: I have been able to use 2 Web Activities in a pipeline. 1 which gets the bearer token and 2 which then uses the bearer token to carry out the GET command. BUT now the question is how can I use the Output from the Web Activity in a subsequent Copy Activity so I can load the data into SQL ????

answered Dec 03 '19 at 06:47

GM3

63
2
6

Has anyone used a copy activity to GET data from REST API and Sink to a SQL Table? – GM3 Dec 03 '19 at 07:56

Import Azure Monitor log data into Azure Data Factory

4 Answers4

Linked