Enable and Disable New Remote Desktop Connections with Powershell

Enable and Disable New Remote Desktop Connections with Powershell

I was doing maintenance this morning on a Remote Desktop farm that contains few Session Collections and 15 + Servers, so I had to Deny all new connections from the farm.

Because I am lazy and don't want to click on GUI too many times, I wanted to do it on powershell. My goal is actually automatise most of the regular maintenance tasks, so this script comes handy. To my supprice, Google didn't find any ready scripts to this, so I had to write it myself.

Here are two scripts, Deny connections and Allow connections. It is easy also to put Allow/Deny as parameter, but in my case, this is more useful. 

This is how it works:

1) Get all RD Sessionhosts to $Hosts variable

2) For each loop to allow/deny connections

3) Write host

Deny New Connections:

$Hosts = Get-RDServer | where 'Roles' -eq "RDS-RD-Server" | Select -Expand Server

 ForEach($Hostx in $Hosts)

{

$Hostx

set-RDSessionHost $Hostx -NewConnectionAllowed No

}

Write-Host -ForegroundColor Red "New Connections are disabled"

Allow New Connections

$Hosts = Get-RDServer | where 'Roles' -eq "RDS-RD-Server" | Select -Expand Server

 ForEach($Hostx in $Hosts)

{

$Hostx

set-RDSessionHost $Hostx -NewConnectionAllowed Yes

}

Write-Host -ForegroundColor Red "New Connections are enabled"

Restoring database with powershell

Related to  SFTP Transfer script with logging -post, I needed to a way to schedule database restore every night. After a bit of googleing and copy pasteing I came up with following script.

Short descrition

1. Look for latest file in folder
2. Drop existing datatabase
3. Restore latest backup
4. Add dbuser rights

Requirements:
- PowerShell Logging Module (https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85)
- SQLServer -Powershell module
- SQLASCmdlets -Powershell module


<#.Synopsis
This scripts restores SQL Database from BAK file and relocates files

.Description
Writen by Miikka Kallberg

.Parameter SQLInstance
SQL Instance name

.Parameter Database
Database name

.Parameter DBBAKPath
Path of BAK file

.Parameter DBDataPath
Database Data Path

.Parameter DBLogPath
Database Log Path

.Parameter logpath
Script log Path

.Parameter synclogfile
Script Log file

.Parameter DBUser
user account that will be added to database

.Parameter DBUserRole
role for that user

.Example
.\restore_tk.ps1
Restores Database with values configured below

#>
param (
# Edited 30.03.2017 Miikka Kallberg /CSC - IT Center for Science Ltd.

$SQLInstance = "",  # SQL Instance name
$Database = "", # Database name
$DBLogicalName = "", # Database logical name
$DBBAKPathFolder = "", #Path of BAK file
$DBDataPath = "", #Database Data Path
$DBLogPath = "", #Database Log Path
$logpath = "", # Log Path
$synclogfile = "", # Log file
$DBUser = "", #user account that will be added to database
$DBUserRole ="" #role for that user
)
#This script requires Powershell Logging and SQLASCmdlets modules to be imported to powershell modules folder

# This is a custom module and needs to be installed before running this script
# Default location for PowerShell Modules is: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
# https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85

Import-Module SQLServer
Import-Module SQLASCmdlets
Import-Module PowerShellLogging

#Add timestamp to script log
get-date | out-file -filePath (Join-Path $logpath $synclogfile) -Encoding ascii -Force

#Start logging
#Format date and time for the logfilename
$sdate = ((get-date).ToString("yyyyMMdd_HHmmss_"))

#Combine logfile name with time
$synclog =  $sdate + $synclogfile

#Define logfile
$LogFile = Enable-LogFile -Path (Join-Path $logpath $synclog)

# Note - it is necessary to save the result of Enable-LogFile to a variable in order to keep the object alive.  As soon as the $LogFile variable is reassigned or falls out of scope, the LogFile object becomes eligible for garbage collection.

$VerbosePreference = 'Continue'
$DebugPreference = 'Continue'

#Start restore script

# Get latest file from network path
$DBBAKfilename = Get-ChildItem $DBBAKPathFolder | select -last 1
$DBBAKfile = $DBBAKfilename.Name
$DBBAKPath = $DBBAKPathFolder + $DBBAKfile

#Drop existing Database
$databasePath = join-path $SQLInstance "Databases\$Database"
Invoke-SqlCmd "USE [master]; ALTER DATABASE [$Database] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$Database] "

#$DBLogicalName = $Database
$DBDataRelocPath =  $DBDataPath + $Database + ".mdf"
$DBLogRelocPatch =  $DBLogPath + $Database + ".log"
$DBLogicalName_Log = $DBLogicalName +"_log"
$Database_Log = $Database +"_log"
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName", "$DBDataRelocPath")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$DBLogicalName_Log","$DBLogRelocPatch")

