EPM : Script pour exporter la liste des Preferred server

Publié par david le

Dans le cas d’une migration côte à côte avec EPM, les prefered server ne sont pas migrés, il faudra le faire manuellement.

Si vous en avez beaucoup, j’ai fait des scripts pour le faire en BDD (il reste la planification à faire).

Les scripts vont afficher les query SQL à exécuter pour la nouvelle BDD EPM, aucune modification ne sera réalisée en direct 

  • Le premier script permet de d’exporter les prefered server
#-------------------------- connecteur SQL ----------------------------------
#Old LDMS
$dataSource = "AncienSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

#------------------------ Query -----------------------------------------------------
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)


foreach ($elementPS in $tablePS) {
  $PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
  $ServerName_PS          = $elementPS.ServerName
  $Description_PS         = $elementPS.Description
  $Username_PS            = $elementPS.Username
  $Password_PS            = $elementPS.Password
  $WriteUsername_PS       = $elementPS.WriteUsername
  $WritePassword_PS       = $elementPS.WritePassword

  $ServerName_PS          = ''''+$ServerName_PS+''''
  $Description_PS         = ''''+$Description_PS+''''
  $Username_PS            = ''''+$Username_PS+''''
  $Password_PS            = ''''+$Password_PS+''''
  $WriteUsername_PS       = ''''+$WriteUsername_PS+''''
  $WritePassword_PS       = ''''+$WritePassword_PS+''''

  Write-host "INSERT INTO dbo.PreferredServer (ServerName,Description,Username,Password,WriteUsername,WritePassword) VALUES ($ServerName_PS,$Description_PS,$Username_PS,$Password_PS,$WriteUsername_PS,$WritePassword_PS);"

}


#foreach ($elementIP in $tableIP) {
#    $PreferredServer_Idn_IP = $elementIP.PreferredServer_Idn
#  $StartingIPAddress      = $elementIP.StartingIPAddress
#  $EndingIPAddress        = $elementIP.EndingIPAddress
#
#  foreach ($elementPF in $tablePF) {
#    $PreferredServer_Idn_PF = $elementPF.PreferredServer_Idn
#    $ServerName             = $elementPF.ServerName
#
#    If ($PreferredServer_Idn_PF -eq $PreferredServer_Idn_IP) {
#      #write-host "$PreferredServer_Idn_PF => $ServerName"
#      #write-host "INSERT INTO dbo.PreferredServerIPLimit (PreferredServer_Idn,StartingIPAddress,EndingIPAddress) VALUES ($PreferredServer_Idn_IP,$StartingIPAddress,$EndingIPAddress);"
#      foreach ($elementPF2 in $tablePF2) {
#        $PreferredServer_Idn_PF2 = $elementPF2.PreferredServer_Idn
#        $ServerName2             = $elementPF2.ServerName
#
#        if($ServerName -eq $ServerName2) {
#          $PreferredServer_Idn_PF2    = ''''+$PreferredServer_Idn_PF2+''''
#          $StartingIPAddress        = ''''+$StartingIPAddress+''''
#          $EndingIPAddress           = ''''+$EndingIPAddress+''''
#          write-host "INSERT INTO dbo.PreferredServerIPLimit (PreferredServer_Idn,StartingIPAddress,EndingIPAddress) VALUES ($PreferredServer_Idn_PF2,$StartingIPAddress,$EndingIPAddress);"
#        }
#      }
#    }
#  }
#}
  • Le second script va ajouter les replicator
#-------------------------- connecteur SQL ----------------------------------
#Old LDMS
$dataSource = "AncienSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

#---------------------  New LDMS ---------------------
$dataSource = "NouveauSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

#------------------------ Query -----------------------------------------------------
$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)

$query = "SELECT * FROM [$database].[dbo].[Computer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableORDI2 = new-object System.Data.DataTable
$tableORDI2.Load($result)

