Active Directory

Updating Active Directory from a CSV

Scenario:

You’ve been asked to populate everyone’s Active Directory job title. The payroll system is correct, and they’re able to export you a list of usernames and correct job titles. All you need to do is get that into AD.

Solution:

You could do this manually of course, but that’s no fun and a waste of time. This is one of those scenarios where you’ll hopefully think ‘PowerShell can do this!’ and possibly wonder how. That’s what I did anyway, so set out to make it work.

Here’s a fake example of the data I was working with, in a file called fake.csv:

EMPLOYEENAME,JOBTITLE
AFOWLER,IT OPERATIONS MANAGER
RSOLE,JANITOR

Tip: If you open a csv file in Excel it is a bit easier to read.

From this data, we want to match the EMPLOYEENAME to the correct AD account, then update the Job Title field from the JOBTITLE entry of the csv file.

A script that will do this is:

Import-module ActiveDirectory
$data = import-csv -path c:\fake.csv
foreach ($user in $data){
Get-ADUser -Filter “SamAccountName -eq ‘$($user.employeename)'” | Set-ADUser -Replace @{title = “$($user.JobTitle)”}
}

So, what’s happening here? It can take a bit to get your head around especially if you’re not used to programming (like me), so I’ll try to explain it:

Import-module ActiveDirectory
Importing the ActiveDirectory module so the Get-ADUser command works. If you can’t load the module, install RSAT (Remote Server Administration Tools) which includes the AD module.

$data = import-csv -path c:\fake.csv
This is setting the $data variable to memory, which will contain all the contents of the fake.csv file.

foreach ($user in $data){
This is saying ‘for each line of information from the $data variable (which is the csv file), map that to $user and do the following”

Get-ADUser -Filter “SamAccountName -eq ‘$($user.employeename)'” | Set-ADUser -Replace @{title = “$($user.Job Title)”}
This is getting any AD User where their SamAccountName matches the employeename column of the $user variable (which is the current line of information from the csv at time of processing). Then with the pipe | it will use the result to then Set the AD User’s title field (where the job title goes) to the Job Title part of our $user variable. This command will run twice, because there are two lines for ‘foreach’ to process.

}
This is closing off the command which each ‘foreach’ command runs.

 

I hope that explains it enough so you’re able to manipulate the script to your own requirements.

 

Getting AD User Data via PowerShell

It’s a common question asked of IT – “Can you give me a list of who’s in Marketing?” or “How many accounts do we actually have?”

Before PowerShell, this was a lot harder to do. There were companies like Quest Software who provided several handy tools (and still do) , or long complicated visual basic scripts.

So, how do you get a list of users? All of this is being done from the Active Directory Module for Windows PowerShell which will install as part of the Windows Server 2012 Feature – Role Administration Tools > AD DS and AD LDS Tools > Active Directory Module for Windows PowerShell.

The ‘Get-ADUser’ command is what we’ll use to demonstrate what you can do.

For starters, ‘Get-ADUser -filter*’ will get you a list of all users, and they’ll output in this format one after the other:

powershell1 (1)

A lot of information. You can specify a single user with:

Get-ADUser -identity username

which will just show you the one result.

As you may be aware, there are a lot more fields a user has than just the ones shown. You can tell PowerShell to show you all the properties by modifying the command like this:

Get-ADUser -identity username -properties *

Note that in PowerShell v4 if you get the error “get-aduser : One or more properties are invalid.” then there may be an issue with your schema. Check out this post for more information.

If there’s just one extra property you need, there’s no point getting everything, so if you needed to see a field such as “Department” for all users then adjust the command like this:

Get-ADUser -filter * -properties Department

Now, this gives the results for every single user in your Active Directory environment. You can narrow this down to a particular OU (and consequent sub OUs) by changing the command to this:

Get-ADUser -searchbase “ou=specialusers,ou=users,dc=mydomain,dc=com” -filter * -Properties Department

Now, you might be wondering how to get rid of all the standard properties and only see the ones you want.

There are two ways to pipe out the data that you want. One is with the ‘Format Table’ and the other is ‘Select Object’. Say you want a list of staff and their departments, we only need to use the ‘name’ field and the ‘department’ field.

Here’s what the two command look like, which are very similar:

Get-ADUser -searchbase “ou=specialusers,ou=users,dc=mydomain,dc=com” -filter * -Properties Department | ft name, department

Get-ADUser -searchbase “ou=specialusers,ou=users,dc=mydomain,dc=com” -filter * -Properties Department | Select-Object name, department

