Loading...

Exporting custom Intune report to a CSV on SharePoint with Logic Apps


In this post I will show you how to use Logic Apps to export Intune report to a CSV on SharePoint every days. in order to create Power BI dashboard for instance.


Context

On Intune you can export a lost of things to a CSV file.

You can for instance export Intune discovered apps, as below:

You can find on this post a way to do this with a Logic App.

You can also export info about update, defender agents, active malware...


Exporting report to a CSV can be helpful to send to someone else or to create Power BI dashboard with a CSV.

You can export a report through the portal meaning you can of course do this using MS Graph.

Actually when you click on the export button, it will run a exportjob action in background with the following URL and a POST method.

To do this with Graph, you will need to specify the name of the report to export and columns to display in a body.

You can find there name of report to specify and where you can get them through the portal.


The solution

The idea is to have a script or a Logic app that will export data from the report as CSV.

In my previous post I explained of the export discovered apps. For that I specified the name of the report in a body and HTTP request.

To export whatever Intune report we want we will proceed as below:

1. Specify the name of the report we want in a variable

1. Run the exportjob action on the specified report

2. The action will return a status

3. When the export action is finished status is Completed

4. Then it will return an URL

5. With this URL you will be able to download a ZIP

6. The ZIP will contain the CSV



Requirements

SharePoint service account

We want to export Intune discovered apps on SharePoint every days so we will need an account to create the file.

I recommend you to use a service account. You can also use your own account of course.


Azure Application or managed identity

The first step is to create an Azure Application.

This one will be used to authenticate and execute MS Graph query.

Click here to see how to proceed.


Creating the Logic App

Click here to see how to proceed.


Logic App step by step

Step 1: Recurrence

This step allows you to schedule the execution of the Logic App.

1. Go to Logic App Designer

2. Click on recurrence

3. Choose your schedule


Step 2: Initialize report variable

In this step we will specify which report we want to export.

For that we will create a new variable containing the report name.

In this example we will play with ActiveMalware report.

We will proceed as below:

1. Click on New step

2. Choose Initialize variable

It's a component of Variables

3. In Name, type ReportName

4. In Type, choose String

5. In Value, type ActiveMalware


Step 2, 3 - Remove existing CSV

Using this Logic App we will create a new CSV on a SharePoint.

There we will first remove the created CSV file if exist.

We will proceed as below:

1. Click on + then Add an action

2. Choose Get files (property only) 

It's a SharePoint action

3. Click on Sign in


4. Type credentials of the account that can create file on SharePoint

5. In site address, choose your site


6. In Library name, choose Documents

7. Click on Add new Parameter


8. Check Filter query


9. Go to Filter query


10. Type  FileLeafRef eq '@{variables('ReportName')}.csv'


Now we will add a condition to check if the file already exists.

If yes we will remove it. 

1. Click on New step

2. Choose Condition

It's a Control action


3. Go to Choose a value


4. Go to Expression


5. Type: length(body('Get_files_(properties_only)')?['value'])

6. Select is greater than


7. Then type 0


6. Go to True

7. Click on Add an action


8. Choose Delete file

It's a SharePoint action


9. In site address, choose your site

10. Go to File identifier 

11. Go to Dynamic content, choose Identifier

12. It will be configured as below:


Step 4 - Export report job

This step allows you to use MS Graph to query Intune in order to initialize the export job action.

This will run an exportjob action and you will get the URL of the ZIP to download.


We will proceed as below:

1. Click on New step

2. Choose HTTP


3. In Method select POST

4. In URI type the below one:

https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs


5. In Body, type the following JSON:

6. Clic on Add new parameter

7. Check Authentication


8. Select Active Directory OAuth

9. In TenantID, type your tenant id

10. In Audience, type https://graph.microsoft.com

11. In Client ID, type the client id of the azure app

12. In Credential type, choose Secret

13. In Secret, type the secret of the azure app


14. Save the app

15. Click on Run trigger > Run

16. Go to the Body part

17. Copy content


We will use the body content in the Parse JSON part (just below).


Step 5 - Parse JSON content

This step allows you to use MS Graph to query Intune and get information provided by the previous MS Graph query.

Info is received through the previous HTTP action. 

We need to parse content of the received information using a Parse JSON action.

We need to configure the schema with provided by the previous HTTP step.

