This is the 5th part of my blog series on the Road to CE 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

Excel

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

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

Accept the column names, untick the prefix checkbox

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

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

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

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

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 Road to CE series will be on Winget and the new app store source in Intune.