powershell2

The results of these commands will look exactly the same. But, when you want to export this information out, you would normally use the ‘Export-CSV’ command. If you use the ‘ft’ option, the results will not be what you expect. There is a brief writeup on this on the Windows PowerShell Blog which shows what you’ll see and explains why. The ‘Select Object’ command doesn’t have this issue.

So, if you want to output this list to a text file, here’s the command to use:

Get-ADUser -searchbase “ou=specialusers,ou=users,dc=mydomain,dc=com” -filter * -Properties Department | Select-Object name, department | export-csv c:\temp\myfile.csv

Note that you can also cheat and just pipe any output to a textfile using the old DOS redirect output method, which works even with the ‘ft’ option:

Get-ADUser -searchbase “ou=specialusers,ou=users,dc=mydomain,dc=com” -filter * -Properties Department | Select-Object name, department > c:\temp\myfile.csv

Note that one ‘>’ creates a new file or overwrites an existing, while a double ‘>>’ will create a new file or append to an existing.

Easy! Now you can provide Active Directory details to whomever asks, with a one line command that will output only the fields you want.

Full Mailbox Access to All Mailboxes in Exchange 2010

I’ll start this out by saying ‘Full Mailbox Access to All Mailboxes’ is generally a bad idea. It should be done on demand with the appropriate approvals and paper trails, but there are times when this may be needed – for example a service account for 3rd party software that has to read or add things to everyone’s mailbox in the company.

In my last post “End User Management of Distribution Groups in Exchange 2010” I explained how the new Role Based Access Control (RBAC) worked. Although this can be used to configure many things, it won’t give you full access to a mailbox as it’s an Active Directory based permission.

You can manually do this on a per mailbox level by either using the Exchange Management Console, or the Exchange Management Shell by following the Microsoft Technet documentation here and it’s fairly easy to convert this to all mailboxes in powershell, but that won’t help you with newly created mailboxes after running the command.

Yes you could run a daily task to get around that, but an alternative is giving AD access at the database level. Any existing or newly created mailbox will get permissions this way.

So, with that all in mind, the Exchange Powershell command to run on a particular database is:

Get-MailboxDatabase -identity “[mailbox database name]” | Add-ADPermission -user [username] -AccessRights GenericAll

If you don’t know what your databases are, just run ‘Get-MailboxDatabase’ or if you want to just apply the permissions to all databases:

Get-MailboxDatabase | Add-ADPermission -user [username] -AccessRights GenericAll

You can apply this to a AD group rather than a user which I’d suggest (no changes to the command required apart from typing the group name rather than user name), because it’s then easier to manage the members of the AD group than re-run this command. Also if you apply the settings to a particular user, and that user launches Outlook, all mailboxes they have full access to will auto-load into their Outlook session. Not ideal if you’ve got hundreds!

If you’d like to know more about the Add-AdPermission command, and the possible AccessRights settings check out this Technet article.

I did something stupid – deleted myself!

This is my first blog post, so be gentle on me :) Yes it’s a long one… hopefully someone does read this to the end.

So the title of this is a bit of a giveaway. I admit it, I did something I really should have double checked before doing. Sit down in front of the glow of your computer screen, and read a tale of sorrow, pain, and frustration… all caused by a small oversight.

It was a sunny morning, or so the desktop widget told me (for I have no windows in my office.. no not Windows, I do have that). As my Windows 7 testing progressed, I decided it was time to clean up AD a little. I moved all my shiny new GPO’s into the root of the domain, and set them to apply to Windows 7 computers only. Rather safe, nobody would get any wacky new settings. So it was also time to clean up that Windows 7 OU I’d created to do some testing. I had a look, and there were 2 users listed. My boss, and a test account. I moved both accounts into a general ‘IT’ OU, and being the good Sys Admin that I am, also decided to delete the OU.

Now, this is where it all goes wrong. I get a very informative popup saying the OU is protected from accidental deletion. That was good of me to set that (or does it just do it by default? I’ll take credit for now). So I go in and remove that pesky tickbox, stopping me from taking out the trash. I then try to delete the OU a second time – this time, another informative popup. I’ll paraphrase here: “Stuff in this OU will be friggen DELETED so you better want this gone, punk”. I think for a second, and yes I removed the accounts visible, so I click the “No worries” button and it’s a job well done.