foreach ($elementPS in $tablePS) {
  $PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
  $ServerName_PS          = $elementPS.ServerName
  $Description_PS         = $elementPS.Description
  $Username_PS            = $elementPS.Username
  $Password_PS            = $elementPS.Password
  $WriteUsername_PS       = $elementPS.WriteUsername
  $WritePassword_PS       = $elementPS.WritePassword


  if ($ServerName_PS -like "*.*") {
    $temp =$ServerName_PS.split(".")
    $ServerName_PS = $Temp[0]
  }

  foreach ($elementORDI2 in $tableORDI2) {
    $DeviceNameOrdi2   = $elementORDI2.DeviceName
    $Computer_IdnOrdi2 = $elementORDI2.Computer_Idn
    if ($DeviceNameOrdi2 -eq $ServerName_PS) {
      $Computer_IdnOrdi2 = ''''+$Computer_IdnOrdi2+''''
      write-host "UPDATE dbo.PreferredServer SET Replicator_Idn = $Computer_IdnOrdi2 WHERE PreferredServer_Idn = $PreferredServer_Idn_PS;"
    }
  }

}
  • Le troisième script ca ajouter les limite IP
#--------------------- Old LDMS ---------------------
$dataSource = "AncienSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection. Open()

$query = "SELECT * FROM [$database].[dbo].[PreferredServerIPLimit]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableIP = new-object System.Data.DataTable
$tableIP.Load($result)

$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)

#---------------------  New LDMS ---------------------
$dataSource = "NouveauSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS2 = new-object System.Data.DataTable
$tablePS2.Load($result)

$query = "SELECT * FROM [$database].[dbo].[PreferredServerIPLimit]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableIP2 = new-object System.Data.DataTable
$tableIP2.Load($result)



foreach ($elementIP in $tableIP) {
  # plage IP sur l'ancien serveur
    $PreferredServer_Idn_IP = $elementIP.PreferredServer_Idn
  $StartingIPAddress      = $elementIP.StartingIPAddress
  $EndingIPAddress        = $elementIP.EndingIPAddress

  foreach ($elementPS in $tablePS) {
    # Prefered server sur l'ancien server
    $PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
    $ServerName             = $elementPS.ServerName

    If ($PreferredServer_Idn_PS -eq $PreferredServer_Idn_IP) {
      foreach ($elementPS2 in $tablePS2) {
        $PreferredServer_Idn_PS2 = $elementPS2.PreferredServer_Idn
        $ServerName2             = $elementPS2.ServerName

        # si le nom de serveur est identique je connais le nouveau ID
        if($ServerName -eq $ServerName2) {

          # je vérifie que la plage IP n'existe pas sur le nouveau serveur (sinon doublon)
          $dejaexistant = 0
          foreach ($elementIP2 in $tableIP2) {
            # plage IP sur l'ancien serveur
            $PreferredServer_Idn_IP2 = $elementIP2.PreferredServer_Idn
            $StartingIPAddress2      = $elementIP2.StartingIPAddress
            $EndingIPAddress2        = $elementIP2.EndingIPAddress

            if (($PreferredServer_Idn_IP2 -eq $PreferredServer_Idn_PS2) -and ($StartingIPAddress2 -eq $StartingIPAddress) -and ($EndingIPAddress2 -eq $EndingIPAddress)) { $dejaexistant = 1 }

          }

          if ($dejaexistant -eq 0) {
            $PreferredServer_Idn_PS2    = ''''+$PreferredServer_Idn_PS2+''''
            $StartingIPAddress        = ''''+$StartingIPAddress+''''
            $EndingIPAddress           = ''''+$EndingIPAddress+''''
            write-host "INSERT INTO dbo.PreferredServerIPLimit (PreferredServer_Idn,StartingIPAddress,EndingIPAddress) VALUES ($PreferredServer_Idn_PS2,$StartingIPAddress,$EndingIPAddress);"
          }
        }
      }
    }
  }
}
  • Le quatrième script va ajouter les sources
#--------------------- Old LDMS ---------------------
$dataSource = "AncienSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS17"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$query = "SELECT * FROM [$database].[dbo].[FileReplSourcePath]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableSRC = new-object System.Data.DataTable
$tableSRC.Load($result)

