Loading...

Intune reporting with Power BI: list local admin accounts on your devices and who added them


In this post I will share a way to use Intune to create a report of local administrator on your device. You will be able to list devices with local admin account(s) and who added them and when.


Context

- You have enrolled devices in Intune

- You have some allowed local administrator or not

- You want to know which device has local administrator

- You want to know the local administrator account name

- You want to know who has added this account

- You want to know: device name and user name

- You want to export result to a CSV

- You want an schedule way for that

- You want to display result in Power BI


The solution

The process result in few steps:

- Create a Proactive Remediation

- It will list local admin account found

- Create a resource group

- Create a storage account

- Create an Azure Automation account

- Add Managed Identity on the Automation account

- Create a Runbook in Automation

- Runbook gets result from Proactive Remediation script

- Runbook exports result to CSV

- Runbook uploads CSV to blob

- PowerBI gets CSV values from the blob


Sometimes the report won't display when local admin account has been added. Indeed the script will check for each account if there is a corresponding event report 4732 . 

This event log is located in Security event which is often purge.


Log Analytics report

You can find there the same report for Log Analytics.


What is a Managed Identity ?

To resume, a managed identity is an account located in Azure AD, here an automation account.

This allows your script or app to access Azure AD resources without dealing with credentials.

Furthermore, credentials are never exposed in the code.

Your app will use the managed identity to get a token.

Managed identity can be used without any additional cost.

See below some interesting links:

Using a system-assigned managed identity for an Azure Automation account

What are managed identities for Azure resources?

Tutorial: Access Microsoft Graph from a secured app as the app

Authenticating to Azure AD protected APIs with Managed Identity


You can find also a pretty good post from Trevor Jones aka SMSAgent, there.


Get the script

Click on the below picture to get scripts required in the solution.

In the downloaded sources you will find below files:

- Assign_Permissions.ps1: script to add permissions to app

- Runbook_script.ps1: runbook azure automation script

- Report_Template.pbit: Power BI template

- Proactive_Remediation_script: detection script



Create the remediation package

1. Go to the Microsoft Endpoint manager admin center

2. Go to Reports

3. Go to Endpoint analytics

4. Go to Proactive remediations

5. Click on Create script package

6. Call it Check local admin

7. Click on Next

8. Click on Detection script file

9. Choose the detection script

12. Click on Next

13. Select the group Lenovo devices

14. In the Schedule part, choose when the package should be run.

15. In our case we will run it every 3 hours (for our test)

16. Click on Apply

17. Click on Next

18. Click on Create


Create a resource group

1. Go to Azure

2. In the search bar type Resource group

3. Go to Resource group

4. Click on Create

5. Choose a Subscription

6. Type a Resource group name

7. Here: intune_reporting

8. Choose your region

9. Here: (Europe)France Central


10. Click on Review + Create

11. Click on Create


Storage account part

Create the account

1. Go to Azure

2. In the search bar type Storage accounts

3. Go to Storage accounts

4. Click on Create

5. Choose a Subscription

6. Choose the Resource group

7. Here: intune_reporting

8. Type a storage account name

9. Here: sdreporting

10. Choose your region

11. Here: (Europe)France Central

12. In Reduncy choose LRS

13. Click on Review + Create

14. Click on Create

15. Wait a bit

16. Click on Go to Resource Group



Create a container

1. Go to the Resource Group if you're not

2. Go to Containers

3. Click on + Container

4. Type a name

5. Here: powerbi-csv

6. Let to Private (no anonymous access)

7. Click on Create



Azure Automation account

Create the account

1. Go to Azure

2. In the search bar type: Automation accounts

3. Go to Automation accounts

4. Click on Create

5. Type a name

6. Here: automating-reports

7. Choose a Subscription

8. Choose the Resource group

9. Here: intune_reporting

10. Choose your region

11. Here: (Europe)France Central

12. In Create Azure Run As Account, select No

13. Click on Create

14. Wait a bit

15. Click on Go to resource



Add modules

1. Go to your automation account

2. Click on Modules gallery

3. Search: az.Accounts

4. Click on az.Accounts

5. Click on Import

6. Click on OK

7. Wait for importing

8. Search: az.Storage

9. Click on az.Storage

10. Click on Import

11. Click on OK

12. Wait for importing


Set Managed Identity

1. Go to your automation account

2. Go to Identity (Preview)

3. Go to System assigned

4. Select On

5. Click on Save

6. Click on Yes


Check Enterprise applications

1. Go to Azure

2. In the search bar type: Enterprise applications

3. Go to Enterprise applications

4. In Application type, select Managed identities

5. Click on Apply

6. You will see the new one

7. Here: automating-reports

8. Click on it

9. Go to Permissions

10. It should be empty


Permissions to add

We want to get values from Proactive Remediation part.

To know which permissions to add, the Graph resource to use is: DeviceHealthScript

You can see on the Graph API ref deoc permissions required, see below:

We will add below permissions:

- DeviceManagementManagedDevices.Read.All

