Friday, 16 March 2018

WMIC /format - Invalid XSL format (or) file name.

You may see an error when using WMIC /format switch, as in the following example where I wanted to write a list of the installed patches on my computer to a CSV file:
wmic /output:"d:\patches.csv" qfe list full /format:csv

But I got this error: Invalid XSL format (or) file name.

Copy the XSL files as follows:


Once you've done that, when you run the WMIC command with the /format switch, it will work. I have successfully tested this on a Windows 7 computer.


Friday, 16 February 2018

List the members of an Active Directory group

From a Windows computer would you like to see a list of the members of a particular Active Directory (network) group? In this article we'll explore how to do this and how to extract and rearrange the usernames into a conventional vertical list.

List the members of a group
Open a command window - press Win-R, type CMD and press Enter
Enter the command: NET GROUP "your group name" /domain

You should replace "your group name" with the name of the group you wish to see the membership of. Remember that the double quotes " " are important, often Active Directory group names have spaces in them, using the quotes is essential for the group name to be recognised. 

Here's an example:

The result is OK. The users in the group are listed under the dotted line. The only thing that's not good is that they are listed in three columns. This makes it difficult to easily extract these usernames for other uses. For example, you may wish to send an e-mail to everyone in this group or display this list in a report. This problem is especially acute when there are many hundreds of group members.

Create a vertical list of usernames
The aim is to create a list as follows:
This is much easier to work with in Excel or Outlook.

I've written a small script using AutoIt. Before you go further, ensure you have AutoIt installed on your computer. It's an excellent scripting language because after you create your script you can compile it to an EXE and use it standalone, without having to install anything on the computer where you use it. More information on AutoIt can be found here:

The script I've written is called Membership. It runs the above NET GROUP command, the output is written to a text file. That file is read by the script, the names are extracted and written to a new text file in order vertically. 

Source Code

#cs ----------------------------------------------------------------------------
 AutoIt Version:
 Author:        Michael Gerrard,, February 2018

 Script Function:
 List users who are the members of an AD group to a file.

#ce ----------------------------------------------------------------------------

$group      = "your group name"  ;change to a valid AD group name!
$title      = "Membership"
$inputFile  = @ScriptDir & "\membership.tmp"
$outputFile = @ScriptDir & "\membership.txt"
$n          = 8 ;line counter (starts at 8)
$u          = 1 ;user counter

; Run the NET GROUP command to list the users
RunWait(@ComSpec & ' /c NET GROUP "' & $group & '" /domain > ' & $inputFile, @ScriptDir, @SW_MINIMIZE)

; Open the input file and read the lines
$file  = FileOpen($inputFile, 0) ;open read only
$aFile  = FileReadToArray($file)
If @error Then  ;An error occurred reading the current script file
    MsgBox(16, $title, "There was an error reading the file.")

; Step through the lines and output the usernames to the output file
$file = FileOpen($outputFile, 2) ;create a new file
While $aFile[$n] <> "The command completed successfully." ;loop through the lines

 $aSplit = StringSplit($aFile[$n], " ") ;split the line into users
 For $u = 1 To $aSplit[0] ;step through the possible users

  $ws = StringIsSpace($aSplit[$u]) ;some users are white spaces
  If $ws = 0 Then ;if not white space
   FileWriteLine($file, $aSplit[$u]) ;write the user to the output file
 Next ;loop again to find another user

 $u = 1  ;reset to 1
 $n = $n + 1 ;increment the loop

FileDelete($inputFile) ;delete the temporary input file
Run("notepad " & $outputFile, @ScriptDir)

Copy and paste the above code into a text file.

Save the text file as Membership.au3

The first line in the code is where a variable is defined for the group name:
$group = "your group name"
Change the text in double quotes to the name of the your group.

Run the script
As long as you have AutoIt installed you can either run the Membership.au3 file or compile it to an EXE for standalone use.

Remember to change the variable $group to the group name of your choice before running or compiling the script.

NOTE: For the script to work it should be in a folder with a path structure without spaces. For example, if it's in D:\VeryImportant\Files that will work fine. If it's in D:\Very Important\Files then it will not work.

When you run the script it will open Notepad with the usernames in a vertical list like this:

From Notepad you can copy/paste the list to Excel, Word or where ever. Also, it is possible from Excel PowerView to query this text file, this can be useful if you wish to match it to other data you may have in a corporate database.

It's a real shame that the NET GROUP command doesn't have the ability to list the names vertically to start with. However, using AutoIt it's possible to get around this and make a simple but effective solution. I hope this is of help to you.

Friday, 26 January 2018

Finddocx - a script to find and backup documents from the C:, where ever they may be hiding!

