Logflare to Common Log Format nodejs Script

logflare_to_common_log_format.js

// require libs
// run npm install @google-cloud/bigquery
const { BigQuery } = require('@google-cloud/bigquery');
const fs = require('fs');
// BigQuery Config - see https://cloud.google.com/docs/authentication/production#create_service_account
const options = {
  keyFilename: '{path_to_key_file}',
  projectId: '{project_id}',
};

// the name of the logfile to create
const logName = 'access.log';

// your logflare BigQuery table 
const table = "{table_name}";

// time period, uncomment / comment, or add your own
/*
---------
ALL TODAY
---------
*/
// const period = `TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AND CURRENT_TIMESTAMP()`;

/*
-------------
ALL YESTERDAY
-------------
*/
const period = `TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), DAY) AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)`;

/*
-------------
LAST 24 HOURS
-------------
*/
// const period = `TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()`;


/* end config */

const bigquery = new BigQuery(options);
async function createLogfile() {


  const query = `SELECT
  FORMAT_TIMESTAMP('%d/%b/%G:%H:%M:%S', t0.timestamp) AS formattedTimestamp,
  t0.id,
  f4.httpProtocol,
  f3.referer,
  t0.event_message
FROM
  ${table} AS t0
  INNER JOIN UNNEST(t0.metadata) AS f1 ON TRUE
  INNER JOIN UNNEST(f1.request) AS f2 ON TRUE
  INNER JOIN UNNEST(f2.headers) AS f3 ON TRUE
  INNER JOIN UNNEST(f2.cf) AS f4 ON TRUE
WHERE
 (t0.timestamp BETWEEN ${period})
ORDER BY
  t0.timestamp ASC
`;

  const options = {
    query: query,
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  const [rows] = await job.getQueryResults();

  console.log('Rows:');
  let logContent = '';
  rows.forEach(row => {
    const parsed = row.event_message.split(' | ');
    let ref = `"-"`;
    if (row.referer) {
      ref = `"${row.referer}"`;
    }
    let formattedRow = `${parsed[2]} - - [${row.formattedTimestamp} +0000] "${parsed[0]} ${parsed[4]} ${row.httpProtocol}" ${parsed[1]} 0 ${ref} "${parsed[5]}"`;
    console.log(formattedRow);
    logContent += `${formattedRow}\n`;
  }
  );
  fs.writeFileSync(logName, logContent, 'utf-8')
}
createLogfile();