[PowerShell] Sending ActiveDirectory User List Weekl

[Requirements]

- Sending all Active Directory user list weekly via email, every Tuesday 

- Including below fields

userPrincipalName, displayName, givenName, userType, jobTitle, department, accountEnabled, officeLocation, city, companyName, createdDateTime

 

[OS] 

-

 

[Development Language]

PowerShell

 

[IDE]

Microsoft Azure Logicapp/Automation Account

 

[Setting]

- Microsoft Azure subscription for Logic app and Automation Accounts

- On-premise hybrid worker

 

[Code]

 

<PowerShell>

#### variable
$date = Get-Date -format 'yyyy-MM-dd'

#### Credential
$credential = Get-AutomationPSCredential -Name 'AzUserPrincipalName'

#### Connection
Connect-AzureAD -Credential $credential | Out-Null # Connects to Azure AD

#### Get AzureADUser full list
$AdReport = Get-AzureAdUser -all $true | select ObjectID, UserPrincipalName, DisplayName, GivenName, UserType, JobTitle, Department, AccountEnabled, @{n="OfficeLocation"; e={$_.PhysicalDeliveryOfficeName}}, City, CompanyName, ExtensionProperty

#### Adding "createdDateTime" column
$AdReport | % { $_ | Add-Member -MemberType NoteProperty -Name "createdDateTime" -Value ($_.ExtensionProperty).createdDateTime }

#### Deleting "ObjectID" and "ExtensionProperty"
$AdReport = $AdReport | select userPrincipalName, DisplayName, givenName, userType, jobTitle, department, AccountEnabled, OfficeLocation, City, CompanyName, createdDateTime

#### Converting to CSV format
$AdReport | Export-Csv -Path "c:\temp\ADFullUserList_$($date).csv" -NoTypeInformation -Encoding UTF8


#### Sending AD report to "HRSystems@rotork.com"
$sendMailMessageSplat = @{
    From = 'Service Delivery Reporting <sd.reporting@rotork.com>'
    To = '<HRSystems@rotork.com>'
    Subject = "Weekly ActiveDirectory Full User List - $date"
    Body = 
    "Hello HRSystems, 
    
Hope you are well.

Please find Weekly ActiveDirectory Full User List - $date

For any inquery, please raise a ticket.
https://rotork.service-now.com/sp


Thanks
    "
    Attachments = "c:\temp\ADFullUserList_$($date).csv"
    DeliveryNotificationOption = 'OnSuccess', 'OnFailure'
    SmtpServer = 'mail.rotork.com'
}
Send-MailMessage @sendMailMessageSplat

#### Deleting CSV file
Remove-Item "c:\temp\ADFullUserList_$($date).csv"

 

- In case of fetching data from one source does not satisfy demanding data, either data column name or data itself should be modified thus it  requires additonal steps modifying data output

• Changing column name of arrary - Use pipe line then select columns with following format. 

... | select @{name="header"; expression="1"}

 

or simply

... | select @{n="header"; e="1"}

 

• Adding an additional column with "Add-Member" function. It is an utility function which cannot be used alone. Base data and pipeline is required or it spits out an error

$AdReport | % { $_ | Add-Member -MemberType NoteProperty -Name "createdDateTime" -Value ($_.ExtensionProperty).createdDateTime }

 

 

- Deliverying input for a function can be done with the following format

$argumentBox = @{
    filter = "userprincipalName -eq 'jaehui.yoon@rotork.com'"
}

Get-AdUser @argumentBox


DistinguishedName : CN=Yoon\, Jaehui,OU=Users,OU=KR-Yangchon,OU=Rotork,DC=Rotork,DC=co,DC=uk
Enabled           : True
GivenName         : Jaehui
Name              : Yoon, Jaehui
ObjectClass       : user
ObjectGUID        : f07a554e-f0f3-4227-bf45-4f800a1f10b3
SamAccountName    : Jaehui.Yoon
SID               : S-1-5-21-2113893433-799429050-625696398-93614
Surname           : Yoon
UserPrincipalName : Jaehui.Yoon@rotork.com

 

 

- When sending email via PowerShell with attachment, "SmtpServer" parameter is mandatory.

$sendMailMessageSplat = @{
    From = 'User01 <user01@fabrikam.com>'
    To = 'User02 <user02@fabrikam.com>', 'User03 <user03@fabrikam.com>'
    Subject = 'Sending the Attachment'
    Body = "Forgot to send the attachment. Sending now."
    Attachments = '.\data.csv'
    Priority = 'High'
    DeliveryNotificationOption = 'OnSuccess', 'OnFailure'
    SmtpServer = 'smtp.fabrikam.com'
}
Send-MailMessage @sendMailMessageSplat

 

 

- Use "Remove-Item" function when deleting a file from specific path

Remove-Item C:\Test\*.*

 

 

<Logic App>

 

- Set "Recurrence" as a trigger running the PowerShell script every Tuesday 10:00 am

 

- Run PowerShell script with hybrid worker which runs the PowerShell commands in the local server

 

- Error handling by getting the PowerShell script output, sending an email to me when it has an issue

 

 

[Test]

<Email>

 

<CSV>

 

 

 

[Lesson Learned]

- When fetching bulk data, response time is one of key considerations for developing a solution