Forums

 
HomeHomeRoyal TS V 1.xRoyal TS V 1.xToolboxToolboxPowerShell Script: Create .rts file from CSVPowerShell Script: Create .rts file from CSV
Previous
 
Next
New Post
10/21/2008 10:09 PM
 

I got a lot of feedback lately, that Royal TS needs an import functionality. I completely agree, but I soon realized, that almost everyone has different requirements. So thinking through all possibilities and providing a powerful UI which can handle all different requirements would be very time consuming.

So I thought, providing a sample script which shows how this can be done should get you started. I guess most Royal TS users are IT professionals and shouldn't have much trouble tweaking and adjusting the script. But keep in mind that this is a sample which handles the most common parameters and depending on the format of your CSV you need to change the field names in the script to match those from the CSV, add more fields, remove some fields, etc.

So here we go:

Save the script to C:\Create-RoyalTSFile.ps1
Save a CSV file to C:\myconnections.csv

Use the script like: C:\Create-RoyalTSFile.ps1 C:\myconnections.csv C:\mynewroyaltsfile.rts

When the script is finished, you should be able to open the "mynewroyaltsfile.rts" with the password "ScriptTemplatePassword" (without the quotes!)

After you opened the .rts file, change the password and/or protection level of the document, make some individual changes or bulk-changes and save it. 

Copy the following code into a new text file named: "Create-RoyalTSFile.ps1":

#============================================================================
#
#    Create-RoyalTSFile.ps1 [csv-file]
#
#============================================================================
# This is a sample PowerShell script which takes a comma-separated file and
# builds a Royal TS file from its content.
#
# The csv file must have the following format:
# ComputerName,Description,UserName,Domain,Password
# MailServer1,Exchange 2007,jlocke,dharma,4815162342
# MailServer2,Exchange 2007,jlocke,dharma,4815162342
#
# If your file format looks differen you need to adjust the script
#
# ----------------------------------------------------------------------------
param
(
    [string]$csvFileName = $(throw write-Host "You need to provide a csv file name."),
    [string]$rtsFileName = $(throw write-Host "You need to provide a rts file name.")
)

# load application to use the encryption method
# =============================================
[Reflection.Assembly]::LoadFrom("$Env:ProgramFiles (x86)\code4ward\Royal TS\RtsApp.exe")

# .rts file is password protected "ScriptTemplatePassword"
# .rts file building blocks
# =========================

# .rts file document begin
# ------------------------
function Get-DocumentBeginBlock
{
    $DocumentBeginBlock = '<?xml version="1.0" encoding="utf-8"?>
<RTSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Categories>'
    return $DocumentBeginBlock
}

# .rts file document end block
# ----------------------------
function Get-DocumentEndBlock([string]$DocumentName, [string]$FileName)
{
    $DocumentEndBlock = "  </Categories>
  <DocumentName>$DocumentName</DocumentName>
  <FileName>$FileName</FileName>
  <Signature>30C9D2AB4461FB1DB9BADD99FBF8496F5CC960F5490FD0EF725EE626BFCFE44AF88AF7E63A76CAF02AE36268315564C2C77FA819A323815EA2F9095CC843E660</Signature>
  <SL2>30C9D2AB4461FB1DB9BADD99FBF8496F5CC960F5490FD0EF725EE626BFCFE44AF88AF7E63A76CAF02AE36268315564C2C77FA819A323815EA2F9095CC843E660</SL2>
  <Level>1</Level>
  <CL2>dIqAMreTI2lYGRVQrApJSAinrW2DOpiI</CL2>
  <DocVersion>2</DocVersion>
</RTSDocument>"
    return $DocumentEndBlock
}

# .rts file category begin block
# ------------------------------
function Get-CategoryBeginBlock([string] $CategoryName)
{
    $CategoryBeginBlock = "    <RTSCategory>
      <CategoryName>$CategoryName</CategoryName>
      <IsExpanded>false</IsExpanded>
      <Connections>
    "
    return $CategoryBeginBlock
}

