Query to get all REST Type of Connections

[harishyara]

Hello All,

What is the query to get all the REST type of connections and also need to update  below config in ConfigJSON with below syntax for all the REST type of connections using custom query. Please help me with the syntax. @rushikeshvartak @naveenss 

{

"connectionTimeoutConfig": {

"timeout": 300,

"retryWait": 500

}

}

[naveenss]

Hi @harishyara 

please try with the below select statement.

select * from externalconnattvalue where connectiontype in (select EXTERNALCONNECTIONKEY from externalconnection where EXTERNALCONNECTIONTYPE in (select EXTERNALCONNECTIONTYPEKEY from externalconnectiontype where connectiontype like '%REST%')) and attributekey='ConfigJson'

[pmahalle]

Hi @harishyara ,

Use below query to get all the connection of type REST present.

select EXTERNALCONNECTIONTYPE, CONNECTIONNAME from externalconnection where EXTERNALCONNECTIONTYPE =30

Make sure to execute this query through analytics and not in data analyzer as this table is not exposed in Data Analyzer.

[Sahana]

Hi @pmahalle Where should i find all columns which belongs "externalconnection" table and "jobs" related tables inside saviynt. is thr anyother way apart from data analyzer.

[naveenss · Accepted solution]

@harishyara below is the sample update statement as well. let me know if this helps!

UPDATE externalconnattvalue ex,
    externalconnection ext,
    externalconnectiontype et 
SET 
    ex.attributevalue = '{\"connectionTimeoutConfig\": {\"timeout\": 300,\"retryWait\": 500}}'
WHERE
    ex.connectiontype=ext.externalconnectionkey
        AND ext.externalconnectiontype = et.externalconnectiontypekey
        AND et.connectiontype = 'REST'
        AND ex.attributekey = 'ConfigJson';

[harishyara]

@naveenss above update statement worked many thanks!! Naveen.

Regards,

Harish