Verify data before export

PowerShell Help
1

Hi,

I’m very new to PS scripting, I have the following script to pull some data from AD and write it to an existing CSV file. My question is what would be the best way for me to verify that the script has actually extracted some data before writing to the CSV? What I want to avoid is the script for whatever reason failing to write any data to the CSV. If the script doesn’t successfully pull any data for e.g. AD isn’t accessible I would like it to just exit or be able send an email and exit.

# Define CSV location
$Csvfile = "C:\Output\Users.csv"

# Import Active Directory module
Import-Module ActiveDirectory

# Set security group as searchbase, you can use one security group or multiple groups
$DNs = @(Get-ADGroupMember -Identity "SEC-Office")

# Create empty array
$AllADUsers = @()

# Loop through every DN
foreach ($DN in $DNs) {
    $Users = Get-ADUser -SearchBase $DN -Filter * -Properties * 

    # Add users to array
    $AllADUsers += $Users
}

# Create list
$AllADUsers | Sort-Object Name | Select-Object `
@{Label = "First name"; Expression = { $_.GivenName } },
@{Label = "Last name"; Expression = { $_.Surname } },
@{Label = "Display name"; Expression = { $_.DisplayName } },
@{Label = "User logon name"; Expression = { $_.SamAccountName } },
@{Label = "User principal name"; Expression = { $_.UserPrincipalName } },
@{Label = "Street"; Expression = { $_.StreetAddress } },
@{Label = "City"; Expression = { $_.City } },
@{Label = "State/province"; Expression = { $_.State } },
@{Label = "Zip/Postal Code"; Expression = { $_.PostalCode } },
@{Label = "Country/region"; Expression = { $_.Country } },
@{Label = "Job Title"; Expression = { $_.Title } },
@{Label = "Department"; Expression = { $_.Department } },
@{Label = "Company"; Expression = { $_.Company } },
@{Label = "Manager"; Expression = { % { (Get-AdUser $_.Manager -Properties DisplayName).DisplayName } } },
@{Label = "Description"; Expression = { $_.Description } },
@{Label = "Office"; Expression = { $_.Office } },
@{Label = "Telephone number"; Expression = { $_.telephoneNumber } },
@{Label = "E-mail"; Expression = { $_.Mail } },
@{Label = "Mobile"; Expression = { $_.mobile } },
@{Label = "Notes"; Expression = { $_.info } },
@{Label = "Account status"; Expression = { if (($_.Enabled -eq 'TRUE') ) { 'Enabled' } Else { 'Disabled' } } },
@{Label = "Last logon date"; Expression = { $_.lastlogondate } }|

# Export report to CSV file
Export-Csv -Append -Encoding UTF8 -Path $Csvfile

Any help is much appreciated.

Thanks

2

kevlav,
Welcome to the forum. :wave:t4:

Below you can see an example of how I would tackle this task. I hope it is at least a little easier to read.

#Requires -Modules ActiveDirectory
Import-Module ActiveDirectory

$Csvfile = 'C:\Output\Users.csv'
try {
    $GroupMemberList = @(Get-ADGroupMember -Identity 'SEC-Office' -ErrorAction Stop)
}
catch {
    $SendMailMessageParams = @{
        From       = 'Sender@Contoso.com'
        To         = 'Recipient@Contoso.com'
        SmtpServer = 'SMTP-Relay.Contoso.com'
        Subject    = 'AD query failed'
        Body       = 'Get-ADGroupMember -> Error'
        Port       = 25
    }
    Send-MailMessage @SendMailMessageParams 
    Exit 
}

$Properties = @(
    'DisplayName',
    'StreetAddress',
    'City',
    'State'
)

$Result = 
foreach ($GroupMember in $GroupMemberList) {
    $ADUser = Get-ADUser -Identity $GroupMember.sAMAccountName -Properties $Properties
    [PSCustomObject]@{
        'First name'          = $ADUser.GivenName
        'Last name'           = $ADUser.Surname
        'Display name'        = $ADUser.DisplayName
        'User logon name'     = $ADUser.SamAccountName
        'User principal name' = $ADUser.UserPrincipalName
        Street                = $ADUser.StreetAddress
        City                  = $ADUser.City
        'State/province'      = $ADUser.State
    }
}

$Result |
Export-Csv -Path $Csvfile -Encoding UTF8

Is your AD that unreliable? :wink:

You can use PowerShell error handling to catch errors … read more about here:

Some general tips:
Your query for individual AD users is actually wrong but because PowerShell is so forgiving it works anyway. :wink: When you query the AD you should only query properties you’re going to use later on. To use the parameter -Properties * is a bad idea because it puts more stress on your AD than necessary.
I’d recommend to use the original AD attribute names for your CSV file instead of the “translated” header names. It makes your code easier to write and to read and it makes it easier to read the CSV file and use the data right away for any needed action against your AD - no need to “re-translate” the header names. :wink:

3 Likes
3

You could also use an if statement after you query AD, like

if ($GroupMemberList.count -lt 1) {code for no results}