import repository from arizona
[raven.git] / apps / logmon / index.py
1 #!/usr/bin/env python
2 """
3 Author: Scott Baker
4 Date: 2010-8-3
5
6 Error logging server
7
8 This script will require:
9 mysql
10 mod_python
11 python-MySQLdb
12 python-json
13 """
14
15 import datetime
16 import dateutil.parser
17 import os
18 import pickle
19 import sys
20 import urllib
21 from errlogging import *
22
23 protocol = "1.2"
24
25 # maximum number of entries in the database
26 TRIM_LEVEL = 400000
27
28 config = {}
29 config["userpasswordfile"] = "/root/mysql-logmon-pass.txt"
30 config["userpassword"] = None
31 config["rootpasswordfile"] = "/root/mysql-root-pass.txt"
32 config["rootpassword"] = None
33 config["host"] = "localhost"
34 config["user"] = "logmon"
35 config["url"] = "/logmon/"
36 config["path"] = "/usr/local/logmon"
37 config["dbname"] = "logmon"
38
39 from mod_python import apache, psp, util, Session
40 from cgi import escape
41 from urllib import unquote
42 from distutils.version import LooseVersion
43 from html import *
44 import json as json_module # avoid name conflict with json function
45 import MySQLdb
46 import ConfigParser
47 import traceback
48 import time
49 import string
50 import urllib
51 from db import *
52
53
54 # DB connection info
55 DB.host = config["host"]
56 DB.user = config["user"]
57 DB.name = None
58 DB.passwd = config["userpassword"]
59 if DB.passwd == None:
60     try:
61         file = config["userpasswordfile"]
62         fd = open(file, "r")
63         DB.passwd = fd.readline().rstrip()
64         fd.close()
65     except:
66         error(None, 'Unable to read mysql logmon password from "%s"' % (file))
67         error(None, 'euid = %s, egid = %s' % (os.geteuid(), os.getegid()))
68         raise
69
70 def _format_dt(dt):
71     return "%04d-%02d-%02d %02d:%02d:%02d.%06d" % (dt.year, dt.month, dt.day, dt.hour, dt.minute, dt.second, dt.microsecond)
72
73 def _format_timestamp(x):
74     x = float(x)
75     dt = datetime.datetime.fromtimestamp(x)
76     return _format_dt(dt)
77
78 def _none_to_blank(x):
79     if x==None:
80         return ""
81     else:
82         return x
83
84
85 def _trim(req, dbconn, limit=TRIM_LEVEL):
86     global config
87
88     if (dbconn == None):
89         db = config["dbname"]
90         dbconn = DB(name = db)
91
92     dbconn.dict_cursor()
93     rows_total = dbconn.execute("SELECT COUNT(*) FROM `messages`;").fetchall()[0].get("COUNT(*)",0)
94
95     if (req != None):
96         req.content_type = "text/html"
97         req.write("rows_total: " + str(rows_total) + "<br>")
98
99     if (rows_total > (limit * 1.1)):
100         if (req != None):
101             req.write("trim triggered<br>")
102         query = "SELECT `timestamp` FROM `messages` ORDER BY `timestamp` DESC LIMIT 1 OFFSET " + str(limit)
103         if (req != None):
104             req.write("executing: " + query + "<br>")
105         timestamp = dbconn.execute(query).fetchall()[0]["timestamp"]
106         dt = _format_dt(timestamp)
107         if (req != None):
108             req.write("timestamp: " + str(timestamp) + " dt: " + dt + "<br>")
109         query = "DELETE FROM `messages` WHERE `timestamp` < '" + dt + "'";
110         if (req != None):
111             req.write("executing: " + query + "<br>")
112         dbconn.execute(query)
113
114     else:
115         if (req != None):
116             req.write("trim skipped<br>")
117
118     if (req != None):
119         req.write("Done")
120
121 def trim(req, limit=TRIM_LEVEL):
122     """ simple wrapper around _trim, for debugging the output """
123     _trim(req, None, int(limit))
124
125 def purge_host(req, hostname):
126     global config
127
128     db = config["dbname"]
129     dbconn = DB(name = db)
130     dbconn.dict_cursor()
131
132     dbconn.execute("DELETE FROM `messages` WHERE hostname = %s", hostname)
133
134     return "ok"
135
136 def _insert(dbconn, item):
137     if (item.get("hostname","")=="") or (item.get("msg","")==""):
138         return
139
140     timestamp = float(item.get("timestamp", "0"))
141
142     if (timestamp > time.time()+60*60*24*2):
143         # if it's more than 48 hours ahead of now then something must be wrong
144         # (like a node misinterpreting the year). Ignore it.
145         return
146
147     try:
148         dbconn.dict_cursor()
149         old_items = dbconn.execute("SELECT `id`, `timestamp`, `qty`, `hash` from `messages` WHERE hostname=%s and msg=%s;", (item["hostname"], item["msg"])).fetchall()
150
151         qty = int(item.get("qty",1))
152
153         # for duplicate messages from a node, keep only the newest one
154         for old_item in old_items:
155            old_date = old_item["timestamp"] # it's already parsed...
156            old_timestamp = time.mktime(old_date.timetuple())
157
158            if (old_timestamp <= timestamp):
159               qty = qty + int(old_item["qty"])
160 #              dbconn.execute("DELETE FROM `messages` WHERE id=%s", str(old_item["id"]))
161               # under the assumption that an UPDATE is less work on mysql than a DELETE+INSERT, let's just update the fields that we
162               # know need updating (qty, timestamp, hash) and a few of the others that might (extra)
163               # as a useful side effect, this also means that IDs aren't constantly changing
164               dbconn.execute("UPDATE `messages` SET qty=%s, timestamp=%s, hash=%s, extra=%s WHERE id=%s",
165                               (str(qty), _format_timestamp(timestamp), str(item.get("hash","")), str(item.get("extra","")), str(old_item["id"])))
166               return
167            else:
168               # the message we're inserting is older than the message that's already
169               # there, so just update the existing one.
170               dbconn.execute("UPDATE `messages` SET qty=qty+" + str(qty) + " WHERE id=%s", str(old_item["id"]))
171               return
172
173         dbconn.execute("""INSERT INTO `messages`
174                           (`timestamp`, `qty`, `kind`, `log`, `hostname`, `program`, `hash`, `msg`, `extra`)
175                           VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s);""",
176                             (_format_timestamp(timestamp),
177                              str(qty),
178                              str(item.get("kind", "")),
179                              str(item.get("log", "")),
180                              str(item.get("hostname", "")),
181                              str(item.get("program", "")),
182                              str(item.get("hash", "")),
183                              str(item.get("msg", "")),
184                              str(item.get("extra", ""))
185                              ))
186     except Exception, e:
187         # ignore duplicates
188         if "Duplicate entry" in str(e):
189             return
190         raise
191
192 def report(req, **args):
193     global config
194
195     db = config["dbname"]
196
197     # create the database if it does not exist
198     _create_db(req, db)
199
200     dbconn = None
201     try:
202         req.content_type = "text/html"
203
204         dbconn = DB(name = db)
205
206         _trim(None, dbconn)
207
208         # pickle_list is a cheap way to bundle a big list of items together
209         # into one request
210
211         if "pickle_list" in args:
212            l = pickle.loads(args.get("pickle_list"))
213         else:
214            l = [args]
215
216         for item in l:
217             _insert(dbconn, item)
218
219         req.write("OK")
220
221     finally:
222         if dbconn != None:
223             dbconn.close()
224
225 def reduce_items(items):
226     """ this is before I knew how to do an SQL GROUP BY """
227     results = []
228     dict = {}
229     for item in items:
230        key = item.get("kind", "") + item.get("program", "") + item.get("msg", "")
231        if key in dict:
232            dict_item = dict[key]
233            dict_item["sum_qty"] = dict_item["sum_qty"] + int(item["qty"])
234            dict_item["count_hostname"] = dict_item["count_hostname"] + 1
235        else:
236            item["count_hostname"] = 1
237            item["qty"] = int(item["qty"])
238            item["sum_qty"] = item["qty"]
239            dict[key] = item
240            results.append(item)
241
242     return results
243
244 def get_distinct(dbconn, column):
245     dbconn.dict_cursor()
246     results = dbconn.execute("SELECT DISTINCT `" + str(column) + "` from `messages`;").fetchall()
247     return [x[column] for x in results]
248
249 def SELECT_BOX(name, vals, v=0):
250     option_str=""
251     found = False
252     for (key, t) in vals:
253         if (t==v):
254             found = key
255         option_str = option_str + OPTION(str(t), t==v, key)
256
257     if not found:
258         option_str = option_str + OPTION(str(v), True, "Custom: " + str(v))
259
260     return SELECT(name, option_str)
261
262 def build_header(reduce="none", limit="100", search=None, max_age=0):
263     ages = [("infinity", 0), ("1 hour", 3600), ("2 hours", 2*60*60), ("4 hours", 4*60*60), ("8 hours", 8*60*60), ("1 day", 24*60*60),
264             ("2 days", 2*24*60*60), ("3 days", 3*24*60*60), ("1 week", 7*24*60*60), ("4 weeks", 4*7*24*60*60)]
265
266     return FORM(config["url"], "GET",
267                   "search: " + INPUT("search", "text", 60, search) + " " + LINK(config['url'] + "searchhelp.html", "(search help)") +
268                   BR() +
269                   " limit: " + INPUT("limit", "text", 10, limit) +
270                   " merge: " + SELECT("reduce", OPTION("none", reduce=="none", "none") + OPTION("hosts", reduce=="hosts", "hosts") + OPTION("hosts_msgs", reduce=="hosts_msgs", "hosts & msgs")) +
271                   " max_age: " + SELECT_BOX("max_age", ages, int(max_age)) +
272                   BR() +
273                   BUTTON("submit", "submit"))
274
275 def _like(what, s):
276     return "(" + what + " like '%" + s + "%')"
277
278 def _notlike(what ,s):
279     return "(" + what + " not like '%" + s + "%')"
280
281 def _search_part(keys, spec, wild=True, kind="and", invert=False):
282     if spec.startswith("^"):
283         kind="join"
284         spec = spec[1:]
285
286     if spec.startswith("-"):
287         invert = True
288         spec = spec[1:]
289     elif spec.startswith("+"):
290         if (kind=="or"):
291             kind = "and"
292         spec = spec[1:]
293     elif spec.startswith("|"):
294         kind = "or"
295         spec = spec[1:]
296
297     if ":=" in spec:
298         wild=False
299         (field,spec) = spec.split(":=")
300     elif ":" in spec:
301         (field,spec) = spec.split(":")
302     else:
303         field = "concat_ws(" + ",".join(keys) + ")"
304
305     if spec == "":
306         # if we stripped off a modifier and the string is now blank, then
307         # abort
308         return ("abort", "search spec was null")
309
310     if wild:
311         spec = "%" + spec + "%"
312         if invert:
313             compare = "NOT LIKE"
314         else:
315             compare = "LIKE"
316     else:
317         if invert:
318             compare = "!="
319         else:
320             compare = "="
321
322     return (kind, field + " " + compare + " '" + spec + "'")
323
324 def _search_clause(dbconn, keys, search, max_age=0, msg=None):
325     and_conds = []
326     or_conds = []
327     join_conds = []
328     if search:
329         for s in search.split(" "):
330             s = s.strip()
331             if s:
332                 (kind, cond) = _search_part(keys, s)
333                 if kind=="and":
334                     and_conds.append(cond)
335                 elif kind=="or":
336                     or_conds.append(cond)
337                 elif kind=="join":
338                     join_conds.append(cond)
339
340     if max_age>0:
341          and_conds.append("`timestamp` >= '" + _format_timestamp(time.time()-max_age) + "'")
342
343     if msg:
344          and_conds.append("`msg` = '" + dbconn._conn.escape_string(msg) + "'")
345
346     if or_conds and and_conds:
347         where = "WHERE (" + " OR ".join(or_conds) + ") AND (" + " AND ".join(and_conds) + ")"
348     elif or_conds:
349         where = "WHERE " + " OR ".join(or_conds)
350     elif and_conds:
351         where = "WHERE " + " AND ".join(and_conds)
352     else:
353         where = ""
354
355     if join_conds:
356         join = "WHERE " + " AND ".join(join_conds)
357     else:
358         join = ""
359
360     return (where, join)
361
362 def htmlescape(s):
363     return s.replace("<", "&lt;").replace(">", "&gt;")
364
365 def _build_query(dbconn, fields="*", limit="250", search=None, max_age=0, msg=None, groupby=[]): # reduce=False):
366     """ the problem with this solution is that the 'id' field that ends up in
367         the result set doesn't correspond to the item with the maximum timestamp
368         when reduce is used
369     """
370     max_age = int(max_age)
371
372     if groupby:
373         gb_fields = ["sum(qty) as sum_qty",
374                      "count(distinct hostname) as count_hostname",
375                      "max(timestamp) as max_timestamp"]
376
377         if not (msg in groupby):
378            gb_fields.append("count(distinct msg) as count_msg")
379
380         fields = fields + "," + ",".join(gb_fields)
381
382
383         #fields = fields + ",sum(qty) as sum_qty,count(distinct hostname) as count_hostname,count(distinct msg) as count_msg,"
384
385     (search_query, join_query) = _search_clause(dbconn, ["hostname", "program", "log", "kind", "msg"], search, max_age, msg)
386
387     if join_query:
388         dbn = "((select hostname as xhostname from `messages` " + join_query + ") as x join messages on hostname=x.xhostname)"
389     else:
390         dbn = "`messages`"
391
392     query = "SELECT " + fields + " FROM " + dbn
393     if search_query:
394         query = query + " " + search_query
395
396     if groupby:
397         query = query + " GROUP BY " + ",".join(groupby) # kind,program,msg"
398         query = query + " ORDER BY max(timestamp) DESC"
399     else:
400         query = query + " ORDER BY `timestamp` DESC"
401
402     if limit:
403         query = query + " LIMIT " + str(limit)
404
405     return query
406
407 def _build_reduce_join_query(dbconn, fields="*", limit="250", search=None, max_age=0, msg=None, groupby=[]): # False):
408     """ This fixes the incorrect id problem with _build_query when reduce is used.
409         It takes the result of _build_query and joins in back into the original
410         table where the kind,program,msg fields are identical to get the
411         id with the highest timestamp back out.
412
413         Note that we can get duplicates if the maximum timestamp for a particular
414         message is the same as two or more nodes
415     """
416     if groupby:
417         subquery = _build_query(dbconn, fields, limit, search, max_age, msg, groupby)
418         conds = ["y." + fld + "=" + "x." + fld for fld in groupby]
419         conds.append("y.timestamp=x.max_timestamp")
420         query = "SELECT x.*,y.id as max_id FROM (" + subquery + ") as x inner join messages as y on " + " and ".join(conds)
421         return query
422     else:
423         return _build_query(dbconn, fields, limit, search, max_age, msg)
424
425 def _agg_field(item, field, func, default=""):
426     #x = func + "(" + field + ")"
427     x = func + "_" + field
428     if x in item:
429         return item[x]
430     else:
431         return item.get(field,default)
432
433 def nodelist(req, search=None, max_age=0, msg="", inbrowser=False):
434     global config
435
436     db = config["dbname"]
437
438     # create the database if it does not exist
439     _create_db(req, db)
440
441     dbconn = None
442     try:
443         if inbrowser:
444             req.content_type = "text/html"
445         else:
446             req.content_type = "text/ascii"
447             req.headers_out["Content-Disposition"] = "attachment; filename=nodelist.txt"
448
449         dbconn = DB(name = db)
450         dbconn.dict_cursor()
451
452         query = _build_query(dbconn, "DISTINCT hostname", limit=None, search=search, max_age=max_age, msg=msg)
453
454         othernodes = dbconn.execute(query).fetchall()
455         for node in othernodes:
456             req.write(node["hostname"] + "\n")
457             if inbrowser:
458                req.write("<br>\n")
459
460     finally:
461         if dbconn != None:
462             dbconn.close()
463
464 def index(req, reduce="hosts", limit="250", search=None, max_age=0):
465     global config
466
467     db = config["dbname"]
468
469     if reduce=="hosts":
470         groupby = ["kind", "program", "msg"]
471     elif reduce=="hosts_msgs":
472         groupby = ["kind", "program"]
473     else:
474         groupby = []
475     # reduce = (reduce == "yes")
476
477     # create the database if it does not exist
478     _create_db(req, db)
479
480     dbconn = None
481     try:
482         req.content_type = "text/html"
483
484         dbconn = DB(name = db)
485
486         req.write(build_header(reduce, limit, search, max_age) + "<br>")
487
488         query = _build_reduce_join_query(dbconn, "*", limit, search, max_age, None, groupby)
489
490         req.write("query-executed: " + query + "<br>")
491
492         dbconn.dict_cursor()
493         items = dbconn.execute(query).fetchall()
494
495         #if reduce_old:
496         #   items = reduce_items(items)
497
498         req.write(LINK(config['url'] + "nodelist?search=" + urllib.quote_plus(_none_to_blank(search)) + "&max_age=" + str(max_age), "[download nodelist]") + "<BR>")
499
500         req.write("<TABLE border=1>")
501
502         req.write(TR(TH("id") +
503                      TH("qty") +
504                      TH("timestamp") +
505                      TH("kind") +
506                      TH("log") +
507                      TH("hostname") +
508                      TH("program") +
509                      TH("msg")
510                      ))
511
512         for item in items:
513              id = str(_agg_field(item,"id","max"))
514              link = config['url'] + "detail?id=" + id
515
516              msg_count = int(item.get("count_msg",1))
517              if (msg_count>1):
518                  msg = "(" + str(msg_count) + " msgs)"
519              else:
520                  msg = item.get("msg","")
521
522              host_count = int(item.get("count_hostname",1))
523              if (host_count > 1):
524                  if (msg_count>1):
525                      nl_msg = ""
526                  else:
527                      nl_msg = item.get("msg","")
528                  node_search = _none_to_blank(search) + " program:=" + item.get("program","") + " kind:=" + item.get("kind","")
529                  hostname = LINK(config['url'] + "nodelist?search=" + urllib.quote_plus(node_search) + "&max_age=" + str(max_age) + "&msg=" + urllib.quote_plus(nl_msg), "[dl]") + \
530                             " (" + \
531                             LINK(config['url'] +  "nodelist?inbrowser=1&search=" + urllib.quote_plus(node_search) + "&max_age=" + str(max_age) + "&msg=" + urllib.quote_plus(nl_msg),str(host_count) + " hosts") + ")"
532              else:
533                  hostname = item.get("hostname", "")
534
535              req.write(TR(TD(LINK(link,id)) +
536                           TD(_agg_field(item,"qty","sum")) +
537                           TD(_agg_field(item,"timestamp","max")) +
538                           TD(item.get("kind", "")) +
539                           TD(item.get("log", "")) +
540                           TD(hostname) +
541                           TD(item.get("program", "")) +
542                           TD(htmlescape(msg)) # item.get("msg", "")))
543                           ))
544
545         req.write("</TABLE>")
546
547         rows_total = dbconn.execute("SELECT COUNT(*) FROM `messages`;").fetchall()[0].get("COUNT(*)",0)
548         rows_query = len(items)
549
550         req.write("<BR><TABLE border=1>")
551         req.write(TR(TD("rows-total") + TD(str(rows_total))))
552         req.write(TR(TD("rows-query") + TD(str(rows_query))))
553         req.write(TR(TD("kind") + TD(", ".join(get_distinct(dbconn, "kind")))))
554         req.write(TR(TD("log") + TD(", ".join(get_distinct(dbconn, "log")))))
555         req.write(TR(TD("program") + TD(", ".join(get_distinct(dbconn, "program")))))
556         req.write("</TABLE>")
557
558     finally:
559         if dbconn != None:
560             dbconn.close()
561
562 def correlate_single(req, dbconn, co_kind, co_program):
563     dbconn.dict_cursor()
564     items = dbconn.execute("""select f.hostname, f.msg
565                      from (\r
566                            select hostname, program, kind, max(timestamp) as maxtimestamp\r
567                            from messages\r
568                            where kind=%s and program=%s\r
569                            group by hostname, program, kind\r
570                         ) as x inner join messages as f on f.hostname=x.hostname and f.kind=x.kind and f.program=x.program and f.timestamp=x.maxtimestamp;""", (co_kind, co_program)).fetchall()
571
572     dict = {}
573     for item in items:
574         dict[item.get("hostname")] = item.get("msg")
575
576     return dict
577
578 def correlate_multi(req, dbconn, co_kind, co_program):
579     dbconn.dict_cursor()
580     items = dbconn.execute("select hostname, msg from messages where kind=%s and program=%s", (co_kind, co_program)).fetchall()
581
582     dict = {}
583     for item in items:
584         hostname = item.get("hostname")
585         msg = item.get("msg")
586         if hostname in dict:
587             dict[hostname].append(msg)
588         else:
589             dict[hostname] = [msg]
590
591     for key in dict:
592         dict[key] = ",".join(dict[key])
593
594     return dict
595
596
597 def detail(req, id, co_kind="", co_program=""):
598     global config
599
600     db = config["dbname"]
601
602     # create the database if it does not exist
603     _create_db(req, db)
604
605     dbconn = None
606     try:
607         req.content_type = "text/html"
608
609         dbconn = DB(name = db)
610
611         dbconn.dict_cursor()
612         items = dbconn.execute("SELECT * FROM `messages` WHERE ID = %s;", id).fetchall()
613         item = items[0]
614
615         req.write("<TABLE>")
616         req.write(TR(TD("id:") + TD(item.get("id",""))))
617         req.write(TR(TD("qty:") + TD(str(item.get("qty","")))))
618         req.write(TR(TD("timestamp:") + TD(item.get("timestamp",""))))
619         req.write(TR(TD("kind:") + TD(item.get("kind",""))))
620         req.write(TR(TD("log:") + TD(item.get("log",""))))
621         req.write(TR(TD("hostname:") + TD(item.get("hostname",""))))
622         req.write(TR(TD("program:") + TD(item.get("program",""))))
623         req.write(TR(TD("msg:") + TD(htmlescape(item.get("msg","")))))
624         req.write("</TABLE>")
625
626         req.write("<PRE>")
627         req.write(htmlescape(item.get("extra")))
628         req.write("</PRE>")
629
630         if co_program and co_kind:
631            co_dict = correlate_multi(req, dbconn, co_kind, co_program)
632            co_header = TH(co_kind+":"+co_program)
633         else:
634            co_dict = None
635            co_header = ""
636
637         othernodes = dbconn.execute("SELECT `hostname`,`id`,`timestamp` FROM `messages` WHERE `msg`=%s AND `kind`=%s AND `program`=%s ORDER BY `timestamp` DESC;", (item.get("msg",""), item.get("kind",""), item.get("program", ""))).fetchall()
638         req.write("<br><br>All Hosts (%d): "  % (len(othernodes)))
639         req.write(LINK(config['url'] + "detail_nodelist?id=" + str(id), "[download nodelist]"))
640         req.write("<TABLE border=1>")
641         req.write(TR(TH("id") + TH("hostname") + TH("timestamp") + co_header))
642         for node in othernodes:
643             link = config['url'] + "detail?id=" + str(node["id"])
644             if co_dict and node["hostname"] in co_dict:
645                 co_data = TD(co_dict[node["hostname"]])
646             else:
647                 co_data = ""
648             req.write(TR(TD(LINK(link,node["id"])) +
649                          TD(node["hostname"]) +
650                          TD(node["timestamp"]) +
651                          co_data
652                          ))
653         req.write("</TABLE>")
654
655     finally:
656         if dbconn != None:
657             dbconn.close()
658
659 def detail_nodelist(req, id):
660     global config
661
662     db = config["dbname"]
663
664     # create the database if it does not exist
665     _create_db(req, db)
666
667     dbconn = None
668     try:
669         req.content_type = "text/ascii"
670
671         req.headers_out["Content-Disposition"] = "attachment; filename=nodelist.txt"
672
673         dbconn = DB(name = db)
674
675         dbconn.dict_cursor()
676         items = dbconn.execute("SELECT * FROM `messages` WHERE ID = %s;", id).fetchall()
677         item = items[0]
678
679         othernodes = dbconn.execute("SELECT `hostname`,`id` FROM `messages` WHERE `msg`=%s AND `kind`=%s AND `program`=%s ORDER BY `timestamp` DESC;", (item.get("msg",""), item.get("kind",""), item.get("program", ""))).fetchall()
680         for node in othernodes:
681             req.write(node["hostname"] + "\n")
682
683     finally:
684         if dbconn != None:
685             dbconn.close()
686
687
688 def _create_db(req, db):
689     global request
690
691     request = req
692     debug(req, "Creating database %s" % (db))
693     dbname = db
694     dbconn = DB(name="information_schema")
695     cursor = dbconn.execute("SELECT * FROM `SCHEMATA` WHERE `SCHEMA_NAME` = '%s'" % (dbname))
696     row = cursor.fetchone()
697     if row == None:
698         try:
699             file = config["rootpasswordfile"]
700             fd = open(file, "r")
701             password = fd.readline().rstrip()
702             fd.close()
703         except:
704             error('Unable to read mysql root password from "%s"' % (file))
705             raise
706         dbconn = DB(user="root", passwd=password, name="information_schema")
707         debug(req, "Creating database")
708         dbconn.execute("CREATE DATABASE IF NOT EXISTS `%s`" % (dbname))
709         dbconn.execute("GRANT ALL PRIVILEGES ON `%s` . * TO 'logmon'@'%%'" % (dbname))
710         dbconn.execute("GRANT ALL PRIVILEGES ON `%s` . * TO 'logmon'@'localhost'" % (dbname))
711
712         dbconn = DB(name=dbname)
713
714         dbconn.execute("""CREATE TABLE IF NOT EXISTS `messages`
715                           (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
716                            `qty` INT,
717                            `timestamp` TIMESTAMP DEFAULT 0,
718                            `kind` VARCHAR(32),
719                            `log` VARCHAR(32),
720                            `hostname` VARCHAR(80),
721                            `program` VARCHAR(32),
722                            `hash` CHAR(16) NOT NULL,
723                            `msg` VARCHAR(255),
724                            `extra` TEXT,
725                            UNIQUE(`hash`),
726                            INDEX (`hostname`),
727                            INDEX (`msg`),
728                            INDEX (`kind`,`program`,`msg`)); """)
729
730
731 def delete_db(req, db=config["dbname"]):
732
733     dbconn = None
734     try:
735         debug(req, "delete %s" % (db))
736         dbname = db
737         req.content_type = "text/plain"
738         if db != None:
739             try:
740                 file = "/root/mysql-root-pass.txt"
741                 fd = open(file, "r")
742                 password = fd.readline().rstrip()
743                 fd.close()
744             except:
745                 error('Unable to read mysql root password from "%s"' % (file))
746                 raise
747             dbconn = DB(name="information_schema")
748             cursor = dbconn.execute("SELECT * FROM `SCHEMATA` WHERE `SCHEMA_NAME` = '%s'" % (dbname))
749             row = cursor.fetchone()
750             if row != None:
751                 dbconn=DB(name=dbname, user="root", passwd=password)
752                 dbconn.execute("DROP DATABASE `%s`" % (dbname))
753             dbconn.close()
754         req.write("OK")
755         request = None
756     finally:
757         if dbconn != None:
758             dbconn.close()
759
760
761 def reset(req, db=config["dbname"]):
762     if db:
763         dbname = db
764         dbconn = DB(name=dbname)
765         dbconn.execute("TRUNCATE `nodes`")
766         dbconn.close()
767     req.write("OK");