Cloud advice

Road to Cyber Essentials: The Device Reports

This is the 5th part of my blog series on the series.  We’ll be starting to look at using Excel to generate the report you need for Cyber Essentials.  Please note I’ll be specifically talking about AAD and Intune.

In sections of your Cyber Essentials you need include a list of Devices, AAD and Excel can make compiling this data easy, and in the future if Jisc releases the CE Device Database tool a one button click will generate a CSV for you.  In the current (Feb 2023) Cyber Essentials you need to provide a list of Manufacturers, OS and OS Version and the quantities of those.

I’ll go through some PowerShell, lets install Graph PowerShell

Install-Module Microsoft.Graph

Next, lets connect to Graph

Connect-MGGraph -Scope Directory.AccessAsUser.All

Now we’ll query to get the info we need

$devices = Get-MgDevice -All -Filter "accountEnabled eq true" -Select "id,displayName,manufacturer,model,operatingSystem,operatingSystemVersion,profileType,managementType,isRooted,registrationDateTime,approximateLastSignInDateTime,deviceOwnership" -Expand "registeredOwners(`$select=id,displayName,userPrincipalName,department)"

We’re now going to flatter the registeredOwners for the export

$lines = @()

foreach ($device in $devices) {

    $line = $device
    foreach ($key in $device.RegisteredOwners[0].AdditionalProperties.Keys) {
        Add-Member -InputObject $line -NotePropertyName "user.$key" -NotePropertyValue $device.RegisteredOwners[0].AdditionalProperties[$key]
    $lines += $line

Finally we’ll export it as a JSON file

$lines | ConvertTo-Json -EnumsAsStrings -Depth 2 | Set-Content .\downloads\temp.json


Lets jump into excel now, data > get data > from file > from json

Excel Get Data from Json File
Excel Get Data from Json File

Select the JSON file from before, in the PowerShell it’s downloads\temp.json.

In the Power Query Editor, under List Tools > Transform > To Table

Accept the defaults on the conversion

Expand the column

Power Query > Expand Column
Power Query > Expand Column

Accept the column names, untick the prefix checkbox

Expand Columns
Expand Columns

Expand the additional properties column, if you don’t see registrationDateTime, make sure you click on the load more link, untick use original column name as prefix as well

Expand Additional Properties
Expand Additional Properties

On the Data/Time columns change the type to Date/Time

Change Data/Time to Date/Time type
Change Data/Time to Date/Time type

You may want to add a filter in here, to only load items that have within the past 6 months logged in

Filter Approx Last Signin Time

Close and Load.  Excel will then load that data.  You can update the Json and use the refresh to reload the data.

You will now see your JSON file in excel, now for some Pivot Table Magic.  Insert > Pivot Table

Drag ApproxLastSignIn and user.department into the filters, you may want some more for now that’s enough

Drag Id into the values, it’ll be go to count, that’s perfect.

Drag deviceOwnership, manufacture(.1), operatingSystem, operatingSystemVersion into the rows

Pivot Table Selection
Pivot Table Selection

That should give you a report of who’s using what with counts.  Next apply some filters to filter the pivot table to the department you need for CE.

Enjoy, this is slightly out of step from my usual posts, the next post in the series will be on Winget and the new app store source in Intune.

By Nick Brown

Senior M365 Developer and Architect @ Jisc Cloud Solutions.
Find me on twitter @techienickb

Leave a Reply

Your email address will not be published. Required fields are marked *