Working with SQL is a powerful way for companies to manipulate large volumes of data stored in relational databases and is a great way to better understand your customers or product.

With a recent update to our API, it is now possible to trend your SQL results over time in Jama Analyze alongside your other team and product data. We do this via an agent script that runs in your infrastructure, behind a firewall, as another secure way to send data to Jama Analyze. This is considered an advanced feature, so if you aren't familiar with SQL and setting up scripts to run at regular intervals, this may not be the feature you are looking for.

Visualizing SQL results over time

For example, you may be curious to know how many users you have who have all performed the same set of actions in your product. Or you may want to know the number of actions all users have taken on a particular feature within a set time period.

Running a SQL query will give you a current snapshot of your data and can be helpful for answering one-off questions, but often you want to see your query trend over time. Now, you'll be able to automate the process of collecting and visualizing that information in Jama Analyze in one step.

To get started, you’ll need:

  • a place to run your script on a regular basis (eg a deployed VM/host or serverless configuration)
  • a Jama Analyze account
  • the API Token associated with your account
  • access (username and password) for your database
  • an SQL query that returns one row with one column
  • a fork of the Jama Analyze SQL Reporter app

Setting up your first SQL

The Jama Analyze SQL Reporter program is designed to automate the process of running your SQL queries and import them into your Jama Analyze account.

For each SQL you wish to run, you’ll save an individual file in a folder that contains the SQL. When you then run the program, it will create a new ingredient and value in Jama Analyze, using the file name to name the ingredient.

Please note: each query that you will be a unique ingredient in Jama Analyze. You can then use our metric builder in Jama Analyze to create advanced recipes and add your metrics to dashboards. Also, if you wish to change the name of your ingredient, you can do so in Notion. However, the SQL file name acts as the reporting key, so that shouldn’t ever change.

Let’s do a simple example. Say we want to know how many users there are in our app.

Our SQL would look like select count(*) from users;

We’ll save that in a file called Number of Users.sql and put that in the folder queries_folder of the Jama Analyze SQL Report app that we forked from Github.

Running the Jama Analyze SQL Reporter program

Once you have added a file with your SQL in it, you can run the program from the command line as a test with the following script:

sqlnotion [-h] --dbtype {postgres,mysql} --dbname DBNAME --user USER --password PASSWORD --host HOST --port PORT --queries-folder QUERIES_FOLDER --api-token API_TOKEN

Be sure to update the appropriate variables with your database name and auth credentials. For API_TOKEN, you can find your Jama Analyze API token here.

To be certain the script ran as expected, log into your Jama Analyze account and head to the Directory. You should now see the ingredient Number of Users with a single number reported for today. That number is the value returned by your SQL.

As you create more SQLs for more ingredients, you’ll only need to run the script once per day. It will automatically run the SQL for each file you have saved in the queries folder.

Running your script with a cron job

The smallest reporting frequency in Jama Analyze is daily. Meaning ff you run the SQL Reporter more than once a day, only the most recently reported value will be used in any calculations or charts displaying that ingredient.

With a simple cron job, you can schedule your script to run daily. Once in Jama Analyze, you can use the ingredients in recipes with different reporting frequencies and we’ll automatically pull the correct value for the correct date.

A simple example of daily cron setup, running the script at 11:55pm, is:

(switch to the VM/host where you want to run the script)

crontab -e

55 23 * * * /path/to/notion-sql-reporter/sqlnotion --dbtype mysql --dbname DBNAME --user USER --password PASSWORD --host HOST --port PORT --queries-folder /path/to/notion-sql-reporter/product-usage --api-token API_TOKEN


The above edits the crontab and installs a recurring run of the sqlnotion script. If you’d like to see what’s set up to run in your crontab, use crontab -l.

As long as your file names have not changed, the SQL Reporter will update your ingredients in Jama Analyze each time the script is run, across all metrics where those ingredients are being used.