Tag: CSV

If you’re like me, you built a new AD for testing. And if you’re also like me, you imported a whole bunch of users into your AD. Some of those users likely had passwords that didn’t quite meet the domain criteria. If that happened, that means those users are disabled.

In past posts, I wrote about moving users into a different OU. Now, we’re going to change passwords for these users so we can enable them later.

For this, you’ll need a CSV in this format below. I took some liberties and retrieved a large amount of random passwords from manytools.org. There are many websites that can do this, I just like the format that manytools provided. I just pasted the passwords into the second column, the first column being the sAMAccountname of the user.

PowerShell code:

Import-Module Active Directory
$Resetpassword = Import-Csv "C:\path_to_username_password_file.csv"
 #Store CSV file into $Resetpassword variable

foreach ($User in $Resetpassword) {
    #For each name or account in the CSV file $Resetpassword, reset the password with the Set-ADAccountPassword string below
    $User.sAMAccountName
    $User.Password
        Set-ADAccountPassword -Identity $User.sAMAccountName -Reset -NewPassword (ConvertTo-SecureString $User.Password -AsPlainText -force)
}
 Write-Host " Passwords changed "
 $total = ($Resetpassword).count
 $total
 Write-Host "Accounts passwords have been reset..."

Showing the Results

Once we’ve set the passwords, we need a way of knowing when or if the passwords were last set for a user. Referring back to the Get-ADuser cmdlet, we can look at the -passwordlastset property.

Get-ADUser -filter * -properties passwordlastset | sort-object samaccountname | select-object samaccountname, passwordlastset, passwordneverexpires | Out-GridView

Need to find all the disabled users in your AD? it’s odd that the built in AD Tools do not have this option. PowerShell to the rescue!

All these commands are documented in the Microsoft Get-ADUser cmdlet. I’ve added some additional types of output with out-gridview and CSV.

Finds all the disabled users in AD.

Get-ADUser -Filter {Enabled -eq $false}

Finds all and outputs to a gridview for editing (but you need excel)

Get-ADUser -Filter {Enabled -eq $false} | Out-GridView

Finds all disabled users and outputs to a CSV file. This exports ALL the readily available attributes.

Get-ADUser -Filter {Enabled -eq $false} | export-csv "C:\users\Administrator\Desktop\disabled_ADusers.csv"

Finds all the disabled users by specific property and selects the objects and outputs them

Get-ADUser -Filter {Enabled -eq $false} -properties SamAccountName,mail | Select-Object SamAccountName,mail | Out-GridView

Finds all the disabled users, looks a properties that aren’t readily available like email, export to a CSV file

For some reason, you can’t search by mail address outright or have it display, you have to select it, then show it with that select-object command

Get-ADUser -Filter {Enabled -eq $False} -Properties SamAccountName,mail | Select-object SamAccountName,mail| export-csv "C:\Path_to_CSV.csv"

Or you can use variables to clean up the code:

$SamAcc = "SamAccountName"
$Desc = "Description"
$mail = "mail"
$title = "title"
$CSVpath = "C:\Path_to_CSV.csv"

Get-ADUser -Filter {Enabled -eq $False} -Properties $SamAcc,$mail | Select-object $SamAcc,$mail| export-csv $CSVpath

What are the situations you’ll need lots of Dummy AD data? When you want to run some awesomely crafted PowerShell AD scripts, that’s where.

I was in a situation a few months ago where I needed replicate a VERY large set of AD data in order to pull some queries with Get-Aduser and Set-Aduser. I toyed with the idea of just creating them manually, or restoring them from production (this is a bad idea, don’t restore from production for a testing environment!). So I found a script that would import a great deal of AD data, and I also found a place online that can create a buttload of fake AD data for me.

The Setup:

• A Running AD Domain controller
• Powershell ISE
• https://mockaroo.com CSV loaded with all sorts of dummy AD data

First, let’s hit mockaroo.com.

For this entry, I’m going to fill as many fields as possible that relate to a powershell script. For each field in powershell, we’ll create a matching one in Mockaroo, and we’ll name it consistently.

This is the set of data with parameters I’m using:

The fields I’m using in AD are (copied from above)

• City
• Company
• Description
• Department
• Email
• EmployeeID
• GivenName
• MobilePhone
• Office
• OfficePhone
• Password
• Path
• Postalcode
• State
• Streetaddress
• Surname
• Title

I also need to place these created accounts into a specific OU. I had to do some editing in Excel, and placed the OU ADSI path into that Path attribute above.

Here’s my example with column and headers:

Generate the data you need, or create the information you require and save it into a CSV file (ideally in a Windows format).

