Script a data-formatting solution
You've received a directive to produce a report that shows which user accounts are active and other account details. Management is concerned that accounts might exist for users who are no longer with the company—accounts that were never properly disabled or deleted. Also, some user accounts are set so that the password never expires, and new security policies require regular password changes.
My GetUserDetails.bat script, which Listing 1 shows, takes another utility's detailed user account output, such as the account-disabled status and the password-not-required status, and reformats it into a spreadsheet. The report displays 19 columns of information for each user, and you can easily sort the spreadsheet output to view user information according to your preferences.
Massaging the Output
GetUserDetails.bat uses the freeware utility GetUserInfo.exe from Joeware (http://www.joeware.net/win32/index.html). This tool gives you output similar to that of the Net User command but lets you specify from which machine to collect the information. (Joe Richards of Joeware offers several useful utilities that I've used in past scripts. I recommend that you look at all the tools he has available.)
Often, after you find a utility that retrieves the desired information, you discover that the output format doesn't meet your requirements. Utility authors are often responsive to requests for additional features in their utilities. If you can't get the proper output from a utility, I recommend that you contact the author about your needs. See the sidebar "Contacting Third-Party Utility Authors" for more information.
GetUserInfo works great for getting information about one user. Figure 1 shows sample output from GetUserInfo. However, if you run GetUserInfo against 100 user accounts and redirect the output to a text file, the results are difficult to read and impossible to sort without manual manipulation. After looking at the GetUserInfo output, I determined that I wanted all the information except the global and local group information, which I already had available in a different report. I also wanted to put the information into a spreadsheet format with one user account per row and all its details arranged in columns. Several options exist for accomplishing these tasks; let's look at two of them.
One approach is to run the data-acquisition command multiple times, getting one piece of information each time and filtering out everything else with the Find command. For example, to capture two pieces of information, such as the User Profile and the Account Expires date, I'd run the following commands:
|find "User Profile"
|find "Account Expires"
The downsides of this approach are that it gathers a lot of data that you discard with the filtering and that your filter strings might turn up in unexpected spots, leading to inaccurate results. If, for example, you search for the string User Profile and a home directory path also contains this string, you could get some unusual output, so be careful when determining your filter strings.
Another approach, which I use in GetUserDetails.bat, is a counter/label system. The script uses this technique to assign each of the 19 data items I want to collect for each user to the appropriate variable. This approach lets me run the GetUserInfo command once for each user account. Although I created GetUserDetails.bat specifically for gathering user details, you could use the counter technique it demonstrates with other utilities.
When I face a new filtering challenge, I first look at the command output to determine which information I want to discard. In the case of the GetUserInfo output, I don't need the first line of information (User information for Sales\DLewis (\\dc-01) in Figure 1. I can use the For command with the skip=1 switch to remove the line, as the code at callout B in Listing 1 shows.
Next, I want to filter out the global and local group memberships. I can do this by using the Find command that the code at callout B shows.
Finally, I want to remove the blank lines in the result. GetUserDetails.bat uses a For command for each data item it collects, as I explain later. The For command automatically removes blank lines, as you can see by creating the testIPconfig.bat file that Web Listing 1 (http://www.winnetmag.com/windowsscripting, InstantDoc ID 42369) shows. When you run this code, you'll see that it removes blank lines from the Ipconfig /all command's output.
Displaying the Desired Data
Now that I've discarded the output I don't want, I need to perform the trickier task of capturing the 19 output items that I do want and setting variables with the desired string. The first output line that I want,
User Name Dlewis
contains three strings. The User and Name strings are trivial because the spreadsheet we're creating will have column headings. The important string is Dlewis in token 3. Another output item that we want is the password age in days. The important string in this output item is in token 5. A quick glance at the other lines in Figure 1 reveals multiple token scenarios, each requiring a different filtering scheme.
Consequently, we need a For command for each of the 19 lines of output to elicit the proper token of information. We also need a means to route the script flow to the correct For command and another For command to display each line of GetUserInfo output. When we put these pieces together, the following pseudocode for GetUserDetails emerges:
- Echo column headings to a tab- separated value (.tsv) file.
- Use a For command to parse the input file that contains the usernames.
- Run the GetUserInfo command once for each user account isolated in Step 2.
- Set a counter equal to 0 for each new user and use the command
Set /Ato increment the counter by 1 before routing each of the 19 user detail lines to the second For command. For example, when you retrieve the first line of detail (User Name), the counter will equal 1; for the second line of detail (Full Name), the counter will equal 2, and so on.
- Use a counter-based label to route each line of the command output to the second For command. For example, the code
GOTO :lablno%Counter%would send flow to the :lablno1 label on the first iteration, to :lablno2 on the next iteration, and so on to :lablno19. (For space reasons, Listing 1 contains only the first three iterations. The downloadable script contains the complete code.) Each label contains a For command that's customized to retrieve the correct token and set a unique variable. Echo all 19 collected variables representing the user details to the .tsv file.
- Repeat Step 4 and Step 5 for each user.
Examine the code that callout C in Listing 1 shows to see how this dynamic code sends the flow to the proper static label names. This technique might come in handy in other scripts, so experiment with it a bit and add it to your scripting tool belt.
Running the Script
To get the script to run in your environment, perform the following steps:
- Download the GetUserInfo utility from the Joeware Web site.
- Download GetUserDetails.bat from the Windows Scripting Solutions Web site.
- Create an input file that contains the domain and name of the users for whom you want to collect information. Put one domain\userID on each line in the file, such as Domain1\fred
- At callout A, modify the code to configure the path to the input file, the output file path, and GetUserInfo.
GetUserDetails.bat gives you a simple way to capture all your user details in a .tsv spreadsheet report that you can easily sort. Also, if you ever need to change another command's output from a vertical format to a horizontal format, you'll know the techniques to accomplish that task.