Code quality insights with SonarQube and CloudQuery #

New SonarQube Source Plugin #

Setting Up #

1. Obtain your API Key: Start by getting the API Key from the SonarQube platform - follow these instructions
2. Install CloudQuery CLI: Download and install the CloudQuery CLI on your machine by following steps on our download page.
3. Set up the Sync Configuration: Create a CloudQuery configuration file by running the following init command. You can use any supported destination, for this example, we will use PostgreSQL:

   cloudquery init --source sonarqube --destination postgresql

4. Update the Configuration File: Open the generated sonarqube_to_postgres.yaml file in your editor. Replace the placeholders:

   • ${SONARQUBE_BASE_URL} with the base URL to your instance (a local instance would be http://localhost:9000 )
   • ${SONARQUBE_USER_TOKEN} with the token generated in step 1. Also set the auth_mode to token . Or alternatively, use ${SONARQUBE_USER} and ${SONARQUBE_PASSWORD}, and use your credentials, or a dedicated SonarQube user.
   • ${POSTGRESQL_CONNECTION_STRING} with connection string to your PostgreSQL database.
5. Sync with CloudQuery:

• Log in using

cloudquery login

• Run the following command:

cloudquery sync sonarqube_to_postgres.yaml

Querying Synced Data #

Get Issue count per severity for all your projects #

SELECT
    project,
    impact->>'severity' AS severity,
    COUNT(*) AS issue_count
FROM
    sonarqube_issues,
    JSONB_ARRAY_ELEMENTS(impacts) AS impact
WHERE
    issue_status = 'OPEN'
GROUP BY
    project,
    severity
ORDER BY
    project,
    severity;

Get user info for all users in a specific SonarQube group #

SELECT
    u.*
FROM
    sonarqube_users u
JOIN
    sonarqube_group_memberships gm ON u.id = gm.user_id
JOIN
    sonarqube_groups g ON gm.group_id = g.id
WHERE
    g.name = 'sonar-users';

Get all issues for a project that require more than 20min of effort #

SELECT
    i.key,
    i.project,
    i.message,
    i.effort,
    i.impacts
FROM
    sonarqube_issues i,
    JSONB_ARRAY_ELEMENTS(i.impacts) AS impact
WHERE
    i.issue_status = 'OPEN'
    AND impact->>'severity' = 'HIGH'
    AND (
       (i.effort LIKE '%h%')
       OR
       (i.effort LIKE '%d%')
       OR
       (i.effort LIKE '%min%' AND
       CAST(SUBSTRING(i.effort, 1, POSITION('min' IN i.effort) - 1) AS INTEGER) >= 20))
ORDER BY
    i.project

Get Started Today #