Use Azure Automation to export Endpoint Analytics Proactive Remediation report to a CSV then upload on Sharepoint

In this post I will show how to use Azure Azure Automation to get device status from a specific Endpoint Analytics Proactive Remediation script then upload report as CSV to a Sharepoint/Teams

Context

- You have created a Remediation Script

- You want to get device status part

- You want to export it as CSV

- You want this CSV on Sharepoint/Teams

- You want a schedule way for this

The solution

We will proceed as below:

1. Create a Sharepoint app for the report

2. Create an Automation account

3. Create a Runbook

4. Add the script on the runbook

5. Schedule the runbook

The script

The script that will do below things:

- Connect to intune

- Get remediation report details

- Upload report to Sharepoint

Replace below values with your own:

$tenant: Your tenant name

$clientId: Your azure app id

$clientSecret: Your azure app secret

$Script_name: Name of the remediation script to export

$app_id: Your sharepoint app id

$App_Secret: Your sharepoint app secret

$Upload_Folder: Sharepoint folder

$Site_URL: Sharepoint URL

Get the script

Click on the below picture to get the script

Remediation part

In this example, my remediation script is the below one:

It's called: Detects no BIOS password devices

It will check if device has a BIOS password.

See the post about how to create this kind of remediation script.

So now we want to get the list of device and details.

Azure application part

Create the app

1. Log in to Azure

2. Go to App registrations

3. Click on New registration

4. Type a name and let by default

5. Click on Register

6. You now have access to your app info

Create a secret client

For authentication we will need a secret.

To create it, you will need to proceed as follows:

1. Click on Certificates & secrets

2. Click on New client secret

3. Enter a name

4. Choose Never

5. Click Add

6. Your secret client will be displayed

7. Copy it somewhere to reuse it

Add permissions

1. Go to your Azure application

2. Click on API permissions

3. Click Add a permission

4. Choose Microsoft Graph

5. Click on Application permissions

6. Navigate to DeviceManagementConfig

7. Check below permissions

8. Click on Add permissions

9. Click on Grant admin consent

Sharepoint part

The idea is to create a Sharepoint app to authenticate to Sharepoint.

This way you will be able to upload a file easily.

You can find in my post here, how to do that.

Azure Automation part

Purpose: In this part we will create a runbook meaning a script that will be executed each hour. 

This script will generate the remediation script report and upload it on Sharepoint.

Create the automation account

1. Go to Azure

2. Go to Automation accounts

3. We will create a new one, click on + New

4. Type a name like RemediationReport

5. Select your subscription

6. Select a Resource group

OR 

6. Click on Create new in resource group part

7. Select your location

8. Click on Create

9. Wait for the success notif

10. You have now your new automation account

Install modules

Purpose: In this part we will import the below modules in our Automation part:

- Microsoft.Graph.Intune

- pnp.PowerShell

Import Intune module

1. Go to your automation account

2. Go to Modules

3. Click on the Browse Gallery

4. Search Microsoft.Graph.Intune

5. Click on the appropriate module

6. Click on Import

7. Click on OK 

8. The import starts

Import sharepoint module

1. Go to your automation account

2. Go to Modules

3. Click on the Browse Gallery

4. Search pnp.powershell

5. Click on the appropriate module

6. Click on Import

7. Click on OK 

8. The import starts

Runbook part

Create the Runbook

1. Click on the automation previously created

2. Click on Runbooks

3. Click on Create a Runbook

4. Type a name like UploadRemediationReport

5. In Runbook type, select PowerShell

6. Click on Create

Test the runbook

1. Your runbook is empty

2. Add the code from the script

3. Click on Save 

4. Click on Test pane 

5. Click on Start

6. The job completed successfully

7. Now check your sharepoint

8. The CSV has been uploaded

9. See below how it looks like:

Publish the runbook

Purpose: Now we have tested our script, we will publish it and add a schedule.

1. Click on Edit

2. Click on Publish

3. Click on Yes

4. Click on Schedules

5. Click on Add a schedule

6. Click on Schedule

7. Click on Add a schedule

8. Type a name

9. Select the start date and Timezone

10. Select Recurring

11. We will set it to every 3 hour

12. Click on Create

13. Click on OK