]>
Commit | Line | Data |
---|---|---|
3e10b3de SS |
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 | ) | |
111 | FROM sessions | |
112 | WHERE | |
113 | disconnected_at IS NULL | |
114 | OR | |
115 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') | |
116 | OR | |
117 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') | |
118 | GROUP BY common_name | |
119 | ORDER BY common_name; | |
120 | ); | |
121 | ||
122 | if ($cgiparams{'CONNECTION_NAME'}) { | |
123 | $database_query = qq( | |
124 | SELECT * | |
125 | FROM sessions | |
126 | WHERE | |
127 | common_name = '$cgiparams{"CONNECTION_NAME"}' AND ( | |
128 | DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') | |
129 | OR | |
130 | DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds')); | |
131 | ); | |
132 | } | |
133 | ||
134 | # Prepare SQL statement. | |
135 | my $statement_handle = $database_handle->prepare($database_query); | |
136 | ||
137 | # Execute SQL statement and get retun value if any error happened. | |
138 | my $database_return_value = $statement_handle->execute(); | |
139 | ||
140 | # If an error has been returned, assign it to the errorstring value for displaying. | |
141 | if($database_return_value < 0) { | |
142 | $errormessage = "$DBI::errstr"; | |
143 | } | |
144 | ||
145 | &Header::showhttpheaders(); | |
146 | ||
147 | &Header::openpage($Lang::tr{'ovpn rw connection log'}, 1, ''); | |
148 | ||
149 | &Header::openbigbox('100%', 'left', '', $errormessage); | |
150 | ||
151 | if ($errormessage) { | |
152 | &Header::openbox('100%', 'left', $Lang::tr{'error messages'}); | |
153 | print "<font class='base'>$errormessage </font>\n"; | |
154 | &Header::closebox(); | |
155 | } | |
156 | ||
157 | &Header::openbox('100%', 'left', "$Lang::tr{'settings'}:"); | |
158 | ||
159 | print "<form method='post' action=\"$ENV{'SCRIPT_NAME'}\">\n"; | |
160 | print "<table width='100%'>\n"; | |
161 | print "<tr>\n"; | |
162 | print "<td class='base' colspan='2'><b>$Lang::tr{'from'}:</b></td>\n"; | |
163 | print "</tr>\n"; | |
164 | ||
165 | print "<tr>\n"; | |
166 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
167 | &generate_select("FROM_DAY", "days"); | |
168 | print "</td>\n"; | |
169 | ||
170 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
171 | &generate_select("FROM_MONTH", "months"); | |
172 | print "</td>\n"; | |
173 | ||
174 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
175 | &generate_select("FROM_YEAR", "years"); | |
176 | print "</td>\n"; | |
177 | print "</tr>\n"; | |
178 | ||
179 | print "<tr><td><br></td></tr>\n"; | |
180 | ||
181 | print "<tr>\n"; | |
182 | print "<td class='base' colspan='2'><b>$Lang::tr{'to'}:</b></td>\n"; | |
183 | print "</tr>\n"; | |
184 | ||
185 | print "<tr>\n"; | |
186 | print "<td class='base'>$Lang::tr{'day'}: \;\n"; | |
187 | &generate_select("TO_DAY", "days"); | |
188 | print "</td>\n"; | |
189 | ||
190 | print "<td class='base'>$Lang::tr{'month'}: \;\n"; | |
191 | &generate_select("TO_MONTH", "months"); | |
192 | print "</td>\n"; | |
193 | ||
194 | print "<td class='base'>$Lang::tr{'year'}: \;\n"; | |
195 | &generate_select("TO_YEAR", "years"); | |
196 | print "</td>\n"; | |
197 | print "</tr>\n"; | |
198 | ||
199 | print "<tr><td><br></td></tr>\n"; | |
200 | ||
201 | print "<tr>\n"; | |
202 | print "<td class='base'>$Lang::tr{'ovpn connection name'}:</td>\n"; | |
203 | print "<td class='base' colspan='2'>\n"; | |
204 | ||
205 | print "<select name='CONNECTION_NAME' size='1'>\n"; | |
206 | print "<option value=''>All</option>\n"; | |
207 | ||
208 | # Loop through all configured OpenVPN connections and sort them by name. | |
209 | foreach my $key (sort { $ovpnsettings{$a}[2] cmp $ovpnsettings{$b}[2] } keys %ovpnsettings) { | |
210 | my $connection_name = $ovpnsettings{$key}[2]; | |
211 | my $selected; | |
212 | ||
213 | # Skip all non roadwarrior connections. | |
214 | next unless ($ovpnsettings{"$key"}[3] eq "host"); | |
215 | ||
216 | # Check and mark the selected one. | |
217 | if ($connection_name eq "$cgiparams{'CONNECTION_NAME'}") { | |
218 | $selected = "selected"; | |
219 | } | |
220 | ||
221 | print "<option value='$connection_name' $selected>$connection_name</option>\n"; | |
222 | } | |
223 | ||
224 | print "</select>\n"; | |
225 | print "</td>\n"; | |
226 | print "</tr>\n"; | |
227 | ||
228 | print "<tr>\n"; | |
229 | print "<td width='100%' align='right' colspan='3'><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>\n"; | |
230 | print "</tr>\n"; | |
231 | print "</table>\n"; | |
232 | print "</form>\n"; | |
233 | ||
234 | &Header::closebox(); | |
235 | ||
236 | &Header::openbox('100%', 'left', $Lang::tr{'log'}); | |
237 | ||
238 | my $lines = 0; | |
239 | ||
240 | print "<table width='100%' class='tbl'>"; | |
241 | ||
242 | my $col=""; | |
243 | ||
244 | while(my @row = $statement_handle->fetchrow_array()) { | |
245 | # Assign some nice to read variable names for the DB fields. | |
246 | my $connection_name = $row[0]; | |
247 | my $connection_open_time = $row[1]; | |
248 | my $connection_close_time = $row[2]; | |
249 | my $connection_bytes_recieved = $row[3]; | |
250 | my $connection_bytes_sent = $row[4]; | |
251 | ||
252 | # Colorize columns. | |
253 | if ($lines % 2) { | |
254 | $col="bgcolor='$color{'color22'}'"; | |
255 | } else { | |
256 | $col="bgcolor='$color{'color20'}'"; | |
257 | } | |
258 | ||
259 | print <<END | |
260 | <tr> | |
261 | <td width="40%" align="left" $col>$connection_name</td> | |
262 | <td width="20%" align="center" $col>$connection_open_time</td> | |
263 | <td width="20%" align="center" $col>$connection_close_time</td> | |
264 | <td width="10%" align="center" $col>$connection_bytes_recieved</td> | |
265 | <td width="10%" align="center" $col>$connection_bytes_sent</td> | |
266 | </tr> | |
267 | END | |
268 | ; | |
269 | ||
270 | # Increase lines count. | |
271 | $lines++; | |
272 | ||
273 | } | |
274 | ||
275 | print "</table><br>\n"; | |
276 | ||
277 | &Header::closebox(); | |
278 | ||
279 | # Close database connection. | |
280 | $database_handle->disconnect(); | |
281 | ||
282 | &Header::closebigbox(); | |
283 | ||
284 | &Header::closepage(); | |
285 | ||
286 | # | |
287 | ## Function for easy select generation. | |
288 | # | |
289 | sub generate_select($$) { | |
290 | my ($name, $type) = @_; | |
291 | ||
292 | my $start = 1; | |
293 | my $stop; | |
294 | ||
295 | # Adjust start and stop by the given type. | |
296 | if ($type eq "days") { | |
297 | $stop = 31; | |
298 | } elsif ($type eq "months") { | |
299 | $stop = 12; | |
300 | } elsif ($type = "years") { | |
301 | $stop = $year; | |
302 | $start = $stop - 10; | |
303 | } | |
304 | ||
305 | # Print select HTML tag. | |
306 | print "<select name='$name' size='1'>\n"; | |
307 | ||
308 | # Loop through the range. | |
309 | for ( my $i = $start; $i <= $stop; $i++) { | |
310 | print "\t<option "; | |
311 | ||
312 | # Check and select the current processed item. | |
313 | if ($i == $cgiparams{$name}) { | |
314 | print 'selected="selected" '; | |
315 | } | |
316 | ||
317 | # Check if months are processed and display the corresponding names. | |
318 | if ($type eq "months") { | |
319 | print "value='$i'>$monthhash{$i}</option>\n"; | |
320 | } else { | |
321 | print "value='$i'>$i</option>\n"; | |
322 | } | |
323 | } | |
324 | ||
325 | # Close select HTML tag. | |
326 | print "</select>\n\n"; | |
327 | } |