Skip to content

Instantly share code, notes, and snippets.

@dwsmart

dwsmart/logflare_to_common_log_format.js

Last active November 14, 2023 10:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dwsmart/f52b62dcfd350390d16d77d4fb2663c4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dwsmart/f52b62dcfd350390d16d77d4fb2663c4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dwsmart revised this gist Aug 3, 2021. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions logflare_to_common_log_format.js
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,9 @@ const options = {
    keyFilename: '{path_to_key_file}',
    projectId: '{project_id}',
    };
    const bigquery = new BigQuery(options);

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

    // your logflare BigQuery table
    const table = "{table_name}";
    @@ -35,7 +37,9 @@ 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() {


    @@ -80,6 +84,6 @@ ORDER BY
    logContent += `${formattedRow}\n`;
    }
    );
    fs.writeFileSync('access.log', logContent, 'utf-8')
    fs.writeFileSync(logName, logContent, 'utf-8')
    }
    createLogfile();
  2. dwsmart created this gist Aug 3, 2021.
    85 changes: 85 additions & 0 deletions logflare_to_common_log_format.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,85 @@
    // 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}',
    };
    const bigquery = new BigQuery(options);

    // 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()`;



    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('access.log', logContent, 'utf-8')
    }
    createLogfile();