How to update new host information in 100 Dataconnections in Qliksense

[VamsiBhagavathula1]

Hello All,

We have a backend (Oracle) DB changes going to happen. As part of this, The host name and DB service names are going to be changed.  So, My question is how could we replace old host to new host updates in Qlik sense?? while doing this kind of bulk changes in data connections.

Editing one connection by one take a lot of time. So if Qlik saves these connections in any properties file, then i could replace the hostname and service with a less effort.

Best Regards

Vamsi

[AustinSpivey · Accepted solution]

Your best bet would probably be to write a script to achieve this. The Qlik software team maintains a freely available command line tool called qlik-cli that you can use for just that purpose. It allows you to write scripts around the Qlik Sense QRS and Cloud APIs.

Here's an example of how you may achieve your task using the qlik-cli tool with PowerShell:

# CONNECT TO QLIK WITH JWT

$token = Get-Content "token.txt"

qlik context rm QlikServer2
qlik context create QlikServer2 --server https://my-machine/jwt --server-type windows --insecure --api-key $token
qlik context use QlikServer2
qlik context get

# GET LIST OF DATA CONNECTIONS THAT USE ODBC
#   DRIVERS, LIKE THE ORACLE CONNECTOR

$dcs = qlik qrs dataconnection full --filter "type eq 'QvOdbcConnectorPackage.exe'" | ConvertFrom-Json -Depth 30 -AsHashtable

# HERE WE'LL ITERATE THROUGH EACH OF THOSE 
#   CONNECTIONS

$dcs | ForEach-Object {
    $r = $_

    # PARSE OUT THE CONNECTION STRING FOR THE
    #   CURRENT DATA CONNECTION SO THAT WE CAN
    #   EASILY FILTER AND EDIT THE PROPERTIES

    $r1 = ((($r.connectionstring -split '"')[1] -replace ";", "`n") `
        | ConvertFrom-StringData -Delimiter "=")
    
    # CHECK TO SEE IF THE CURRENT DATA CONNECTION
    #   HAS "oracle" AS THE DRIVER TYPE AND HAS
    #   THE HOST PROPERTY THAT WE WANT TO UPDATE

    if ($r1.driver -eq "oracle" `
            -and `
            $r1.host -eq "orcl.test-server.us"
    ) {
        
        # UPDATE THE HOST PROPERTY FROM THE CONNECTION
        #   STRING OF THE CURRENT DATA CONNECTION TO
        #   OUR NEW HOST

        $r1.host = "orcl.newhost.net"
        
        # CREATE A LIST THAT WE CAN POPULATE WITH
        #   OUR CONNECTION STRING VALUES

        $new_conn_string_list = New-Object System.Collections.Generic.List[System.Object]
        
        # ITERATE THROUGH THE PROPERTY-VALUE PAIRS FOR
        #  THE CONNECTION STRING OF THE CURRENT DATA
        #  CONNECTION AND ADD THEM TO OUR LIST FROM
        #  ABOVE AS "property=value"

        $r1.GetEnumerator() | ForEach-Object {
            $r2 = $_
            $new_conn_string_list.Add(@($r2.Key, $r2.Value) -join "=")
        }

        # JOIN THE PROPERTY-VALUE PAIRS, ALL SEPARATED
        #   BY A SEMI-COLON. FORMAT THE BEGINNING AND
        #   END OF THE STRING TO MATCH THE USUAL
        #   CONNECTION STRING FORMAT

        $new_conn_string = 'CUSTOM CONNECT TO "' + ($new_conn_string_list -join ";") + ';"'
        
        # CLONE THE CURRENT DATA CONNECTION OBJECT SO
        #   THAT WE DON'T MESS IT UP

        $rc = $r.Clone()

        # UPDATE OUR CLONED OBJECT WITH THE NEW CONNECTION
        #   STRING WITH THE UPDATED HOST PROPERTY

        $rc.connectionstring = $new_conn_string

        # DEFINE THE NAME OF OUR TEMPORARY JSON FILE TO
        #   INCLUDE THE ID OF THE CURRENT DATA CONNECTION
        #   SO THAT WE CAN MORE EASILY TRACK THINGS IF
        #   SOMETHING GOES WRONG

        $out_file = "temp-$($r.id).json"

        # CONVERT OUR UPDATED DATA CONNECTION OBJECT TO
        #   JSON AND THEN OUTPUT IT TO A JSON FILE IN
        #   THE CURRENT WORKING DIRECTORY

        $rc | ConvertTo-Json -EnumsAsStrings -Depth 30 > $out_file

        # USE THE UPDATE OPERATION TO UPDATE THE
        #   CURRENT DATA CONNECTION USING ALL OF THE
        #   SETTINGS THAT WE OUTPUT TO OUR JSON FILE
        #   WHICH INCLUDES, OF COURSE, OUR UPDATED
        #   HOST

        qlik qrs dataconnection update $r.id --file $out_file

        # DELETE THE TEMPORARY FILE ONCE QLIK GETS
        #   A RESPONSE AFTER UPDATING THE DATA
        #   CONNECTION

        Remove-Item -Path $out_file
    }
}

This basically gets all of your data connections that use an ODBC driver (as the Oracle connector does), loops through those and checks to see if they are an Oracle connector and has the host that we want to change, then for those relevant connections, it parses the ODBC connection string, updates the host to the new one, reformats back to a valid connection string, clones the data connection object, updates it with the new connection string, outputs the data connection properties to a temporary JSON file, uses the qlik-cli tool to update that connection (which, itself, is just a wrapper around the PUT /dataconnection/{id} operation in the QRS API) in Qlik using the updated settings from that file, and then we delete that file.

Some helpful links:

• Installing the qlik-cli tool
• Using qlik-cli with Qlik Sense Enterprise client-managed Repository API (QRS)
  • This one is great because it tells you how to set up a JWT virtual proxy, as well. You probably don't actually need to set up a JWT virtual proxy just to achieve this, you can probably just use your regular credentials as like basic auth