-
-
Notifications
You must be signed in to change notification settings - Fork 219
/
Copy pathevents.php
executable file
·450 lines (377 loc) · 16.9 KB
/
events.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
<?php
//------------------------------------------------------------------------------
// NetAlertX
// Open Source Network Guard / WIFI & LAN intrusion detector
//
// events.php - Front module. Server side. Manage Events
//------------------------------------------------------------------------------
# Puche 2021 / 2022+ jokob jokob@duck.com GNU GPLv3
//------------------------------------------------------------------------------
//------------------------------------------------------------------------------
// External files
require dirname(__FILE__).'/init.php';
//------------------------------------------------------------------------------
// check if authenticated
require_once $_SERVER['DOCUMENT_ROOT'] . '/php/templates/security.php';
//------------------------------------------------------------------------------
// Action selector
//------------------------------------------------------------------------------
// Set maximum execution time to 1 minute
ini_set ('max_execution_time','60');
// Action functions
if (isset ($_REQUEST['action']) && !empty ($_REQUEST['action'])) {
$action = $_REQUEST['action'];
switch ($action) {
case 'getEventsTotals': getEventsTotals(); break;
case 'getEvents': getEvents(); break;
case 'getDeviceSessions': getDeviceSessions(); break;
case 'getDevicePresence': getDevicePresence(); break;
case 'getDeviceEvents': getDeviceEvents(); break;
case 'getEventsCalendar': getEventsCalendar(); break;
default: logServerConsole ('Action: '. $action); break;
}
}
//------------------------------------------------------------------------------
// Query total numbers of Events
//------------------------------------------------------------------------------
function getEventsTotals() {
global $db;
// Request Parameters
$periodDate = $_REQUEST['period'];
$periodDateSQL = "";
$days = "";
switch ($periodDate) {
case '7 days':
$days = "7";
break;
case '1 month':
$days = "30";
break;
case '1 year':
$days = "365";
break;
case '100 years':
$days = "3650"; //10 years
break;
default:
$days = "1";
}
$periodDateSQL = "-".$days." day";
$resultJSON = "";
// check cache if JSON available in a cookie
if(getCache("getEventsTotals".$days) != "")
{
$resultJSON = getCache("getEventsTotals".$days);
} else
{
// one query to get all numbers, which is quicker than multiple queries
$sql = "select
(SELECT Count(*) FROM Events WHERE eve_DateTime >= date('now', '".$periodDateSQL."')) as all_events,
(SELECT Count(*) FROM Sessions as sessions WHERE ( ses_DateTimeConnection >= date('now', '".$periodDateSQL."') OR ses_DateTimeDisconnection >= date('now', '".$periodDateSQL."') OR ses_StillConnected = 1 )) as sessions,
(SELECT Count(*) FROM Sessions WHERE ((ses_DateTimeConnection IS NULL AND ses_DateTimeDisconnection >= date('now', '".$periodDateSQL."' )) OR (ses_DateTimeDisconnection IS NULL AND ses_StillConnected = 0 AND ses_DateTimeConnection >= date('now', '".$periodDateSQL."' )))) as missing,
(SELECT Count(*) FROM Events WHERE eve_DateTime >= date('now', '".$periodDateSQL."') AND eve_EventType LIKE 'VOIDED%' ) as voided,
(SELECT Count(*) FROM Events WHERE eve_DateTime >= date('now', '".$periodDateSQL."') AND eve_EventType LIKE 'New Device' ) as new,
(SELECT Count(*) FROM Events WHERE eve_DateTime >= date('now', '".$periodDateSQL."') AND eve_EventType LIKE 'Device Down' ) as down";
$result = $db->query($sql);
$row = $result -> fetchArray (SQLITE3_NUM);
$resultJSON = json_encode (array ($row[0], $row[1], $row[2], $row[3], $row[4], $row[5]));
// save JSON result to cache
setCache("getEventsTotals".$days, $resultJSON );
}
// Return json
echo ($resultJSON);
}
//------------------------------------------------------------------------------
// Query the List of events
//------------------------------------------------------------------------------
function getEvents() {
global $db;
// Request Parameters
$type = $_REQUEST ['type'];
$periodDate = getDateFromPeriod();
// SQL
$SQL1 = 'SELECT eve_DateTime AS eve_DateTimeOrder, devName, devOwner, eve_DateTime, eve_EventType, NULL, NULL, NULL, NULL, eve_IP, NULL, eve_AdditionalInfo, NULL, devMac, eve_PendingAlertEmail
FROM Events_Devices
WHERE eve_DateTime >= '. $periodDate;
$SQL2 = 'SELECT IFNULL (ses_DateTimeConnection, ses_DateTimeDisconnection) ses_DateTimeOrder,
devName, devOwner, Null, Null, ses_DateTimeConnection, ses_DateTimeDisconnection, NULL, NULL, ses_IP, NULL, ses_AdditionalInfo, ses_StillConnected, devMac
FROM Sessions_Devices ';
// SQL Variations for status
switch ($type) {
case 'all': $SQL = $SQL1; break;
case 'sessions':
$SQL = $SQL2 . ' WHERE ( ses_DateTimeConnection >= '. $periodDate .' OR ses_DateTimeDisconnection >= '. $periodDate .' OR ses_StillConnected = 1 ) ';
break;
case 'missing':
$SQL = $SQL2 . ' WHERE (ses_DateTimeConnection IS NULL AND ses_DateTimeDisconnection >= '. $periodDate .' )
OR (ses_DateTimeDisconnection IS NULL AND ses_StillConnected = 0 AND ses_DateTimeConnection >= '. $periodDate .' )';
break;
case 'voided': $SQL = $SQL1 .' AND eve_EventType LIKE "VOIDED%" '; break;
case 'new': $SQL = $SQL1 .' AND eve_EventType = "New Device" '; break;
case 'down': $SQL = $SQL1 .' AND eve_EventType = "Device Down" '; break;
default: $SQL = $SQL1 .' AND 1==0 '; break;
}
// Query
$result = $db->query($SQL);
$tableData = array();
while ($row = $result -> fetchArray (SQLITE3_NUM)) {
if ($type == 'sessions' || $type == 'missing' ) {
// Duration
if (!empty ($row[5]) && !empty($row[6]) ) {
$row[7] = formatDateDiff ($row[5], $row[6]);
$row[8] = abs(strtotime($row[6]) - strtotime($row[5]));
} elseif ($row[12] == 1) {
$row[7] = formatDateDiff ($row[5], '');
$row[8] = abs(strtotime("now") - strtotime($row[5]));
} else {
$row[7] = '...';
$row[8] = 0;
}
// Connection
if (!empty ($row[5]) ) {
$row[5] = formatDate ($row[5]);
} else {
$row[5] = '<missing event>';
}
// Disconnection
if (!empty ($row[6]) ) {
$row[6] = formatDate ($row[6]);
} elseif ($row[12] == 0) {
$row[6] = '<missing event>';
} else {
$row[6] = '...';
}
} else {
// Event Date
$row[3] = formatDate ($row[3]);
}
// IP Order
$row[10] = formatIPlong ($row[9]);
$tableData['data'][] = $row;
}
// Control no rows
if (empty($tableData['data'])) {
$tableData['data'] = '';
}
// Return json
echo (json_encode ($tableData));
}
//------------------------------------------------------------------------------
// Query Device Sessions
//------------------------------------------------------------------------------
function getDeviceSessions() {
global $db;
// Request Parameters
$mac = $_REQUEST['mac'];
$periodDate = getDateFromPeriod();
// SQL
$SQL = 'SELECT IFNULL (ses_DateTimeConnection, ses_DateTimeDisconnection) ses_DateTimeOrder,
ses_EventTypeConnection, ses_DateTimeConnection,
ses_EventTypeDisconnection, ses_DateTimeDisconnection, ses_StillConnected,
ses_IP, ses_AdditionalInfo
FROM Sessions
WHERE ses_MAC="' . $mac .'"
AND ( ses_DateTimeConnection >= '. $periodDate .'
OR ses_DateTimeDisconnection >= '. $periodDate .'
OR ses_StillConnected = 1 ) ';
$result = $db->query($SQL);
// arrays of rows
$tableData = array();
while ($row = $result -> fetchArray (SQLITE3_ASSOC)) {
// Connection DateTime
if ($row['ses_EventTypeConnection'] == '<missing event>') {
$ini = $row['ses_EventTypeConnection'];
} else {
$ini = formatDate ($row['ses_DateTimeConnection']);
}
// Disconnection DateTime
if ($row['ses_StillConnected'] == true) {
$end = '...';
} elseif ($row['ses_EventTypeDisconnection'] == '<missing event>') {
$end = $row['ses_EventTypeDisconnection'];
} else {
$end = formatDate ($row['ses_DateTimeDisconnection']);
}
// Duration
if ($row['ses_EventTypeConnection'] == '<missing event>' || $row['ses_EventTypeConnection'] == NULL || $row['ses_EventTypeDisconnection'] == '<missing event>' || $row['ses_EventTypeDisconnection'] == NULL) {
$dur = '...';
} elseif ($row['ses_StillConnected'] == true) {
$dur = formatDateDiff ($row['ses_DateTimeConnection'], ''); //***********
} else {
$dur = formatDateDiff ($row['ses_DateTimeConnection'], $row['ses_DateTimeDisconnection']);
}
// Additional Info
$info = $row['ses_AdditionalInfo'];
if ($row['ses_EventTypeConnection'] == 'New Device' ) {
$info = $row['ses_EventTypeConnection'] .': '. $info;
}
// Push row data
$tableData['data'][] = array($row['ses_DateTimeOrder'], $ini, $end, $dur, $row['ses_IP'], $info);
}
// Control no rows
if (empty($tableData['data'])) {
$tableData['data'] = '';
}
// Return json
echo (json_encode ($tableData));
}
//------------------------------------------------------------------------------
// Query Device Presence Calendar
//------------------------------------------------------------------------------
function getDevicePresence() {
global $db;
// Request Parameters
$mac = $_REQUEST['mac'];
$startDate = '"'. formatDateISO ($_REQUEST ['start']) .'"';
$endDate = '"'. formatDateISO ($_REQUEST ['end']) .'"';
// SQL
$SQL = 'SELECT ses_EventTypeConnection, ses_DateTimeConnection,
ses_EventTypeDisconnection, ses_DateTimeDisconnection, ses_IP, ses_AdditionalInfo, ses_StillConnected,
CASE
WHEN ses_EventTypeConnection = "<missing event>" THEN
IFNULL ((SELECT MAX(ses_DateTimeDisconnection) FROM Sessions AS SES2 WHERE SES2.ses_MAC = SES1.ses_MAC AND SES2.ses_DateTimeDisconnection < SES1.ses_DateTimeDisconnection), DATETIME(ses_DateTimeDisconnection, "-1 hour"))
ELSE ses_DateTimeConnection
END AS ses_DateTimeConnectionCorrected,
CASE
WHEN ses_EventTypeDisconnection = "<missing event>" OR ses_EventTypeDisconnection = NULL THEN
(SELECT MIN(ses_DateTimeConnection) FROM Sessions AS SES2 WHERE SES2.ses_MAC = SES1.ses_MAC AND SES2.ses_DateTimeConnection > SES1.ses_DateTimeConnection)
ELSE ses_DateTimeDisconnection
END AS ses_DateTimeDisconnectionCorrected
FROM Sessions AS SES1
WHERE ses_MAC="' . $mac .'"
AND (ses_DateTimeConnectionCorrected <= date('. $endDate .')
AND (ses_DateTimeDisconnectionCorrected >= date('. $startDate .') OR ses_StillConnected = 1 )) ';
$result = $db->query($SQL);
// arrays of rows
while ($row = $result -> fetchArray (SQLITE3_ASSOC)) {
// Event color
if ($row['ses_EventTypeConnection'] == '<missing event>' || $row['ses_EventTypeDisconnection'] == '<missing event>') {
$color = '#f39c12';
} elseif ($row['ses_StillConnected'] == 1 ) {
$color = '#00a659';
} else {
$color = '#0073b7';
}
// tooltip
$tooltip = 'Connection: ' . formatEventDate ($row['ses_DateTimeConnection'], $row['ses_EventTypeConnection']) . chr(13) .
'Disconnection: ' . formatEventDate ($row['ses_DateTimeDisconnection'], $row['ses_EventTypeDisconnection']) . chr(13) .
'IP: ' . $row['ses_IP'];
// Save row data
$tableData[] = array(
'title' => '',
'start' => formatDateISO ($row['ses_DateTimeConnectionCorrected']),
'end' => formatDateISO ($row['ses_DateTimeDisconnectionCorrected']),
'color' => $color,
'tooltip' => $tooltip
);
}
// Control no rows
if (empty($tableData)) {
$tableData = '';
}
// Return json
echo (json_encode($tableData));
}
//------------------------------------------------------------------------------
// Query Presence Calendar for all Devices
//------------------------------------------------------------------------------
function getEventsCalendar() {
global $db;
// Request Parameters
$startDate = '"'. $_REQUEST ['start'] .'"';
$endDate = '"'. $_REQUEST ['end'] .'"';
// SQL
$SQL = 'SELECT SES1.ses_MAC, SES1.ses_EventTypeConnection, SES1.ses_DateTimeConnection,
SES1.ses_EventTypeDisconnection, SES1.ses_DateTimeDisconnection, SES1.ses_IP,
SES1.ses_AdditionalInfo, SES1.ses_StillConnected,
CASE
WHEN SES1.ses_EventTypeConnection = "<missing event>" THEN
IFNULL (
(SELECT MAX(SES2.ses_DateTimeDisconnection)
FROM Sessions AS SES2
WHERE SES2.ses_MAC = SES1.ses_MAC
AND SES2.ses_DateTimeDisconnection < SES1.ses_DateTimeDisconnection
AND SES2.ses_DateTimeDisconnection BETWEEN Date('. $startDate .') AND Date('. $endDate .')
),
DATETIME(SES1.ses_DateTimeDisconnection, "-1 hour")
)
ELSE SES1.ses_DateTimeConnection
END AS ses_DateTimeConnectionCorrected,
CASE
WHEN SES1.ses_EventTypeDisconnection = "<missing event>" THEN
(SELECT MIN(SES2.ses_DateTimeConnection)
FROM Sessions AS SES2
WHERE SES2.ses_MAC = SES1.ses_MAC
AND SES2.ses_DateTimeConnection > SES1.ses_DateTimeConnection
AND SES2.ses_DateTimeConnection BETWEEN Date('. $startDate .') AND Date('. $endDate .')
)
ELSE SES1.ses_DateTimeDisconnection
END AS ses_DateTimeDisconnectionCorrected
FROM Sessions AS SES1
WHERE (SES1.ses_DateTimeConnection BETWEEN Date('. $startDate .') AND Date('. $endDate .'))
OR (SES1.ses_DateTimeDisconnection BETWEEN Date('. $startDate .') AND Date('. $endDate .'))
OR SES1.ses_StillConnected = 1';
$result = $db->query($SQL);
// arrays of rows
while ($row = $result -> fetchArray (SQLITE3_ASSOC)) {
// Event color
if ($row['ses_EventTypeConnection'] == '<missing event>' || $row['ses_EventTypeDisconnection'] == '<missing event>') {
$color = '#f39c12';
} elseif ($row['ses_StillConnected'] == 1 ) {
$color = '#00a659';
} else {
$color = '#0073b7';
}
// tooltip
$tooltip = 'Connection: ' . formatEventDate ($row['ses_DateTimeConnection'], $row['ses_EventTypeConnection']) . chr(13) .
'Disconnection: ' . formatEventDate ($row['ses_DateTimeDisconnection'], $row['ses_EventTypeDisconnection']) . chr(13) .
'IP: ' . $row['ses_IP'];
// Save row data
$tableData[] = array(
'resourceId' => $row['ses_MAC'],
'title' => '',
'start' => formatDateISO ($row['ses_DateTimeConnectionCorrected']),
'end' => formatDateISO ($row['ses_DateTimeDisconnectionCorrected']),
'color' => $color,
'tooltip' => $tooltip,
'className' => 'no-border'
);
}
// Control no rows
if (empty($tableData)) {
$tableData = '';
}
// Return json
echo (json_encode($tableData));
}
//------------------------------------------------------------------------------
// Query Device events
//------------------------------------------------------------------------------
function getDeviceEvents() {
global $db;
// Request Parameters
$mac = $_REQUEST['mac'];
$periodDate = getDateFromPeriod();
$hideConnections = $_REQUEST ['hideConnections'];
// SQL
$SQL = 'SELECT eve_DateTime, eve_EventType, eve_IP, eve_AdditionalInfo
FROM Events
WHERE eve_MAC="'. $mac .'" AND eve_DateTime >= '. $periodDate .'
AND ( (eve_EventType <> "Connected" AND eve_EventType <> "Disconnected" AND
eve_EventType <> "VOIDED - Connected" AND eve_EventType <> "VOIDED - Disconnected")
OR "'. $hideConnections .'" = "false" ) ';
$result = $db->query($SQL);
// arrays of rows
$tableData = array();
while ($row = $result -> fetchArray (SQLITE3_NUM)) {
$row[0] = formatDate ($row[0]);
$tableData['data'][] = $row;
}
// Control no rows
if (empty($tableData['data'])) {
$tableData['data'] = '';
}
// Return json
echo (json_encode ($tableData));
}
?>