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
|
<?php
class Queries
{
// Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized)
public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translate('notAssigned', true);
Database::exec("SET SESSION group_concat_max_len = 1000000000");
$res = Database::simpleQuery("SELECT name AS clientName, timeSum, medianSessionLength, offlineSum, IFNULL(lastStart, 0) as lastStart, IFNULL(lastLogout, 0) as lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM (
SELECT machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions', MAX(sessionTable.endInBound) AS 'lastLogout'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
GROUP BY machine.machineuuid
) t1
RIGHT JOIN (
SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.endInBound) AS 'lastStart', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId'
FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY machine.machineuuid
) t2
ON t1.uuid = t2.uuid", array("salt" => GetData::$salt));
return $res;
}
// Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) {
$notassigned = Dictionary::translate('notAssigned', true);
Database::exec("SET SESSION group_concat_max_len = 1000000000");
$res = Database::simpleQuery("SELECT t1.locId, locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM (
SELECT location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions'
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable
INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY machine.locationid
) t1
RIGHT JOIN (
SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum'
FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable
INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid
LEFT JOIN location ON machine.locationid = location.locationid
GROUP BY machine.locationid
) t2
ON t1.locId = t2.locId", array("salt" => GetData::$salt));
return $res;
}
// User Data: Name, Name(anonymized), Number of Logins
public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to
AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound
GROUP BY username
ORDER BY 2 DESC", array("salt" => GetData::$salt));
return $res;
}
// Virtual Machine Data: Name, Number of Usages
public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count'
FROM statistic
WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to
AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound
GROUP BY data
ORDER BY 2 DESC", array("salt" => GetData::$salt));
return $res;
}
//Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec
public static function getOverallStatistics ($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
Database::exec("SET SESSION group_concat_max_len = 1000000000");
$res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions
FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable");
return $res;
}
// Total Data(2): Time Offline
public static function getTotalOfflineStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) {
$res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff
FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable");
return $res;
}
// query string which provides table with time-cutoff and time-bounds
private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound)
{
// get Clients that are currently oflfine (the offline time is not yet recorded in the statistic table)
$union = $typeid == '~offline-length' ?
"union
select CAST(IF(lastseen < $from, $from, lastseen) as UNSIGNED) as start, $to as end,
'~offline-length' as typeid, machineuuid, 'machine'
from machine where lastseen <= $to and UNIX_TIMESTAMP() - lastseen >= 600" : "";
$lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'";
$upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'";
$queryString = "
select
# The whole length of the session/offline time.
(end-start
# Now the time that is not within the daily time bounds is subtracted.
# This includes the time before the first daily bound, the time after the last daily bound
# and the time between the daily bounds (if a session/offline time spans multiple days)
# Time before the first daily bound is subtracted.
- IF(
start > startUpper,
UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat) + INTERVAL 1 DAY) - start,
IF(
start < startLower,
startLower - start,
0
)
)
# Time after the last daily bound is subtracted.
- IF(
end > endUpper,
end - (endUpper + 1),
IF(
end < endLower,
end - (UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat) - INTERVAL 1 DAY) + 1),
0
)
)
# Time between the daily bounds is subtracted.
- ( daysDiff - 2
+ IF(start <= startUpper, 1, 0)
+ IF(end >= endLower, 1, 0)
) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600)
# If the session crossed a clock change (to/from daylight saving time), the last subtraction may have subtracted
# one hour too much or too little. This IF will correct this.
- IF(
innerStart < innerEnd,
IF(
timeDiff = 1 AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2),
3600,
IF(timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0)
),
0
)
) as 'length',
IF(end < endUpper AND end > endLower AND end < $to, end, 0) as endInBound,
machineuuid
# These nested selects are necessary because some things need to be calculated before others.
# (e.g. start is needed to calculate startLower)
from (
select
*,
# timeDiff is the clock change between innerStart and innerEnd. ( 0 = no clock change)
((CAST(date_format(from_unixtime(innerStart), '%H') as SIGNED) -
CAST(date_format(convert_tz(from_unixtime(innerStart), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24
-
(CAST(date_format(from_unixtime(innerEnd), '%H') as SIGNED) -
CAST(date_format(convert_tz(from_unixtime(innerEnd), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24) as timeDiff
from (
select
*,
# innerStart and innerEnd are start and end but excluding the time before the first daily upper bound and after the last daily lower bound.
CAST(IF(start <= startUpper, startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat) + INTERVAL 1 DAY)) as UNSIGNED) as innerStart,
CAST(IF(end >= endLower, endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat) - INTERVAL 1 DAY)) as UNSIGNED) as innerEnd
from (
select
*,
# daysDiff = how many different days the start and end are apart (0 = start and end on the same day)
(TO_DAYS(FROM_UNIXTIME(end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(start, '%y-%m-%d'))) as daysDiff,
# startLower = lower daily time bound on the starting day
CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat)) as UNSIGNED) as startLower,
# startUpper = upper daily time bound on the starting day
CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) as UNSIGNED) as startUpper,
# endLower = lower daily time bound on the ending day
CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)) as UNSIGNED) as endLower,
# endUpper = upper daily time bound on the ending day
CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat)) as UNSIGNED) as endUpper
from (
# Statistic logs (combined with currently offline machines if offline times are requested) .
select CAST(IF(dateline < $from, $from, dateline) as UNSIGNED) as start,
CAST(IF(dateline+data > $to, $to, dateline+data) as UNSIGNED) as end,
typeid, machineuuid, 'statistic'
from statistic where dateline+data >= $from and dateline <= $to and typeid = '$typeid'
$union
) t
) t
) t
) t
# Filter out the session that are at least overlapping with the time bounds.
where (
(daysDiff = 0 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) and end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat))))
or
(daysDiff = 1 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) or end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat))))
or
daysDiff >= 2
)
";
return "(".$queryString.")";
}
}
|