Now, the powershell code:

01	# Import active directory module for running AD cmdlets
02	Import-Module activedirectory
03	 
04	#Store the data from ADUsers.csv in the $ADUsers variable
05	$ADUsers = Import-csv "C:\Users\Administrator\Desktop\2AD_users.csv"
06	 
07	#Loop through each row containing user details in the CSV file 
08	foreach ($User in $ADUsers)
09	{
10	#Read user data from each field in each row and assign the data to a variable as below
11	$City           =$User.City
12	$Company        =$User.Company
13	$Description    =$User.Description
14	$Department     =$User.Department
15	$Email          =$User.Email
16	$Employeeid     =$User.Employeeid
17	$GivenName      =$User.GivenName
18	$MobilePhone    =$User.MobilePhone
19	$Office         =$User.Office
20	$OfficePhone    =$User.OfficePhone
21	$Password       =$User.Password
22	$Path           =$User.Path
23	$Postalcode     =$User.Postalcode
24	$State          =$User.State
25	$Streetaddress  =$User.Streetaddress
26	$SurName        =$User.SurName
27	$Title          =$User.Title
28	$Username       =$GivenName+"."+$Surname
29	 
30	    #Check to see if the user already exists in AD
31	if (Get-ADUser -F {SamAccountName -eq $Username})
32	    {    #If user does exist, give a warning
33	         Write-Warning "A user account with username $Username already exist in AD."
34	    }
35	    else
36	    {
37	        #User does not exist then proceed to create the new user account
38	        #Account will be created in the OU provided by the $Path variable read from the CSV file
39	        New-ADUser `
40	            -City $City `
41	            -Company $Company `
42	            -Description $Description `
43	            -Department $Department `
44	            -Email $Email `
45	            -EmployeeID $EmployeeID `
46	            -GivenName $GivenName `
47	            -MobilePhone $MobilePhone `
48	            -Office $Office `
49	            -OfficePhone $OfficePhone `
50	            -AccountPassword (convertto-securestring $Password -AsPlainText -Force) `
51	            -Path $Path `
52	            -Postalcode $Postalcode `
53	            -State $State `
54	            -Streetaddress $Streetaddress `
55	            -SurName $SurName `
56	            -Title $Title `
57	            -SamAccountName $Username `
58	            -UserPrincipalName "$Username@dtab.org" `
59	            -Name "$GivenName $SurName" `
60	            -DisplayName "$GivenName $SurName" `
61	            -Enabled $true
62	    }
63	}

I had to reference the New-ADUser powershell commandlet. A great deal of the switches came from this article. As you can see, I’ve also alphabetized the fields to make sorting a little easier.Notes about this script:

Code Syntax explanation – lines 11 – 28

01	$City           =$User.City
02	$Company        =$User.Company
03	$Description    =$User.Description
04	$Department     =$User.Department
05	$Email          =$User.Email
06	$Employeeid     =$User.Employeeid
07	$GivenName      =$User.GivenName
08	$MobilePhone    =$User.MobilePhone
09	$Office         =$User.Office
10	$OfficePhone    =$User.OfficePhone
11	$Password       =$User.Password
12	$Path           =$User.Path
13	$Postalcode     =$User.Postalcode
14	$State          =$User.State
15	$Streetaddress  =$User.Streetaddress
16	$SurName        =$User.SurName
17	$Title          =$User.Title
18	$Username       =$GivenName+"."+$Surname

This code stores the CSV columns (headers) information for each user into $variables. I’ve kept the variable names the same as the CSV headers tokeep things simple. The last line, $username stores the firstname (period) lastname. Everyone will likely have a different standard for usernames, I just like this format since I don’t have to re-write code.

Code Syntax explanation – lines 31-33

1	if (Get-ADUser -F {SamAccountName -eq $Username})
2	    {    #If user does exist, give a warning
3	         Write-Warning "A user account with username $Username already exist in AD."

Some error checking code. Checking for duplicate information in the imported CSV file.

The variables used in this script are considered ‘more than necessary’. The bare basic amount needed (I think) are username, password, first and last name. Anything you add or take away, requires some editing of the code, and the CSV file.

Notes about this Script:

• For some reason the New-ADUser command does not allow the -country variable, it seems to fail each time. This is not a deal breaker for me, but for you it might be something worth investigating.
• When importing passwords, use 10 characters at a minimum. Mockaroo only shows a password “between 6 to 12 chars”. By trial and error, I found manytools.org where I could generate the passwords necessary in the format I wanted, and copy/pasted them into my user CSV.
• Should you feel hesitant about running this script, add “-Whatif” on the end of the Add-ADUser cmdlet line