Problem

A MySQL query to get the list of accounts, which have transfer sites with SSH as protocol and these Sites are using SSH key for authentication.

Resolution

Connect to the MySQL database as described in the respective KB: for Linux/UNIX refer to KB 178322; for Windows refer to KB 178144.

Once inside the MySQL, run the below query:

SELECT
A.name AS AccountName, S.name AS SiteName, S.protocol AS Protocol
FROM
Account AS A
INNER JOIN Site AS S ON A.id=S.accountId AND S.protocol='ssh'
INNER JOIN AT_CustAttr_LocalCerts AS M ON S.customAttributesId=M.customAttributesId
ORDER BY A.name, S.name;

The result will provide the list of accounts with such Transfer Sites, using SSH key authentication.