For that we need to first run the trigger and get Body result from the HTTP action. 

This is what we have done in the part 18 from the previous step.


See below how to proceed:

1. Click on + then Add an action

2. Choose Parse JSON

It's a component of Data operations


3. Click on Content 

4. Go to Dynamic content and type Body

5. Select Body

6. Click on Use sample payload to generate schema

7. Paste JSON content copied from Step 2 part



Step 6 - Initialize appid variable

In this step we will get the application id provided by the previous Graph query and the Parse JSON step.

We will proceed as below:

1. Click on + then Add an action

2. Choose Initialize variable

3. Type appid

4. Go to Value

5. Choose ID (from the Parse JSON)



Step 7 - Initialize Status variable

Here we will initialize a variable to get the application id of the Graph query.

1. Click on + then Add an action

2. Choose Initialize variable

3. In Name, type AppStatus

4. In Type, choose String



Step 8 - Get status of the export

This step allows you to use MS Graph to query the previous export action.

Here we want to know if the action is finished or not.

If finish status will be completed.

Do add it proceed as below:

1. Click on + then Add an action

2. Choose HTTP

3. In Method select GET

4. In URI type the below one:

https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('@{variables('appid')}')


5. Clic on Add new parameter

6. Check Authentication

7. Select Active Directory OAuth

8. In TenantID, type your tenant id

9. In Audience, type https://graph.microsoft.com

10. In Client ID, type the client id of the azure app

11. In Credential type, choose Secret

12. In Secret, type the secret of the azure app

13. Save the app

14. Click on Run trigger > Run

15. Go to the Body part

16. Copy content


Step 9 - Parse JSON content

This step allows you to use MS Graph to query Intune and get information provided by the previous MS Graph query.

Info is received through the previous HTTP action. 

We need to parse content of the received information using a Parse JSON action.

We need to configure the schema with provided by the previous HTTP step.

For that we need to first run the trigger and get Body result from the HTTP action. 

This is what we have done in the part 18 from the previous step.


See below how to proceed:

1. Click on + then Add an action

2. Choose Parse JSON

It's a component of Data operations

3. Click on Content 

4. Go to Dynamic content and type Body

5. Select Body

6. Click on Use sample payload to generate schema

7. Paste JSON content copied from Step 2 part



Step 10 - Wait for action is completed
We have run the exportjob action.
Now we have to wait for the job status is completed.
Then we will be able to get the URL to download the ZIP.
For that we will need to create a Until loop to check the job until the status is completed.
The process is the following:
- Wait for status is completed
- If not completed wait 5 seconds
- Do an HTTP request to check status

We will proceed as below:
1. Click on New step
2. Choose Until
It's a component of Control

3. Go to Choose a value

4. Select Status (from the previous Parse JSON action)
5. Choose is equal to
6. Choose Completed

Now we will add a delay of 5 seconds:
1. Click on Add an action
2. Choose Delay
It's a Schedule action

3. In Count, choose 5
4. In Unit, choose Second

Now we will check the status of the job with an HTTP request:
1. Click on Add an action
2. Choose HTTP
3. In Method, choose GET
4. In Uri, type the below one
https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('@{variables('appid')}')
5. Clic on Add new parameter
6. Check Authentication
7. Select Active Directory OAuth
8. In TenantID, type your tenant id
9. In Audience, type https://graph.microsoft.com
10. In Client ID, type the client id of the azure app
11. In Credential type, choose Secret
12. In Secret, type the secret of the azure app
13. Save the app
14. Click on Run trigger > Run
15. Go to the Body part
16. Copy content

Now we will parse result of the previous HTTP request:
1. Click on + then Add an action
2. Type Parse JSON
It's a component of Data operations
3. Select Parse JSON
4. Click on Content 
5. Go to Dynamic content and type Body
6. Select Body
7. Click on Use sample payload to generate schema
8. Paste JSON content copied before

Step 11 - Get job details after it completes
Now we will check the details of the job with an HTTP request:
1. Click on Add an action
2. Choose HTTP
3. In Method, choose GET
4. In Uri, type the below one
https://graph.microsoft.com/beta/deviceManagement/reports/exportJobs('@{variables('appid')}')
5. Clic on Add new parameter
6. Check Authentication
7. Select Active Directory OAuth
8. In TenantID, type your tenant id
9. In Audience, type https://graph.microsoft.com
10. In Client ID, type the client id of the azure app
11. In Credential type, choose Secret
12. In Secret, type the secret of the azure app
13. Save the app
14. Click on Run trigger > Run
15. Go to the Body part
16. Copy content