# .rts file category end block
# ----------------------------
function Get-CategoryEndBlock
{
    $CategoryEndBlock = "      </Connections>
    </RTSCategory>"
    return $CategoryEndBlock
}

# .rts file connection block
# --------------------------
function Get-ConnectionBlock
{
    param
    (
        [string]$ConnectionName = $(throw write-Host "You need to provide a unique connection name."),
        [string]$HostName = $ConnectionName,
        [string]$Description = "",
        [string]$UserName = "",
        [string]$Domain = "",
        [string]$Password = "",
        [bool]$SavePassword = $false
    )

    # check if we can set auto logon (only possible if user, domain and password is set
    if (($UserName -ne "") -and ($Domain -ne "") -and ($Password -ne "") -and ($SavePassword))
    {
        $SavePasswordString = "true"
    }
    else
    {
        $SavePasswordString = "false"
    }
   
    # check if a password should be encrypted
    if ($Password -ne "")
    {
        $EncryptedPassword = [code4ward.RoyalTS.SecurityExtensions]::Encrypt($Password, "ScriptTemplatePassword")
    }
    else
    {
        $EncryptedPassword = ""
    }
   
    # build a connection
    $ConnectionBlock = "    <RTSConnection>
          <ConnectionName>$ConnectionName</ConnectionName>
          <Host>$HostName</Host>
          <Description>$Description</Description>
          <User>$UserName</User>
          <Domain>$Domain</Domain>
          <SavedPassword>$EncryptedPassword</SavedPassword>
          <SavePassword>$SavePasswordString</SavePassword>
          <ColorDepth>BPP24_TrueColor</ColorDepth>
          <DesktopWidth>640</DesktopWidth>
          <DesktopHeight>480</DesktopHeight>
          <AutoExpand>true</AutoExpand>
          <ExternalWindow>false</ExternalWindow>
          <MinimizaMainWindow>false</MinimizaMainWindow>
          <SmartSize>true</SmartSize>
          <BitmapCaching>true</BitmapCaching>
          <RedirectDisks>true</RedirectDisks>
          <RedirectPrinters>true</RedirectPrinters>
          <RedirectPorts>true</RedirectPorts>
          <RedirectSmartCard>true</RedirectSmartCard>
          <ConnectToConsole>true</ConnectToConsole>
          <EnableWindowsKeys>true</EnableWindowsKeys>
          <AllowBackgroundInput>true</AllowBackgroundInput>
          <AutoReconnect>true</AutoReconnect>
          <EnableTSGateway>false</EnableTSGateway>
          <TSGatewayServer></TSGatewayServer>
          <RdpPort>3389</RdpPort>
          <AllowWallpaper>true</AllowWallpaper>
          <AllowThemes>true</AllowThemes>
          <AudioSetting>PlayOnClient</AudioSetting>
          <PropertySplitterPosition>177</PropertySplitterPosition>
          <HideProperties>false</HideProperties>
          <HideTSCNavigation>true</HideTSCNavigation>
          <Top>-1</Top>
          <Left>-1</Left>
          <Screen />
        </RTSConnection>
"
    return $ConnectionBlock
}


# ==========================================================================
# Now let's begin to build the .rts file
# ==========================================================================

# start with the document
$RTSFile = Get-DocumentBeginBlock

# now we create one category where we place all connections
$RTSFile += Get-CategoryBeginBlock "My Test Category"

# here we open the csv file and create a connection for every line
Import-Csv $csvFileName | ForEach-Object { $RTSFile += Get-ConnectionBlock $_.ComputerName $_.ComputerName $_.Description $_.UserName $_.Domain $_.Password $true }

# now let's finish the document
$RTSFile += Get-CategoryEndBlock
$RTSFile += Get-DocumentEndBlock "MyTestDocument" $rtsFileName

# write the .rts file to the file system
$RTSFile | Out-File -filepath $rtsFileName -NoClobber

Here the sample CSV file: 

ComputerName,Description,UserName,Domain,Password
MailServer1,Exchange 2007,jlocke,dharma,4815162342 
MailServer2,Exchange 2007,jlocke,dharma,4815162342