Restore-SqlDatabase -ServerInstance $SQLInstance -Database $Database -BackupFile $DBBAKPath  -ReplaceDatabase  -RelocateFile @($RelocateData,$RelocateLog) -RestoreAction Database

#Rename logical name
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName',NEWNAME=N'$Database') "
Invoke-SqlCmd "ALTER DATABASE [$Database] MODIFY FILE (NAME=N'$DBLogicalName_Log',NEWNAME=N'$Database_Log') "

# Add user rights to database
# Define server defaults
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLInstance

# Define Databse
$db = $svr.Databases[$Database]

# Add User
$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $DBUser)
$usr.Login = $DBUser
$usr.Create()

#Add user to role
$Rol = $db.Roles[$DBUserRole]
$Rol.AddMember($DBUser)
Write-Host "$DBUser added to $DBUserRole Role in $Database on $SQLInstance "
 
# Disable logging before the script exits (good practice, but the LogFile will be garbage collected so long as this variable was not in the Global Scope, as a backup in case the script crashes or you somehow forget to call Disable-LogFile).

$LogFile | Disable-LogFile

SFTP Transfer script with logging

We had a need to copy database dumbs from our data provider to our servers daily. Since I have already used WinSCP with Powershell, so I re-used that script and added logging and folder cleanup.

Requirements:
- WinSCP installed
- PowerShell Logging Module (https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85)


param (
# Edited 30.03.2017 Miikka Kallberg /CSC - IT Center for Science Ltd.

$sftpserver = "", # SFTP hostname
$sftpuser = "", # SFTP Username
$sftppass = "", # SFTP Password
$sftpkeyfp = "", # SFP Host Key Finger Print
$winscppath = "C:\Program Files (x86)\WinSCP\WinSCPnet.dll", # Path to WinCSPnet.dll
$localPath = "", # Path where to sync
$remotePath = "", # SFTP Path
$filenumber = "", # Number of saved files
$sleepTime = "",  # sleep time in seconds between synchronizations
$logpath = "", # Synck log Path
$synclogfile = "" # Sync Log file
)

# This is a custom module and needs to be installed before running this script
# Default location for PowerShell Modules is: C:\Windows\System32\WindowsPowerShell\v1.0\Modules
# https://gallery.technet.microsoft.com/scriptcenter/Enhanced-Script-Logging-27615f85
#And WinSCP installed with automation package

#Add timestamp to script log
get-date | out-file -filePath (Join-Path $logpath $synclogfile) -Encoding ascii -Force

#Start logging
#Format date and time for the logfilename
$sdate = ((get-date).ToString("yyyyMMdd_HHmmss_"))

#Combine logfile name with time
$synclog =  $sdate + $synclogfile

Import-Module PowerShellLogging

#Define logfile
$LogFile = Enable-LogFile -Path (Join-Path $logpath $synclog)

# Note - it is necessary to save the result of Enable-LogFile to a variable in order to keep the object alive.  As soon as the $LogFile variable is reassigned or falls out of scope, the LogFile object becomes eligible for garbage collection.

$VerbosePreference = 'Continue'
$DebugPreference = 'Continue'

#Start syncin script
try
{
    # Load WinSCP .NET assembly
Add-Type -Path $winscppath

    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions
    $sessionOptions.Protocol = [WinSCP.Protocol]::Sftp
    $sessionOptions.HostName = $sftpserver
    $sessionOptions.UserName = $sftpuser
    $sessionOptions.Password = $sftppass
    $sessionOptions.SshHostKeyFingerprint = $sftpkeyfp

    $session = New-Object WinSCP.Session
 
     try
    {
        # Connect
        $session.Open($sessionOptions)

        # Download files
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary

        $transferResult = $session.GetFiles( $remotePath, $localPath, $False, $transferOptions)

        # Throw on any error
        $transferResult.Check()

        # Print results
        foreach ($transfer in $transferResult.Transfers)
        {
            Write-Host ("Download of {0} succeeded" -f $transfer.FileName)
        }
    }

    finally
    {
        # Disconnect, clean up
        $session.Dispose()
   }
    exit 0
}
catch [Exception]
{
    Write-Host $_.Exception.Message
    exit 1
}

#Delete files that are oldest based on $filenumber
Get-ChildItem $localPath | where{-not $_.PsIsContainer}| sort CreationTime -desc|  select -Skip $filenumber | Remove-Item -Force

# Disable logging before the script exits (good practice, but the LogFile will be garbage collected so long as this variable was not in the Global Scope, as a backup in case the script crashes or you somehow forget to call Disable-LogFile).

$LogFile | Disable-LogFile

