Capturing standard EXE output in PowerShell

I’m sure there’s a better way of doing this, but here’s how I captured the output of SQL’s bcp.exe in order to email it and the CSV that we were creating automatically on a schedule overnight.

I ended up piping the executable to a temp file, and then grabbing the contents of that same temp file to populate the email body. Here are the lines in question:

&$exe $arg1 $arg2 $arg3 $arg4 $arg5 $arg6 > c:tempout.txt

$bcpOutput = Get-Content c:tempout.txt | Out-String
Remove-Item c:tempout.txt

Here’s the script in its entirety:

# This needs to be set, otherwise Send-MailMessage doesn’t have a server to send through$PSEmailServer ="mailserver.local"
$PSEmailServer ="mailserver.local"

$emailRecipient = "Daniel <daniel@contoso.com>"

# Some date and filename related stuff. This part's not important
$d = Get-Date
$yesterday = $d.AddDays(-1)
$filepath = "C:ExportsSQL Export"
$filename = "SQL-DailyExport-{0}.{1}.{2}.csv" -f $yesterday.Day,$yesterday.Month,$yesterday.Year
$fullpath = $filepath + $filename

# Path to the executable
$exe = "C:Program FilesMicrosoft SQL Server90ToolsBinnbcp.exe"

# arguments, as required
$arg1 = "DBName..ViewName"
$arg2 = "out"
$arg3 = $fullpath
$arg4 = "-c"
$arg5 = "-t,"
$arg6 = "-T"

# Run the executable with the arguments, and pipe the STDOut output to a text file
&$exe $arg1 $arg2 $arg3 $arg4 $arg5 $arg6 > c:tempout.txt

# An intro line for the body of the email
$bcpOutput = "SQL daily export process information: `r`n"

# Get the contents of the temporary file that we created earlier
$bcpOutput += Get-Content c:tempout.txt | Out-String

# Remove the temporary file
Remove-Item c:tempout.txt

# Build up our subject line. This part's not important
$subjectText = "SQL export for {0}/{1}/{2}" -f $yesterday.Day,$yesterday.Month,$yesterday.Year

# Append some more information to the end of the body text. Again, not important
$bcpOutput += "`r`nThe output file is attached, and can also be found in sqlExports"

# Send the email with the file attached and the body text as we've built it up
Send-MailMessage -From "SQL Export <Process@sql.server>" -To $emailRecipient -Subject $subjectText -Body $bcpOutput -Attachments $fullpath

This results in an email that contains the output from bcp.exe as well as the actual SQL export file attached to the email.

Using bcp.exe from a PowerShell script

Recently had to convert a batch file that calls bcp.exe to a more involved PowerShell script. The script exports the contents of a view to CSV format.

I originally had some issues getting the command line arguments to work, but here’s how I got it working:

$fullpath = c:tempout.csv

$exe = "C:Program FilesMicrosoft SQL Server90ToolsBinnbcp.exe"
$arg1 = "DBName..ViewName"
$arg2 = "out"
$arg3 = $fullpath
$arg4 = "-c"
$arg5 = "-t,"
$arg6 = "-T"

&$exe $arg1 $arg2 $arg3 $arg4 $arg5 $arg6

Batch convert images to B&W with PowerShell and ImageMagick

I knocked together this PowerShell script today to batch-convert 600+ staff photo images to B&W.

2012-08-02 14-07-00_000101

You’ll require the following items installed and in your path before this script will work:

  1. ImageMagick. I used the normal version, not one of the alternate ones.
  2. jhead: exif jpeg header manipulation tool. This requires ImageMagick in order to work.

ImageMagick does the actual conversion to B&W. I played with converting to pure grayscale, but it didn’t look good. This method instead strips out all colour information by setting saturation to zero:

convert.exe {sourcefile} -modulate 100,0 {destinationfile}

An issue I came across then is that ImageMagick doesn’t update the embedded JPG thumbnail. This issue almost stumped me, but then I came across this great little tool called jhead. Amongst other things, jhead can regenerate the JPG thumbnail (only if one existed originally):

