401 lines
18 KiB
JavaScript
401 lines
18 KiB
JavaScript
/**
|
|
* generate_export.js
|
|
*
|
|
* Mengekspor SELURUH data dari semua tabel SQLite (database)
|
|
* ke dalam file backone_data_export.txt
|
|
*
|
|
* Format output:
|
|
* - Header metadata (tanggal, versi, jumlah tabel)
|
|
* - Untuk setiap tabel: header section, row count, schema, dan semua data (JSON per baris)
|
|
* - Footer summary
|
|
*/
|
|
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const db = require('./database');
|
|
|
|
const OUTPUT_FILE = path.join(__dirname, '../backone_data_export.txt');
|
|
const d = db.getDB();
|
|
|
|
// ─── Helpers ────────────────────────────────────────────────────────────────
|
|
function fmtBytes(bytes) {
|
|
if (!bytes || bytes === 0) return '0 B';
|
|
const units = ['B', 'KB', 'MB', 'GB', 'TB'];
|
|
let b = Math.abs(bytes);
|
|
let i = 0;
|
|
while (b >= 1024 && i < units.length - 1) { b /= 1024; i++; }
|
|
return b.toFixed(2) + ' ' + units[i];
|
|
}
|
|
|
|
function fmtNum(n) {
|
|
if (n == null) return 'N/A';
|
|
return Number(n).toLocaleString('id-ID');
|
|
}
|
|
|
|
function separator(char = '═', len = 80) {
|
|
return char.repeat(len);
|
|
}
|
|
|
|
function sectionHeader(tableName, rowCount, description) {
|
|
return [
|
|
'',
|
|
separator('═'),
|
|
`[TABLE: ${tableName}]`,
|
|
`Row Count: ${fmtNum(rowCount)}`,
|
|
description ? `Description: ${description}` : '',
|
|
separator('─'),
|
|
].filter(l => l !== '').join('\n');
|
|
}
|
|
|
|
// ─── Table descriptions ──────────────────────────────────────────────────────
|
|
const TABLE_DESCRIPTIONS = {
|
|
bandwidth_apps : 'Bandwidth per aplikasi (YouTube, Facebook, dll) dari BackOne DPI',
|
|
bandwidth_timeline : 'Timeline bandwidth per menit (download/upload historis)',
|
|
bittorrent_info_hashes: 'Deteksi aktivitas BitTorrent berdasarkan info hash',
|
|
countries : 'Distribusi traffic berdasarkan negara tujuan',
|
|
devices : 'Daftar perangkat (IP/MAC) beserta bandwidth & OS',
|
|
dhcp_fingerprints : 'Fingerprint DHCP untuk identifikasi tipe device',
|
|
discovered_os : 'OS yang terdeteksi dari traffic scanning',
|
|
dns_stats : 'Query DNS teratas dan statistik resolusi domain',
|
|
events : 'Event log dari BackOne agent (koneksi, peringatan, dll)',
|
|
flows : 'Data aliran jaringan per-sesi (src IP, dst IP, aplikasi, domain, bytes)',
|
|
flow_origins : 'Asal flow: lokal (LAN) atau eksternal (WAN)',
|
|
flow_types : 'Tipe flow: TCP, UDP, ICMP, dll',
|
|
http_user_agents : 'HTTP User-Agent yang terdeteksi (browser, OS, framework)',
|
|
intel_crypto_mining : 'Deteksi aktivitas crypto mining (pool host, protokol)',
|
|
intel_device_discovery: 'Penemuan perangkat baru di jaringan (tipe, OS, manufaktur)',
|
|
intel_encryption_audit: 'Audit enkripsi traffic per perangkat (encrypted%, risk level)',
|
|
intel_insecure_protocols: 'Protokol tidak aman yang terdeteksi (HTTP, Telnet, FTP, dll)',
|
|
intel_ip_reputation : 'Reputasi IP eksternal (blacklist, threat score)',
|
|
intel_server_discovery: 'Server yang terdeteksi (HTTPS, SSH, HTTP, dll)',
|
|
intel_tor_detection : 'Deteksi penggunaan jaringan Tor',
|
|
intel_unencrypted_passwords: 'Deteksi pengiriman password dalam bentuk plaintext',
|
|
intel_vpn_detection : 'Deteksi penggunaan VPN (OpenVPN, WireGuard, dll)',
|
|
interfaces : 'Interface jaringan per agent (WAN/LAN, bandwidth)',
|
|
ip_versions : 'Distribusi traffic IPv4 vs IPv6',
|
|
mac_bandwidth : 'Bandwidth per MAC address perangkat',
|
|
mdns_hostnames : 'mDNS hostname yang terdeteksi di jaringan lokal',
|
|
netbios_hostnames : 'NetBIOS hostname (nama komputer Windows)',
|
|
protocols : 'Distribusi protokol jaringan (port usage)',
|
|
quic_hostnames : 'Hostname via QUIC/HTTP3 (Google, Cloudflare, dll)',
|
|
regions : 'Distribusi traffic berdasarkan region/kota tujuan',
|
|
remote_ips : 'IP remote teratas yang diakses perangkat',
|
|
sni_hostnames : 'Server Name Indication dari koneksi TLS',
|
|
ssh_versions : 'Versi SSH yang terdeteksi di jaringan',
|
|
ssl_server_cn : 'Common Name sertifikat SSL server',
|
|
threats : 'Ancaman keamanan terdeteksi (threat alerts)',
|
|
tls_ciphers : 'Cipher suite TLS yang digunakan',
|
|
tls_security : 'Tingkat keamanan TLS (Modern, Compatible, Old)',
|
|
tls_versions : 'Versi TLS yang digunakan (1.0, 1.2, 1.3)',
|
|
vlans : 'VLAN yang terdeteksi di jaringan',
|
|
};
|
|
|
|
// ─── Main Export Logic ───────────────────────────────────────────────────────
|
|
async function main() {
|
|
console.log('🚀 Memulai export data...');
|
|
|
|
const exportDate = new Date().toISOString();
|
|
const lines = [];
|
|
|
|
// ── File Header ──────────────────────────────────────────────────────────
|
|
lines.push(separator('═'));
|
|
lines.push(' BACKONE DATA EXPORT');
|
|
lines.push(' Seluruh data hasil parsing dari BackOne API');
|
|
lines.push(separator('─'));
|
|
lines.push(` Export Date: ${exportDate}`);
|
|
lines.push(` Generated by: generate_export.js`);
|
|
lines.push(` Source: database (SQLite lokal)`);
|
|
lines.push(` API Base: BackOne API Service`);
|
|
lines.push(` Format: Per-tabel, data JSON satu record per baris (JSONL)`);
|
|
lines.push(separator('─'));
|
|
|
|
// ── Get all tables ────────────────────────────────────────────────────────
|
|
const tables = d.prepare(
|
|
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
|
|
).all().map(r => r.name);
|
|
|
|
lines.push(` Total Tables: ${tables.length}`);
|
|
lines.push(separator('═'));
|
|
lines.push('');
|
|
|
|
// ── Table of Contents ─────────────────────────────────────────────────────
|
|
lines.push('TABLE OF CONTENTS');
|
|
lines.push(separator('─', 40));
|
|
let totalRows = 0;
|
|
const tableSummaries = [];
|
|
for (const tableName of tables) {
|
|
const cnt = d.prepare(`SELECT COUNT(*) as c FROM ${tableName}`).get().c;
|
|
totalRows += cnt;
|
|
const desc = TABLE_DESCRIPTIONS[tableName] || '-';
|
|
lines.push(` ${tableName.padEnd(35)} ${String(cnt).padStart(8)} rows`);
|
|
tableSummaries.push({ name: tableName, count: cnt, description: desc });
|
|
}
|
|
lines.push(separator('─', 40));
|
|
lines.push(` ${'TOTAL'.padEnd(35)} ${String(totalRows).padStart(8)} rows`);
|
|
lines.push('');
|
|
|
|
// ── Per-Table Export ──────────────────────────────────────────────────────
|
|
for (const { name: tableName, count, description } of tableSummaries) {
|
|
console.log(` 📋 Exporting: ${tableName} (${fmtNum(count)} rows)...`);
|
|
|
|
// Section header
|
|
lines.push(sectionHeader(tableName, count, description));
|
|
|
|
// Schema
|
|
const cols = d.prepare(`PRAGMA table_info(${tableName})`).all();
|
|
lines.push('Schema:');
|
|
cols.forEach(c => {
|
|
lines.push(` - ${c.name} [${c.type || 'TEXT'}]${c.notnull ? ' NOT NULL' : ''}${c.pk ? ' PRIMARY KEY' : ''}`);
|
|
});
|
|
lines.push('');
|
|
|
|
// Statistics for numeric columns
|
|
const numericCols = cols.filter(c =>
|
|
['INTEGER', 'REAL', 'NUMERIC'].includes((c.type || '').toUpperCase()) &&
|
|
!['id'].includes(c.name.toLowerCase())
|
|
);
|
|
|
|
if (count > 0 && numericCols.length > 0) {
|
|
lines.push('Statistics:');
|
|
for (const col of numericCols.slice(0, 5)) { // max 5 numeric cols
|
|
try {
|
|
const stat = d.prepare(`
|
|
SELECT MIN(${col.name}) as min, MAX(${col.name}) as max,
|
|
AVG(${col.name}) as avg, SUM(${col.name}) as total
|
|
FROM ${tableName}
|
|
`).get();
|
|
if (stat && stat.max !== null) {
|
|
lines.push(` ${col.name}: min=${fmtNum(stat.min)} max=${fmtNum(stat.max)} avg=${Number(stat.avg || 0).toFixed(2)} total=${fmtNum(stat.total)}`);
|
|
}
|
|
} catch(e) { /* skip */ }
|
|
}
|
|
lines.push('');
|
|
}
|
|
|
|
// Data rows (ALL rows)
|
|
if (count === 0) {
|
|
lines.push('(No data)');
|
|
} else {
|
|
lines.push(`Data (${fmtNum(count)} records):`);
|
|
const rows = d.prepare(`SELECT * FROM ${tableName}`).all();
|
|
for (const row of rows) {
|
|
lines.push(JSON.stringify(row));
|
|
}
|
|
}
|
|
lines.push('');
|
|
}
|
|
|
|
// ── Agent-specific sections (derived from flows) ───────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: AGENT ANALYSIS]');
|
|
lines.push('Description: Analisis traffic per agent berdasarkan MAC address dari flows table');
|
|
lines.push(separator('─'));
|
|
|
|
const AGENT_MAC_MAP = {
|
|
'2F-TF-1D-GK': { label: 'JRP Cibubur', macs: ['60:be:b4:1f:05:96'] },
|
|
'8A-V3-PB-85': { label: 'IFG LT.18', macs: ['04:f4:1c:ce:c2:e6'] },
|
|
'F6-2V-DT-8A': { label: 'CPI Balaraja', macs: ['2c:7b:a0:d8:86:91', '16:11:ac:73:34:1d', 'bc:45:5b:ca:d5:be', 'de:ed:cc:57:58:34', 'f4:6d:3f:ef:01:a0', '60:be:b4:29:d3:36'] },
|
|
};
|
|
|
|
for (const [uuid, agent] of Object.entries(AGENT_MAC_MAP)) {
|
|
lines.push('');
|
|
lines.push(`Agent: ${agent.label} (${uuid})`);
|
|
lines.push(`MACs: ${agent.macs.join(', ')}`);
|
|
lines.push(separator('─', 40));
|
|
|
|
const ph = agent.macs.map(() => '?').join(',');
|
|
|
|
// Summary
|
|
const sumRow = d.prepare(`
|
|
SELECT COUNT(DISTINCT src_ip) AS device_count, COUNT(*) AS flow_count,
|
|
SUM(bytes_download) AS total_dl, SUM(bytes_upload) AS total_ul
|
|
FROM flows WHERE src_mac IN (${ph})
|
|
`).get(...agent.macs);
|
|
|
|
lines.push(` Devices: ${fmtNum(sumRow.device_count)}`);
|
|
lines.push(` Total Flows: ${fmtNum(sumRow.flow_count)}`);
|
|
lines.push(` Total Download: ${fmtBytes(sumRow.total_dl)}`);
|
|
lines.push(` Total Upload: ${fmtBytes(sumRow.total_ul)}`);
|
|
|
|
// Top apps
|
|
const apps = d.prepare(`
|
|
SELECT app_label, SUM(bytes_download) AS dl, SUM(bytes_upload) AS ul, COUNT(*) AS cnt
|
|
FROM flows WHERE src_mac IN (${ph}) AND app_label IS NOT NULL
|
|
GROUP BY app_label ORDER BY dl DESC LIMIT 10
|
|
`).all(...agent.macs);
|
|
|
|
lines.push(` Top Applications:`);
|
|
apps.forEach((a, i) => {
|
|
lines.push(` ${String(i+1).padStart(2)}. ${(a.app_label||'?').padEnd(30)} DL:${fmtBytes(a.dl).padStart(12)} UL:${fmtBytes(a.ul).padStart(12)} Flows:${a.cnt}`);
|
|
});
|
|
|
|
// Top devices
|
|
const devs = d.prepare(`
|
|
SELECT src_ip, SUM(bytes_download) AS dl, MAX(last_seen) AS last
|
|
FROM flows WHERE src_mac IN (${ph})
|
|
GROUP BY src_ip ORDER BY dl DESC LIMIT 10
|
|
`).all(...agent.macs);
|
|
|
|
lines.push(` Top Devices:`);
|
|
devs.forEach((d2, i) => {
|
|
lines.push(` ${String(i+1).padStart(2)}. ${(d2.src_ip||'?').padEnd(20)} DL:${fmtBytes(d2.dl).padStart(12)} Last:${d2.last||'-'}`);
|
|
});
|
|
}
|
|
|
|
// ── Bandwidth Apps Summary ─────────────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: BANDWIDTH APPS LATEST SNAPSHOT]');
|
|
lines.push('Description: Snapshot terakhir bandwidth per aplikasi (nilai aktual, bukan akumulasi)');
|
|
lines.push(separator('─'));
|
|
|
|
const latestBwSnap = d.prepare('SELECT MAX(fetched_at) as t FROM bandwidth_apps').get()?.t;
|
|
if (latestBwSnap) {
|
|
lines.push(`Latest Snapshot: ${latestBwSnap}`);
|
|
const bwApps = d.prepare('SELECT app_label, category, download, upload, total, flow_count FROM bandwidth_apps WHERE fetched_at = ? ORDER BY download DESC').all(latestBwSnap);
|
|
lines.push(`Total Apps: ${bwApps.length}`);
|
|
lines.push('');
|
|
bwApps.forEach((a, i) => {
|
|
lines.push(` ${String(i+1).padStart(3)}. ${(a.app_label||'?').padEnd(30)} [${(a.category||'?').padEnd(20)}] DL:${fmtBytes(a.download).padStart(12)} UL:${fmtBytes(a.upload).padStart(12)} Flows:${fmtNum(a.flow_count)}`);
|
|
});
|
|
}
|
|
|
|
// ── Encryption Audit Summary ───────────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: ENCRYPTION RISK SUMMARY]');
|
|
lines.push('Description: Distribusi risk level enkripsi per perangkat (snapshot terbaru)');
|
|
lines.push(separator('─'));
|
|
|
|
const latestEncSnap = d.prepare('SELECT MAX(fetched_at) as t FROM intel_encryption_audit').get()?.t;
|
|
if (latestEncSnap) {
|
|
const riskDist = d.prepare(`
|
|
SELECT risk_level, COUNT(*) as cnt, AVG(encrypted_pct) as avg_enc
|
|
FROM intel_encryption_audit WHERE fetched_at = ?
|
|
GROUP BY risk_level ORDER BY cnt DESC
|
|
`).all(latestEncSnap);
|
|
|
|
lines.push(`Latest Snapshot: ${latestEncSnap}`);
|
|
lines.push('Risk Distribution:');
|
|
riskDist.forEach(r => {
|
|
lines.push(` ${(r.risk_level||'Unknown').padEnd(15)} ${String(r.cnt).padStart(5)} devices avg encrypted: ${Number(r.avg_enc||0).toFixed(1)}%`);
|
|
});
|
|
|
|
// Highest risk devices
|
|
lines.push('');
|
|
lines.push('Critical Risk Devices (0% encrypted):');
|
|
const critDevs = d.prepare(`
|
|
SELECT ip_address, mac_address, device_label, encrypted_pct, total
|
|
FROM intel_encryption_audit WHERE fetched_at = ? AND risk_level = 'Critical'
|
|
ORDER BY total DESC LIMIT 20
|
|
`).all(latestEncSnap);
|
|
critDevs.forEach(r => {
|
|
lines.push(JSON.stringify(r));
|
|
});
|
|
}
|
|
|
|
// ── DNS Top Domains ────────────────────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: TOP DNS DOMAINS]');
|
|
lines.push('Description: Domain paling sering diquery dari DNS stats');
|
|
lines.push(separator('─'));
|
|
|
|
const latestDnsSnap = d.prepare('SELECT MAX(fetched_at) as t FROM dns_queries').get()?.t;
|
|
if (latestDnsSnap) {
|
|
const dnsRows = d.prepare('SELECT * FROM dns_queries WHERE fetched_at = ? ORDER BY query_count DESC LIMIT 30').all(latestDnsSnap);
|
|
|
|
lines.push(`Latest Snapshot: ${latestDnsSnap}`);
|
|
dnsRows.forEach(r => lines.push(JSON.stringify(r)));
|
|
}
|
|
|
|
// ── IP Reputation Blacklisted ─────────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: BLACKLISTED IP ADDRESSES]');
|
|
lines.push('Description: IP address yang terdeteksi blacklisted (dari intel_ip_reputation)');
|
|
lines.push(separator('─'));
|
|
|
|
const latestRepSnap = d.prepare('SELECT MAX(fetched_at) as t FROM intel_ip_reputation').get()?.t;
|
|
if (latestRepSnap) {
|
|
const blacklisted = d.prepare('SELECT * FROM intel_ip_reputation WHERE fetched_at = ? AND blacklisted = 1').all(latestRepSnap);
|
|
lines.push(`Latest Snapshot: ${latestRepSnap}`);
|
|
lines.push(`Blacklisted count: ${blacklisted.length}`);
|
|
blacklisted.forEach(r => lines.push(JSON.stringify(r)));
|
|
}
|
|
|
|
// ── Flows: Active Sessions Summary ────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: ACTIVE FLOWS SUMMARY]');
|
|
lines.push('Description: Ringkasan aliran jaringan aktif (50 terbaru per download)');
|
|
lines.push(separator('─'));
|
|
|
|
const flowSummary = d.prepare(`
|
|
SELECT COUNT(*) as total_flows,
|
|
COUNT(DISTINCT src_ip) as unique_src_ips,
|
|
COUNT(DISTINCT dst_ip) as unique_dst_ips,
|
|
COUNT(DISTINCT src_mac) as unique_macs,
|
|
SUM(bytes_download) as total_dl,
|
|
SUM(bytes_upload) as total_ul,
|
|
MIN(first_seen) as earliest,
|
|
MAX(last_seen) as latest
|
|
FROM flows
|
|
`).get();
|
|
|
|
lines.push(`Total Flows in DB: ${fmtNum(flowSummary.total_flows)}`);
|
|
lines.push(`Unique Source IPs: ${fmtNum(flowSummary.unique_src_ips)}`);
|
|
lines.push(`Unique Dest IPs: ${fmtNum(flowSummary.unique_dst_ips)}`);
|
|
lines.push(`Unique MAC Addresses: ${fmtNum(flowSummary.unique_macs)}`);
|
|
lines.push(`Total Download: ${fmtBytes(flowSummary.total_dl)}`);
|
|
lines.push(`Total Upload: ${fmtBytes(flowSummary.total_ul)}`);
|
|
lines.push(`Data from: ${flowSummary.earliest}`);
|
|
lines.push(`Data to: ${flowSummary.latest}`);
|
|
lines.push('');
|
|
|
|
// Top 50 flows by download
|
|
lines.push('Top 50 Flows by Download:');
|
|
const topFlows = d.prepare('SELECT * FROM flows ORDER BY bytes_download DESC LIMIT 50').all();
|
|
topFlows.forEach(r => lines.push(JSON.stringify(r)));
|
|
|
|
// ── Unencrypted Password Events ───────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push('[DERIVED: UNENCRYPTED PASSWORD DETECTIONS]');
|
|
lines.push('Description: Kejadian pengiriman credential dalam plaintext (HIGH severity)');
|
|
lines.push(separator('─'));
|
|
|
|
const unencPwdHigh = d.prepare(`
|
|
SELECT ip_address, mac_address, dst_ip, dst_port, protocol, username, download, upload, severity, detected_at
|
|
FROM intel_unencrypted_passwords ORDER BY detected_at DESC
|
|
`).all();
|
|
lines.push(`Total detections: ${unencPwdHigh.length}`);
|
|
unencPwdHigh.forEach(r => lines.push(JSON.stringify(r)));
|
|
|
|
// ── Footer ────────────────────────────────────────────────────────────────
|
|
lines.push('');
|
|
lines.push(separator('═'));
|
|
lines.push(' END OF EXPORT');
|
|
lines.push(` Generated at: ${new Date().toISOString()}`);
|
|
lines.push(` Total lines: ${lines.length + 3}`);
|
|
lines.push(separator('═'));
|
|
|
|
// Write to file
|
|
const output = lines.join('\n');
|
|
fs.writeFileSync(OUTPUT_FILE, output, 'utf-8');
|
|
|
|
const stats = fs.statSync(OUTPUT_FILE);
|
|
console.log(`\n✅ Export selesai!`);
|
|
console.log(` File: ${OUTPUT_FILE}`);
|
|
console.log(` Size: ${fmtBytes(stats.size)}`);
|
|
console.log(` Lines: ${fmtNum(lines.length)}`);
|
|
console.log(` Tables: ${tables.length}`);
|
|
console.log(` Total Rows: ${fmtNum(totalRows)}`);
|
|
}
|
|
|
|
main().catch(e => {
|
|
console.error('❌ Export FAILED:', e);
|
|
process.exit(1);
|
|
});
|