]>
Commit | Line | Data |
---|---|---|
1 | #!/usr/bin/perl | |
2 | ############################################################################### | |
3 | # # | |
4 | # IPFire.org - A linux based firewall # | |
5 | # Copyright (C) 2020 IPFire Team <info@ipfire.org> # | |
6 | # # | |
7 | # This program is free software: you can redistribute it and/or modify # | |
8 | # it under the terms of the GNU General Public License as published by # | |
9 | # the Free Software Foundation, either version 3 of the License, or # | |
10 | # (at your option) any later version. # | |
11 | # # | |
12 | # This program is distributed in the hope that it will be useful, # | |
13 | # but WITHOUT ANY WARRANTY; without even the implied warranty of # | |
14 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # | |
15 | # GNU General Public License for more details. # | |
16 | # # | |
17 | # You should have received a copy of the GNU General Public License # | |
18 | # along with this program. If not, see <http://www.gnu.org/licenses/>. # | |
19 | # # | |
20 | ############################################################################### | |
21 | ||
22 | use strict; | |
23 | use POSIX(); | |
24 | use DBI; | |
25 | ||
26 | # enable only the following on debugging purpose | |
27 | #use warnings; | |
28 | #use CGI::Carp 'fatalsToBrowser'; | |
29 | ||
30 | require '/var/ipfire/general-functions.pl'; | |
31 | require "${General::swroot}/lang.pl"; | |
32 | require "${General::swroot}/header.pl"; | |
33 | ||
34 | my %color = (); | |
35 | my %mainsettings = (); | |
36 | &General::readhash("${General::swroot}/main/settings", \%mainsettings); | |
37 | &General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/include/colors.txt", \%color); | |
38 | ||
39 | # Path and file of the OVPN connections database. | |
40 | my $database = "/var/ipfire/ovpn/clients.db"; | |
41 | ||
42 | my %cgiparams=(); | |
43 | my %logsettings=(); | |
44 | my %ovpnsettings=(); | |
45 | ||
46 | my $errormessage=''; | |
47 | ||
48 | # Hash wich contains the month numbers and the translated names for easy access. | |
49 | my %monthhash = ( | |
50 | "1" => "$Lang::tr{'january'}", | |
51 | "2" => "$Lang::tr{'february'}", | |
52 | "3" => "$Lang::tr{'march'}", | |
53 | "4" => "$Lang::tr{'april'}", | |
54 | "5" => "$Lang::tr{'may'}", | |
55 | "6" => "$Lang::tr{'june'}", | |
56 | "7" => "$Lang::tr{'july'}", | |
57 | "8" => "$Lang::tr{'august'}", | |
58 | "9" => "$Lang::tr{'september'}", | |
59 | "10" => "$Lang::tr{'october'}", | |
60 | "11" => "$Lang::tr{'november'}", | |
61 | "12" => "$Lang::tr{'december'}" | |
62 | ); | |
63 | ||
64 | # Get current time. | |
65 | my ($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst) = localtime(time); | |
66 | ||
67 | # Adjust month, because Jan starts as month "0". | |
68 | $month = $month+1; | |
69 | ||
70 | # Adjust year number. | |
71 | $year = $year+1900; | |
72 | ||
73 | # Assign default vaules. | |
74 | $cgiparams{'FROM_DAY'} = $mday; | |
75 | $cgiparams{'FROM_MONTH'} = $month; | |
76 | $cgiparams{'FROM_YEAR'} = $year; | |
77 | $cgiparams{'TO_DAY'} = $mday; | |
78 | $cgiparams{'TO_MONTH'} = $month; | |
79 | $cgiparams{'TO_YEAR'} = $year; | |
80 | ||
81 | &Header::getcgihash(\%cgiparams); | |
82 | ||
83 | # Read-in OpenVPN settings and connections. | |
84 | &General::readhasharray("${General::swroot}/ovpn/ovpnconfig", \%ovpnsettings); | |
85 | ||
86 | # Init DB Module and connect to the database. | |
87 | my $database_handle = DBI->connect("DBI:SQLite:dbname=$database", "", "", { RaiseError => 1 }); | |
88 | ||
89 | # Generate datestrings for SQL queries. | |
90 | my $from_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"FROM_YEAR"}, $cgiparams{"FROM_MONTH"}, $cgiparams{"FROM_DAY"}); | |
91 | my $to_datestring = sprintf '%04d-%02d-%02d', ($cgiparams{"TO_YEAR"}, $cgiparams{"TO_MONTH"}, $cgiparams{"TO_DAY"}); | |
92 | ||
93 | my $database_query = qq( | |
94 | SELECT | |
95 | common_name, SUM( | |
96 | STRFTIME('%s', ( | |
97 | CASE | |
98 | WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') | |
99 | THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') | |
100 | ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') | |
101 | END | |
102 | ), 'utc') - | |
103 | STRFTIME('%s', ( | |
104 | CASE | |
105 | WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') | |
106 | THEN DATETIME(connected_at, 'localtime') | |
107 | ELSE DATETIME('$from_datestring', 'localtime', 'start of day') | |
108 | END | |
109 | ), 'utc') | |
110 | ) AS duration | |
111 | FROM sessions | |
112 | WHERE | |
113 | ( | |
114 | disconnected_at IS NULL | |
115 | OR | |
116 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') | |
117 | ) | |
118 | AND | |
119 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') | |
120 | GROUP BY common_name | |
121 | ORDER BY common_name, duration DESC; | |
122 | ); | |
123 | ||
124 | if ($cgiparams{'CONNECTION_NAME'}) { | |
125 | $database_query = qq( | |
126 | SELECT common_name, DATETIME(connected_at, 'localtime'), DATETIME(disconnected_at, 'localtime'), bytes_received, bytes_sent FROM sessions | |
127 | WHERE | |
128 | common_name = '$cgiparams{"CONNECTION_NAME"}' | |
129 | AND ( | |
130 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') | |
131 | AND | |
132 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') | |
133 | ) | |
134 | ORDER BY connected_at; | |
135 | ); | |
136 | } | |
137 | ||
138 | # Prepare SQL statement. | |
139 | my $statement_handle = $database_handle->prepare($database_query); | |
140 | ||
141 | # Execute SQL statement and get retun value if any error happened. | |
142 | my $database_return_value = $statement_handle->execute(); | |
143 | ||
144 | # If an error has been returned, assign it to the errorstring value for displaying. | |
145 | if($database_return_value < 0) { | |
146 | $errormessage = "$DBI::errstr"; | |
147 | } | |
148 | ||
149 | &Header::showhttpheaders(); | |
150 | ||
151 | &Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, ''); | |
152 | ||
153 | &Header::openbigbox('100%', 'left', '', $errormessage); | |
154 | ||
155 | if ($errormessage) { | |
156 | &Header::openbox('100%', 'left', $Lang::tr{'error messages'}); | |
157 | print "<font class='base'>$errormessage </font>\n"; | |
158 | &Header::closebox(); | |
159 | } | |
160 | ||
161 | &Header::openbox('100%', 'left', "$Lang::tr{'settings'}:"); | |
162 | ||
163 | print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n"; | |
164 | print "<table width='100%'>\n"; | |
165 | print "<tr>\n"; | |
166 | print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n"; | |
167 | print "</tr>\n"; | |
168 | ||
169 | print "<tr>\n"; | |
170 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
171 | &generate_select("FROM_DAY", "days"); | |
172 | print "</td>\n"; | |
173 | ||
174 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
175 | &generate_select("FROM_MONTH", "months"); | |
176 | print "</td>\n"; | |
177 | ||
178 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
179 | &generate_select("FROM_YEAR", "years"); | |
180 | print "</td>\n"; | |
181 | print "</tr>\n"; | |
182 | ||
183 | print "<tr><td><br></td></tr>\n"; | |
184 | ||
185 | print "<tr>\n"; | |
186 | print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n"; | |
187 | print "</tr>\n"; | |
188 | ||
189 | print "<tr>\n"; | |
190 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
191 | &generate_select("TO_DAY", "days"); | |
192 | print "</td>\n"; | |
193 | ||
194 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
195 | &generate_select("TO_MONTH", "months"); | |
196 | print "</td>\n"; | |
197 | ||
198 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
199 | &generate_select("TO_YEAR", "years"); | |
200 | print "</td>\n"; | |
201 | print "</tr>\n"; | |
202 | ||
203 | print "<tr><td><br></td></tr>\n"; | |
204 | ||
205 | print "<tr>\n"; | |
206 | print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n"; | |
207 | print "<td class='base' colspan='2'>\n"; | |
208 | ||
209 | print "<select name='CONNECTION_NAME' size='1'>\n"; | |
210 | print "<option value=''>$Lang::tr{'all'}</option>\n"; | |
211 | ||
212 | # Loop through all configured OpenVPN connections and sort them by name. | |
213 | foreach my $key (sort { $ovpnsettings{$a}[2] cmp $ovpnsettings{$b}[2] } keys %ovpnsettings) { | |
214 | my $connection_name = $ovpnsettings{$key}[2]; | |
215 | my $selected; | |
216 | ||
217 | # Skip all non roadwarrior connections. | |
218 | next unless ($ovpnsettings{"$key"}[3] eq "host"); | |
219 | ||
220 | # Check and mark the selected one. | |
221 | if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") { | |
222 | $selected = "selected"; | |
223 | } | |
224 | ||
225 | print "<option value='$connection_name' $selected>$connection_name</option>\n"; | |
226 | } | |
227 | ||
228 | print "</select>\n"; | |
229 | print "</td>\n"; | |
230 | print "</tr>\n"; | |
231 | ||
232 | print "<tr>\n"; | |
233 | print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n"; | |
234 | print "</tr>\n"; | |
235 | print "</table>\n"; | |
236 | print "</form>\n"; | |
237 | ||
238 | &Header::closebox(); | |
239 | ||
240 | &Header::openbox('100%', 'left', $Lang::tr{'log'}); | |
241 | ||
242 | my $lines = 0; | |
243 | ||
244 | print "<table width='100%' class='tbl'>"; | |
245 | ||
246 | my $col = "bgcolor='$color{'color20'}'"; | |
247 | ||
248 | print "<tr>\n"; | |
249 | print "<td width='40%' $col><b>$Lang::tr{'ovpn connection name'}</b></td>\n"; | |
250 | ||
251 | if ($cgiparams{'CONNECTION_NAME'}) { | |
252 | print "<td width='20%' $col><b>$Lang::tr{'connected'}</b></td>\n"; | |
253 | print "<td width='20%' $col><b>$Lang::tr{'disconnected'}</b></td>\n"; | |
254 | print "<td width='10%' $col><b>$Lang::tr{'recieved'}</b></td>\n"; | |
255 | print "<td width='10%' $col><b>$Lang::tr{'sent'}</b></td>\n"; | |
256 | } else { | |
257 | print "<td $col><b>$Lang::tr{'total connection time'}</b>\n"; | |
258 | } | |
259 | ||
260 | print "</tr>\n"; | |
261 | ||
262 | while(my @row = $statement_handle->fetchrow_array()) { | |
263 | # Assign some nice to read variable names for the DB fields. | |
264 | my $connection_name = $row[0]; | |
265 | my $connection_open_time = $row[1]; | |
266 | my $connection_close_time = $row[2]; | |
267 | my $connection_bytes_recieved = &General::formatBytes($row[3]); | |
268 | my $connection_bytes_sent = &General::formatBytes($row[4]); | |
269 | ||
270 | # Colorize columns. | |
271 | if ($lines % 2) { | |
272 | $col="bgcolor='$color{'color20'}'"; | |
273 | } else { | |
274 | $col="bgcolor='$color{'color22'}'"; | |
275 | } | |
276 | ||
277 | print "<tr>\n"; | |
278 | print "<td width='40%' $col>$connection_name</td>\n"; | |
279 | ||
280 | if ($cgiparams{'CONNECTION_NAME'}) { | |
281 | print "<td width='20%' $col>$connection_open_time</td>\n"; | |
282 | print "<td width='20%' $col>$connection_close_time</td>\n"; | |
283 | print "<td width='10%' $col>$connection_bytes_recieved</td>\n"; | |
284 | print "<td width='10%' $col>$connection_bytes_sent</td>\n"; | |
285 | } else { | |
286 | # Convert total connection time into human-readable format. | |
287 | my $total_time = &General::format_time($row[1]); | |
288 | ||
289 | print "<td $col>$total_time</td>\n"; | |
290 | } | |
291 | ||
292 | print "</tr>\n"; | |
293 | ||
294 | # Increase lines count. | |
295 | $lines++; | |
296 | ||
297 | } | |
298 | ||
299 | print "</table><br>\n"; | |
300 | ||
301 | &Header::closebox(); | |
302 | ||
303 | # Close database connection. | |
304 | $database_handle->disconnect(); | |
305 | ||
306 | &Header::closebigbox(); | |
307 | ||
308 | &Header::closepage(); | |
309 | ||
310 | # | |
311 | ## Function for easy select generation. | |
312 | # | |
313 | sub generate_select($$) { | |
314 | my ($name, $type) = @_; | |
315 | ||
316 | my $start = 1; | |
317 | my $stop; | |
318 | ||
319 | # Adjust start and stop by the given type. | |
320 | if ($type eq "days") { | |
321 | $stop = 31; | |
322 | } elsif ($type eq "months") { | |
323 | $stop = 12; | |
324 | } elsif ($type = "years") { | |
325 | $stop = $year; | |
326 | $start = $stop - 10; | |
327 | } | |
328 | ||
329 | # Print select HTML tag. | |
330 | print "<select name='$name' size='1'>\n"; | |
331 | ||
332 | # Loop through the range. | |
333 | for ( my $i = $start; $i <= $stop; $i++) { | |
334 | print "\t<option "; | |
335 | ||
336 | # Check and select the current processed item. | |
337 | if ($i == $cgiparams{$name}) { | |
338 | print 'selected="selected" '; | |
339 | } | |
340 | ||
341 | # Check if months are processed and display the corresponding names. | |
342 | if ($type eq "months") { | |
343 | print "value='$i'>$monthhash{$i}</option>\n"; | |
344 | } else { | |
345 | print "value='$i'>$i</option>\n"; | |
346 | } | |
347 | } | |
348 | ||
349 | # Close select HTML tag. | |
350 | print "</select>\n\n"; | |
351 | } |