Powershell Script - User License Report

Question

Hey, I have a script which identifies if a user has a specific license and exports the data in a csv. The script works and I get the data I want but when I manipulate the data using a pivot table it doesn't add up. The reason for this is the data is a mix of TRUE, FALSE, BLANK and text (AccountSkuId) so the pivot table doesn't work. Below is the script:

Get-MsolUser -All | where {$.isLicensed -contains $true} | Select-Object DisplayName, {$.Licenses.AccountSkuId -eq "Blah:SPE_F1"}, {$.Licenses.AccountSkuId -eq "Blah:MCOEV"}, {$.Licenses.AccountSkuId -eq "Blah:ENTERPRISEPACK"}, {$_.Licenses.AccountSkuId -eq "Blah:MEETING_ROOM"}, {$_.Licenses.AccountSkuId -eq "Blah:POWER_BI_PRO"}, {$_.Licenses.AccountSkuId -eq "Blah:POWERAUTOMATE_ATTENDED_RPA"}, {$_.Licenses.AccountSkuId -eq "Blah:STANDARDPACK"} | export-csv C:\PS-CSV\LicenseUser1.csv

Is there a way to get round this so its consistent using PowerShell?

Thanks,

Answer 1

Hi Rich,

thanks for that.

I managed to get it running by moving lines 1,2,3 as part of one line. This gives me the data in True or False statements which is great. However I've ran into the same problem as before where the pivot table doesn't work, must be something I need to do in Excel.

Thanks for your help.

Ben

Answer 2

Hi Rich,
I've changed it to contains but it still get this error:

The hash literal was incomplete.

• CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
• FullyQualifiedErrorId : IncompleteHashLiteral

Get-MsolUser -All |
Where-Object { $.isLicensed -contains $true } |
Select-Object DisplayName,
@{n = 'SPE_F1'; e = { $_.Licenses.AccountSkuId -contains "Blah:SPE_F1" } },
@{n = 'MCOEV'; e = { $.Licenses.AccountSkuId -contains "blah:MCOEV" } },
@{n = 'ENTERPRISEPACK'; e = { $.Licenses.AccountSkuId -contains "Blah:ENTERPRISEPACK" } },
@{n = 'MEETING_ROOM'; e = { $_.Licenses.AccountSkuId -contains "Blah:MEETING_ROOM" } },
@{n = 'POWER_BI_PRO'; e = { $_.Licenses.AccountSkuId -contains "Blah:POWER_BI_PRO" } },
@{n = 'POWERAUTOMATE_ATTENDED_RPA'; e = { $_.Licenses.AccountSkuId -contains "Blah:POWERAUTOMATE_ATTENDED_RPA" },
@{n = 'STANDARDPACK'; e = { $_.Licenses.AccountSkuId -contains "Blah:STANDARDPACK" } } |
Export-Csv C:\PS-CSV\LicenseUser14.csv -NoTypeInformation

thanks,

Answer 3

Sorry Rich I'm getting the same error.

I've just noticed there is an extra } at the end of the script, when I remove this I get a different error:

The hash literal was incomplete.

• CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
• FullyQualifiedErrorId : IncompleteHashLiteral

thanks,

Answer 4

Thanks Rich for your reply much appreciated.

When I run the above script I get this error:

Select-Object : The "e" key has a type, System.Object[], that is not valid; expected types are {System.String, System.Management.Automation.ScriptBlock}.
At line:4 char:10

• Select-Object DisplayName, @{n='SPE_F1';e={$_.Licenses.Acco ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidArgument: (:) [Select-Object], NotSupportedException
• FullyQualifiedErrorId : DictionaryKeyIllegalValue1,Microsoft.PowerShell.Commands.SelectObjectCommand

Have I done something wrong?

Thanks,

Answer 5

I think this is what you want:

Get-MsolUser -All | 
    Where-Object { $.isLicensed -contains $true } | 
        Select-Object   DisplayName, 
                        @{n = 'SPE_F1'; e = { $_.Licenses.AccountSkuId -eq "Blah:SPE_F1" } }, 
                        @{n = 'MCOEV'; e = { $_.Licenses.AccountSkuId -eq "Blah:MCOEV" } }, 
                        @{n = 'ENTERPRISEPACK'; e = { $_.Licenses.AccountSkuId -eq "Blah:ENTERPRISEPACK" } }, 
                        @{n = 'MEETING_ROOM'; e = { $_.Licenses.AccountSkuId -eq "Blah:MEETING_ROOM" } }, 
                        @{n = 'POWER_BI_PRO'; e = { $_.Licenses.AccountSkuId -eq "Blah:POWER_BI_PRO" } }, 
                        @{n = 'POWERAUTOMATE_ATTENDED_RPA'; e = { $_.Licenses.AccountSkuId -eq "Blah:POWERAUTOMATE_ATTENDED_RPA" } }, 
                        @{n = 'STANDARDPACK'; e = { $_.Licenses.AccountSkuId -eq "Blah:STANDARDPACK" } } | 
                Export-Csv C:\PS-CSV\LicenseUser1.csv -NoTypeInformation