clients.crate = function(query, connection, callback) {
var crateConfig = {
host: connection.host
};
if (connection.port) {
crate.connect(crateConfig.host, connection.port);
} else {
crate.connect(crateConfig.host);
}
query = query.replace(/\;$/, '');
crate.execute(query).success(function (res) {
var results = {
rows: [],
fields: [],
incomplete: false
}
for (row in res.rows) {
results.rows[row] = {};
for(val in res.rows[row]) {
col_name = res.cols[val];
type = res.col_types[val];
val = res.rows[row][val];
if ( type === 11) {
val = new Date(val);
}
results.rows[row][col_name] = val;
results.fields[row] = col_name;
}
}
callback(null, results);
}).error(function(err) {
callback(err.message);
});
}
router.get('/test2', async ctx => {
const { min, max } = ctx.query;
if (!min || !max)
ctx.throw(400, 'Must specify min and max in query string.');
const minDate = moment.utc(min, moment.ISO_8601);
const maxDate = moment.utc(max, moment.ISO_8601);
if (!minDate.isValid() || !maxDate.isValid())
ctx.throw(400, 'Min and max must be ISO 8601 date strings');
const entries = await crate.execute(
'SELECT * FROM logs WHERE time BETWEEN ? AND ? LIMIT ?',
[minDate.toDate(), maxDate.toDate(), max_limit]
);
var res = [];
var k=0;
for(var i = 0; i < entries.json.length; i++)
{
var flag = 0;
for(var j = 0; j < res.length; j++)
{
if(res[j].value == entries.json[i].contentLength)
flag = 1;
}
if(flag == 0)
{
res[k] = {};
res[k].value = entries.json[i].contentLength;
res[k++].unit = entries.json[i].ms;
}
if(res.length == 50)
break;
}
var data = res;
res = {};
res.data = data;
var myData = {};
myData.JSChart = {};
myData.JSChart.datasets = [];
myData.JSChart.datasets[0] = {};
myData.JSChart.datasets[0].type = "line";
myData.JSChart.datasets[0].data = data;
fs.writeFileSync('../public/myjsonfile.json', JSON.stringify(myData));
console.log("json file written");
ctx.status = 200;
ctx.body = res;
});
router.get('/test1', async ctx => {
const { min, max } = ctx.query;
if (!min || !max)
ctx.throw(400, 'Must specify min and max in query string.');
const minDate = moment.utc(min, moment.ISO_8601);
const maxDate = moment.utc(max, moment.ISO_8601);
if (!minDate.isValid() || !maxDate.isValid())
ctx.throw(400, 'Min and max must be ISO 8601 date strings');
const entries = await crate.execute(
'SELECT * FROM logs WHERE time BETWEEN ? AND ? LIMIT ?',
[minDate.toDate(), maxDate.toDate(), max_limit]
);
var res = [];
var conLen = [];
var resTime = [];
var conLenSum = 0;
var resTimeSum = 0;
for(var i = 0; i < entries.json.length; i++)
{
res[i] = {};
res[i].contentLength = entries.json[i].contentLength;
res[i].ms = entries.json[i].ms;
res[i].time = entries.json[i].time;
conLen.push(entries.json[i].contentLength);
resTime.push(entries.json[i].ms);
conLenSum = conLenSum + Number(entries.json[i].contentLength);
resTimeSum = resTimeSum + Number(entries.json[i].ms);
}
var data = res;
res = {};
res.data = data;
res.stat = {};
res.stat.minConLen = Math.min.apply(null, conLen);
res.stat.maxConLen = Math.max.apply(null, conLen);
res.stat.minResTime = Math.min.apply(null, resTime);
res.stat.maxResTime = Math.max.apply(null, resTime);
res.stat.avgConLen = (conLenSum/conLen.length).toFixed(3);
res.stat.avgResTime = (resTimeSum/resTime.length).toFixed(3);
conLen.sort();
resTime.sort();
res.stat.medConLen = conLen[Math.floor(conLen.length/2)];
res.stat.medResTime = resTime[Math.floor(resTime.length/2)];
ctx.status = 200;
ctx.body = res;
});
router.get('/logs/cratedb', async ctx => {
const { min, max } = ctx.query;
if (!min || !max)
ctx.throw(400, 'Must specify min and max in query string.');
const minDate = moment.utc(min, moment.ISO_8601);
const maxDate = moment.utc(max, moment.ISO_8601);
if (!minDate.isValid() || !maxDate.isValid())
ctx.throw(400, 'Min and max must be ISO 8601 date strings');
const entries = await crate.execute(
'SELECT * FROM logs WHERE time BETWEEN ? AND ? LIMIT ?',
[minDate.toDate(), maxDate.toDate(), max_limit]
);
ctx.status = 200;
ctx.body = entries.json;
});
function updateWhois ()
{
currentRun = lastRun
lastRun = new Date()
crate.execute (NEW_IPS_SQL, [currentRun]).success (function (res){
console.log (res)
if (res && res.json && res.json.length > 0)
{
// doing lookup for all unique IP's in the logfile
res.json.forEach (function (e){
whois.lookup (e.client_ip, function (whoisInfo) {
crate.update ('web_log',
{ whois_info: whoisInfo.description },
"client_ip = '$IP' AND whois_info is null".replace ("$IP", e.client_ip ))
.error (console.log)
.success (console.log)
})
})
}
})
}
sqlSection.addEventListener('click', function(event) {
var code = event.target;
while (code.tagName.toUpperCase() !== "CODE") {
code = code.parentNode;
}
var wrapperElem = getEmptyWrapper(code);
var sqlCommand = code.textContent;
if (sqlCommand.charAt(sqlCommand.length - 1) === ';') {
sqlCommand = sqlCommand.substr(0, sqlCommand.length - 1)
}
console.log(sqlCommand);
crate.execute(sqlCommand).success(function(res) {
var wrapperElem = getEmptyWrapper(code);
if (res.error) {
res.cols = ["Error"];
res.rows = [
[res.error]
];
} else if (res.rows.length == 0 && res.rowcount > 0) {
res.cols = ["Duration", "Rowcount"];
res.rows = [
[res.duration, res.rowcount]
];
}
// DURATION
var durationElem = document.createElement("p");
durationElem.classList.add("crate-result-duration");
durationElem.innerHTML = "" + res.rowcount + " rows in " + (res.duration / 1000.0) + "s";
tableElem = document.createElement('table');
// HEADER
var headerElems = document.createElement('thead');
headerElems.classList.add("crate-result-header");
rowElem = document.createElement('tr');
res.cols.forEach(function(c, i) {
rowElem.innerHTML += '<th class="crate-result-header-cell">' + c + "</th>"
});
headerElems.appendChild(rowElem);
// BODY
var tableBodyElem = document.createElement('tbody');
res.rows.forEach(function(row, i) {
var node = document.createElement('tr');
node.innerHTML = '';
res.cols.forEach(function(c, i) {
node.innerHTML += "<td class='crate-result-table-cell'>" + format(row[i]) + "</td>";
});
tableBodyElem.appendChild(node);
});
console.debug(res.rows);
tableElem.appendChild(headerElems);
tableElem.appendChild(tableBodyElem);
wrapperElem.appendChild(durationElem);
wrapperElem.appendChild(tableElem);
}).error(function(e) {
console.error("could not execute statement:", e);
});
})
var RevealCrate = (function() {
var crateQuerySelector = '[data-crate]';
var queryResultSelector = 'crate-result';
var crateAddress = 'localhost';
var cratePort = 8000;
var crate = require('node-crate'),
sqlSections = document.querySelectorAll(crateQuerySelector),
sqlSection,
sqlCommand;
crate.connect(crateAddress, cratePort);
crate.execute("select * from sys.cluster").error(function(e) {
console.error("could not connect to crate server", e);
});
function format(element) {
if (typeof element === 'object') {
return JSON.stringify(element);
5
} else {
return element;
}
}
function getEmptyWrapper(node) {
var element = node.parentNode.parentNode.querySelector(queryResultSelector);
if (element !== null) {
element.innerHTML = '';
}
return element;
}
for (var i = 0, len = sqlSections.length; i < len; i++) {
sqlSection = sqlSections[i];
if (sqlSection.tagName.toUpperCase() === "CODE") {
sqlSection.setAttribute("style", "cursor:pointer;");
sqlSection.addEventListener('click', function(event) {
var code = event.target;
while (code.tagName.toUpperCase() !== "CODE") {
code = code.parentNode;
}
var wrapperElem = getEmptyWrapper(code);
var sqlCommand = code.textContent;
if (sqlCommand.charAt(sqlCommand.length - 1) === ';') {
sqlCommand = sqlCommand.substr(0, sqlCommand.length - 1)
}
console.log(sqlCommand);
crate.execute(sqlCommand).success(function(res) {
var wrapperElem = getEmptyWrapper(code);
if (res.error) {
res.cols = ["Error"];
res.rows = [
[res.error]
];
} else if (res.rows.length == 0 && res.rowcount > 0) {
res.cols = ["Duration", "Rowcount"];
res.rows = [
[res.duration, res.rowcount]
];
}
// DURATION
var durationElem = document.createElement("p");
durationElem.classList.add("crate-result-duration");
durationElem.innerHTML = "" + res.rowcount + " rows in " + (res.duration / 1000.0) + "s";
tableElem = document.createElement('table');
// HEADER
var headerElems = document.createElement('thead');
headerElems.classList.add("crate-result-header");
rowElem = document.createElement('tr');
res.cols.forEach(function(c, i) {
rowElem.innerHTML += '<th class="crate-result-header-cell">' + c + "</th>"
});
headerElems.appendChild(rowElem);
// BODY
var tableBodyElem = document.createElement('tbody');
res.rows.forEach(function(row, i) {
var node = document.createElement('tr');
node.innerHTML = '';
res.cols.forEach(function(c, i) {
node.innerHTML += "<td class='crate-result-table-cell'>" + format(row[i]) + "</td>";
});
tableBodyElem.appendChild(node);
});
console.debug(res.rows);
tableElem.appendChild(headerElems);
tableElem.appendChild(tableBodyElem);
wrapperElem.appendChild(durationElem);
wrapperElem.appendChild(tableElem);
}).error(function(e) {
console.error("could not execute statement:", e);
});
})
}
}
})();