jhead.exe -rgt {filename}

Tying it all together is PowerShell:

<#
    .SYNOPSIS
    Generate B&W versions of images 
   
       Daniel Streefkerk
    
    THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE 
    RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE USER.
    
    Version 10, 02/08/2012
    
    .DESCRIPTION
    
    This script runs though a folder full of images and creates B&W versions of said images
    
    IMPORTANT NOTE: This script requires ImageMagick and jhead. For more information, see my blog
    
    .PARAMETER RootFolder
    The folder within which to process images

    .PARAMETER Recursive
    Also scan subfolders? This is enabled by default
    
    .EXAMPLE
    Process all images in c:temp
    .Generate-BWImages.ps1 -RootFolder c:temp
    
    #>

Param(
  [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$false,HelpMessage='The folder that contains the photos')]
  [String]
  #[ValidateScript({Test-Path $_ -PathType 'Container'})] 
  $RootFolder,
  [Parameter(Position=1,Mandatory=$false,ValueFromPipeline=$false,HelpMessage='Recurse through all subfolders?')]
  [bool]
  $Recursive=$true
)

cls

# Change these if necessary
$fileExtensions = "*.jpg"
$fileNameSuffix = "_bw" # the text to be appended to the file name to indicate that it has been modified

$files = $null;
$fileCount = 0

# Check if the root folder is a valid folder. If not, try again.
if ((Test-Path $RootFolder -PathType 'Container') -eq $false) {
    Write-Host "'$RootFolder' doesn't seem to be a valid folder. Please try again" -ForegroundColor Red
    break
}

# Get all image files in the folder
if ($Recursive) {
    $files = gci $RootFolder -Filter $fileExtensions -File -Recurse
} else {
    $files = gci $RootFolder -Filter $fileExtensions -File
}
# If there are no image files found, write out a message and quit
if ($files.Count -lt 1) {
    Write-Host "No image files with extension '$fileExtensions' were found in the folder '$RootFolder'" -ForegroundColor Red
    break
}

# Loop through each of the files and process it
foreach ($image in $files) {
    $newFilename = $image.DirectoryName + "" + $image.BaseName + $fileNameSuffix + $image.Extension
    $imageFullname = $image.FullName

    write-host "Processing image: $imageFullname" -ForegroundColor Green
    & convert.exe $image.FullName -modulate "100,0" $newFilename
    write-host "Updating embedded thumbnail for: $newFilename" -ForegroundColor Green
    & jhead.exe -rgt $newFilename

    $fileCount++
}

Write-Host "$fileCount images processed" -ForegroundColor Yellow

VBScript to disable all users in specified OUs

When staff members leave the organisation, we move their account to a sub-OU named “Leavers” under their office’s OU. This triggers their mailbox to be archived in Enterprise Vault.

I thought it was about time to put together a quick scheduled task to ensure that all these “leavers” were automatically disabled and hidden from the Exchange address lists without IT manually having to do it.

I came across this handy and concise example, and modified it to run through a group of OUs while doing what I needed it to do.

Here’s the code. As always, run at your own risk, and test it before putting it into production:

On Error Resume Next

Dim arrLeaverOrgUnits, objOU
arrLeaverOrgUnits = Array("LDAP://OU=Leavers,OU=Sydney,DC=contoso,DC=com",_
                          "LDAP://OU=Leavers,OU=Melbourne,DC=contoso,DC=com")

For Each strOU in arrLeaverOrgUnits
  Set objOU = GetObject(strOU)
    ' Let's be extra-paranoid here, and make sure we're only working on the leavers OU
    '  in case someone adds the wrong OU into the array above
    If objOU.Name <> "OU=Leavers" Then Exit For
    
    ' Loop through each object in the current OU
    For Each objObject In objOU
      ' If the current object is a user
      If objObject.class="user" then
        'Disable the account
        objObject.AccountDisabled = True
        ' Hide the account from the Exchange address lists
        objObject.Put "msExchHideFromAddressLists", True
        ' Write the information back to the user object in AD
        objObject.SetInfo
        
        'WScript.Echo objObject.Name & " disabled and hidden from Exchange address lists"
      End if
    Next
  Set objOU = Nothing