- Device.Read.All

- DeviceManagementConfiguration.Read.All

- DeviceManagementConfiguration.ReadWrite.All


Add API permissions

In this part we will add permissions through PowerShell. 

Indeed you can't do it through the Portal.

You can find in the sources the script to add permissions.

Script name: Assign_permissions.ps1

Set below variables:

$TenantID: your tenant ID 

$MSI_Name: name of the enterprise app or automation account


See below result:



Check permissions again

1. Go to tour Enterprise applications

2. Here: automating-reports

3. Go to Permissions

4. You should see your permissions

5. Click on Refresh it you don't see them



Create a custom role

1. Go to your Resource Group

2. Go to Access Control (IAM)

3. Go to Roles

4. Search role: Storage Blob Data Contributor

5. Click on the ...

6. Click on Clone

7. Type a name

8. Here: CUSTOM_Blob_Upload

9. Let by default

10. Click on Next

11. In Permissions tab, click on Add permissions

12. Search: Microsoft.Storage/storageAccounts/read

13. Select Microsoft Storage

14. Check Read : List/Get Storage Account(s)

15. Click on Add

16. Click on Add permissions

17. Search: Microsoft.Storage/storageAccounts/listkeys/action

18. Select Microsoft Storage

19. Check Other : List Storage Account Keys

20. Click on Add

21. Click on Next

22. Click on Next

23. Click on Next

24. Click on Create

25. Click on OK



Assign role to automation

1. In Access Control(IAM), go to Check access

2. Click on Add role assignment (Preview)

3. Select: CUSTOM_Blob_Upload

4. Click on Next

4. Select: User, group, or service principal

5. Click on Select members

6. Choose the enterprise app

7. Click on Select

8. Click on Next

9. Click on Select

10. Click on Review + assign


Azure Automation Runbook

Create a Runbook

1. Go to Azure

2. In the search bar type: Automation accounts

3. Go to your Automation accounts

4. Here: automating-reports

5. Go to Runbooks

6. Click on + Create a runbook

7. Type a name

8. Here: BIOS-reporting

9. In Runbook type, select PowerShell

10. Click on Create


Add script in Runbook

The runbook script is located downloaded sources.

Script name: Runbook_script.ps1

Set the below variables:

- $ResourceGroup: name of the resource group

- $StorageAccount: name of the storage account

- $container: name of the container

- $Script_name: name of the proactive remediation script


Here below my variables:

$ResourceGroup = "intune_reporting"

$StorageAccount = "sdreporting"

$container = "powerbi-csv"

$Script_name = "compare bios"


Test the Runbook

1. Click on Test pane

2. Click on Start

3. Once finished, you should see Completed

4. Go to your Container

5. You should see the CSV



Publish the Runbook

1. Go to your Runbook

2. Click on Edit

3. Click on Publish

4. Click on Yes


Schedule the Runbook

1. Go to your Runbook

2. Click on Schedules

3. Click on + Add a schedule

4. Click on Link a schedule to your runbook

5. Click on + Add a schedule

6. Type a schedule name

7. In Recurrence, select Recuring

8. Click on Create



Power BI report

Blob Storage info

1. Go to Storage accounts

2. Click on the storage account containing the CSV

3. Keep in mind the storage account name

4. Go to Access keys


5. Click on Show keys

6. Copy value from Key1


Get datas from Azure

1. Open file Local admin inventory.pbit

2. The report will be displayed (empty of course)


4. Go to Fields Get data


5. Select Azure > Azure Blob Storage

5. Click on Connect

6. Type the account name then OK

7. Paste the Access key copied previously


8. Check the appropriate folder


9. Click on Transform Data

10. Click on Binary on your CSV


11. Datas will be listed


12. Click on File > Close & Apply 


15. Datas will be loaded



Edit the report

1. Click on the first chart as below:


2. In Visualizations, click on the cross in Legend and Values


3. Extend the container folder and check Local admin status


4. Move Local admin status in Values

5. Result will be as below:


6. In Visualiations, go to Format


7. Go in Data colors and set as below:


8. See below result:


9. Click on the chart below:


10. Remove the Fields part


11. Check Device name


12. Select Count


13. See below the result:


14. Click on the last chart below:



15. Remove everything in Values:


16. Check below things:


17. In Filters, go to Local admin status

18. Expand it and check AdminFound


19. In Fields, uncheck Local admin status

20. See below the resut:


21. See below the full result:


Publish the report

1. Click on Publish

2. Click on Select

3. Click on Open report in Power BI

4. The report will be opened in your browser

5. Click on Datasets

6. Select your report

8. Click on Settings

9. In Datasets, click on Scheduled refresh

10. Click on On

11. Select a frequency

12. Click on Apply


And what about log analytics ?

Next step will be to do the same using log analytics 😄

slider 3449358920738046584

Enregistrer un commentaire

Accueil item

Award

Sponsors

You want to support me ?

Mes articles en français

Endpoint Manager award

Author of Books

Stats