I hope I could give you a headstart with that script. Of course you could easily extend the script to create categories according to a field in the csv and assign connections to them.

You could also make it more flexible to control much more settings (like redirection, color depth, etc) by defining them in the csv. In any case, don't be shy and post your custom scripts to this thread.

cheers

Stefan


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
10/23/2008 3:17 PM
 

Here a small variation of the script which takes another column to control the folder the connection should be placed in:

Consider a CSV file like this: 

ComputerName,Description,UserName,Domain,Password,Folder
MailServer1,Exchange 2007,jlocke,dharma,4815162342,Folder 1
MailServer2,Exchange 2007,jlocke,dharma,4815162342,Folder 1
DC1,Domain Controller,jlocke,dharma,4815162342,Folder 2
DC2,Domain Controller,jlocke,dharma,4815162342,Folder 2
MailServer3,Exchange 2007,jlocke,dharma,4815162342,Folder 1

Now you only need to replace the very last part of the powershell script with the following snippet:

# ==========================================================================
# Now let's begin to build the .rts file
# ==========================================================================

# start with the document
$RTSFile = Get-DocumentBeginBlock

# here we open the csv file and create a connection for every line
$CSVFile = Import-Csv $csvFileName

$CSVFile | Group-Object Folder | ForEach-Object {
    # now we create a folder
    $RTSFile += Get-CategoryBeginBlock $_.Name
    # now we create all connections for that folder
    $_.Group | ForEach-Object { $RTSFile += Get-ConnectionBlock $_.ComputerName $_.ComputerName $_.Description $_.UserName $_.Domain $_.Password $true }
    $RTSFile += Get-CategoryEndBlock
}


# now let's finish the document
$RTSFile += Get-DocumentEndBlock "MyTestDocument" $rtsFileName

# write the .rts file to the file system
$RTSFile | Out-File -filepath $rtsFileName -NoClobber


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
11/10/2008 6:40 PM
 

I get the following error when running the script:

Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///C:\Program Files (x86)\code4ward\Royal TS\RtsApp.exe' or one of its dependencies. The system cannot find the file specified."

I have RTS installed in C:\Program Files\code4ward\Royal TS\RTSApp.exe.

Can you help? Thanks.

 
New Post
11/10/2008 10:10 PM
 

You already have the answer to your problem. Just change the line:

[Reflection.Assembly]::LoadFrom("$Env:ProgramFiles (x86)\code4ward\Royal TS\RtsApp.exe")

to

[Reflection.Assembly]::LoadFrom("$Env:ProgramFiles\code4ward\Royal TS\RtsApp.exe")

cheers


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
11/12/2008 3:14 PM
 

Thanks for the script...it's working well for me, with one exception.  How can we change the password required to open the document that is created?  It seem hard coded to "ScriptTemplatePassword".  The only place this text appears is in the comment at the beginning and when creating the connection block:

$EncryptedPassword = [code4ward.RoyalTS.SecurityExtensions]::Encrypt($Password, "ScriptTemplatePassword")

I tried changing the text in these locations, but the created file still must be opened with the old password.  I suspect it has something to do with the signature in the end block...but I don't know how to go about changing that.  Thoughts?

 
New Post
11/12/2008 3:21 PM
 

It's not a good idea to place the final password in the powershell script in clear-text. That's why I use the "dummy" password. A secure and good procedure is to create the file using the dummy password, open the file, change the password, save the file and you're done.

Another way is to create an empty .rts file with the password of your choice, open the XML, copy/replace all the surrounding XML tags into the powershell and create the file using your own password.

But I warned you, using the final password in the script is a huge security risk!

Hope this helps...

cheers

Stefan


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
11/12/2008 4:37 PM
 

I went with replacing the signature block with one from a known password, and that seems to have worked fine.  The problem with opening and saving is that this is going to be completely automated.  We pull connection info out of a database, into a CSV, then kick off the PS script to create the RTS file.  Putting a manual step in there defeats our purpose of automating the process.

 
New Post
11/12/2008 4:58 PM
 

I see. Glad you solved it...

cheers


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
2/4/2009 9:43 PM
 