Next

PowerShell: Cancel all print jobs

Here’s a quick PowerShell script I put together to delete all print jobs from our Windows Server 2008 R2 print server. It’s run overnight as a scheduled task. We do this because sometimes our print accounting software doesn’t clear out old jobs if users haven’t released them at the printer.

EDIT: January 2015

Just reviewing my old posts. Here’s how I’d do this nowadays:

Get-WmiObject Win32_Printer -Filter "(Local = $true) and (Shared = $true)" | ForEach-Object {$_.CancelAllJobs()}

It’s better practice to do the filtering in WMI earlier, rather than retrieve more printers and filter them out in PowerShell afterwards.

Here’s the old code

$printers = Get-WmiObject Win32_Printer

if ($printers.Count -eq 0) {
    Write-Host -ForegroundColor red "No Printers Found"
    exit
}

foreach ($printer in $printers) {
    
    if ($printer.Local -and $printer.Shared)
    {
        Write-Host -ForegroundColor Green "Cancelling all jobs on: $($printer.Name)"
        $printer.CancelAllJobs()
        
    }
}

It will only clear print jobs on local printers that are shared out. There’s some more info about the Win32_Printer class and the CancelAllJobs method on MSDN.

Note that I haven’t included any error-checking in this script at all, so run it manually first to see if it will work. As always, run at your own risk.

To schedule it, simply create a new scheduled task:

    1. Run with highest privileges
    2. Run whether user is logged on or not
    3. Do not store password
    4. Action: Start a program: C:WindowsSystem32WindowsPowerShellv1.0powershell.exe
    5. Arguments: -File “C:path-to-script-filename-of-script-file.ps1”

clearqueues-generaltab

clearqueues-action

Updated: VBScript – Check if current user is a member of a certain group

I found some code to do this out on the net the other day. I’ve modified it a little, and added the part that checks environment variables.

Note 3: 30th May 2014 – This post is consistently one of the most popular posts on my blog. By now, you should be looking at PowerShell to replace VBScript. The 40-odd lines of VBScript below can be replaced by a single line of PowerShell. See this post here.

Note 2: 21st February 2013 – I’ve updated the script so that it will work with Option Explicit. People who used this would see every check returning “true”. Thanks to “Zounder1” for making me aware of this.

Note: 11th October 2010 – Since this post is so popular, I’ve cleaned up the code a bit and re-posted it below.

Option Explicit
Dim objShell,grouplistD,ADSPath,userPath,listGroup
On Error Resume Next

set objShell = WScript.CreateObject( "WScript.Shell" )
 
'Calls the isMember function with the specified group to see if the current user
' is a member of that group.
If isMember("GroupNameToCheckGoesHere") Then
       'MsgBox("Is member") ' Do something here if they are a member of the group
    Else
       'MsgBox("Is not member") ' Do something here if they are not a member of the group
End If
 
' *****************************************************
'This function checks to see if the passed group name contains the current
' user as a member. Returns True or False
Function IsMember(groupName)
    If IsEmpty(groupListD) then
        Set groupListD = CreateObject("Scripting.Dictionary")
        groupListD.CompareMode = 1
        ADSPath = EnvString("userdomain") & "/" & EnvString("username")
        Set userPath = GetObject("WinNT://" & ADSPath & ",user")
        For Each listGroup in userPath.Groups
            groupListD.Add listGroup.Name, "-"
        Next
    End if
    IsMember = CBool(groupListD.Exists(groupName))
End Function
' *****************************************************
 
' *****************************************************
'This function returns a particular environment variable's value.
' for example, if you use EnvString("username"), it would return
' the value of %username%.
Function EnvString(variable)
    variable = "%" & variable & "%"
    EnvString = objShell.ExpandEnvironmentStrings(variable)
End Function
' *****************************************************
 
' Clean up
Set objShell = Nothing