Trying to create a custom report for Azure MB users

This topic contains 3 replies, has 2 voices, and was last updated by  Don Jones 3 months, 3 weeks ago.

  • Author
    Posts
  • #78157

    Ed O’Connor
    Participant

    Hi,

    I am trying to create a report for our O365 team as we are beginning our migration to 0365 and they have requested some information that will help them in troubleshooting a few issues as well as confirming certain group memberships.

    They want the report (single csv)to have the following as columns:

    Name,DisplayName,Alias,UserPrincipalName,LastLoginTime, and if a member of two AD security groups (Group1 and Group2)

    I can get the command I have come up with to run perfectly and export to CSV if exclude the security groups. Every time I try something to include the csv if the user is a member of the AD security groups it does not work.

    This is what I have come up with:

    Get-Mailbox -ResultSize Unlimited –RecipientTypeDetails UserMailbox,SharedMailbox -WarningAction silentlyContinue | Where {(Get-MailboxStatistics $_.Identity).LastLogonTime -gt (Get-Date).AddDays(-14)} -WarningAction silentlyContinue | Sort -Property @{e={(Get-MailboxStatistics $_.Identity).LastLogonTime}} -WarningAction silentlyContinue | Select-Object Name,DisplayName,Alias,UserPrincipalName,@{n="LastLogonTime";e={(Get-MailboxStatistics $_.Identity).LastLogonTime}},@{n="Member of Group1";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Group1'}) {[pscustomobject]@{ ' Member of Group1 ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Office365 Outlook Azure Duo ' = "No"}}}}},@{n="Member of Office365 Outlook MFA";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Group2'}) {[pscustomobject]@{ ' Member of Member of Group2 ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Group2 ' = "No"}}}}} | export-csv c:\temp\AzureMBUsers1.csv
    

    As I mentioned if I remove the portion for the AD security groups it works:

    @{n="Member of Group1";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Group1'}) {[pscustomobject]@{ ' Member of Group1 ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Office365 Outlook Azure Duo ' = "No"}}}}},@{n="Member of Office365 Outlook MFA";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Group2'}) {[pscustomobject]@{ ' Member of Member of Group2 ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Group2 ' = "No"}}}}}
    

    Prior to trying the pscustomobject I simply had a write-output yes or no for the if/else statements but that did not work either.

    Any advice/guidance is greatly appreciated.

  • #78163

    Don Jones
    Keymaster

    So, I think writing this as a ginormous one-liner is part of what's making this harder on you than it needs to be. Would you consider refactoring this into a more traditional procedural script? This isn't even all that efficient, given what you're doing with the objects in the pipeline, and it's frankly about impossible for me to parse :).

  • #78186

    Ed O’Connor
    Participant

    Sorry,
    Is this better?

    Get-Mailbox -ResultSize Unlimited –RecipientTypeDetails UserMailbox,SharedMailbox -WarningAction silentlyContinue | 
    	Where 	{
    			(Get-MailboxStatistics $_.Identity).LastLogonTime -gt (Get-Date).AddDays(-14)
    			} -WarningAction silentlyContinue | 
    	Sort -Property @{
    					e={(Get-MailboxStatistics $_.Identity).LastLogonTime}
    					} -WarningAction silentlyContinue | 
    	Select-Object Name,DisplayName,Alias,UserPrincipalName,
    		@{n="LastLogonTime";e={(Get-MailboxStatistics $_.Identity).LastLogonTime}
    		 },
    		@{n="Member of Office365 Azure Duo";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Office365 Outlook Azure Duo'}) {[pscustomobject]@{ ' Member of Office365 Outlook Azure Duo ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Office365 Outlook Azure Duo ' = "No"}}}}
    		 },
    		@{n="Member of Office365 Outlook MFA";{e={If (Get-ADPrincipalGroupMembership $_.Identity | select -ExpandProperty name | Where-Object {$_ -like 'Office365 Outlook MFA'}) {[pscustomobject]@{ ' Member of Office365 Outlook MFA ' = "Yes"}} Else {[pscustomobject]@{ ' Member of Office365 Outlook MFA ' = "No"}}}}
    		 } | 
    	export-csv c:\temp\AzureMBUsers1.csv
    
  • #78195

    Don Jones
    Keymaster

    Well... I guess the difficulty is that it's still just a giant one-liner, right? It becomes a lot harder to unwind logic, and more to the point, there's no great way to run it through a debugger. Were I debugging this, I'd want to stick a breakpoint in at the problem point, so I could get into the command-line inside the scope of the script, and see what was what. For example, you're making some assumptions, where you run Get-ADPrincipalGroupMembership, in what its return value would or wouldn't be – and I'd want to validate that in the context of the script.

You must be logged in to reply to this topic.