Loading...

Exporting Intune discovered apps to a CSV on SharePoint with Logic Apps

1 A+ A-


In this post I will show you how to use Logic Apps to export Discovered apps to a CSV on SharePoint every days.


Context

You want to export all applications installed on all devices.

You want to export info as a CSV on a SharePoint.

You want to export this CSV every day.

This way you can for instance use this CSV in a Power BI dashboard.


The solution

The idea is to have a script that will export all discovered apps as CSV.

You can do this manually through the Intune portal by clicking on Apps > Monitor > Discovered apps > Export


Using the development mode of your browser helps you to understand what is used behind when you click on the Export button.

Here below you can see that it will run exportjob action and export the following job: AppInvRawData.


Looking on MS docs, you can get below info:


The process when you run exportjob action is the following:

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


In the next post we will see how to export any Intune report with Logic App.

Requirements

SharePoint service account

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

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


Azure Application or managed identity

We will need to create or use 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

The Logic App workflow looks like as below:


Step 1 - Recurrence

The Recurence step in Logic App allows you to schedule the execution of the Logic App.

This way you can choose to run your app every hour, day, month...whenever you want.

1. Go to Logic App Designer

2. Click on recurrence

3. Choose your schedule


Step 2, 3 - Remove existing CSV

In this Logic App we will create a CSV on SharePoint.

There we want to first remove DiscoveredApps.csv if exists.

For that 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 'DiscoveredApps.csv'


Now we will add a condition to check if the file already exists and 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. Type the path of DiscoveredApps.csv

It should be something like this: /Documents partages/Windows/Logs/DiscoveredApps.csv



Step 4 - Export applications job

Here we will use MS Graph to query Intune to get list of discovered apps. We will first need 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 - Set 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 DiscoverepApps.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 path of the ZIP

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

6. Go to Destination Folder Path

7. Type the folder where the ZIP is

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

8. In Overwrite Flag, select yes



Step 16 - Filter array

When you export discovered apps from Intune, it will be download a csv file with name AppInvRawData.

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

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

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. Type AppInvRawData



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 the report name for discovered apps is: AppInvRawData

The name of the CSV will be something like: AppInvRawData_7219da44-96af-4ceb-af9e-17074863a8dd.

Here we want to rename the CSV to have something better like DiscoveredApps.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 DiscoveredApps.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



Download the Logic App

See below how to download directly this Logic App and implement it:

1. Click here to download the Logic App as JSON file

2. Create your Logic App

3. Go to Logic App code view

4. Paste content of the JSON

slider 4322407783143188087

Enregistrer un commentaire

1 commentaire

Frederick a dit…

The link to download the logic app is not available.
Thanx for sharing

Accueil item

Award

Sponsors

You want to support me ?

Mes articles en français

Author of Books


Stats