How do I create a custom metric with additional information from a custom SQL query in DataDog?

Question

I am currently working on setting up a monitor to monitor slow queries in the Cloud SQL DB. I built a custom query to get the processes running on the SQL server, because currently slow query monitoring doesn't report until the process is completed. To get a check every 15-20 seconds (or whatever is configured in DD) of currently running queries over 5 minutes I have this in my DD agent's config.

    custom_queries:
      - query: SELECT COUNT(*) as processes FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 
        (5 * 60 * 1000);
        columns:
        - name: mysql.processlist.processes
          type: count
        tags:
        - staging:mysql

And my results in DD are:

As you can see it shows the count of queries that have been running for over 5 minutes. How would I be able to get more information about each query. For example I would like to see the exact query statement that is being executed. I know I can use the query: SELECT INFO as QUERY FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query'; to get the Query statement, but Id like to be able to click on the graph in DD and dig further into each process to see the statements. Is there a way to add this information or another feature in Datadog where I can the processes being queried to each process individually?

My first thought was to change the custom query to this:

  - query: SELECT COUNT(*) as PROCESSES, ID, INFO as QUERY FROM 
    INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query';
    columns:
    - name: mysql.processlist.ID
      type: tag
    - name: mysql.processlist.PROCESSES
      type: count
    - name: mysql.proccesslist.QUERY
      type: tag

But this only returns one row with the count number of all process and the ID and Query of the first result.

# PROCESSES, ID, QUERY
'3', '61550', 'SELECT ....'

Answer 1

After testing different queries I found that running this query groups the results by query statements and will return the count of each.

SELECT COUNT(*) as Query_Count, DB, INFO as Query FROM 
INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query' AND TIME > (5 * 60 * 1000) 
GROUP BY QUERY;

In Datadogs mysql configuration I added tags for the query statement and database name. Now since they are grouped, I can see information per different statement in datadog.

    custom_queries:
      - query: SELECT COUNT(*) as Query_Count, DB, INFO as Query FROM 
        INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query' GROUP BY QUERY;
        columns:
         - name: mysql.processlist.Slow_Query.Query_Count
           type: count
         - name: mysql.processlist.Slow_Query.DB
           type: tag
         - name: mysql.processlist.Slow_Query.Query
           type: tag