Transforming with JavaScript

SQL excels in data extraction, but sometimes results need further refinement. By integrating JavaScript, you can enhance dashboard data manipulation, offering flexibility beyond SQL's scope.

Getting started

In Dashboards, the data returned from an SQL query can be transformed using a JavaScript function. This function is triggered every time new data arrives from a SQL query. The JavaScript function can manipulate the incoming data in various ways and must return the transformed data.

Merging new data into existing results

Understanding the transformation script:

async (existingDataByQuery, newDataByQuery, lastRun) => {
  return Object.entries(newDataByQuery).reduce((result, [queryIndex, newData]) => {
    return {
      ...result,
      [queryIndex]: result[queryIndex].concat(newData)
    }
  }, existingDataByQuery)
}

This script seamlessly merges incoming new data with the already available data for each query result.

Detailed explanation

1. Initialization: The script begins with two primary inputs - existingDataByQuery, which contains the current set of results, and newDataByQuery, which holds the new results fetched.

2. Iterate over new data: Using Object.entries(newDataByQuery), the script iterates over each data entry in the newly fetched results.

3. Reduce function: The main action is performed within a reducer. The reducer's purpose is to build up a result object by combining the new data with the existing one.

4. Appending new data: For every specific queryIndex in the new results, the corresponding data from newData is concatenated to the already existing data using the concat method.

5. Return merged data: The reducer finally returns an object with all the merged data sets, which combines both existing and new data for each query index.

By the end of the script, you have a consolidated dataset that includes both previous and new results for each query, ensuring continuity and completeness in your data representation.

Transforming log statuses into log levels

In another example, raw log data might not always conform to standard log levels. Instead, logs might contain various codes or statuses that need to be categorized into recognizable levels. Using SQL and JavaScript transforms, you can effectively convert these statuses into standard log levels.

SQL query

To start, use SQL to extract the log statuses:

SELECT {{time}} AS time, 
  countMerge(events_count) as value, 
  status as series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}} AND response_status IS NOT NULL
GROUP BY time, response_status

This SQL query charts the number of logs for each response status over time. Chart will contain one series for each response status.

Transform with JavaScript

Post the SQL query; you can use the following JavaScript transformation to categorize these statuses into log levels:

async (existingDataByQuery, newDataByQuery, lastRun) => {
  const STATUS_CATEGORIES = {
    '439': 'Warn',
    '404': 'Error',
    '200': 'Info',
    'debug_query_optimization': 'Debug',     
    'trace_protocol_data': 'Trace',          
    'fatal_out_of_memory': 'Fatal',          
    // ... add other status mappings as needed
  };

  return Object.entries(newDataByQuery).reduce((result, [queryIndex, newData]) => {
    const logLevelData = newData.map(entry => ({
      ...entry,
      series: STATUS_CATEGORIES[entry.series] || 'Other'
    }));

    return {
      ...result,
      [queryIndex]: result[queryIndex].concat(logLevelData)
    };
  }, existingDataByQuery);
}

The transformation maps various statuses to their corresponding log levels, and then updates the series with this log level.

Need help?

Please let us know at hello@betterstack.com.
We're happy to help! 🙏