OpenVPN stats/status page using PHP

Posted on September 22, 2011 by jerbob92

Yesterday I was facing a problem that I don’t face many times: Something isn’t widely documented on the internet.

In this case it was about OpenVPN, the status of the server, per client, more specifically. After doing some searching on the internet I figured that OpenVPN isn’t used much, or that only pro’s use it and they write their own code anyway.

The only thing I found was a very basic(and non-working) PHP page from 2005, and a simple PHP script(also from 2005) that parses the logfile and shows the current status. I decided to base my script on that.

Before we start coding, make sure your OpenVPN server configuration contains: “status openvpn-status.log”. Now let’s start coding. First I made a table in the database.

DB Scheme:

 MySQL | 
 
 copy code |
?

01
CREATE TABLE IF NOT EXISTS `stats` (
02
`CommonName` text NOT NULL,
03
`RealAddress` text NOT NULL,
04
`BytesReceived` text NOT NULL,
05
`BytesSent` text NOT NULL,
06
`Since` text NOT NULL,
07
`VirtualAddress` text NOT NULL,
08
`LastRef` text NOT NULL,
09
`updated` bigint(20) NOT NULL
10
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Sorry for my dirty type choosing, this script was made in a hurry.

Then I made a cron that saves the stats:

Cron.php

 PHP | 
 
 copy code |
?

01
<?
02
mysql_connect('localhost', 'openvpn', 'openvpn');
03
mysql_select_db('openvpn');
04
function parseLog ($log) {
05
    $handle = fopen($log, "r");
06
    $uid = 0;
07
        while (!feof($handle)) {
08
            $buffer = fgets($handle, 4096);
09
            unset($match);
10
            if (ereg("^Updated,(.+)", $buffer, $match)) {
11
                $status['updated'] = $match[1];
12
            }
13
            if (preg_match("/^(.+),(\d+\.\d+\.\d+\.\d+\:\d+),(\d+),(\d+),(.+)$/", $buffer, $match)) {
14
                if ($match[1] <> "Common Name") {
15
                    $cn = $match[1];
16
 
17
                    $userlookup[$match[2]] = $uid;
18
                    $status['users'][$uid]['CommonName'] = $match[1];
19
                    $status['users'][$uid]['RealAddress'] = $match[2];
20
                    $status['users'][$uid]['BytesReceived'] = $match[3];
21
                    $status['users'][$uid]['BytesSent'] = $match[4];
22
                    $status['users'][$uid]['Since'] = $match[5];
23
                    $uid++;
24
                }
25
            }
26
            if (preg_match("/^(\d+\.\d+\.\d+\.\d+),(.+),(\d+\.\d+\.\d+\.\d+\:\d+),(.+)$/", $buffer, $match)) {
27
                if ($match[1] <> "Virtual Address") {
28
                    $address = $match[3];
29
                    $uid = $userlookup[$address];
30
                    $status['users'][$uid]['VirtualAddress'] = $match[1];
31
                    $status['users'][$uid]['LastRef'] = $match[4];
32
                }
33
            }
34
        }
35
        fclose($handle);
36
        return($status);
37
    }
38
    function sizeformat($bytesize){
39
        $i=0;
40
        while(abs($bytesize) >= 1024){
41
            $bytesize=$bytesize/1024;
42
            $i++;
43
            if($i==4) break;
44
        }
45
        $units = array("Bytes","KB","MB","GB","TB");
46
        $newsize=round($bytesize,2);
47
        return("$newsize $units[$i]");
48
    }
49
$stats = parseLog("/etc/openvpn/openvpn-status.log");
50
foreach($stats['users'] as $user)
51
{
52
    if($user['CommonName'] != "UNDEF")
53
    {
54
       $result = mysql_query("UPDATE stats SET updated = '".time()."', VirtualAddress = '".$user['VirtualAddress']."', BytesSent='".$user['BytesSent']."', BytesReceived='".$user['BytesReceived']."', LastRef='".$user['LastRef']."' WHERE CommonName='".$user['CommonName']."' AND Since='".$user['Since']."'") or die(mysql_error());
55
       echo mysql_affected_rows();
56
       if (mysql_affected_rows()==0) {
57
          $result = mysql_query("insert into stats (CommonName, RealAddress, BytesReceived, BytesSent, Since, VirtualAddress, LastRef) values ('".$user['CommonName']."', '".$user['RealAddress']."', '".$user['BytesReceived']."', '".$user['BytesSent']."', '".$user['Since']."', '".$user['VirtualAddress']."', '".$user['LastRef']."')");
58
       }
59
    }
60
}
61
?>

You have to edit the mysql details and the log location.

Here we insert the stats in the db, if the session start time and the client name is the same, it updates. If not, it inserts. We filter out UNDEF, UNDEF sometimes appears in the log, my guess is when the client isn’t fully connected yet.

Then I made a cron that saves the stats every minute:

Cron:

 PHP | 
 
 copy code |
?

1
* * * * * cd [cron location]; php cron.php

Replace [location] with the absolute folder to the cron, like /home/openvpn

I run the script every minute to prevent stats loss, but you could also do this less.

Stats.php:

 PHP | 
 
 copy code |
?

001
<?php
002
function parseLog ($log) {
003
    $handle = fopen($log, "r");
004
    $uid = 0;
005
        while (!feof($handle)) {
006
            $buffer = fgets($handle, 4096);
007
            unset($match);
008
            if (ereg("^Updated,(.+)", $buffer, $match)) {
009
                $status['updated'] = $match[1];
010
            }
011
            if (preg_match("/^(.+),(\d+\.\d+\.\d+\.\d+\:\d+),(\d+),(\d+),(.+)$/", $buffer, $match)) {
012
                if ($match[1] <> "Common Name") {
013
                    $cn = $match[1];
014
                    $userlookup[$match[2]] = $uid;
015
                    $status['users'][$uid]['CommonName'] = $match[1];
016
                    $status['users'][$uid]['RealAddress'] = $match[2];
017
                    $status['users'][$uid]['BytesReceived'] = sizeformat($match[3]);
018
                    $status['users'][$uid]['BytesSent'] = sizeformat($match[4]);
019
                    $status['users'][$uid]['Since'] = $match[5];
020
                    $uid++;
021
                }
022
            }
023
            if (preg_match("/^(\d+\.\d+\.\d+\.\d+),(.+),(\d+\.\d+\.\d+\.\d+\:\d+),(.+)$/", $buffer, $match)) {
024
                if ($match[1] <> "Virtual Address") {
025
                    $address = $match[3];
026
                    $uid = $userlookup[$address];
027
                    $status['users'][$uid]['VirtualAddress'] = $match[1];
028
                    $status['users'][$uid]['LastRef'] = $match[4];
029
                }
030
            }
031
        }
032
        fclose($handle);
033
        return($status);
034
    }
035
    function sizeformat($bytesize){
036
        $i=0;
037
        while(abs($bytesize) >= 1024){
038
            $bytesize=$bytesize/1024;
039
            $i++;
040
            if($i==4) break;
041
        }
042
        $units = array("Bytes","KB","MB","GB","TB");
043
        $newsize=round($bytesize,2);
044
        return("$newsize $units[$i]");
045
    }
046
$stats = parseLog("/etc/openvpn/openvpn-status.log");
047
echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><body><center>';
048
echo '<h3>logged in users</h3><br>
049
<table>
050
    <tr style="font-weight: bold;" bgcolor="#888888">
051
        <td>Common Name</td>
052
        <td>Real Address</td>
053
        <td>Virtual Address</td>
054
        <td>Logged In At</td>
055
        <td>Bytes Sent</td>
056
        <td>Bytes Received</td>
057
        <td>Last Activity</td>
058
    </tr>
059
';
060
foreach($stats['users'] as $user)
061
{  
062
    echo '<tr bgcolor="#eeeeee">
063
        <td>'.$user['CommonName'].'</td>
064
        <td>'.$user['RealAddress'].'</td>
065
        <td>'.$user['VirtualAddress'].'</td>
066
        <td>'.$user['Since'].'</td>
067
        <td>'.$user['BytesReceived'].'</td>
068
        <td>'.$user['BytesSent'].'</td>
069
        <td>'.$user['LastRef'].'</td>
070
    </tr>';
071
 }
072
echo '
073
</table>
074
<br>
075
<center>Live status Last Updated: <b>'.$stats['updated'].'
076
</b>
077
';
078
mysql_connect('localhost', 'openvpn', 'openvpn');
079
mysql_select_db('openvpn');
080
echo '<h3>bandwidth totals - all time</h3><br>
081
<table>
082
    <tr style="font-weight: bold;" bgcolor="#888888">
083
        <td>Common Name</td>
084
        <td>Bytes Sent</td>
085
        <td>Bytes Received</td>
086
    </tr>
087
 
088
';
089
$result = mysql_query("SELECT sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats") or die(mysql_error());
090
while ($row = mysql_fetch_assoc($result)) {
091
    echo '<tr bgcolor="#eeeeee">
092
        <td>Total</td>
093
        <td>'.sizeformat($row['totalreceived']).'</td>
094
        <td>'.sizeformat($row['totalsend']).'</td>
095
        </tr>';
096
}
097
unset($result);
098
$result = mysql_query("SELECT CommonName, sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats GROUP BY CommonName ORDER BY CommonName ASC") or die(mysql_error());
099
while ($row = mysql_fetch_assoc($result)) {
100
    echo '<tr bgcolor="#eeeeee">
101
        <td>'.$row['CommonName'].'</td>
102
        <td>'.sizeformat($row['totalreceived']).'</td>
103
        <td>'.sizeformat($row['totalsend']).'</td>
104
       </tr>';
105
}
106
echo '
107
</table>
108
';
109
 
110
 
111
unset($result);
112
echo '<h3>bandwidth totals - Today</h3><br>
113
<table>
114
    <tr style="font-weight: bold;" bgcolor="#888888">
115
        <td>Common Name</td>
116
        <td>Bytes Sent</td>
117
        <td>Bytes Received</td>
118
    </tr>
119
 
120
';
121
$result = mysql_query("SELECT sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("M")." ".date("j")."%".date("Y")."%'") or die(mysql_error());
122
while ($row = mysql_fetch_assoc($result)) {
123
    echo '<tr bgcolor="#eeeeee">
124
        <td>Total</td>
125
        <td>'.sizeformat($row['totalreceived']).'</td>
126
        <td>'.sizeformat($row['totalsend']).'</td>
127
    </tr>';
128
}
129
unset($result);
130
$result = mysql_query("SELECT CommonName, sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("M")." ".date("j")."%".date("Y")."%' GROUP BY CommonName ") or die(mysql_error());
131
while ($row = mysql_fetch_assoc($result)) {
132
    echo '<tr bgcolor="#eeeeee">
133
        <td>'.$row['CommonName'].'</td>
134
        <td>'.sizeformat($row['totalreceived']).'</td>
135
        <td>'.sizeformat($row['totalsend']).'</td>
136
    </tr>';
137
}
138
echo '
139
</table>
140
';
141
 
142
 
143
unset($result);
144
echo '<h3>bandwidth totals - last 7 days</h3><br>
145
<table>
146
    <tr style="font-weight: bold;" bgcolor="#888888">
147
        <td>Common Name</td>
148
        <td>Bytes Sent</td>
149
        <td>Bytes Received</td>
150
    </tr>
151
 
152
';
153
$like = "LastRef LIKE '%".date("M")." ".date("j")."%".date("Y")."%'";
154
for ($i=0; $i<7; $i++)
155
{
156
    $like .= " OR LastRef LIKE '%".date("M",strtotime("-".($i+1)." day"))." ".date("j",strtotime("-".($i+1)." day"))."%".date("Y",strtotime("-".($i+1)." day"))."%'";
157
}
158
$result = mysql_query("SELECT sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE ".$like."") or die(mysql_error());
159
while ($row = mysql_fetch_assoc($result)) {
160
    echo '<tr bgcolor="#eeeeee">
161
        <td>Total</td>
162
        <td>'.sizeformat($row['totalreceived']).'</td>
163
        <td>'.sizeformat($row['totalsend']).'</td>
164
    </tr>';
165
}
166
unset($result);
167
$result = mysql_query("SELECT CommonName, sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE ".$like." GROUP BY CommonName ") or die(mysql_error());
168
while ($row = mysql_fetch_assoc($result)) {
169
    echo '<tr bgcolor="#eeeeee">
170
        <td>'.$row['CommonName'].'</td>
171
        <td>'.sizeformat($row['totalreceived']).'</td>
172
        <td>'.sizeformat($row['totalsend']).'</td>
173
    </tr>';
174
}
175
echo '
176
</table>
177
';
178
 
179
unset($result);
180
echo '<h3>bandwidth totals - This month</h3><br>
181
<table>
182
    <tr style="font-weight: bold;" bgcolor="#888888">
183
        <td>Common Name</td>
184
        <td>Bytes Sent</td>
185
        <td>Bytes Received</td>
186
    </tr>
187
 
188
';
189
$result = mysql_query("SELECT sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("M")."%".date("Y")."%'") or die(mysql_error());
190
while ($row = mysql_fetch_assoc($result)) {
191
    echo '<tr bgcolor="#eeeeee">
192
        <td>Total</td>
193
        <td>'.sizeformat($row['totalreceived']).'</td>
194
        <td>'.sizeformat($row['totalsend']).'</td>
195
    </tr>';
196
}
197
unset($result);
198
$result = mysql_query("SELECT CommonName, sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("M")."%".date("Y")."%' GROUP BY CommonName ") or die(mysql_error());
199
while ($row = mysql_fetch_assoc($result)) {
200
    echo '<tr bgcolor="#eeeeee">
201
        <td>'.$row['CommonName'].'</td>
202
        <td>'.sizeformat($row['totalreceived']).'</td>
203
        <td>'.sizeformat($row['totalsend']).'</td>
204
    </tr>';
205
}
206
echo '
207
</table>
208
';
209
 
210
unset($result);
211
echo '<h3>bandwidth totals - This year</h3><br>
212
<table>
213
    <tr style="font-weight: bold;" bgcolor="#888888">
214
        <td>Common Name</td>
215
        <td>Bytes Sent</td>
216
        <td>Bytes Received</td>
217
    </tr>
218
 
219
';
220
$result = mysql_query("SELECT sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("Y")."%'") or die(mysql_error());
221
while ($row = mysql_fetch_assoc($result)) {
222
    echo '<tr bgcolor="#eeeeee">
223
        <td>Total</td>
224
        <td>'.sizeformat($row['totalreceived']).'</td>
225
        <td>'.sizeformat($row['totalsend']).'</td>
226
    </tr>';
227
}
228
unset($result);
229
$result = mysql_query("SELECT CommonName, sum(BytesSent) as 'totalsend', sum(BytesReceived) as 'totalreceived' FROM stats WHERE LastRef LIKE '%".date("Y")."%' GROUP BY CommonName ") or die(mysql_error());
230
while ($row = mysql_fetch_assoc($result)) {
231
    echo '<tr bgcolor="#eeeeee">
232
        <td>'.$row['CommonName'].'</td>
233
        <td>'.sizeformat($row['totalreceived']).'</td>
234
        <td>'.sizeformat($row['totalsend']).'</td>
235
    </tr>';
236
}
237
echo '
238
</table>
239
';
240
echo '</center></body></html>';
241
?>

Edit the database details and log location here too, and you’re done. I advise to protect the folder with a .htaccess+.htpasswd or some simple php login.

Sorry for my dirty code, but hey, it works.

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to OpenVPN stats/status page using PHP

  1. Paul says:
    August 8, 2023 at 7:48 pm

    Hai,

    Would you be willing to rewrite this to the current versions of php/mysql ?

    I tried it but keep on failing. I replaced mysql_ to mysqli_ ereg to preg_match but now ending up with fgets and feof errors 🙂

    Cheers Paul

    Reply
    • jerbob92 says:
      August 8, 2023 at 8:11 pm

      Hi Paul,

      Sure, but I can imagine the openvpn logs format have been changed since 2011, maybe you can send me a sample?

      Reply

Leave a Reply

Your email address will not be published.