ThinPrint Management Center – Documentation

Creating a CSV export file
for Map Additional Printers

329 views 0

Purpose of this PowerShell script

With this script you can create a CSV file from the Management Center’s SQL database to import it in Auto­Connect’s Map Additional Printers table. (See also how to create a CSV file for Dynamic Printer Matrix and how to import CSV files with AutoConnect).

  • Before running the script open it using an editor and enter values for the following varables:
    • $csvpath (path to the CSV file to be created; this can be exported from Map Additional Printers beforehand but don’t have to)
    • $sql_server (name of the SQL server instance)
    • $sql_db (database name on the SQL server)
Running the script for creating the Map Additional Printers table (took about three minutes with 4689 print­ers of whom 3719 were selected in the Edit Printers menu)

running the script for creating the Map Additional Printers table (took about three minutes with 4689 print­ers of whom 3719 were selected in the Edit Printers menu)

PowerShell script

#####  ThinPrint Management Center script for creating or supplementing CSV files for AutoConnect's Map Additional Printers table (MAP) #####
#####  Run this PowerShell script on the SQL server as an administrator  #####
#####  Fill in values to the variables $csvpath (path to the CSV file to be created), $sql_server (SQL server instance), $sql_db (Management Center database name) #####
$csvpath = "C:\temp\MapAdditionalPrinters.csv"
$sql_server = "server-name\SQLEXPRESS"
$sql_db = "ManagementCenter"

# further variables
$sql_table1 = "CONPRINTER"
$sql_table2 = "CONNECTION"
$sql_table3 = "CLUSTER"
$sql_table4 = "SOURCEPRINTER"
$sql_table5 = "SOURCE"
$sql_Con = New-Object Data.SqlClient.SqlConnection
$targetIDs = @()
$types = @()
$serveraddresses = @()
$tpclientIDs = @()
$clientaddresses = @()
$counter = @()

# reads the data from the SQL table dbo.CONPRINTER
$output = "............... Reading the database .............."
$output
$output = "number of rows (per table):"
$output
$sql_Con.ConnectionString = "Data Source=$sql_server;Integrated Security=True;Initial Catalog=$sql_db"
$sql_Con.open()                                                     # opens the connection to the database
$sql_Cmd1 = New-Object Data.SqlClient.SqlCommand
$sql_Query1 = "select * from "+$sql_table1                          # SQL query
$sql_Cmd1.CommandText = $sql_Query1                             # defines the SQL query
$sql_Adapter1 = New-Object System.Data.SqlClient.SqlDataAdapter     # creates a query adapter
$sql_Adapter1.SelectCommand = $Sql_Cmd1
$sql_Cmd1.Connection = $sql_Con                                     # dbo.CONPRINTER connection
$sql_Data1 = New-Object System.Data.DataSet                         # creates an object for storing the SQL data
$sql_Adapter1.Fill($sql_Data1)                                      # writes the data to this object
$sql_Con.Close()                                                    # closes the connection to the database

# the same with dbo.CONNECTION
$sql_Con.open()
$sql_Cmd2 = New-Object Data.SqlClient.SqlCommand
$sql_Query2 = "select * from "+$sql_table2
$sql_Cmd2.CommandText = $sql_Query2
$sql_Adapter2 = New-Object System.Data.SqlClient.SqlDataAdapter
$sql_Adapter2.SelectCommand = $Sql_Cmd2
$sql_Cmd2.Connection = $sql_Con
$sql_Data2 = New-Object System.Data.DataSet
$sql_Adapter2.Fill($sql_Data2)
$sql_Con.Close()

# the same with dbo.CLUSTER
$sql_Con.open()
$sql_Cmd3 = New-Object Data.SqlClient.SqlCommand
$sql_Query3 = "select * from "+$sql_table3
$sql_Cmd3.CommandText = $sql_Query3
$sql_Adapter3 = New-Object System.Data.SqlClient.SqlDataAdapter
$sql_Adapter3.SelectCommand = $Sql_Cmd3
$sql_Cmd3.Connection = $sql_Con
$sql_Data3 = New-Object System.Data.DataSet
$sql_Adapter3.Fill($sql_Data3)
$sql_Con.Close()

# the same with dbo.SOURCEPRINTER
$sql_Con.open()
$sql_Cmd4 = New-Object Data.SqlClient.SqlCommand
$sql_Query4 = "select * from "+$sql_table4
$sql_Cmd4.CommandText = $sql_Query4
$sql_Adapter4 = New-Object System.Data.SqlClient.SqlDataAdapter
$sql_Adapter4.SelectCommand = $Sql_Cmd4
$sql_Cmd4.Connection = $sql_Con
$sql_Data4 = New-Object System.Data.DataSet
$sql_Adapter4.Fill($sql_Data4)
$sql_Con.Close()

