Removing old folders with powershell

In attempt to automate mundane tasks I have put together some scripts to schedule on the server. Here is one of them I am planning on using to remove old snapshots of a SQL Server database that are more than 45 days old:

$initpath = "C:\Snapshots"
(get-date).addDays(-45)
get-childitem -Path $initpath | where-object {$_.lastwritetime -lt (get-date).addDays(-45)} |
Foreach-Object { 
    if($_.Attributes -eq "Directory") {
        $_.FullName 
        if (Test-Path $_.FullName) {
            Remove-Item -r $_.FullName
        }

    }
}

Powershell and Active Directory: Moving user to another OU

Given a username passed as a parameter to the script, the code below will find the specified user in AD and move to the specified OU

$UserName = $args[0]

$root=[ADSI]''

$searcher = new-object System.DirectoryServices.DirectorySearcher($root)

$searcher.filter = "(&(objectClass=user)(sAMAccountName= $UserName))"

$User = $searcher.findone()

# Binding the user account to $AUser and the OU to move to to $MovetoOU
Write-Host $User.Properties.adspath

$ADSPath = $User.Properties.adspath

$MoveToOU = [ADSI]("LDAP://OU=NewOU,DC=mydomain,DC=com")

$AUser = [ADSI]("$ADSPath")


# Command to Do the actual move
$AUser.PSBase.moveto($MoveToOU)

Running PowerShell script with parameters

When running powershell from a command line and passing it one or more parameters, parameters passed in should be separated by a space and if any of the parameters contain spaces, they should be wrapped in double quotes:

C:\> .\MyPScript.ps1 "this is foirst parameter" "this is second"

And when accessing command line arguments from within the script, use args array with subscripts:

$param1=args[0]
$param2=args[1]

AD Security Groups and Creating Folders on a Share with Powershell

The below script is used to loop through all security groups in a particuler OU in Active Directory and create a folder on the share for each group and then, inside that folder, create a separate folder for each member of that group.

function CreateSubdirectory($path)
{
 New-Item -ItemType Directory -Path $path

}


foreach ($group in get-adgroup -searchbase "OU=MyOU,DC=mydomain,DC=com" -filter *){
  
 #create a subfolder $group.name
 $sharepath="\\SERVER\SHARE" + $group.name
 CreateSubdirectory $sharepath

 foreach ($member in ($group.member | sort)) 
 {
  #create a folder for each memeber $member.name
  CreateSubdirectory $sharepath + $member.name

 }
 }

Running Powershell script with spaces in the name from VBScript

Running Powershell script from VBScript is rather simple:

Set objShell=CreateObject("Wscript.Shell)
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe C:\test.ps1"
objShell.Run sCmd, 1, true

But what to do when the Powershell script file has spaces in the name, like C:\My Path\test.ps1?

Theanswer is to use ampersand and single quotes:

Set objShell=CreateObject("Wscript.Shell)
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe " & Chr(34) & "& 'C:\script with spaces.ps1'"  & Chr(34) 
objShell.Run sCmd, 1, true

or

Set objShell=CreateObject("Wscript.Shell)
sPSPath="C:\script with spaces.ps1"
sCmd="C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe " & Chr(34) & "& '" & sPSPath & "'"  & Chr(34) 
objShell.Run sCmd, 1, true

Add an array value to the Registry using Powershell

The code below initializes array $a and creates a key named "MyArray" of type REG_MULTI_SZ in registry path HKEY_CURRENT_USER\Control Panel\Desktop\Test and sets its value to an array

$a = ("val1", "val2", "val3")

$RegPath= "Registry::HKEY_CURRENT_USER\Control Panel\Desktop\Test"

Set-itemProperty $RegPath -name "myArray" -value $a -type MultiString

Strange occurrence when returning Datatable from a function

I was trying to retrieve some data from a SQL Server database using Powershel using the following function:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"

$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return $Datatable
}

When I would call it from my script

$dt = New-Object System.Data.DataTable
$dt=GetLocationData $item.name 
Write-Host "row count is " +  $dt.Rows.Count

It would not return anything and if I tried accessing a field

Write-Host $dt.Rows[0].Field1

I would get an error "Cannot index into null array"


The issue is that function would be returning a child collection of the table object instead of the table itself. What helped me is using the comma operator to wrap the rows collection in an array so that when the array is unrolled leaving me with the original rows collection.

So all I had to do to fix this issue is put a comma in the return statement right before datatable object:

function GetLocationData($Name)
{
$SqlConnection = New-Object system.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=ServerName;Database=MyDB;User Id=dbuser;Password=pwd;"


$SqlCommandText = "SELECT Field1, Field2 FROM TableName WHERE [Name]='" + $Name + "'"
$SqlConnection.Open()

$SqlCmd = New-Object Data.SqlClient.SqlCommand($SqlCommandText, $SqlConnection)
$Reader = $SqlCmd.ExecuteReader()

$Datatable = New-Object System.Data.DataTable 
$DataTable.Load($Reader)  

# Close the database connection 
$sqlConnection.Close() 

return ,$Datatable
}