That is, until I start to realise something’s wrong. My Communicator dropped offline. My Outlook is offline. I can’t click on other OU’s in AD. That feeling when you lean too far back on your chair had hit me. What’s wrong with my account? A quick search of AD by RDPing to a DC and searching for my username confirms the worst. GONE!

Tip 1. Always know of another domain admin account in case yours gets screwed somehow. Maybe just expired, disabled, whatever.

At this stage, I can’t work out how I missed my own account. The OU was empty??

Tip 2. Always refresh your view in AD Users & Computers before you delete an OU

After thinking about this for a minute, I remember that AD accounts are just tombstoned. So why don’t I just recover it? Google FU! Good news, Microsoft’s KB about what to do in this exact situation is the first hit, so I eagerly open the linkhttp://support.microsoft.com/kb/840001 and my eagerness turns to dread.

Seriously, all that just to recover an AD account? A few minutes and a few commands, and I decide there must be a better way than this giant process. I could go to backup tape, but no lets stick with trying to get it back without that first. Thanks for the detail Microsoft :(

Google FU! Attempt two gets a much better result: http://www.petri.co.il/recovering-deleted-items-active-directory.htm – now this is much better, giving me a nice little program called ADRestore.net with simple instructions on restoration. I somehow manage to restore the OU and my AD account… somewhat.

Tip 3. You can’t restore an account from a deleted OU which has been Tombstoned, unless you restore the OU first.

So, there’s my old account back in AD. Sweet…. except hang on, all the details are missing, almost every field is blank and my pants are feeling a little damp. What’s going on?

According to the article, I ‘should’ have had all the settings back. At this stage, I just deal with it and start setting myself up again – no biggie. A few groups here, a few phone numbers there and tada! It looks like it’s old self. Oh, don’t forget to put email back on it!

At this stage, my brain had not just fizzled out by stupidity, but had turned off the lights, gone out, left the door open for a stray cat to wander in and went to watch a 3D movie. For SOME reason, I had the memory that in Exchange 2007, if you remove someone’s email account, just recreate a new one and it’ll link up the old and new.

Tip 4. Don’t just recreate a new email account if you want to link a user back up to their old email account on Exchange 2007.

I don’t realise this yet, so I launch Outlook. Strange, it is saying “Offline”. OK fine, I’ll just export everything, recreate my profile and then import it. Minutes pass of many progression bars for exporting and importing. But I get to the end, and think surely that’s the end of it?

I also ‘should’ have gone to the deleted mailboxes, and just rejoined my mailbox to my AD account. That would have saved so much time. But then I wouldn’t have spent the next few hours working pulling my hair out, which is actually a bad thing, so I’m not sure why I’m pointing this out.

All is quiet for a while, and everything works. Then, I get a call. “How come your email is bouncing, I wondered if you were fired?” Hmm maybe I should have been for the above stupidity, but no that wasn’t the case. A few test emails later and I couldn’t work out what was wrong. I got the person to delete the cached entry for me in Outlook, and great it worked. A few other people called, and I sorted them out too.

Case closed.

Tip 5: Cases normally aren’t closed when you expect them to be.

That is, until the next day. Another call from the same person as the day before “I’m still getting bouncebacks, what’s going on? Where am I? Are you my daddy?” I might be remembering the conversation differently, but it’s probably not too far off the truth. For some reason, the same people were getting bouncebacks again? What THE!? Other testing shows it’s OK. The bouncebacks they were getting was that my email address didn’t exist. But it did, and I quadruple checked that it was all set up correct.

It then takes me a few hours of research to eventually discover a few people saying ‘Make sure your account has an entry for “legacyExchangeDN” by using ADSI edit. I look, and yeah I can see my details there so it can’t be that. A few circles are followed, often taking me back to that field. I check again, and this time I scroll all the way to the end. And then I see it, that moment of grasping the situation by the dangly bits, and realising THAT THERE IS THE PROBLEM MISTER! It has my full email address, with a 1 on the end. A little 1. Nowhere could I see this 1, because it was never referenced by anything. The bouncebacks, my email address in Exchange console and so on, were all correct. But this field wasn’t. I deleted that naughty numeral, and got those confused users to try emailing me again…

Success!

Tip 6: Check fields even if you think they are correct, they could be 1 character wrong (or one character extra on the end!)

So, that’s the end of my tale. Sometimes it’s good to go through these things to remind you why you normally double check things before taking action. And I’m really glad it was my own account and nobody else’s.

I hope you enjoyed reading this, and pointed and laughed at me because I deserve it.