Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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