196 lines
8.4 KiB
JavaScript
196 lines
8.4 KiB
JavaScript
// SQL Server statement / procedure live monitor
|
||
// -------------------------------------------------
|
||
// • Starts a dedicated Extended-Event session on the SQL-Server instance.
|
||
// • Streams finished RPC & batch events every second.
|
||
// • Each event (sql-text, parameters, duration, row-count …) is appended
|
||
// to a session-specific log-file using fs.appendFileSync so the file
|
||
// can be watched while it grows (e.g. with “tail-f”).
|
||
// • A fresh log directory is created per run. Left-overs from previous
|
||
// runs (old XE sessions + log files) are removed automatically and
|
||
// again on Ctrl-C / SIGTERM.
|
||
//
|
||
// REQUIREMENTS
|
||
// -------------
|
||
// • npm install mssql (already present in package.json)
|
||
// • The executing principal must have ALTER ANY EVENT SESSION permission.
|
||
//
|
||
// USAGE
|
||
// ------
|
||
// > node mssql_monitor.js # uses env-vars for credentials
|
||
//
|
||
// Environment variables recognised:
|
||
// SQLSERVER ( default: 'localhost' )
|
||
// SQLUSER
|
||
// SQLPASSWORD
|
||
// SQLDATABASE ( default: 'master' )
|
||
|
||
/* eslint-disable no-console */
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
const sql = require('mssql');
|
||
|
||
// ---------- configuration ----------------------------------------------------
|
||
const cfg = {
|
||
server : process.env.SQLSERVER || '10.10.10.3',
|
||
port : process.env.SQLPORT || 1433, // Add port configuration
|
||
user : process.env.SQLUSER || 'sa',
|
||
password: process.env.SQLPASSWORD || 'sa_tekno23',
|
||
database: process.env.SQLDATABASE || 'eazybusiness',
|
||
options : { encrypt: false, trustServerCertificate: true }
|
||
};
|
||
|
||
// delay (ms) between polling the ring-buffer for new events
|
||
const POLL_INTERVAL = 1_000;
|
||
// keep log-files / XE sessions younger than:
|
||
const KEEP_HOURS = 24;
|
||
|
||
// -----------------------------------------------------------------------------
|
||
(async function main () {
|
||
const sessionName = `js_monitor_${Date.now()}`; // XE session
|
||
const logDir = path.join(__dirname, 'monitor_logs');
|
||
fs.mkdirSync(logDir, { recursive: true });
|
||
await cleanupLeftovers(logDir, sessionName); // old sessions
|
||
const pool = await sql.connect(cfg); // connect
|
||
await createXeSession(pool, sessionName); // XE start
|
||
console.log(`Monitoring started → ${logDir} (per client session)`);
|
||
|
||
let lastRead = new Date(0); // first run
|
||
|
||
// -------------------------------------------------------------------------
|
||
const timer = setInterval(async () => {
|
||
try {
|
||
const events = await fetchNewEvents(pool, sessionName, lastRead);
|
||
for (const ev of events) {
|
||
// Compose a unique log file name per client session
|
||
const sid = ev.session_id || 'unknown';
|
||
const user = (ev.username || 'unknown').replace(/[\\/:<>|?*"]/g, '_');
|
||
const host = (ev.client_hostname || 'unknown').replace(/[\\/:<>|?*"]/g, '_');
|
||
const perClientFile = path.join(
|
||
logDir,
|
||
`js_monitor_${user}_${host}_sid${sid}.log`
|
||
);
|
||
// Remove fields not needed in log entry
|
||
const { username, session_id, client_hostname, ...logEntry } = ev;
|
||
fs.appendFileSync(perClientFile, JSON.stringify(logEntry,null,2) + '\n', 'utf8');
|
||
lastRead = ev.timestamp;
|
||
}
|
||
} catch (e) { console.error('Polling error:', e); }
|
||
}, POLL_INTERVAL);
|
||
|
||
// graceful shutdown -------------------------------------------------------
|
||
const shutdown = async () => {
|
||
console.log('\nCtrl-C received, cleaning up...');
|
||
clearInterval(timer);
|
||
await dropXeSession(pool, sessionName);
|
||
await pool.close();
|
||
console.log('Monitor stopped and cleaned-up.');
|
||
process.exit(0);
|
||
};
|
||
process.on('SIGINT' , shutdown);
|
||
process.on('SIGTERM', shutdown);
|
||
})().catch(e => { console.error(e); process.exit(1); });
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
async function createXeSession (pool, name) {
|
||
const batch = `
|
||
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = '${name}')
|
||
DROP EVENT SESSION [${name}] ON SERVER;
|
||
|
||
DECLARE @sql NVARCHAR(MAX) = '
|
||
CREATE EVENT SESSION [${name}] ON SERVER
|
||
ADD EVENT sqlserver.rpc_completed
|
||
(SET collect_statement=(1)
|
||
ACTION(sqlserver.sql_text, sqlserver.username, sqlserver.session_id, sqlserver.client_hostname)),
|
||
ADD EVENT sqlserver.sql_batch_completed
|
||
(ACTION(sqlserver.sql_text, sqlserver.username, sqlserver.session_id, sqlserver.client_hostname))
|
||
ADD TARGET package0.ring_buffer
|
||
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS);
|
||
';
|
||
EXEC (@sql);
|
||
|
||
ALTER EVENT SESSION [${name}] ON SERVER STATE = START;`;
|
||
await pool.request().batch(batch);
|
||
}
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
async function dropXeSession (pool, name) {
|
||
const cmd = `
|
||
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @name)
|
||
BEGIN
|
||
ALTER EVENT SESSION [${name}] ON SERVER STATE = STOP;
|
||
DROP EVENT SESSION [${name}] ON SERVER;
|
||
END`;
|
||
await pool.request().input('name', sql.NVarChar, name).batch(cmd);
|
||
}
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
async function fetchNewEvents (pool, name, last) {
|
||
// convert last JS Date → SQL datetime2
|
||
const lastTS = last.toISOString();
|
||
|
||
const query = `
|
||
;WITH src AS (
|
||
SELECT CAST(t.target_data AS XML) AS x
|
||
FROM sys.dm_xe_session_targets AS t
|
||
JOIN sys.dm_xe_sessions AS s
|
||
ON t.event_session_address = s.address
|
||
WHERE s.name = @name
|
||
AND t.target_name = 'ring_buffer'
|
||
)
|
||
SELECT
|
||
evt.value('@timestamp', 'datetime2') AS [timestamp],
|
||
evt.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)')
|
||
AS statement_text,
|
||
evt.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)')
|
||
AS batch_text,
|
||
evt.value('(data[@name="object_name"]/value)[1]', 'nvarchar(max)')
|
||
AS object_name,
|
||
evt.value('(data[@name="row_count"]/value)[1]', 'bigint')
|
||
AS rows,
|
||
evt.value('(data[@name="duration"]/value)[1]', 'bigint') / 1000
|
||
AS duration_ms,
|
||
evt.value('(action[@name="username"]/value)[1]', 'nvarchar(128)')
|
||
AS username,
|
||
evt.value('(action[@name="session_id"]/value)[1]', 'int')
|
||
AS session_id,
|
||
evt.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(128)')
|
||
AS client_hostname
|
||
FROM src
|
||
CROSS APPLY x.nodes('//RingBufferTarget/event') n(evt)
|
||
WHERE evt.value('@timestamp', 'datetime2') > @last
|
||
ORDER BY [timestamp];`;
|
||
|
||
const rs = await pool.request()
|
||
.input('name', sql.NVarChar, name)
|
||
.input('last', sql.DateTime2, lastTS)
|
||
.query(query);
|
||
|
||
// ensure proper JS dates
|
||
return rs.recordset.map(r => ({ ...r, timestamp: new Date(r.timestamp) }));
|
||
}
|
||
|
||
/* -------------------------------------------------------------------------- */
|
||
async function cleanupLeftovers (logDir, currentSession) {
|
||
// delete old log files ----------------------------------------------------
|
||
const now = Date.now();
|
||
for (const f of fs.readdirSync(logDir)) {
|
||
const p = path.join(logDir, f);
|
||
try {
|
||
const stat = fs.statSync(p);
|
||
if (now - stat.mtimeMs > KEEP_HOURS * 3_600_000) fs.rmSync(p);
|
||
} catch (_) { /* ignore */ }
|
||
}
|
||
|
||
// stop / drop stale XE sessions ------------------------------------------
|
||
try {
|
||
const pool = await sql.connect(cfg);
|
||
const rs = await pool.request()
|
||
.query(`SELECT name FROM sys.server_event_sessions
|
||
WHERE name LIKE 'js_monitor_%'`);
|
||
for (const { name } of rs.recordset) {
|
||
if (name !== currentSession) await dropXeSession(pool, name);
|
||
}
|
||
await pool.close();
|
||
} catch (e) { /* might lack permission – ignore */ }
|
||
}
|