Choose what fields to add to CSV

[sharpharp94 Participant Topics: 2 Replies: 0 Points: 23Rank: Member]

Hi,

Noob to powershell, been trying for days with no success.

Can anyone help with this snippet, i am trying to output the results to a CSV

 

# Load the users
$MailUsers = Get-ADUser -SearchBase “OU=etc etc” -Filter “(SAMAccountName -like ‘A12*’) -AND `
(PasswordNeverExpires -eq ‘$false’ -AND Enabled -eq ‘$true’)” -Properties PasswordLastSet, DisplayName, PasswordNeverExpires, mail, SAMAccountName

# Loop through them
foreach ($MailUser in $MailUsers) {

#Write-Output “$($MailUser.SAMAccountName, ” “, $MailUser.GivenName)”

# Count how many days are left before the password expires and round that number
$PasswordExpiresInDays = [System.Math]::Round((New-TimeSpan -Start $CurrentPWChangeDateLimit -End ($MailUser.PasswordLastSet)).TotalDays)

# Write some status…
# Write-Output “$($MailUser.SAMAccountName, ” “, $MailUser.DisplayName) needs to change password in $PasswordExpiresInDays days.”

# Build the body depending on where in the organisation the user is

if (($PasswordExpiresInDays -eq $LastPasswordWarningDays) -or ($PasswordExpiresInDays -eq $FirstPasswordWarningDays) -or ($PasswordExpiresInDays -eq $SecondPasswordWarningDays) -or ($PasswordExpiresInDays -eq $ThirdPasswordWarningDays)) {
# Write-Output “$($MailUser.SAMAccountName, ” “, $MailUser.DisplayName) needs to change password in $PasswordExpiresInDays days.”
}

$MailUser | export-csv $outputFile -append -NoTypeInformation
}

My Output csv has all the properties in it, I only want the SAMAccountName, DisplayName, Email Address and I also want to Include that Variable $PasswordExpiresInDays in the CSV… The idea is to run this script via SSIS and output to CSV, then use the CSV to send out the emails via another task.

Anyone able to help?

[Mike R. Participant Topics: 5 Replies: 253 Points: 1,007Rank: Community Hero]

Pipe your objects to Select-Object and pick the properties you want.  You can also create new custom properties if needed.

PowerShell

$MailUser | Select-Object -Property SAMAccountName, DisplayName, EmailAddress, @{n="Password-Expires"; e={[System.Math]::Round((New-TimeSpan -Start $CurrentPWChangeDateLimit -End ($MailUser.PasswordLastSet)).TotalDays)}} | Export-Csv $outputFile -Append -NoTypeInformation

1 2 3 4

$MailUser |     Select-Object -Property SAMAccountName, DisplayName, EmailAddress,                             @{n="Password-Expires"; e={[System.Math]::Round((New-TimeSpan -Start $CurrentPWChangeDateLimit -End ($MailUser.PasswordLastSet)).TotalDays)}} |                 Export-Csv $outputFile -Append -NoTypeInformation

Recommend reviewing Select-Object

[Rob Simmers Participant Topics: 17 Replies: 1951 Points: 3,988Rank: Community Hero]

Couple of things:

• Splat – Use splatting rather than the line continuation accent (`) with long commands. It’s easy to read and provides the ability to update, add, remove parameters on the fly. Easy to read code and functionality.
• Date Math – Not sure what $CurrentPWChangeDateLimit is in your post, but the math should be from when they set the password to Now. This would indicate someone set the password X number of days ago
• Contains or In – When you are sending notifications on an interval, this script should be running every day to send notifications to users that meet that interval period. An array of intervals can be used to determine who would get notifications rather than checking if each interval is met with -eq

PowerShell

#splatting $params = @{ SearchBase = "OU=etc etc" Filter = "(SAMAccountName -like 'A12*') -AND (PasswordNeverExpires -eq '$false' -AND Enabled -eq '$true')" Properties = 'PasswordLastSet', 'DisplayName', 'PasswordNeverExpires', 'mail', 'SAMAccountName' } $MailUsers = Get-ADUser @params | Select-Object -Property SAMAccountName, DisplayName, EmailAddress, @{Name='PasswordExpiresInDays';Expression={[System.Math]::Round((New-TimeSpan -Start $_.PasswordLastSet -End (Get-Date)).TotalDays)}} $reminderInterval = 14,10,5,2 foreach ($MailUser in $MailUsers | Where-Object -FilterScript {$_.PasswordExpiresInDays -in $reminderInterval}) { #Send-MailMessage .... }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

#splatting $params = @{     SearchBase = "OU=etc etc"     Filter     = "(SAMAccountName -like 'A12*') -AND (PasswordNeverExpires -eq '$false' -AND Enabled -eq '$true')"     Properties = 'PasswordLastSet', 'DisplayName', 'PasswordNeverExpires', 'mail', 'SAMAccountName' }   $MailUsers = Get-ADUser @params |              Select-Object -Property SAMAccountName,                                      DisplayName,                                      EmailAddress,                                      @{Name='PasswordExpiresInDays';Expression={[System.Math]::Round((New-TimeSpan -Start $_.PasswordLastSet -End (Get-Date)).TotalDays)}}   $reminderInterval = 14,10,5,2   foreach ($MailUser in $MailUsers | Where-Object -FilterScript {$_.PasswordExpiresInDays -in $reminderInterval}) {     #Send-MailMessage .... }

[Author]

Posts