Are you working in IT support, have you ever had to backup user documents from the C: drive before replacing or re-imaging their computer? I've had to do this and I know what a nightmare it can be. Copying the Documents folder is not enough, there are always more user documents elsewhere, potentially anywhere on the computer! Sometimes the user doesn't even know where their treasured files are.

In this article we'll look at a solution, to use Robocopy to scan the C: drive for document files and back them up (for example; copy to a USB flash drive).

This is a free command line program included with Windows as standard. In my example here I'll be using Windows 7 but this will also work for Windows 10 as well.

If you'd like to learn more about the basic functionality of Robocopy please see my previous article on the subject:

Finddocx is just a name I've given my script. It consists of two files, a command (batch) file and a Robocopy job file.

Finddocx.cmd (command file)
The command (batch) file, double click to execute. This contains the source and destination locations. Here's what it looks like inside:

ROBOCOPY C:\ %username% /JOB:finddocx 

The first line is as follows:
ROBOCOPY <source> <destination> /JOB:<jobfile>

In my example I will search the entire C: drive (C:\). The backup files will be stored in a folder named after my own Windows username, the %username% is a variable. The /JOB:finddocx will use the finddocx.rcj for parameters.

The pause command at the end is just to stop the command window from closing when the script ends.

Finddocx.rcj (job file)
The job file. It contains various parameters including which file types to backup. The following is inside the file:

:: Finddocx example job file
:: January 2018

:: Use two colons :: to disable a command from running. 
:: This job file is a modified version of one generated by Robocopy.
:: See Robocopy /? for more details. 

:: Include These Files :
 /IF  :: Include Files matching these names
::  *.xls?
::  *.ppt?
::  *.pdf
::  *.txt
::  *.png
::  *.jpg
:: Exclude These Directories :
 /XD  :: eXclude Directories matching these names
:: Copy options :
 /S  :: copy Subdirectories, but not empty ones.
 /COPY:DAT :: what to COPY for files (default is /COPY:DAT).
 /PURGE  :: delete dest files/dirs that no longer exist in source.
:: Retry Options :
 /R:0  :: number of Retries on failed copies: default 1 million.
 /W:30  :: Wait time between retries: default is 30 seconds.
:: Logging Options :
 /LOG+:log.txt :: output status to LOG file (append to existing log).
 /TEE  :: output to console window, as well as the log file.

In the above job file, where you see two colons :: it means that the text on that line is ignored. In other words, :: means a comment.

You'll notice that there's an /IF command at line 12. Below it there is a list of file types. In my example above only *.doc? is enabled. However, if you wish, remove the :: next to the others to backup more file types. You could also extend this list and add even more file types.

Add folders here that you do not want it to search. I've put AppData here because it was just giving 'access denied' errors so I prefer to exclude it. You can add more folders here as needed.

Setup Finddocx
The following is an example only:

  1. Insert a USB flash drive
  2. Create a folder on it called "finddocx"
  3. Copy the source code for finddocx.cmd (above)
  4. Open Notepad and Paste
  5. Save the file to the USB drive into the finddocx folder as finddocx.cmd
  6. Copy the source code for finddocx.rcj (above)
  7. Open Notepad (a new window) and Paste
  8. Save the file to the USB drive into the finddocx folder as finddocx.rcj
  9. Edit the finddocx.cmd and rcj files as needed (you may wish to add more file types to search for, you may wish to change the destination folder, etc). 

Run Finddocx
Here's what happens if I double click my finddocx.cmd, a command window will open you'll see thousands of files zoom by:

Don't worry, don't touch, just let it work. Once it has finished press a key to close it.

Look in the folder on your flash drive:

A log.txt file has been created, open in Notepad to see what happened, what was backed up, if there were errors, etc.

You'll see a folder with your username, in my example "Michael". This is where the backup is. Double click Michael (or whatever your folder is called) and you'll see something like this:

There will be sub-folders. These represent the folders from your C: drive. If you explorer these folders you'll find the various doc files that were backed up. The folder structure where they were found on your C: is maintained and replicated here.

  • The second time you run Finddocx with the same user it will delete the existing files on your backup destination (USB drive) and replace them with new copies from the C: drive.
  • Using the %username% variable means you can take your USB flash drive from computer to computer backing up data. The backed up files will be stored in separate folders on your USB drive.
  • Instead of using %username% you could edit the finddocx.cmd and change it to backup to %computername%.
  • If you want to create your own Robocopy job file from scratch you can do. You must use the /SAVE:<job> parameter. Please see the Robocopy documentation or Robocopy /? for details.

Use at your own risk! The script I've included here is just an example to get you started. I am not guaranteeing anything. I am not responsible if you mess something up! Take care, especially with user data.

This script is just a start, using it and the power of Robocopy you can easily backup user files. Of course it can't replace a proper backup solution but as explained in the introduction, it could be a simple way to make a copy of important user files prior to replacing a computer or re-imaging it. I hope you found this article useful.