# the same with dbo.SOURCE
$sql_Con.open()
$sql_Cmd5 = New-Object Data.SqlClient.SqlCommand
$sql_Query5 = "select * from "+$sql_table5
$sql_Cmd5.CommandText = $sql_Query5
$sql_Adapter5 = New-Object System.Data.SqlClient.SqlDataAdapter
$sql_Adapter5.SelectCommand = $Sql_Cmd5
$sql_Cmd5.Connection = $sql_Con
$sql_Data5 = New-Object System.Data.DataSet
$sql_Adapter5.Fill($sql_Data5)
$sql_Con.Close()

# reads share names, connection IDs, client printer IDs and selections from CONPRINTER
$output = "................. Processing data ................."
$output
$sharenames = $sql_Data1.Tables.SHARENAME
$connIDs = $sql_Data1.Tables.FK_CONNECTION
$srcprinterIDs = $sql_Data1.Tables.FK_SRCPRINTER
$actionflags = $sql_Data1.Tables.ACTIONFLAG
$output = "number of all printers:"
$output
$number = $sharenames.length
$number = ($number/2)
$number

# reads connection IDs, target server IDs and client types from CONNECTION
$connectionIDs = $sql_Data2.Tables.CONNECTION_ID
$serverIDs = $sql_Data2.Tables.CLUSTER_ID
$clienttypes = $sql_Data2.Tables.CLIENTTYPE

# reads target server IDs and target server addresses from CLUSTER
$targetserverIDs = $sql_Data3.Tables.CLUSTER_ID
$servers = $sql_Data3.Tables.NAME

# reads client IDs from SOURCEPRINTER
$clientIDs = $sql_Data4.Tables.SOURCE_ID
$printerIDs = $sql_Data4.Tables.SOURCEPRINTER_ID

# reads client printer IDs and client addresses from SOURCE
$sourceIDs = $sql_Data5.Tables.SOURCE_ID
$clients = $sql_Data5.Tables.NAME

# indentifies print server IDs and client types using connection IDs from CONNECTION
$connIDs | Foreach {
   For($j = 0; $j -lt $connectionIDs.Count; $j += 1){
      If( $_ -eq $connectionIDs[$j]){
         $targetIDs += $serverIDs[$j]
         $types += $clienttypes[$j]
      }
   }
}

# indentifies central print server addresses using print server IDs from CLUSTER
$targetIDs | Foreach {
   For($j = 0; $j -lt $targetserverIDs.Count; $j += 1){
      If( $_ -eq $targetserverIDs[$j]){
         $serveraddresses += $servers[$j]
      }
   }
}

# indentifies client IDs using client printer IDs from SOURCEPRINTER
$srcprinterIDs | Foreach {
   For($j = 0; $j -lt $printerIDs.Count; $j += 1){
      If( $_ -eq $printerIDs[$j]){
         $tpclientIDs += $clientIDs[$j]
      }
   }
}

# indentifies client addresses using client printer IDs from SOURCE
$tpclientIDs | Foreach {
   For($j = 0; $j -lt $sourceIDs.Count; $j += 1){
      If( $_ -eq $sourceIDs[$j]){
         $clientaddresses += $clients[$j]
      }
   }
}

# prepares the Map Additional Printers CSV file
$output = "................. Writing CSV file ................"
$output
   If( [system.io.file]::Exists($csvpath) -eq $True){                                # checks whether the specified CSV file exists
      $mapold = Get-Content $csvpath -readcount 0                               # reads the current CSV file
      Set-Content $csvpath -value $mapold                                                # adds a linefeed after the paragraph sign ("¶")
   }
   Else{
      Add-Content -path $csvpath -value "DefaultPrinter,UserGroup,IPRange,ClientName,Destination"      # writes the table head if file doesn't already exist
   }      

# adds a row per share name to the Map Additional Printers CSV file
For($o = 0; $o -lt $sharenames.Count; $o += 2){
   $entry0 = $clientaddresses[$o]
   $entry1 = $serveraddresses[$o]
   $entry2 = $sharenames[$o]
   If( $actionflags[$o] -eq 2){
      $counter += 1
      $match = $types[$o] -match "(^[1].*$)"
      If( $match -eq $True){
         $match = $clientaddresses[$o] -match "(^[0-9].*$)"
         If( $match -eq $True){
            $mapnew = ([string]::Format("True,*,{0},*,\\{1}\{2}", $entry0,$entry1,$entry2))
         }
         Else{
            $mapnew = ([string]::Format("True,*,*,{0},\\{1}\{2}", $entry0,$entry1,$entry2))
         }
      }
      Else{
         $mapnew = ([string]::Format("True,*,*,*,\\{0}\{1}", $entry1,$entry2))
      }
      Add-Content $csvpath -Value $mapnew
   }
}
$output = "number of lines:"
$output
$number = $counter.length
$number
$output = ".................... Finished ....................."
$output

 

Previous Page
Next Page

Was this helpful?