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 AutoConnect’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 printers 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