I have dloaded the script and double checked it all looks good however when I run it against an csv file I just end up with a rts file that is blank. Not sure where to go from here but any ideas or pointers would be great.

Thanks,
Randy G.

 
New Post
2/5/2009 10:50 AM
 

I will be happy to help. Can you give me some more information? Like is it working for you with the sample csv file I posted in this thread or do you use the same schema? Best would be when you PM me and send me your files (script and csv with dummy passwords) to let me have a look at it.

cheers

Stefan


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
3/6/2009 8:39 PM
 

Would it be possible to edit the script in such a way I can define the IP adress myselve?

 
New Post
3/7/2009 9:37 AM
 

Hi michel,

what do you mean by "define the IP adress myself"?

cheers


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
3/7/2009 1:04 PM
 

StefanKoell wrote

Hi michel,

what do you mean by "define the IP adress myself"?

cheers

ComputerName,Description,UserName,Domain,Password
MailServer1,Exchange 2007,jlocke,dharma,4815162342 
MailServer2,Exchange 2007,jlocke,dharma,4815162342
I'm missing the option to enter the Host IP, because I don't use a DNS server in one of my environment so the ComputerName can't be resolved.
That would be in my opinion a great option.

 

 
New Post
3/7/2009 4:01 PM
 

Actually it's very easy to extend the script. All you need to do, is to extend the CSV with a column like IPAddress and in the script look for the Import-CSV line. The Get-ConnectionBlock command accepts multiple parameters:

Get-ConnectionBlock $_.ComputerName $_.ComputerName $_.Description $...

As you can see the first two are the same.
1. is the computer displayname
2. is the hostname - which in your case should be the IP address
3. is the description
and so on

So if your CSV file has a column IPAddress the command should look like:
Get-ConnectionBlock $_.ComputerName $_.IPAddress $_.Description ...

Hope this helps

cheers

Stefan

 


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
1/12/2010 11:40 AM
 

Hi,

for some funny reason, after importing 1800 hosts i found that file created did not open. After some hours and 100 times of looking whats rong with my modified script, i begin to try modify .rts file manually, adding and deleteing sections. At some point, file begun to work. Added sections until it broked again. And found out, whats wrong: the current xml parser wont support  "&". After replaceing all &'s with and, all worked fine. This is not the error with the script but more like with the program itself. When i insert & throug gui, its saves it as "&amp;".

So it would be good idea for all the folks out there, to use some inline replace (ex. $_.lol -replace "&", "&amp;")

 


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
4/20/2010 6:44 PM
 

I have been using this script for a while now and it works great!  But I have started using a Terminal Server Gateway to connect to remote servers.  I want to be able to add a password to the file for the tsg server.  I tried to create a small file and save it with the password and replace it in the script but it fails.  Not sure how to get the encryption to work.

TIA!

 
New Post
2/14/2011 9:05 PM
 

Is there a way to remove the ADV setting other wise when i connect  all the disk printer ports, themes sound etc..... are connected really annoying and freezes the server for a while

 
New Post
2/14/2011 9:08 PM
 

Just modify the settings in the $ConnectionBlock variable. You can also create a dummy-file with one connection configured exactly as you want and copy over the relevant XML elements into the $ConnectionBlock variable.

cheers,
Stefan


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
New Post
8/4/2011 10:48 PM
 

 Finally i got this to work...

As it turns out it probably worked all along, but RoyalTS dosn't like having an "&" in <Description>

And my test-file had just that.

So now i added this in the script and all is good:

    # check for & in desciption

    $Description = $Description.Replace("&", "&amp;")

 
New Post
8/8/2011 8:06 PM
 

Good catch, Nico. Thanks for sharing!


Stay in touch with code4ward.net:

Subscribe to our Blog RSS FeedFollow us on TwitterLike us on FacebookOur Google+ page
 
Previous
 
Next
HomeHomeRoyal TS V 1.xRoyal TS V 1.xToolboxToolboxPowerShell Script: Create .rts file from CSVPowerShell Script: Create .rts file from CSV


spacer
dummy