Announcing the JFrog Source Integration

1. Obtain your token Start by getting the token used for accessing your JFrog instance. For this, either access or identity tokens can be used.
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=jfrog --destination=postgresql
   

   This command will create a jfrog_to_postgresql.yaml file in your current directory.
4. Update the Configuration File: Open the generated config file and replace the placeholders:
   • ${JFROG_INSTANCE_URL} with the base URL to your instance (a local instance would be http://localhost:9000 )
   • ${JFROG_ACCESS_TOKEN} with the token generated in step 1.
   • ${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 jfrog_to_postgresql.yaml
  

WITH latest_builds AS (
    SELECT
        jbr.build_name,
        MAX((jbr.build_info->>'started')::timestamp) AS latest_started
    FROM
        jfrog_build_run_info jbr
    GROUP BY
        jbr.build_name
)
SELECT
    jbb.build_name,
    jbb.project_key,
    jbr.build_info
FROM
    jfrog_builds jbb
JOIN
    latest_builds lb
ON
    jbb.build_name = lb.build_name
JOIN
    jfrog_build_run_info jbr
ON
    jbb.build_name = jbr.build_name
    AND (jbr.build_info->>'started')::timestamp = lb.latest_started
WHERE
    jbb.project_key = 'your_project_key';

WITH build_dates AS (
    SELECT
        jbb.project_key,
        jbr.build_name,
        (jbr.build_info->>'started')::date AS build_date
    FROM
        jfrog_builds jbb
    JOIN
        jfrog_build_run_info jbr
    ON
        jbb.build_name = jbr.build_name
    WHERE
        jbb.project_key = 'your_project_key'
)
SELECT
    project_key,
    build_name,
    AVG(build_count) AS average_builds_per_day
FROM (
    SELECT
        project_key,
        build_name,
        build_date,
        COUNT(*) AS build_count
    FROM
        build_dates
    GROUP BY
        project_key, build_name, build_date
) daily_builds
GROUP BY
    project_key, build_name
ORDER BY average_builds_per_day DESC;