Just the other day I read a blog post by Jeffery Hicks ( twitter | blog ) he wrote back in April, about using a free tool made by Quest Software that makes working with Active Directory so easy. It is called “Active Roles Management Shell for Active Directory” and is FREE to download. I decided to try it on my test server at work and the next day I was able to solve a problem another project was having trouble doing.
Problem at hand
I was talking with another DBA on our team about on of his projects. Seems they had some sort of licensing issue and they needed to get the users from several of the groups on the domain and output the data to a file periodically.
Well, I told him about this new Powershell Plug-in by Quest. I thought the tool was so cool I bet it would do what he needed. He said one of the SAs was working on a solution and to talk with him to see if he needed any help. The SA in question happen to reside in the cubical next to me at the office. Was this fate or something? Everything was just falling into place.
The next morning I talked with him (my SA neighbor) for a few minutes to see what he was trying to do and where he was in the process. Seems the driving factor was, as the DBA had mentioned, related to vendor licensing per user on the system. But he was having trouble getting what was needed. He went on to say had been working with Microsoft to get some help but really was not getting what he needed, help or the data.
All he needed was the domain/username and name of the user, like this.
PRW\prwsmithjj, Smith, John J.
I told him about what I was doing the day before with the tool from Quest Software and if he wanted I would see what I could figure out. All I needed from him was the names of the groups and we could go from there.
Turns out I had exactly what he needed within 10-15 minutes and most of that was logging onto my servers. He was so happy, I heard him drop my name on the conference call later that morning say, “All the credit goes to AJ. He really knocked it out of the park”. True, I wrote the code needed, but I am not taking all the credit. Most goes to Quest Software for creating and distributing such cool (and FREE) software and some goes to Jeff for writing his post.
So the following is a short guide to what I did and the coded need to resolve the issue.
Install the Management Shell
You can download the management shell from here. There is a License Agreement that must be accepted before downloading. You have the option of downloading a .zip file or the .msi binary file. I highly suggest downloading the Admin Guide as well for future reference. Once you download the tool, installation is straight forward.
I think it is important to note the Management shell uses the permissions of the user executing the code. Read more about the permissions on page 26 of the Admin Guide.
Getting User Information
Before we can use any of the commandlets we need to load the reference to the snap-in by running this.
Next get information about a user (this code is so simple you are going to love this).
That will return some basic (default) information on the current user running the query (you). Mainly you name and the CN for you. Something like this:
CN=Smith\, John J., OU=PRW Solutions, OU=Contractor,…
You can also query by any of the fields in AD like city, company, or lastname.
Get-QADuser lastname = ‘Sm*’
Just like any query using a wildcard search, less specific in your filter is, the wider your dataset, the more specific your filter the smaller the return.
It is important to note here there are many fields in Active Directory and as you might expect every organization is different. So to get all the columns it is a little more complicated. You will use a “SELECT” statement to query by adding the pipe and “SELECT” statement at the end of the line like so.
Get-QADUser Smith | SELECT *
OK, so it is not really complicated, I was trying to add some drama. As you know this statement will return all the columns. Probably not something you want to do all the time. So, now that we reviewed all the columns returned we can narrow our SELECT statement to our desired output.
Get-QADUser Smith | SELECT firstname, lastname, city
Now that we have an idea of what we are doing let’s move on to the issue at hand.
Getting group information is just as simple as user information. If you don’t actually know the full spelling of the group name you can use the wide card character to search just like we did for username above.
Getting Group Members
This returns all the users in the group. And as before we are going to limit the columns returned by using the following line.
Get-QADgroupMember ‘ PRW ‘ | select NTAccountName, name
This returns exactly what we need. The only additional thing we need to do is save this to a .csv file for tracking and archiving. That is done by adding a small bit of code and piping the output to a file.
Get-QADgroupMember ‘ PRW ‘ | select NTAccountName, name | Export-CSV C:\domainUsersdata\domainuserdata.csv
That all there is too it. Hopefully this will help someone else along the way.