Export devices without primary users to a CSV on SharePoint with Logic Apps

In this post we will see how to use Logic Apps to create a report with devices without primary users and send it through a Teams notification.

Context

- You want to know which recent devices have n primary users

- You want to get the list in a Excel file

- You want a Teams notification with link to Excel

Notification and Excel

See below how looks like the Teams notification:

See below how looks like the Excel file:

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 works as below:

1. Create an empty XLSX file on SharePoint

2. Prepare the XSLX file by creating a new sheet

3. Prepare the XSLX file by creating a new table

4. Query MS Graph to get all devices

5. Filter content on last x days (last sync)

6. Filter content where UPN is empty

7. Add all info int the Excel table

8. Send a notif on Teams with the pah of the file

Step 1 - Recurrence

The Recurrence 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 - Create an empty Excel file

We want to create an Excel report with list of devices without primary users.

The first step will be to create an empty Excel file that we will use later.

By default there is no way to create an empty file on SharePoint.

Before creating the Excel file we will initialize a variable that will be used to specify the content of our file meaning nothing.

For that we will proceed as below:

1. Click on New step

2. Choose Initialize variable

It's a part of Variables

3. In Name, type FileContent

4. In Type, choose Object

5. In Value, type below content:

Now we will create the Excel file with the empty content from the variable.

For that we will proceed as below:

1. Click on new step

2. Choose Create file

It's a component of SharePoint actions

3. In Site Address, choose the SharePoint site

4. Go to Folder Path

5. Choose where to create the file in SharePoint

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

7. In File Name, type Devices_without_primaryuser.xlsx

8. Go to File Content then Expression

9. Type: variables('FileContent')

10. Click on OK

11. Save the Logic App

Now if you click on Run trigger > Run, this will automatically create an empty Excel file.

Step 3 - Create worksheet

The next step is to create a sheet in our empty Excel file.

For that we will proceed as below:

1. Click on new step

2. Choose Create worksheet

It's a component of Excel online actions

3. In Location, choose the SharePoint site

4. In Documents library, type Documents

5. Go to Name then Dynamic content

6. Choose Id from the create file step

7. In Name, type Sheet

8. Save the Logic App

Now if you click on Run trigger > Run, this will automatically create an empty Excel file with a sheet.

Step 4 - Create a table

Now we will create the structure of our table.

We will proceed as below:

1. Click on New step

2. Choose Create table

It's a component of Excel online actions

3. In Location, choose the SharePoint site

4. In Documents library, type Documents

5. In File, type the path of your file, in my case

/Documents partages/Windows/Logs/

6. Go the end of the path

7. Go to Dynamic content

8. Type Name and choose name from Create file step

9. In Table range, type something like A1:C1

10. In Columns names, type columns to display, in my case:

deviceName,serialNumber,model,lastSyncDateTime,enrolledDateTime,osVersion

11. In Table name, type Report

12. Save the Logic App

Now if you click on Run trigger > Run, this will automatically create an empty Excel file wit the structure of the table.

Step 5 - Query all devices with Graph

Here we will use MS Graph to query Intune to get list of all devices in Intune.

We will filter devices later.

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/managedDevices?$select=deviceName,userPrincipalName,lastSyncDateTime,enrolledDateTime,serialNumber,model,osVersion,manufacturer&$filter=contains(operatingSystem,'Windows')

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. Go to the 3 ...

15. Go to Settings

16. Enable Pagination

17. In the field type something like 10000

18. Click on Run trigger > Run

19. Go to the Body part

20. Copy content

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

Step 6 - 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. 

See below how to proceed:

1. Click on New step

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 7 - Filter on devices last sync

Here we want to filter result from the previous step on all devices with an activity during the last x days, here 2 days.

For that we will filter on the property lastSyncDateTime.

See below how to proceed:

1. Click on New step

2. Choose Filter array

It's a component of Data Operations

3. Go to From then Dynamic content

4. Choose value from the Parse JSON step

5. In first textbox go to Expression

6. Type addDays(utcNow(), -2) then OK

7. Choose is less than or equal to

8. In second textbox go to Dynamic content

9. Choose lastSyncDateTime from the Parse JSON step

Step 7 - Filter on no primary user found

Here we want to filter result from the previous step on all devices without primary users.

For that we will filter on the property userPrincipalName.

See below how to proceed:

1. Click on New step

2. Choose Filter array

It's a component of Data Operations

3. Go to From then Dynamic content

4. Choose Body from the previous Filter array step

5. In first textbox go to Dynamic content

6. Type userPrincipalName

7. Choose is equal to

8. Let the second textbox empty

Step 8 - Get devices count

Here we just want to get count of devices without primary users.

This will be used in th Teams notif we will send.

For that we will proceed as below:

1. Click on new step

2. Choose Initialize variable

It's a part of Variables

3. In Name, type DevicesCount

4. In Type, choose String

5. In Value, type length(body('Filter_Array_2')) then OK

Step 9 - Add data to the Excel table

Now let's populate the Excel file with info provided from the previous steps.

1. Click on new step

2. Choose For Each

It's a component of Control

3. Click on the field the Dynamic content

4. Choose Body from last filter array step

5. Click on Add an action

6. Choose Add a row into a table

It's a component of Excel online

7. In Location, choose your SharePoint site

8. In Documents library, type Documents

9. In File, type the path of your file, in my case

/Documents partages/Windows/Logs/

10. Go the end of the path

11. Go to Dynamic content

12. Type Name and choose name from Create file step

13. In Table, click on Enter custom value

14. Type Report

15. In Body, the the following body:

16. Check all values provided and replace them if needed

17. Save the Logic App

Now if you click on Run trigger > Run, this will create an Excel file with list of devices without primary users.

Step 10 - Send Teams notif

Here we want to send a Teams notif on a channel with number of devices without primary users and button to open the Excel file.

We will first need to add a webhook to the Teams channel.

To create a webhook proceed as below:

1. Go to your channel

2. Click on the ...

3. Click on Connectors

4. Go to Incoming Webhook

5. Type a name

6. Click on Create

7. Copy the Webhook path

Now let's add step to send a Teams notif

1. Click on New step

2. Choose HTTP

3. Choose method POST

4. In URL type the path of your webhook

5. In Body type the following body:

6. Save the Logic App

Now if you click on Run trigger > Run, this will create an Excel file with list of devices without primary users and send a Teams notif.