Export Security Groups and Members to csv file

I needed to export AD Security groups and their memberships, so made a new script based on previous script.  I used table variable to sort the export data.



<#.Synopsis
This scripts exports Security Groups and member information from your active directory OU to CSV file 

Selected information: Group Name, Info, Description, Username, distinguishedname.

.Description
Writen by Miikka Kallberg

.Parameter FilePath
Path to save the ouput  CSV File

.Parameter csvfile
Filename of the output CSV File name

.Parameter OU
Organization Unit where you want to get the user information

.Example
.\groupinfo.ps1 -FilePath c:\temp\ -csvfile marvin.csv -OU Groups
This will export Security Group information from Groups OU to c:\temp\marvin.csv 

#>
Param([String]
[Parameter(Mandatory=$true)]
$FilePath='',
[Parameter(Mandatory=$true)]
$csvfile='',
[Parameter(Mandatory=$true)]
$OU='')


#Clear output variables to avoid multiple entries

if ( $FilteredOutput) {
Remove-Variable FilteredOutput}

if ( $Output) {
Remove-Variable Output}


# Import active directory module for running AD cmdlets
Import-Module activedirectory

#Get domain name
$domainname= get-addomain | select distinguishedname 

#Filter unneeded information from variable
foreach ($DN in $Domainname)
{
$domain = $Domainname.distinguishedname
}

# Create searchpath combining OU and domain name in correct format
$Oubase="OU=$OU,$Domain"

#Get AD Groups
[array]$Output += Get-ADgroup -filter * -properties CN,Description,Info,distinguishedname -SearchBase "$Oubase" 

#Create table in variables
$Table = @()

$Record = @{
"Group Name" = ""
"Username" = ""
"Description" = ""
"Info" = ""
"distinguishedname" = ""
}

#Sort data and add it to the table
Foreach ($Group in $output)
{

$Arrayofmembers = Get-ADGroupMember -identity $Group

foreach ($Member in $Arrayofmembers)
{
$Record."Group Name" = $Group.CN
$Record."UserName" = $Member.samaccountname
$Record."Description" = $Group.Description
$Record."Info" = $Group.Info
$Record."distinguishedname" = $Member.distinguishedname

$objRecord = New-Object PSObject -property $Record
$Table += $objrecord

}

}

#Export table to the table
$Table | export-csv (Join-Path $filepath $csvfile) -NoTypeInformation

Connection Report for Remote Desktop

I came across this Powershell script, I haven't done any modifications, but I wanted to add this to my collection, since I tend to forget where I found information/scripts.

This has been writen by Mike Crowley, link to his blog: https://mikecrowley.wordpress.com

Link to his blog post: https://mikecrowley.wordpress.com/2015/04/08/a-new-and-an-updated-powershell-script/

UPDATE 10th August 2015:
I noticed that if I run the script more than once, it makes multiple entries. I added Remove-Variable command to the beginning to make sure that results are correct.

The script:

<#

Features:

    1) This script reads the event log "Microsoft-Windows-TerminalServices-LocalSessionManager/Operational" from multiple servers and outputs the human-readable results to a CSV.

Instructions:

    1) Before you run, edit this line to include one or several of your own servers.

    

Requirements:

    1) TBD

    

April 8 2015 - Version 0.2

Mike Crowley

http://BaselineTechnologies.com

#>

#Clear output variables to avoid multiple entries
#Added by Miikka Kallberg 10th August 2015

if ( $FilteredOutput) {
Remove-Variable FilteredOutput}

if ( $Output) {
Remove-Variable Output}

#

$SessionHosts = @('Server2', 'Server3', 'Server4', 'Server5')

foreach ($Server in $SessionHosts) {

    $LogFilter = @{

        LogName = 'Microsoft-Windows-TerminalServices-LocalSessionManager/Operational'

        ID = 21, 23, 24, 25

        }

    $AllEntries = Get-WinEvent -FilterHashtable $LogFilter -ComputerName $Server

    $AllEntries | Foreach { 

           $entry = [xml]$_.ToXml()

        [array]$Output += New-Object PSObject -Property @{

            TimeCreated = $_.TimeCreated

            User = $entry.Event.UserData.EventXML.User

            IPAddress = $entry.Event.UserData.EventXML.Address

            EventID = $entry.Event.System.EventID

            ServerName = $Server

            }        

           } 

}

$FilteredOutput += $Output | Select TimeCreated, User, ServerName, IPAddress, @{Name='Action';Expression={

            if ($_.EventID -eq '21'){"logon"}

            if ($_.EventID -eq '22'){"Shell start"}

            if ($_.EventID -eq '23'){"logoff"}

            if ($_.EventID -eq '24'){"disconnected"}

            if ($_.EventID -eq '25'){"reconnection"}

            }

        }