$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS = new-object System.Data.DataTable
$tablePS.Load($result)



$query = "SELECT * FROM [$database].[dbo].[FileReplJob]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableJob = new-object System.Data.DataTable
$tableJob.Load($result)



#---------------------  New LDMS ---------------------
$dataSource = "NouveauSQL"
$user = "CompteSQL"
$PassSQL = 'motdepasseSQL'
$database = "LDMS21"
$connectionString = "Server=$dataSource;uid=$user; pwd=$PassSQL;Database=$database;Integrated Security=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$query = "SELECT * FROM [$database].[dbo].[FileReplSourcePath]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tableSRC2 = new-object System.Data.DataTable
$tableSRC2.Load($result)


$query = "SELECT * FROM [$database].[dbo].[PreferredServer]"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$tablePS2 = new-object System.Data.DataTable
$tablePS2.Load($result)

foreach ($elementJob in $tableJob) {
    $FileReplSourcePath_Idn_Job   = $elementJob.FileReplSourcePath_Idn
  $PreferredServer_Idn_Job      = $elementJob.PreferredServer_Idn

  $Nomsource = ""
  foreach ($elementSRC in $tableSRC) {
    $FileReplSourcePath_Idn_SRC = $elementSRC.FileReplSourcePath_Idn
    $Name_SRC                   = $elementSRC.Name
    If ($FileReplSourcePath_Idn_Job -eq $FileReplSourcePath_Idn_SRC) { $Nomsource = $Name_SRC }
  }

  $NomPF     = ""
  foreach ($elementPS in $tablePS) {
    $PreferredServer_Idn_PS = $elementPS.PreferredServer_Idn
    $ServerName_PS          = $elementPS.ServerName
    #write-host $ServerName_PS
    If ($PreferredServer_Idn_Job -eq $PreferredServer_Idn_PS) { $NomPF = $ServerName_PS }
  }

  $PFID2     = ""
  foreach ($elementPS2 in $tablePS2) {
    $PreferredServer_Idn_PS2 = $elementPS2.PreferredServer_Idn
    $ServerName_PS2          = $elementPS2.ServerName
    #write-host $ServerName_PS
    If ($NomPF -eq $ServerName_PS2) { $PFID2 = $PreferredServer_Idn_PS2 }
  }

  $SRCID2    = ""
  foreach ($elementSRC2 in $tableSRC2) {
    $FileReplSourcePath_Idn_SRC2 = $elementSRC2.FileReplSourcePath_Idn
    $Name_SRC2                   = $elementSRC2.Name
    #write-host "$Name_SRC2  = $Nomsource"
    If ($Name_SRC2 -eq $Nomsource) { $SRCID2 = $FileReplSourcePath_Idn_SRC2 }
  }

  $SRCID2           = ''''+$SRCID2+''''
  $PFID2           = ''''+$PFID2+''''

  #write-host $ServerName_PS2
  write-host "INSERT INTO dbo.FileReplJob (FileReplSourcePath_Idn,PreferredServer_Idn) VALUES ($SRCID2,$PFID2);"
  #write-host ""


}

Catégories : EPM

Articles similaires

EPM

Framework Issue with EPM

This guide outlines a procedure for addressing Framework errors in LANDESK logs. It includes a specific command to run on the LANDESK server and emphasizes validating the procedure with LANDESK support prior to implementation. The necessary tool is linked for easy access.

EPM

Issue with deleting a EPM task

This guide details how to resolve issues with tasks linked to non-existent packages using SQL Management Suite. It includes steps for identifying task IDs, validating queries, updating the database, and deleting tasks through the LANDESK console. A database backup and contacting support are mandatory.

EPM

Uninstall EPM agent

This documentation provides guidance on using UninstallWinClient.exe, available on the ldmain share of the EPM core server. It details options like /REBOOT, /UI, and /FORCECLEAN, and warns against using certain options on core servers.