0

How can I produce the Result.CSV file after comparing the two variable contents below?

$LicensedUsers.UserPrincipalName against $AuditLogsUser

Get the users:

$LicensedUsers = Get-MgUser | Sort-Object UserPrincipalName

Get the User account (UPN) from the audit logs:

$AuditLogsUser = Import-Csv -Path 'C:\Logs.csv' | Select-Object -ExpandProperty UserID -Unique | Sort-Object

Result.CSV file:

DisplayName, Mail, UserPrincipalName, Status
John Matrix, John.Matrix@domain.com, John.Matrix@domain.local, Active
user name, user.name@domain.com, user.name@domain.local, Inactive
old. User, old.user@domain.com, old.user@domain.local, Inactive
Senior Systems Engineer
  • 1,061
  • 2
  • 27
  • 63
  • 1
    I guess you want to join the to lists where `UserPrincipalName` equals the `UserID`: see [In PowerShell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026). Using the [JoinModule](https://www.powershellgallery.com/packages/JoinModule): `$LicensedUsers = Get-MgUser @paramGetMgUser | Join-Object (Import-Csv -Path C:\Logs.csv) -on UserPrincipalName -eq UserID` – iRon Aug 29 '23 at 07:44
  • @iRon, almost correct, but I need to get the result as the .CSV file by comparing the $LicensedUsers.UserPrincipalName against $AuditLogsUser and then show which UPN is not available in the $AuditLogsUser. – Senior Systems Engineer Aug 29 '23 at 09:18
  • 1
    You might do a `FullJoin` instead and than `... | Where-Object { !$_.UserID }`. Or simply: `$LicensedUsers = Get-MgUser @paramGetMgUser | Where-Object { !($AuditLogsUser -eq $_.UserPrincipalName) }` which might be slower as `$AuditLogsUser` isn't indexed. – iRon Aug 29 '23 at 09:32
  • 1
    Perhaps, `$UserNotInAuditLog = $LicensedUsers | Where-Object { $_.UserPrincipalName -notin $AuditLogUser }`. – lit Aug 29 '23 at 12:14
  • Yes, that does make sense. Thank you @lit for the pointer. – Senior Systems Engineer Aug 29 '23 at 14:29

0 Answers0