OSDN -- Desenvolver e Download de Software Open Source
Estado tradução de Português translation status for pt
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

Commit

Tags
No Tags

Frequently used words (click to add to your profile)

javac++androidlinuxc#windowsobjective-ccocoa誰得qtpythonphprubygameguibathyscaphec計画中(planning stage)翻訳omegatframeworktwitterdomtestvb.netdirectxゲームエンジンbtronarduinopreviewer

OpengateM Source Repository


Commit MetaInfo

Revisão47d56ffb1096a0892887036b51eca061ee185982 (tree)
Hora2016-06-24 11:05:36
Autorwatanaby <watanaby@user...>
Commiterwatanaby

Mensagem de Log

restored optional log table in management db

Mudança Sumário

Diff

--- a/conf/opengatemd.conf.sample
+++ b/conf/opengatemd.conf.sample
@@ -92,6 +92,9 @@
9292 <!-- SQLite busy timeout (milli-seconds) -->
9393 <SqliteBusyTimeout>100</SqliteBusyTimeout>
9494
95+ <!-- Save Log of session to Management DB (1:YES/0:NO) -->
96+ <SaveLogToMngDb>1</SaveLogToMngDb>
97+
9598 <!-- SQLite database file -->
9699 <!-- for opengatemd work -->
97100 <SqliteDbMd>/tmp/opengatemd.db</SqliteDbMd>
--- a/conf/opengatemmng.conf.sample
+++ b/conf/opengatemmng.conf.sample
@@ -80,7 +80,7 @@
8080
8181 <!-- SQLite busy timeout (milli-seconds) -->
8282 <SqliteBusyTimeout>100</SqliteBusyTimeout>
83-
83+
8484 <!-- SQLite database file -->
8585 <!-- opengatemd work db -->
8686 <SqliteDbMd>/tmp/opengatemd.db</SqliteDbMd>
--- a/doc/Changes.html
+++ b/doc/Changes.html
@@ -273,8 +273,12 @@ OpengateM History</H3>
273273 </DD>
274274 <DT>Ver.1.1.0 at 2016.6.22
275275 </DT><DD>
276- Modified code to prevent inflation of Databases (drop table macippair&sessionmd:MySQL, delete old records of macmodify:MySQL and macinfo:SQLite3).
277-w </DD>
276+ Modified code to prevent inflation of Databases (drop table macippair/sessionmd:MySQL, delete old records of macmodify:MySQL and macinfo:SQLite3).
277+ </DD>
278+ <DT>Ver.1.1.1 at 2016.6.24
279+ </DT><DD>
280+ Restored table sessionmd in management db (It holds the log expired after 1 month. Saving-log can be disabled in opengatemd.conf).
281+ </DD>
278282 </DL>
279283 <P>
280284 <B>Please see GIT on osdn.jp to check the differences between versions.</B>
--- a/html/en/macupdate.html
+++ b/html/en/macupdate.html
@@ -2,7 +2,21 @@
22 <head>
33 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
44 <title></title>
5+<script type="text/javascript">
6+<!--
7+var loglist={
8+%%USAGELOG%%
9+};
10+-->
11+</script>
12+<script type="text/javascript" src="/opengate/showlog.js"></script>
513 <script type="text/javascript" src="/opengate/editstring.js"></script>
14+<script>
15+function toggleDisplay(id){
16+ if (document.getElementById(id).style.display=="none") document.getElementById(id).style.display="block";
17+ else document.getElementById(id).style.display="none";
18+}
19+</script>
620 </head>
721 <body>
822 <center>
@@ -32,6 +46,7 @@ action="%%CGINAME%%?lang=en&redirectedurl=%%REDIRECTEDURL%%">
3246 %%MACLIST%%
3347 </table>
3448 <p><input type="submit" value=" Send ">
49+ <span style="color:blue;text-decoration:underline;cursor:pointer;" onclick="toggleDisplay('help1')"> help </span>
3550 </p>
3651 </form>
3752
@@ -41,7 +56,7 @@ action="%%CGINAME%%?lang=en&redirectedurl=%%REDIRECTEDURL%%">
4156 </center>
4257
4358 <div style="width:640;margin:0 auto;">
44-<ul>
59+<ul id="help1" style="display:none">
4560 <li><p>
4661 Select <strong>"Renew"</strong> to renew the registration (resume
4762 and extend the limit date).
@@ -64,5 +79,32 @@ action="%%CGINAME%%?lang=en&redirectedurl=%%REDIRECTEDURL%%">
6479 </ul>
6580 </div>
6681
82+<div style="%%LOGDISPLAYSTYLE%%">
83+<center>
84+ <p><font size="5"> Usage Log </font>
85+ <span style="color:blue;text-decoration:underline;cursor:pointer;" onclick="toggleDisplay('help2')"> help </span>
86+ <br><font size="2">
87+ (To prevent hijacking, please confirm your log.)</font>
88+</p>
89+</center>
90+
91+<div style="width:640;margin:0 auto;">
92+<ul id="help2" style="display:none">
93+<li>The color-bars in the chart show the time range when you used your devices.</li>
94+<li>If there is a use without your memory, please inform it to the administrator.</li>
95+<li>The red frame indicates that one device has plural logs. Please check it carefully.</li>
96+<li>Even if you do not intend to use the device, the automatic access might be done.</li>
97+<li>The closing of the network has time delay after finishing the use.</li>
98+</ul>
99+</div>
100+
101+<center>
102+<div id="usagelog"></div>
103+<div id="popup"></div>
104+<p><br><br></p>
105+</center>
106+
107+</div>
108+
67109 </body>
68110 </html>
--- a/html/ja/macupdate.html
+++ b/html/ja/macupdate.html
@@ -2,7 +2,21 @@
22 <head>
33 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
44 <title></title>
5+<script type="text/javascript">
6+<!--
7+var loglist={
8+%%USAGELOG%%
9+};
10+-->
11+</script>
12+<script type="text/javascript" src="/opengate/showlog.js"></script>
513 <script type="text/javascript" src="/opengate/editstring.js"></script>
14+<script>
15+function toggleDisplay(id){
16+ if (document.getElementById(id).style.display=="none") document.getElementById(id).style.display="block";
17+ else document.getElementById(id).style.display="none";
18+}
19+</script>
620 </head>
721 <body>
822 <center>
@@ -32,6 +46,7 @@ action="%%CGINAME%%?lang=ja&redirectedurl=%%REDIRECTEDURL%%">
3246 </table>
3347
3448 <p><input type="submit" value="　送信　">
49+ <span style="color:blue;text-decoration:underline;cursor:pointer;" onclick="toggleDisplay('help1')"> help </span>
3550 </p>
3651 </form>
3752
@@ -41,7 +56,7 @@ action="%%CGINAME%%?lang=ja&redirectedurl=%%REDIRECTEDURL%%">
4156 </center>
4257
4358 <div style="width:640;margin:0 auto;">
44- <ul>
59+<ul id="help1" style="display:none">
4560 <li><p>
4661 <strong>「更新」</strong>をチェックすると、機器の登録を更新して利用期限を延長します。
4762 </p></li>
@@ -60,5 +75,29 @@ action="%%CGINAME%%?lang=ja&redirectedurl=%%REDIRECTEDURL%%">
6075 </ul>
6176 </div>
6277
78+<div id="logheader" style="display:block">
79+<center>
80+ <p><font size="5">利用履歴</font>
81+<span style="color:blue;text-decoration:underline;cursor:pointer;" onclick="toggleDisplay('help2')"> help </span>
82+ <br>
83+ <font size="2">(不正利用を防ぐため、妥当か確認して下さい。)</font>
84+</p>
85+</center>
86+
87+<div style="width:640;margin:0 auto;">
88+<ul id="help2" style="display:none">
89+<li>利用された時間帯を１時間ごとに色付け表示しています。覚えの無い履歴があれば管理者までお知らせください。</li>
90+<li>赤枠の時間帯は１つの機器に対する２つ以上の履歴を含んでいます。特に注意下さい。</li>
91+<li>意図して利用しなくても、自動通信による利用がありえます。また使い終わってしばらくしてから閉鎖します。</li>
92+</ul>
93+</div>
94+
95+<center>
96+<div id="usagelog"></div>
97+<div id="popup"></div>
98+<p><br><br></p>
99+</center>
100+</div>
101+
63102 </body>
64103 </html>
--- /dev/null
+++ b/html/showlog.js
@@ -0,0 +1,324 @@
1+// Program to show chart type opengatem usage log in web page
2+// used in macupdate.html in lang(ja/en) directory
3+//
4+// Programmed by Yoshiaki Watanabe, 2013
5+// Revised by Minoru Kishira, 2013
6+//
7+// canvas.js by Satoru Takabayashi is used partially.
8+// http://0xcc.net/blog/archives/canvas.js
9+//
10+// You can redistribute it and/or modify it under the terms of
11+// the GNU General Public License version 2.
12+//
13+
14+// variables
15+var nTime=24; // time division per day
16+var beginTime;
17+var endTime;
18+var cellWidth="20px";
19+var cellHeight="8px";
20+var deviceList=[]; // devices used
21+
22+var colorList={0:"LimeGreen", 1:"Orange", 2:"SkyBlue", 3:"Tan", 4:"Thistle",
23+ "none":"WhiteSmoke", "sunday":"Pink","saturday":"LightBlue", "blank":"white", "popup":"yellow"};
24+var weekString = new Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat");
25+var today = new Date();
26+var weekday;
27+var monthday;
28+
29+////////////////////////////
30+// make handler of mouseover
31+function onmouseoverHandler(element, logItemNo) {
32+
33+ // save local values
34+ var day=monthday;
35+ var week=weekday;
36+
37+ // return the mandler
38+ return function(e) {
39+
40+ // if log item is found, show the item as popup
41+ if(logItemNo[0]>=0){
42+
43+ // popup style
44+ popup.style.position = "fixed";
45+ popup.style.left = e.clientX+20;
46+ popup.style.top = e.clientY+10;
47+ popup.style.background = colorList["popup"];
48+ popup.style.display = "block";
49+
50+ // popup string
51+ popup.innerHTML=loglist[logItemNo[0]].device +" ["+loglist[logItemNo[0]].macAddress+"]<BR>";
52+ for(var i=0; i<logItemNo.length; i++){
53+ popup.innerHTML+="Used at "+ loglist[logItemNo[i]].gateway+"<BR>"
54+ +"( "+loglist[logItemNo[i]].openTime+" -> "+loglist[logItemNo[i]].closeTime+" )<BR>";
55+ }
56+ }
57+ }
58+}
59+
60+////////////////////////////
61+// make handler of mouseout
62+function onmouseoutHandler(element) {
63+
64+ // off the display
65+ return function() {
66+ popup.style.display = "none";
67+ }
68+}
69+
70+////////////////////////////
71+// make cells to show every date-time range
72+function makeCells() {
73+
74+ // make html table
75+ var table = document.createElement("table");
76+ var tbody = document.createElement("tbody");
77+ table.border ="2px #808080 solid";
78+
79+ // grouping columns (title, 0-5, 6-11, 12-17, 18-23 hours)
80+ table.rules ="groups";
81+ cgroup0=document.createElement("colgroup"); cgroup0.span=1; table.appendChild(cgroup0);
82+ cgroup1=document.createElement("colgroup"); cgroup1.span=6; table.appendChild(cgroup1);
83+ cgroup2=document.createElement("colgroup"); cgroup2.span=6; table.appendChild(cgroup2);
84+ cgroup3=document.createElement("colgroup"); cgroup3.span=6; table.appendChild(cgroup3);
85+ cgroup4=document.createElement("colgroup"); cgroup4.span=6; table.appendChild(cgroup4);
86+
87+ // add header to display the hours
88+ tbody.appendChild(makeHeaderRow());
89+
90+ // make row for each day and each device
91+ var nDate=loglist["info"].dateCount;
92+ var nDevice=deviceList.length;
93+ for (var date = nDate-1; date >= 0; date--) {
94+ for(var iDevice = 0; iDevice < nDevice+1; iDevice++){
95+ var tr = document.createElement("tr");
96+
97+ // make leftmost title column spanning 6 rows
98+ if(iDevice==0) tr.appendChild(makeHeaderColumn(date));
99+
100+ // make column representing 0 hour to 23 hour
101+ for (var time = 0; time < nTime; time++) {
102+ var td = document.createElement("td");
103+ setupCellProperties(td, date, time, iDevice);
104+ if(iDevice==nDevice) td.style.background=colorList["blank"];
105+ tr.appendChild(td);
106+ }
107+
108+ tbody.appendChild(tr);
109+ }
110+ }
111+ table.appendChild(tbody);
112+ return table;
113+}
114+
115+////////////////////////////
116+// make the top title row to display hours
117+function makeHeaderRow(){
118+ var tr = document.createElement("tr");
119+ var td = document.createElement("td");
120+ tr.appendChild(td);
121+ for (var time = 0; time < nTime; time++) {
122+ var td = document.createElement("td");
123+ var text = document.createTextNode(time);
124+ td.appendChild(text);
125+ tr.appendChild(td);
126+ }
127+ return tr;
128+}
129+
130+////////////////////////////
131+// make the leftmost title comumn spanning to all devices row
132+function makeHeaderColumn(date){
133+ var td = document.createElement("td");
134+ var nDevice=deviceList.length;
135+
136+ td.rowSpan=nDevice+1;
137+ dateStr=getRealDate(date);
138+ var text = document.createTextNode(dateStr);
139+ td.appendChild(text);
140+ if(dateStr.indexOf(weekString[0])!=-1)
141+ td.style.background=colorList["sunday"];
142+ else if(dateStr.indexOf(weekString[6])!=-1)
143+ td.style.background=colorList["saturday"];
144+ return td;
145+}
146+
147+////////////////////////////
148+// get the real date string from date number(0 means begin date)
149+function getRealDate(date){
150+ var beginTime=new Date(loglist["info"].beginTime.replace(/-/g, "/"));
151+ var y=beginTime.getFullYear();
152+ var m=beginTime.getMonth();
153+ var d=beginTime.getDate();
154+ var realDate = new Date(y, m, d+date);
155+ return (realDate.getMonth()+1)+"/"+realDate.getDate()+" "+ weekString[realDate.getDay()];
156+}
157+
158+////////////////////////////
159+// setup cell properties
160+function setupCellProperties(td, date, time, iDevice){
161+
162+ td.width=cellWidth; td.height=cellHeight;
163+
164+ // search log for the time range;
165+ var logItemNo = searchLog(date, time, iDevice);
166+
167+ td.onmouseover = onmouseoverHandler(td, logItemNo);
168+ td.onmouseout = onmouseoutHandler(td);
169+
170+ //set background color of the cell
171+ //if two or more items are found, display red border
172+ if(logItemNo.length > 1){
173+ td.style.background=colorList[iDevice%5];
174+ td.style.border="3px solid Red";
175+ }
176+ // one item is found (if the item number == -1, no log is matched)
177+ else if(logItemNo.length == 1){
178+ if(logItemNo[0] == -1) td.style.background=colorList["none"];
179+ else td.style.background=colorList[iDevice%5];
180+ }
181+ // no item
182+ else td.style.background=colorList["blank"];
183+}
184+
185+////////////////////////////
186+// search the usage log corresponding to to the time range and the device number
187+function searchLog(date, hour, iDevice){
188+
189+ var y=beginTime.getFullYear();
190+ var m=beginTime.getMonth();
191+ var d=beginTime.getDate();
192+
193+ var timeRangeBegin = new Date(y, m, d+date, hour);
194+ var timeRangeEnd = new Date(y, m, d+date, hour+1);
195+
196+ // omit the range before beginTime or after endTime
197+ if(timeRangeEnd < beginTime || endTime < timeRangeBegin) return -2;
198+
199+ // make the week number and month/date string
200+ weekday=timeRangeBegin.getDay();
201+ monthday=timeRangeBegin.getFullYear()+"/"+(timeRangeBegin.getMonth()+1)+"/"+timeRangeBegin.getDate();
202+
203+ // search the device in loglist
204+ var logItemNoArray = new Array();
205+
206+ nList=loglist["info"].listCount;
207+ for (var i=0; i<nList; i++){
208+ if(deviceList[iDevice] == loglist[i].device){
209+
210+ // loglist includes '2000-10-10 00:00:00' but Date() reads '2000/10/10 00:00:00'
211+ var openTime = new Date(loglist[i].openTime.replace(/-/g, "/"));
212+ var closeTime = new Date(loglist[i].closeTime.replace(/-/g, "/"));
213+
214+ // if the device is used in the time range, return the log item number
215+ // second line check the device not closed yet
216+ if(timeRangeBegin < closeTime && openTime < timeRangeEnd) logItemNoArray.push(i);
217+
218+ if(loglist[i].closeTime=="0000-00-00 00:00:00" && openTime < timeRangeEnd) logItemNoArray.push(i);
219+ }
220+ }
221+
222+ // cannot found
223+ if(logItemNoArray.length==0) logItemNoArray.push(-1);
224+ return logItemNoArray;
225+}
226+
227+////////////////////////////
228+// make table for the legend of devices
229+function makeDevicesLegend() {
230+
231+ var table = document.createElement("table");
232+ table.border ="2px #808080 solid";
233+ var tbody = document.createElement("tbody");
234+ var nDevice=deviceList.length;
235+
236+ if(nDevice>0){
237+ for (var iDevice = 0; iDevice<nDevice; iDevice++) {
238+ var tr = document.createElement("tr");
239+ var td = document.createElement("td");
240+ td.innerHTML=deviceList[iDevice];
241+ tr.appendChild(td);
242+ var td = document.createElement("td");
243+ td.width=50;
244+ td.style.background=colorList[iDevice%5];
245+ tr.appendChild(td);
246+ tbody.appendChild(tr);
247+ }
248+ }else{
249+ var tr = document.createElement("tr");
250+ var td = document.createElement("td");
251+ td.innerHTML="No device is used in this period";
252+ tr.appendChild(td);
253+ tbody.appendChild(tr);
254+ }
255+
256+ table.appendChild(tbody);
257+ return table;
258+}
259+
260+////////////////////////////
261+// get the device names in loglist
262+function getDeviceList(){
263+
264+ var nList=loglist["info"].listCount;
265+ var deviceList = new Array();
266+ for (var i=0; i<nList; i++){
267+ logItem=loglist[i];
268+ if(deviceList.indexOf(logItem.device)==-1){
269+ deviceList.push(logItem.device);
270+ }
271+ }
272+ return deviceList;
273+}
274+
275+// make canvas to display usage log
276+function makeCanvas() {
277+
278+ // if no log in loglist, hide the log header and skip chart creation
279+ if(loglist["info"].listCount==0){
280+ document.getElementById('logheader').style.display="none";
281+ return;
282+ }
283+
284+ // make canvas for drawing chart
285+ var canvas = document.getElementById("usagelog");
286+ if (canvas) {
287+ var cell = makeCells();
288+ canvas.appendChild(cell);
289+ var devicesLegend = makeDevicesLegend();
290+ canvas.appendChild(devicesLegend);
291+ }
292+}
293+
294+////////////////////////////
295+// set up event listner
296+function addOnLoadEvent(func) {
297+ if (window.addEventListener) {
298+ window.addEventListener("load", func, false);
299+ } else if (window.attachEvent) { // for IE
300+ window.attachEvent("onload", func);
301+ } else {
302+ window.onload = func;
303+ }
304+}
305+
306+////////////////////////////
307+// initialize
308+function init(){
309+
310+ // get loglist info
311+ beginTime=new Date(loglist["info"].beginTime.replace(/-/g, "/"));
312+ endTime=new Date(loglist["info"].endTime.replace(/-/g, "/"));
313+
314+ // get device names in loglist
315+ deviceList=getDeviceList();
316+
317+ // set up onLoad event listner
318+ addOnLoadEvent(makeCanvas);
319+}
320+
321+////////////////////////////
322+// startup from here
323+init();
324+
--- a/mdsrc/managementdb.c
+++ b/mdsrc/managementdb.c
@@ -28,8 +28,8 @@ Email: watanaby@is.saga-u.ac.jp
2828 #include "opengatemd.h"
2929 #include <mysql.h>
3030
31-
32-MYSQL mysql;
31+static MYSQL mysql;
32+static int saveLogToMngDb=0;
3333
3434 /******************************************
3535 initialize management db
@@ -41,6 +41,7 @@ int initMngDb(void){
4141 char *user = GetConfValue("MySqlDb/User");
4242 char *password = GetConfValue("MySqlDb/Password");
4343 char *database = GetConfValue("MySqlDb/Database");
44+ char *saveLogStr = GetConfValue("SaveLogToMngDb");
4445 my_bool reconnect;
4546
4647 /* initialize mysql */
@@ -61,7 +62,11 @@ int initMngDb(void){
6162
6263 /* set auto-reconnect true */
6364 reconnect = TRUE;
64- mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);
65+ mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);
66+
67+ /* read a flag from conf file and hold in a variable */
68+ if(isNull(saveLogStr)) saveLogToMngDb = 0;
69+ else saveLogToMngDb = strtol(saveLogStr, NULL, 2);
6570
6671 return TRUE;
6772 }
@@ -120,6 +125,82 @@ int queryMacFromMngDb(char* macAddress, char* userId, char* extraId){
120125 return found;
121126 }
122127
128+/******************************************
129+put gate-open log to management db
130+******************************************/
131+int putOpenToMngDb(char* macAddress){
132+
133+ char queryStr[BUFFMAXLN];
134+ struct utsname uts;
135+
136+ /* if session log table is not used, return */
137+ if(!saveLogToMngDb) return FALSE;
138+
139+ /* get domain name */
140+ uname(&uts);
141+
142+ /* prepare query string */
143+ snprintf(queryStr, BUFFMAXLN,
144+ "insert into sessionmd "
145+ "(macAddress, gatewayName, openTime, closeTime) "
146+ "values ('%s','%s', now(), 0)",
147+ macAddress, uts.nodename);
148+
149+ /* send SQL query */
150+ if (mysql_query(&mysql, queryStr)){
151+ err_msg("ERR at %s#%d: mysql query: %s",__FILE__,__LINE__,
152+ mysql_error(&mysql));
153+ return FALSE;
154+ }
155+
156+ return TRUE;
157+}
158+
159+/******************************************
160+put gate-close log to management db
161+******************************************/
162+int putCloseToMngDb(char* macAddress){
163+
164+ char queryStr[BUFFMAXLN];
165+
166+ /* if session log table is not used, return */
167+ if(!saveLogToMngDb) return FALSE;
168+
169+ /* prepare query string */
170+ snprintf(queryStr, BUFFMAXLN,
171+ "update sessionmd set closeTime=now() "
172+ "where macAddress='%s' and closeTime=0", macAddress);
173+
174+ /* send SQL query */
175+ if (mysql_query(&mysql, queryStr)){
176+ err_msg("ERR at %s#%d: mysql query: %s",__FILE__,__LINE__,
177+ mysql_error(&mysql));
178+ return FALSE;
179+ }
180+
181+ return TRUE;
182+
183+}
184+
185+/******************************************
186+delete old session log in management DB
187+******************************************/
188+int delOldSessionLogInMngDb(void){
189+
190+ char* queryStr="delete from sessionmd where closeTime>0 and closeTime<adddate(now(),interval -1 month)";
191+
192+ /* if session log table is not used, return */
193+ if(!saveLogToMngDb) return FALSE;
194+
195+ /* send SQL query */
196+ if (mysql_query(&mysql, queryStr)){
197+ err_msg("ERR at %s#%d: mysql query: %s",__FILE__,__LINE__,
198+ mysql_error(&mysql));
199+ return FALSE;
200+ }
201+
202+ return TRUE;
203+}
123204
124205 /*******************************************
125206 get next mac address from watchlist table in management db
@@ -239,6 +320,22 @@ void CloseMngDb(void){
239320 if(debug>1) err_msg("DEBUG:<=closeMngDb()");
240321 }
241322
323+int PutCloseToMngDb(char* macAddress){
324+ int ret;
325+ if(debug>1) err_msg("DEBUG:=>putCloseToMngDb(%s)", macAddress);
326+ ret = putCloseToMngDb(macAddress);
327+ if(debug>1) err_msg("DEBUG:(%d)<=putCloseToMngDb()",ret);
328+ return ret;
329+}
330+
331+int PutOpenToMngDb(char* macAddress){
332+ int ret;
333+ if(debug>1) err_msg("DEBUG:=>putOpenToMngDb(%s)", macAddress);
334+ ret = putOpenToMngDb(macAddress);
335+ if(debug>1) err_msg("DEBUG:(%d)<=putOpenToMngDb()",ret);
336+ return ret;
337+}
338+
242339 int GetNextRecordFromWatchlistTableInMngDb(char* macAddress){
243340 int ret;
244341 if(debug>1) err_msg("DEBUG:=>getNextRecordFromWatchlisttableInMngDb()");
@@ -256,3 +353,10 @@ int IsAllFoundInWatchlistTable(void){
256353 return ret;
257354 }
258355
356+int DelOldSessionLogInMngDb(void){
357+ int ret;
358+ if(debug>1) err_msg("DEBUG:=>delOldSessionLogInMngDb()");
359+ ret = delOldSessionLogInMngDb();
360+ if(debug>1) err_msg("DEBUG:(%d)<=delOldSessionLogInMngDb()", ret);
361+ return ret;
362+}
--- a/mdsrc/opengatemd.c
+++ b/mdsrc/opengatemd.c
@@ -182,8 +182,9 @@ int main(int argc, char **argv)
182182 checkInterval=atoi(GetConfValue("UselessCheckInterval"));
183183 uselessCheckTime=time(NULL);
184184 DelUselessSessions();
185+ DelOldMacInfoInWorkDb();
186+ DelOldSessionLogInMngDb();
185187
186- DelOldMacInfoInWorkDb(); /**********************************/
187188 /*** enter infinite loop of packet inspection ***/
188189 while(1){
189190
@@ -218,6 +219,7 @@ int main(int argc, char **argv)
218219 uselessCheckTime = time(NULL);
219220 DelUselessSessions();
220221 DelOldMacInfoInWorkDb();
222+ DelOldSessionLogInMngDb();
221223 }
222224
223225 /* and return to loop top */
@@ -313,6 +315,7 @@ int main(int argc, char **argv)
313315 uselessCheckTime = time(NULL);
314316 DelUselessSessions();
315317 DelOldMacInfoInWorkDb();
318+ DelOldSessionLogInMngDb();
316319 }
317320 }
318321 /*** end of infinite loop ***/
--- a/mdsrc/opengatemd.h
+++ b/mdsrc/opengatemd.h
@@ -173,8 +173,11 @@ int ReFormatMacAddr(char* macAddr);
173173 int InitMngDb(void);
174174 int QueryMacFromMngDb(char* macAddress, char* userid, char* extraid);
175175 void CloseMngDb(void);
176+int PutCloseToMngDb(char* macAddress);
177+int PutOpenToMngDb(char* macAddress);
176178 int GetNextRecordFromWatchlistTableInMngDb(char* macAddress);
177179 int IsAllFoundInWatchlistTable(void);
180+int DelOldSessionLogInMngDb(void);
178181
179182 /* workdb.c */
180183 int SetupSqliteBusyTimeoutValue(void);
--- a/mdsrc/session.c
+++ b/mdsrc/session.c
@@ -87,6 +87,9 @@ int addSession(char* macAddress, char* userId, char* extraId){
8787 /* write open log to syslog */
8888 WriteOpenToSyslog(userId, extraId, macAddress);
8989
90+ /* write log to management db */
91+ PutOpenToMngDb(macAddress);
92+
9093 return TRUE;
9194 }
9295
@@ -119,6 +122,9 @@ void delSession(char* macAddress){
119122 WriteCloseToSyslog(userId, extraId, macAddress, openTime);
120123 if(debug>0) WriteSessionInfoToSyslog(userId, extraId,
121124 macAddress, ruleNumber);
125+
126+ /* write close log to management db */
127+ PutCloseToMngDb(macAddress);
122128
123129 /* reset the memory of MAC IP pair */
124130 ResetMacIpPairs(macAddress);
@@ -175,6 +181,9 @@ int closeSession(void* pParam, int argc, char *argv[], char* colName[]){
175181 WriteCloseToSyslog(userId, extraId, macAddress, openTime);
176182 if(debug>0) WriteSessionInfoToSyslog(userId, extraId, macAddress, ruleNumber);
177183
184+ /* write close log to management db */
185+ PutCloseToMngDb(macAddress);
186+
178187 /* reset the memory of MAC IP pair */
179188 ResetMacIpPairs(macAddress);
180189
@@ -330,6 +339,7 @@ void removeSessionUnmatchedToIpfwRule(DB* ruleTable, DB* sessionTable){
330339 err_msg("ERR at %s#%d: fail to get session info",__FILE__,__LINE__);
331340 }else{
332341 WriteCloseToSyslog(userId, extraId, macAddress, openTime);
342+ PutCloseToMngDb(macAddress);
333343 DelSessionFromWorkDb(macAddress);
334344 }
335345
@@ -387,6 +397,7 @@ void removeIpfwRuleUnmatchedToSession(DB* ruleTable, DB* sessionTable){
387397
388398 /* write log */
389399 WriteCloseToSyslog("?", "", macAddress, time(NULL));
400+ PutCloseToMngDb(macAddress);
390401
391402 /* write session info to syslog (no user info)*/
392403 if(debug>0) WriteSessionInfoToSyslog("?","", macAddress, ruleNumber);
--- a/mngsrc/cgi.c
+++ b/mngsrc/cgi.c
@@ -1171,6 +1171,10 @@ int putUpdatePageToClient(char *language, char* userId, char* extraId, int ownUp
11711171 if(strstr(buff, "%%MACLIST%%")!=NULL){
11721172 PutMacListToClient(userId,extraId);
11731173 }
1174+ else if(strstr(buff, "%%USAGELOG%%")!=NULL){
1175+ PutUsageLogToClient(userId, extraId, language);
1176+ }
1177+
11741178 else if(strstr(buff, "%%ERRORLIST%%")!=NULL){
11751179 InsertMessageToPage(language);
11761180 }
@@ -1241,6 +1245,55 @@ void putMacListToClient(char* userId, char* extraId){
12411245 }
12421246 }
12431247
1248+/*********************************************/
1249+/* put usage log to the client */
1250+/*********************************************/
1251+void putUsageLogToClient(char *userId, char* extraId, char* language){
1252+
1253+ char macAddr[ADDRMAXLN]="";
1254+ char deviceName[WORDMAXLN]="";
1255+ char openTime[WORDMAXLN]="";
1256+ char closeTime[WORDMAXLN]="";
1257+ char gatewayName[WORDMAXLN]="";
1258+ char beginTime[WORDMAXLN]="";
1259+ char endTime[WORDMAXLN]="";
1260+ int dateCount=0;
1261+ char* p;
1262+ int count=0;
1263+
1264+ /*** get usage log and insert it to html doc as JSON format */
1265+ /* get one log form management db */
1266+ while(GetNextUsageLogFromMngDb(userId,extraId,macAddr,deviceName,
1267+ openTime,closeTime,gatewayName)){
1268+
1269+ /* split hostname only (opengate01.example.com -> opengate01) */
1270+ if((p=strchr(gatewayName,'.'))!=NULL) *p='\0';
1271+
1272+ /* put out the log as a JSON item */
1273+ /* table skeleton is prepared in template file */
1274+ printf("\t %d:{\n", count);
1275+ printf("\t\t macAddress: '%s',\n", macAddr);
1276+ printf("\t\t device: '%s',\n",deviceName);
1277+ printf("\t\t openTime: '%s',\n",openTime);
1278+ printf("\t\t closeTime: '%s',\n",closeTime);
1279+ printf("\t\t gateway: '%s'\n",gatewayName);
1280+ printf("\t },\n");
1281+
1282+ /* increment log counter */
1283+ count++;
1284+ }
1285+
1286+ /* put out the JSON item for summary info of the list */
1287+ GetTimeRangeToShowLog(beginTime, endTime, &dateCount);
1288+ printf("\t info:{\n");
1289+ printf("\t\t beginTime: '%s',\n", beginTime);
1290+ printf("\t\t endTime: '%s',\n", endTime);
1291+ printf("\t\t listCount: %d,\n", count);
1292+ printf("\t\t dateCount: %d\n", dateCount);
1293+ printf("\t }\n");
1294+}
1295+
1296+
12441297 /********************************************************/
12451298 /* the client is an allowable http-user-agent or not */
12461299 /* the allowable http-user-agent can be defined in conf */
@@ -1479,6 +1532,12 @@ void PutMacListToClient(char *userId, char* extraId){
14791532 if(debug>1) err_msg("DEBUG:<=putMacListToClient( )");
14801533 }
14811534
1535+void PutUsageLogToClient(char *userId, char* extraId, char* language){
1536+ if(debug>1) err_msg("DEBUG:=>putUsageLogToClient(%s,%s,%s)",userId,extraId,language);
1537+ putUsageLogToClient(userId,extraId,language);
1538+ if(debug>1) err_msg("DEBUG:<=putUsageLogToClient( )");
1539+}
1540+
14821541 int IsAccessedFromAllowableAgent(void){
14831542 int ret;
14841543 if(debug>1) err_msg("DEBUG:=>isAccessedFromAllowableAgent( )");
--- a/mngsrc/managementdb.c
+++ b/mngsrc/managementdb.c
@@ -480,6 +480,66 @@ int pauseMacAddrInMngDb(char* macAddr){
480480 }
481481
482482 /*******************************************
483+ get next next usage log from management db
484+ if end of list, return false
485+*******************************************/
486+int getNextUsageLogFromMngDb(char* userId, char* extraId, char* macAddr, char* deviceName, char* openTime, char* closeTime, char* gatewayName){
487+ static MYSQL_RES *res=NULL;
488+ MYSQL_ROW row;
489+ char queryStr[BUFFMAXLN];
490+
491+ /* set default values */
492+ macAddr[0]=deviceName[0]=openTime[0]=gatewayName[0]='\0';
493+
494+ /* if do not get result yet */
495+ if(res==NULL){
496+
497+ /* prepare query string */
498+ /* get log where addr.entry < session.open < addr.limit */
499+ /* (the same device may be registered by other users in old days) */
500+ snprintf(queryStr, BUFFMAXLN,
501+ "select macaddrs.macAddress, device, openTime, closeTime, "
502+ " gatewayName "
503+ " from macaddrs, sessionmd "
504+ " where macaddrs.macAddress=sessionmd.macAddress "
505+ " and entryDate < openTime and openTime < limitDate "
506+ " and (%s<closeTime or closeTime=0) "
507+ " and userId='%s' and extraId='%s'",
508+ GetConfValue("ShowLogAfter"), userId, extraId);
509+
510+ /* send SQL query */
511+ if (mysql_query(&mysql, queryStr)){
512+
513+ /* query error */
514+ /* might be caused by <SaveLogToMngDb> setting in opengatemd.conf */
515+ return FALSE;
516+ }
517+
518+ /* store result */
519+ res = mysql_store_result(&mysql);
520+ }
521+
522+ /* output table name */
523+ row = mysql_fetch_row(res);
524+
525+ /* if found, return values */
526+ if(row!=NULL){
527+ strlcpy(macAddr, row[0],ADDRMAXLN);
528+ strlcpy(deviceName,row[1],WORDMAXLN);
529+ strlcpy(openTime,row[2],WORDMAXLN);
530+ strlcpy(closeTime,row[3],WORDMAXLN);
531+ strlcpy(gatewayName,row[4],WORDMAXLN);
532+ return TRUE;
533+ }
534+ /* if not found, free memory area */
535+ else{
536+ mysql_free_result(res);
537+ return FALSE;
538+ }
539+}
540+
541+
542+/*******************************************
483543 get next mail address near limit date
484544 from management db
485545 *******************************************/
@@ -860,6 +920,17 @@ int PauseMacAddrInMngDb(char* macAddr){
860920 return ret;
861921 }
862922
923+int GetNextUsageLogFromMngDb(char* userId, char* extraId, char* macAddr,
924+ char* deviceName, char* openTime, char* closeTime, char* gatewayName){
925+ int ret;
926+ if(debug>1) err_msg("DEBUG:=>getNextUsageLogFromMngDb(%s,%s)",userId,extraId);
927+ ret=getNextUsageLogFromMngDb(userId,extraId,macAddr,deviceName,
928+ openTime,closeTime,gatewayName);
929+ if(debug>1) err_msg("DEBUG:(%d)<=getNextUsageLogFromMngDb(,,%s,%s,%s,%s,%s)",
930+ ret,macAddr,deviceName,openTime,closeTime,gatewayName);
931+ return ret;
932+}
933+
863934 int GetNextMailAddressFromMngDb(char* mailAddress, char* limitDate, char*device){
864935 int ret;
865936 if(debug>1) err_msg("DEBUG:=>getnextMailAddressFromMngDb( )");
--- a/mngsrc/opengatemmng.h
+++ b/mngsrc/opengatemmng.h
@@ -189,6 +189,7 @@ int CountMacModifyPerDayInMngDb(char* userId, char* extraId, char* macAddress);
189189 int DelMacAddrFromMngDb(char* macAddr);
190190 int RenewMacAddrInMngDb(char* macAddr);
191191 int PauseMacAddrInMngDb(char* macAddr);
192+int GetNextUsageLogFromMngDb(char* userId, char* extraId, char* macAddr, char* deviceName, char* openTime, char* closeTime, char* gatewayName);
192193 int GetNextMailAddressFromMngDb(char* mailAddress, char* limitDate, char*device);
193194 int GetTimeRangeToShowLog(char* beginTime, char* endTime, int* dateCount);
194195 int RenameDeviceNameInMngDb(char* macAddr, char* nameStr);
@@ -244,6 +245,7 @@ int IsCorrectCookie(char* cookie, int userType);
244245 int AnalyzeUpdateRequestAndExecute(char *content, char* userId, char* extraId);
245246 int PutUpdatePageToClient(char *language, char* userId, char* extraId, int ownUpdate, char* redirectedUrl);
246247 void PutMacListToClient(char *userId, char* extraId);
248+void PutUsageLogToClient(char *userId, char* extraId, char* language);
247249 int IsAccessedFromAllowableAgent(void);
248250
249251 /* messages.c */
--- a/phpsrc/sendreportmail.php
+++ b/phpsrc/sendreportmail.php
@@ -38,6 +38,12 @@ if(!(list($device, $mailAddress)=getDataFromMysql($macAddress))){
3838 return;
3939 }
4040
41+// if reported recently, skip reporting
42+if(skipReporting($link, $macAddress, $gatewayName, $reportInterval)){
43+ mysqli_close($link);
44+ return;
45+}
46+
4147 // close database
4248 mysqli_close($link);
4349
@@ -125,6 +131,36 @@ function getDataFromMysql($macAddress){
125131 }
126132
127133 /***
134+To avoid to send too many mails,
135+ skip if there are recent logs having same macaddress and same gateway.
136+If you want to change the report period, $reportPeriod is defined at the top of this file.
137+***/
138+function skipReporting($link, $macAddress, $gatewayName, $reportInterval){
139+
140+ // query
141+ $result = mysqli_query($link, 'SELECT count(*) FROM sessionmd '
142+ .'WHERE EXISTS (SELECT * FROM sessionmd '
143+ .'WHERE macAddress="'.$macAddress.'" '
144+ .'AND gatewayName LIKE "'.$gatewayName.'.%" '
145+ .'AND openTime > NOW() - INTERVAL '.$reportInterval.' '
146+ .'AND openTime < NOW() - INTERVAL 1 MINUTE '
147+ .')'
148+ );
149+
150+ if (!$result){
151+ return FALSE; // if table doesn't exist, no skip
152+ }
153+
154+ // get data
155+ if($row = mysqli_fetch_row($result)) $count = $row[0];
156+ else $count = 0;
157+
158+ // if recent logs exist, skip is true
159+ if($count>0)return TRUE;
160+ else return FALSE;
161+}
162+
163+/***
128164 send mail to the user mail address
129165 ***/
130166 function sendMailToUser($mailSender, $mailAddress, $device, $gatewayName,
--- a/phpsrc/showtable.php
+++ b/phpsrc/showtable.php
@@ -7,6 +7,7 @@
77 <h2>Show Tables</h2>
88 <p>
99 <a href='showtable.php?table=macaddrs'>macaddrs</a>
10+<a href='showtable.php?table=sessionmd'>sessionmd</a>
1011 <a href='showtable.php?table=nicvendors'>nicvendors</a> <br>
1112 (Show max 1000 rows of selected table).
1213 </p>
--- a/sqlscript/createtablescript
+++ b/sqlscript/createtablescript
@@ -15,6 +15,17 @@ create table if not exists macaddrs(
1515 renewDate DATETIME,
1616 limitDate DATETIME);
1717
18+create table if not exists sessionmd(
19+ macAddress CHAR(18),
20+ gatewayName TINYTEXT,
21+ openTime DATETIME,
22+ closeTime DATETIME);
23+
24+create table if not exists macippair(
25+ macAddress CHAR(18),
26+ ipAddress TINYTEXT,
27+ findTime DATETIME);
28+
1829 create table if not exists macmodify(
1930 userId TINYTEXT,
2031 extraId TINYTEXT,