Skip to content

Instantly share code, notes, and snippets.

@dwsmart
Last active November 14, 2023 10:26
Show Gist options
  • Save dwsmart/f52b62dcfd350390d16d77d4fb2663c4 to your computer and use it in GitHub Desktop.
Save dwsmart/f52b62dcfd350390d16d77d4fb2663c4 to your computer and use it in GitHub Desktop.
Logflare to Common Log Format nodejs Script
// 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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment