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
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 😄
Enregistrer un commentaire