$Date = (Get-Date -Format s) -replace ":", "."

$FilteredOutput | Sort TimeCreated | Export-Csv $env:USERPROFILE\Desktop\$Date`_RDP_Report.csv -NoTypeInformation

#End

This script deletes symLinks to project data folders based on csv file

This script deletes symlinks on data folders based on CSV file.

Customer had a linux based file server on a MS Remote Desktop environment. They replaced it with Windows server, but they wanted to have the Windows server to act just like the old linux server. RD farm is locked down, and users cannot browse network or map drives on their own.  I have few other post how to map drives with login script with powershell. To make things a bit more tricky, they are using project-001 syntax on group and OU names, but 001 on folder names.

<#.Synopsis
This script deletes symLinks to project data folders based on csv file.

File Format:
Project,Data

.Description
Writen by Miikka Kallberg
.Parameter FilePath
Path to CSV File
.Parameter csvfile
CSV File name
.Example
.\removedatalinks.ps1 -FilePath c:\temp\ -csvfile project.csv
This will make link to project folders from Project.csv file
#>
#Define script parameters
Param([String]
[Parameter(Mandatory=$true)]
$FilePath='',
[Parameter(Mandatory=$true)]
$csvfile='')

#Define funtion for deleting SymLinks
Function Remove-SymLink ($link)
{
    if (test-path -pathtype container $link)
    {
        $command = "cmd /c rmdir"
    }
    else
    {
        $command = "cmd /c del"
    }

    invoke-expression "$command $link"
}

#Import CSV file 
$projectCSV = Import-Csv "$FilePath\$csvfile" 

#Look each line
foreach ($csv in $projectcsv)
{
    #Define variables
    $Project = $csv.project
    $Data     = $csv.data
    $projectpath = "d:\fileshare\data\projects"
    $link        = "$projectpath\$project\data\$data"
   
    #Remove SymLinks
    Remove-SymLink $link
    }

#Clear variables
    $csv         = ""
    $Project = ""
    $Data     = ""
    $projectpath = ""
    $link        = ""
    $command     = ""

Create symLinks to project data folders and add projects to data groups from csv file with Powershell

This script creates symlinks on data folders based on CSV File and  names and adds project security groups to correct data groups.

Customer had a linux based file server on a MS Remote Desktop environment. They replaced it with Windows server, but they wanted to have the Windows server to act just like the old linux server. RD farm is locked down, and users cannot browse network or map drives on their own.  I have few other post how to map drives with login script with powershell. To make things a bit more tricky, they are using project-001 syntax on group and OU names, but 001 on folder names.

<#.Synopsis
This script makes symLinks to project data folders and adds projects to data groups from csv file.

File Format:
Project,Data

.Description
Writen by Miikka Kallberg
.Parameter FilePath
Path to CSV File
.Parameter csvfile
CSV File name
.Example
.\projectdatalinks.ps1 -FilePath c:\temp\ -csvfile project.csv
This will make link to project folders from Project.csv file
#>
#Define script parameters
Param([String]
[Parameter(Mandatory=$true)]
$FilePath='',
[Parameter(Mandatory=$true)]
$csvfile='')

#Define funtion for creating SymLinks
Function New-SymLink ($link, $target)
{
    if (test-path -pathtype container $target)
    {
        $command = "cmd /c mklink /d"
    }
    else
    {
        $command = "cmd /c mklink"
    }

    invoke-expression "$command $link $target"
}

#Import CSV file 
$projectCSV = Import-Csv "$FilePath\$csvfile" 

#Look each line
foreach ($csv in $projectcsv)
{
    #Define variables
    $Project = $csv.project
    $Data = $csv.data
    $datapath    = "\\server\data\data"
    $projectpath = "d:\fileshare\data\projects"
    $link        = "$projectpath\$project\data\$data"
    $target      = "$datapath\$data"
    $Projectgroup= "Project-$Project"
    $DataGroup   =  "data-$Data"
           
    #Create Data, Output and Work Folders under projects
    New-Item -ItemType Directory -Force -Path $projectpath\$project\data\
    New-Item -ItemType Directory -Force -Path $projectpath\$project\output\
    New-Item -ItemType Directory -Force -Path $projectpath\$project\work\

    #Check if link already exists    
    If (Test-Path $link){Write-Host $link already exists}
    Else{
    #Create SymLinks
    New-SymLink $link $target
    }

   # Add projects security groups to data group based on CSV file
    Add-ADGroupMember -Identity $DataGroup -Members $Projectgroup
    }

#Clear variables
    $csv         = ""
    $Project = ""
    $Data = ""
    $datapath    = ""
    $projectpath = ""
    $link        = ""
    $target      = ""
    $command     = ""