Exporting User List from Active Directory

Sometimes you just need a simple file with a list of users in it.

In my case, I've made various programs to streamline and automate the work of my department. We "feed" one of these programs user data from Active Directory and elsewhere so it can make and delete accounts when students transfer in or out of the district.

You may not have a custom system like that, but there are many other reasons to be able to export data from Active Directory into a spreadsheet or text listing. One example would be turning over a list of users to the payroll department, so they can tell you what accounts should have been closed but slipped through the cracks. (Side note: I actually recommend doing at least annually and preferably every three to six months.)

To make such a list, login to a Domain Controller for your Active Directory system as a Domain Admin, run the command line, and use a command like this:

csvde -f ad.txt -n -d "ou=students,ou=People,dc=controller,dc=example,dc=com" -r "(&(objectCategory=person)(objectClass=user))" -l "sAMAccountName,givenName,sn,description"

That was probably too long to fit on the page, so let's break it down.

• csvde:
  This will make a file in the current directory (a.k.a. folder.) That file is in the CSV format. To remember this command, think of it as as "CSV Data Export."
• -f ad.txt:
  This file will be named "ad.txt".
• -n:
  Any binary data is excluded.
• -d "ou=students,ou=People,dc=controller,dc=example,dc=com"
  It will limit itself to data in the Organizational Unit (OU) named "students", which is inside "People", and in the Active Directory system at controller.example.com.
• -r "(&(objectCategory=person)(objectClass=user))":
  It will limit the export to only user accounts. For example, if there are computers or groups in that OU, those will not be exported.
• -l "sAMAccountName,givenName,sn,description":
  Its columns will be the username, the first name, the last name, and the description. Note that the first name is labeled "givenName" and the last name is labeled "sn" as in "surname."

If you want to change the OU, just adjust the part after the -d to include your OU and DC structure. If you want to change the data in the export file, just change the part after the -l. To learn more details, check out Microsoft's article on the csvde command.

If you adjust this to suit your environment, you should be able to generate CSV files that list your users very quickly. At my job, we can export over 1,000 users in under a minute. The CSV file can be read by scripts we write or imported into a Google Sheet and shared with Payroll for a quick account audit.

Software License Enforcement

So you manage a few hundred or maybe a few thousand computers. What do you do when it's time to buy software? If you buy too many copies, you wasted money. Too few copies means you're vulnerable to an expensive lawsuit. How do you ensure that you're in compliance and not wasting money?

I solved this problem about a decade ago and sometimes forget that others still face this challenge. If you're one of them, this article was written for you.

If you find yourself in this situation, I highly recommend having a conversation with the folks over at Sassafras Software about their K2 (a.k.a. KeyServer) product. I've been a happy customer for years.

Their customer support is knowledgeable, thorough, and friendly. I've never been on hold for more than 2 minutes or so. In fact, it is kind of like calling a buddy for advice -- no customer number to remember or case number to track. You just get through to them and start talking about your situation.

The product itself is great. You can install it on Windows or MacOS quite easily. I even did an automated install to hundreds of Macs via FileWave without problems. (This should work on Munki, Casper, etc. as well.) This customized installer is already configured with my KeyServer's address, so it connects as soon as it is installed. The computer then shows up in a list of monitored computers. As end users run programs, those programs are added to a list of "discovered" software. You can also add purchases, products, computers, and policies manually.

Here is a recent real-world example from my job. We purchased 500 installations of Microsoft Office 2016 for Mac. I told KeyServer that we had a new product and it checked in with Sassafras about what constituted "Office 2016" and set up some criteria for me. For example, if the user runs OneNote 2016, that computer is considered to have a license to Office 2016. So it is entitled to run Word, Excel, PowerPoint, and Outlook as well. Another computer might run Word 2008, but K2 knows that is a different version and doesn't count it as Office 2016.

Then I told it that we had purchased Office 2016 and filled out a form telling KeyServer it was 500 units of single-computer installations, that it was an original license and not an upgrade license, what it cost, my organization's purchase order number, that it didn't expire (i.e. that it wasn't a subscription,) etc. This is great data for tracking the licenses during a future audit. I could pull up a list of every time we purchased this product, how many "seats" we bought, which purchase orders to pull out as proof for the lawyers, and so on. It also helps calculate costs for supporting different products in the future. These kinds of hard numbers can help you make calculations and drive discussions about maintaining products in the future.

Lastly, and perhaps most importantly, KeyServer will let you choose how to monitor the license usage. It can passively track installations, track frequency of usage, or even enforce licensing. In the previous example, I configured it to allow the first 500 Macs to run Office 2016 to be automatically registered as users of it. After that, the 501st Mac to try to run that particular version of Office would receive a message saying that they weren't licensed to use it. So if someone was "helping out" and installed it when they shouldn't, that would turn up rather quickly. We could then choose to buy additional licenses or have a conversation about who really needs the software. If it turns out that one of the 500 to grab the license automatically shouldn't have it, we could withdraw that license and assign it to someone else. Next year, when computers are replaced, I can move the licenses around. A few years later, when the next version of Office is released, I could run a report to see how many computers actually used the software and factor that into future buying decisions.

There are a lot of features and situations that I didn't cover in this example. My point was just to show how K2 can make many common situations much easier. Given what we pay on it compared to the manpower (and the salary) wasted on walking around doing manual audits, I think K2 is a huge time and money saver. It saves even more time and money if you use the utilization reports to find licenses to move around or drive budgeting of upgrades.

If you manage a few hundred Windows PCs or Macs, I highly recommend a look into what Sassafras Software could do to help you.

What Username Mounted That Drive?

A few months ago, I had to upgrade a system that wasn't documented. A key piece of the system was a Windows network drive that automatically connected when the workstations started up. In order to do the upgrade without accidentally breaking the system, I needed to know what user account was used to mount the drive.

A little bit of research turned up the following command, which I found really helpful. I am documenting it here just in case it helps anyone (including me) in the future.

wmic netuse where LocalName="Z:" get UserName /value

Note that "Z:" should be replaced with whatever drive letter you need to check.

Credit where its due: http://stackoverflow.com/questions/9037503/determine-domain-and-username-used-to-map-a-network-drive