Fetch_Opsgenie_Alerts_DB_To_Incidents_API.js

const { Client } = require('pg');
const axios = require('axios');

// Set debug mode
const debug = true;  // Set to false to make actual API calls

// Database connection string
const connectionString = [CONNECTION STRING FROM DX]

// Create a new PostgreSQL client
const client = new Client({
  connectionString: connectionString,
});

async function runQueryAndSendRequests() {
  try {
    // Connect to the database
    await client.connect();

    // SQL query
    const query = `
      SELECT oa.source_id as reference_id,
  dt.name as service,
  oa.message as name,
  oa.created_at as started_at,
  oa.created_at + (oa.close_time * INTERVAL '1 second') as finished_at,
  oa.priority
FROM opsgenie_alerts oa
  LEFT OUTER JOIN dx_users du ON LOWER(du.email) = LOWER(oa.owner)
  LEFT OUTER JOIN dx_teams dt ON dt.id = du.team_id
WHERE oa.priority IN ('P1', 'P2')
  AND LOWER(oa.message) NOT LIKE '%test%'
  AND oa.source_id NOT IN (
    select source_id
    from incidents
  )
ORDER BY oa.created_at asc;
    `;

    // Execute the query
    const res = await client.query(query);

    // Iterate over each row in the result
    for (const row of res.rows) {
      const apiPayload = {
        reference_id: row.reference_id,
        services: row.services ? row.services.split(',') : [], // Assuming services are comma-separated
        name: row.name,
        started_at: row.started_at,
        resolved_at: row.resolved_at
      };

      // Log the payload for debugging
      console.log(`Prepared payload for ${row.reference_id}:`, JSON.stringify(apiPayload, null, 2));

      if (!debug) {
        // Send POST request to the DX Incidents API
        try {
          const apiResponse = await axios.post('https://api.getdx.com/incidents', apiPayload, {
            headers: {
              Authorization: `Bearer YOUR_API_TOKEN`,  // Replace with your actual API token
              'Content-Type': 'application/json'
            }
          });

          console.log(`Successfully sent incident: ${row.reference_id}`, apiResponse.data);
        } catch (error) {
          console.error(`Error sending incident ${row.reference_id}:`, error.message);
        }
      } else {
        console.log(`Debug mode is ON. No actual API call made for ${row.reference_id}.`);
      }
    }

  } catch (err) {
    console.error('Database query error:', err.stack);
  } finally {
    // Close the database connection
    await client.end();
  }
}

// Run the function
runQueryAndSendRequests();