Now we will parse result of the previous HTTP request:
1. Click on + then Add an action
2. Type Parse JSON
It's a component of Data operations
3. Select Parse JSON
4. Click on Content 
5. Go to Dynamic content and type Body
6. Select Body
7. Click on Use sample payload to generate schema
8. Paste JSON content copied

Step 12 - Set variable JobURL

In this we we will store the job URL in the JobURL variable.

The job URL is provided by the previous Parse JSON as url.

We will proceed as below:

1. Click on + then Add an action

2. Choose Initialize variable

3. Type JobURL

4. Go to Value

5. Choose url (from the Parse JSON)


Step 13 - HTTP query on the JobURL

This step allows you to use MS Graph to query job URL we get from the previous action.

With the body content we will be able to create a file in a new step.

Do add it proceed as below:

1. Click on + then Add an action

2. Choose HTTP

3. In Method select GET

4. In URI type JobURL from the previous action


Step 14 - Create ZIP file

1. Click on + then Add an action

2. Type Create file

3. Choose Create file

It's a component of SharePoint 

4. In site address, choose your site

5. In Folder path, type the path of your folder

In my case it's: /Documents partages/Windows/Logs

6. In File name, type @{variables('ReportName')}.zip

7. Click in File content

8. Choose Body from the previous HTTP step



Step 15 - Extract ZIP file

1. Click on + then Add an action

2. Choose Extract folder

It's a SharePoint action

3. In site address, choose your site

4. Go to Source File path

5. Type the SharePoint folder where to save the file

In my case it's: /Documents partages/Windows/Logs/

6. Go to the end of the path then Dynamic content

7. Choose ReportName variable


8. Then type .zip, it should be as below:


9. Go to Destination Folder Path

10. Type the folder where the ZIP is

In my case it's: /Documents partages/Windows/Logs

11. In Overwrite Flag, select yes



Step 16 - Filter array

When you export your report from Intune, it will be downloaded as a CSV file with name.

When you do this with Graph the export job will download a ZIP file and the ZIP will extract a CSV with name YourReport_***.csv

Here we want to filter result of the previous Extract folder action on the name of the CSV.

We have already set the name of the CSV by specifying the variable ReportName.

We will proceed as below:

1. Click on + then Add an action

2. Type Filter

3. Choose Filter array

It's a Data operations action

4. Go to From

5. Select Body from the Extract_Folder action

6. Go to value

7. Choose Path 

8. Select Contains

9. Then go to Dynamic content

10. Choose variable ReportName 



Step 17 - Get file metadata

1. Click on + then Add an action

2. Choose Get file metadata using path

It's a SharePoint action

3. In site address, choose your site

4. Go to File path



Step 18 - Get file properties

1. Click on + then Add an action

2. Choose Get file properties

It's a SharePoint action

3. In Library name select Documents

4. In Id, select ItemId



Step 19 - Send HTTP request to SharePoint

By default when you export a report from Intune, it will create a CSV with name of the report then the appid.

Here in our example we wan to export ActiveMalware report.  The name of the CSV will be something like: ActiveMalware_7219da44-96af-4ceb-af9e-17074863a8dd.

Here we want to rename the CSV to have something better like ActiveMalware.csv

We will proceed as below:

1. Click on + then Add an action

2. Choose Send HTTP request to SharePoint

It's a SharePoint action

3. In site address, choose your site

4. Choose POST as Method

5. in Uri, type the below one:

_api/web/lists/getbytitle('Documents')/items(@{body('Get_file_properties')?['ID']})/validateUpdateListItem

6. In Body, type the below one:



Step 20 - Delete the ZIP

Here we want to delete the ZIP generated in the step 16.

We will proceed as below:

1. Click on + then Add an action

2. Choose Delete file

It's a SharePoint action

3. In site address, choose your site

4. Go to File identifier

5. Type the path of DiscoveredApps.zip

slider 6544746065763806891

Enregistrer un commentaire

Accueil item

Award

Sponsors

You want to support me ?

Mes articles en français

Author of Books


Stats