Permet d'organiser une base de données brutes de fonctionnement du relais

| svxlogreport.py
#!/usr/bin/python
""""
"  SVXLINK ANALYSING OF SVXLINK LOGFILES 
"  SVXLink Card on http://svxcard.f5uii.net
"
"  This Python software generate a SQLite database, in which the statitics of activities of SVXlink is recorded
"  04.04.2016 (b)- Christian, F5UII
"  version 0.2 - Under Creative Commons licence [by-nc-sa] http://creativecommons.org/licenses/by-nc-sa/4.0/
"
"""
import ConfigParser, os
import time,datetime # used by follow function
import sys
import sqlite3
 
svxlogfile="/var/log/svxlink"         # Real time log which is analysed by this software
#svxlogfile="/home/pi/test/svxlink"         # Real time log which is analysed by this software
#svxlogfile="/home/pi/svxlink.log"         # Real time log which is analysed by this software
#svxlogfile="/home/pi/svxlink-log-old"
 
svxconfigfile="/etc/svxlink/svxlink.conf" # Configuration File of SVXLink
svxstatusfile="/home/pi/svx_log.conf" # Generated file by this software
sensorids = ["10-0008010955c3"]          # Here the serial number of the 18b20 : ls /sys/bus/w1/devices/
db='/home/pi/svxreport.sqlite'
 
 
#LOGICS NAMES
StrSimplex = "SimplexLogic"                 # Identification of a Simplex Logic in Svxlink, by default = "SimplexLogic"
StrRepeater = "RepeaterLogic"               # Identification of a Repeater Logic in Svxlink, by default = "RepeaterLogic"
#MODULES NAMES
StrHelp = "Help"                      # Identification of a Help Module in Svxlink, by default = "Help"
StrParrot = "Parrot"                  # Identification of a Parrot Module in Svxlink, by default = "Parrot"
StrEcholink = "EchoLink"              # Identification of a Echolink Module in Svxlink, by default = "EchoLink"
StrVoicemail = "TclVoiceMail"         # Identification of a Voicemail Module in Svxlink, by default = "TclVoiceMail"
StrMetarinfo = "MetarInfo"            # Identification of a Metar informations Module in Svxlink, by default = "MetarInfo"
StrDtmfrepeater = "DtmfRepeater"      # Identification of a DTMF Repeater Module in Svxlink, by default = "DtmfRepeater"
StrSelcallenc = "SelCallEnc"          # Identification of a Selective Call Encoder Module in Svxlink, by default = "SelCallEnc"
StrPropagation = "PropagationMonitor" # Identification of a Propagation information Module in Svxlink, by default = "PropagationMonitor"
 
# Variables initialisation
tx=0
rx=0
tone=0
svxrun=0
longbeacon=0
shortbeacon=0
module_echolink=0
echolinkstat_count=0
process = 0
rep_mod_help=0
rep_mod_parrot=0
rep_mod_echolink=0
rep_mod_metar=0
rep_mod_voicemail=0
rep_mod_dtmf=0
rep_mod_selcall=0
rep_mod_propag=0
rep_recoder=0   #QSO recorder , by default be activated by DTMF = 81#, desactivated by DTMF = 80#
sim_mod_help=0
sim_mod_parrot=0
sim_mod_echolink=0
sim_mod_metar=0
sim_mod_voicemail=0
sim_mod_dtmf=0
sim_mod_selcall=0
sim_mod_propag=0
sim_recoder=0   #QSO recorder , by default be activated by DTMF = 81#, desactivated by DTMF = 80#
Last_Echolink_station = ""   #Callsign of the last Echolink station
Echok_Station_conn = 0      # 1 = Echolink Station connected on repeater 
 
def file_len(fname):
    with open(fname) as f:
        for i, l in enumerate(f):
            pass
    return i + 1
 
nblines = file_len(svxlogfile)
 
# Writing the file of current status
def writeconf():
 
    fo = open(svxstatusfile, "w")
    lin = fo.writelines( "\n###  SVXLINK LOG STATS  ###\n\n\n" )
    lin = fo.writelines( "[STAT]          \n\n")
    lin = fo.writelines( "STATION_INFO="+ svxconfigfile +"\n")
 
    fo.close()
 
 
 
def follow(thefile):
    #thefile.seek(0,2)      # Go to the end of the file
    while True:
         line = thefile.readline()
         if not line:
             print 'o'+'\r'
             time.sleep(0.1)    # Sleep briefly
             print '-'+'\r'
             continue
         yield line
 
 
# Checking of the log files 
config = ConfigParser.ConfigParser()
config.read(svxconfigfile)
log_format = ""
log_format = config.get('GLOBAL', 'TIMESTAMP_FORMAT')[1:-1]
Sim_modules = ""
Rep_modules = ""
Sim_callsign = ""
Rep_callsign = ""
 
timer_module = {}
timer_transmit = {}
timer_squelch = {}
timer_echolink = {}
 
Station_info=""
Freq=""
Ant_h=""
Ant_gain=""
try: 
    Freq = config.get('LocationInfo', 'FREQUENCY')
except ConfigParser.NoOptionError, err: print "Info : "+str(err)
try:
    Power = config.get('LocationInfo', 'TX_POWER')
except ConfigParser.NoOptionError, err: print "Info : "+str(err)
try:
    Ant_gain = config.get('LocationInfo', 'ANTENNA_GAIN')
except ConfigParser.NoOptionError, err: print ("Info : "+str(err))
try:
    Ant_h = config.get('LocationInfo', 'ANTENNA_HEIGHT')
except ConfigParser.NoOptionError, err: print "Info : "+str(err)
if Freq :
    Station_info = "Frequency is set to " + Freq + ". "
if Ant_h :
    Station_info =  Station_info + "The antenna is installed on "+ Ant_h + " height. "
if Ant_gain :
    Station_info = Station_info + "Antenna gain is " + Ant_gain +". "
print Station_info
 
 
#writeconf()  # Write the status file on disk
#Checking the real time status with Log file
i=0
 
 
conn = sqlite3.connect(db)
cursor = conn.cursor()
 
def create_table (table):
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS """+table+"""(
     id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,
     date DATETIME NOT NULL,
     duration REAL,
     channel VARCHAR)""")
    conn.commit()
 
create_table ('squelch')
create_table ('tone')
create_table ('transmit')
create_table ('echolink_activity')
 
 
cursor.execute("""
CREATE TABLE IF NOT EXISTS  modules (
 id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,
 date DATETIME NOT NULL,
 duration REAL,
 module VARCHAR,
 channel VARCHAR)""")
conn.commit()
cursor.execute("""
CREATE TABLE IF NOT EXISTS  identification (
 id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,
 date DATETIME NOT NULL,
 duration REAL,
 module VARCHAR,
 channel VARCHAR)""")
conn.commit()
cursor.execute("""
CREATE TABLE IF NOT EXISTS  dtmfcmd (
 id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,
 date DATETIME NOT NULL,
 module VARCHAR,
 code VARCHAR)""")
conn.commit()
 
 
def insert_ifnotexist(table, dater, dur, c):
 
 
	sql = "insert into "+ table +" (date, duration, channel) select  ?, ?,? where not exists (select 1 from "+ table +"  WHERE date = ? AND duration = ? AND channel = ?)"
	cursor.execute(sql,(  dater, dur, c, dater, dur, c))
	conn.commit()
	'''
 
    '''
 
def insert_ifnotexist4(table, dater, dur, m,  c):
 
    sql2 = "insert into "+ table +" (date, duration, module,channel) select  ?, ?, ?,? where not exists (select 1 from "+ table +"  WHERE date = ? AND duration = ? AND module = ? AND channel = ?)"
    cursor.execute(sql2,(  dater, dur, m, c, dater, dur,m,  c))
    conn.commit()
 
 
 
def insert_ifnotexist_dtmf(table, dater,  m,  c):
 
	sql3 = "insert into "+ table +" (date, module, code) select  ?, ?,? where not exists (select 1 from "+ table +"  WHERE date = ? AND module = ? AND code = ?)"
	cursor.execute(sql3,(  dater, m, c, dater, m, c))
	conn.commit()
 
 
 
def det_channel(line_string, position_timestamp):
    comment = line_string[position_timestamp+2:]  #extract of the channel (exemple "Rx1")
    comment_parts =  comment.split(':')
    return comment_parts[0].strip(' ').replace('.', '').replace('\n', '')
 
 
l= len (time.strftime( "%c", time.localtime() ))   # length of timestamp in log line
print "Log format extract from "+svxconfigfile+" : "+ log_format
logfile = open(svxlogfile)
loglines = follow(logfile)
 
#with  open(svxlogfile): # as loglines:
#loglines = follow(logfile)  # go to the end of the file
n=0
for line in loglines:
            n=n+1
            #print line
            #line = line[3:]  #specific for a multiple %M %c - to comment in the general case %c
            print "{:3.2f}%".format(n/float(nblines)*100)+'\r',
            try:
                t1 = datetime.datetime.strptime(line[:l], "%c")
            except Exception, e:
                print e
                print line
 
            #print "returned tuple: %s " % struct_time
            '''      
 
            elif "Shutting down application" in line:
                    print "SHUTTING DOWN SVX"
                    svxrun = 0
 
 
            '''            
            if "The squelch is OPEN"  in line:
                    #print "SQUELCH OPEN"
                    squelch_open_time = t1
 
                    channel_squelch_open = det_channel(line, l)
                    timer_squelch [channel_squelch_open] = t1
 
 
                    rx = 1
            elif "The squelch is CLOSED" in line:   ############# ATTENTION TRAITER LES MULTI SQUEL / channel
                    #print "SQUELCH CLOSED"
                    try:
                        channel_squelch_close = det_channel(line, l)
                        squelch_duration = t1 - timer_squelch [channel_squelch_open]
                        squelch_duration_s = squelch_duration.total_seconds()                      
                        insert_ifnotexist('squelch',squelch_open_time, squelch_duration_s, channel_squelch_close)    
                    except NameError:
                      print "No  t1"
 
 
                    rx = 0
            elif "tone call detected" in line:
                    #print "TONE DETECTED"
                    channel =  det_channel(line, l)
                    insert_ifnotexist('tone',t1, 1, channel)    
                    tone = 1
            elif "Activating module" in line:
                    #print "ACTIVATED MODULE"
                    channel =  det_channel(line, l)
                    #print channel
                    pos=line.find("Activating module")+len("Activating module")
                    module=  line[pos:]
                    module= module.strip(' ')
                    module = module.replace('.', '')
 
                    timer_module[module] = t1
 
 
            elif "Deactivating module" in line:
                    #print "DEACTIVATED MODULE"
                    channel =  det_channel(line, l)
                    #print channel
                    pos=line.find("Deactivating module")+len("Deactivating module")
                    module=  line[pos:]
                    module= module.strip(' ')
                    module = module.replace('.', '')
                    #print timer_module[module]
                    module_duration = t1 - timer_module[module]
                    duree= module_duration.total_seconds()
                    insert_ifnotexist4('modules',t1,duree, module, channel)
 
            elif "DTMF command received in module" in line:        
                    channel =''
                    #print "DTMF command received in module"
                    pos=line.find("DTMF command received in module")+len("DTMF command received in module")
                    dtmfcmd=  line[pos:]
                    p=dtmfcmd.split(':')
                    module= p[0].strip(' ').replace('.', '').replace('\n', '')
                    dtmf= p[1].strip(' ').replace('.', '').replace('\n', '')
                    if dtmf=='' : dtmf="#"
                    insert_ifnotexist_dtmf('dtmfcmd', t1,  module,  dtmf)
            elif " Turning the transmitter ON" in line:
                    channel =  det_channel(line, l)
                    #timer_module.update({channel: t1})
                    timer_transmit[channel] = t1
 
            elif " Turning the transmitter OFF" in line:
                    try : 
                        channel =  det_channel(line, l)
                        transmit_duration= t1 - timer_transmit[channel]
                        insert_ifnotexist('transmit',t1,transmit_duration.total_seconds(),channel)
                    except Exception, e:
                        print "No transmit On timestamp "
            elif "Sending long identification" in line:
                    #print "LONG BEACON"
                    channel =  det_channel(line, l)
                    insert_ifnotexist4('identification',t1,1,'long',channel)
                    longbeacon = 1
            elif "Sending short identification." in line:
                    #print "SHORT BEACON"
                    channel =  det_channel(line, l)
                    insert_ifnotexist4('identification',t1,1,'short',channel)
                    shortbeacon=1
            elif "EchoLink QSO state changed to CONNECTED" in line:
                    callsign =  det_channel(line, l) 
                    timer_echolink[callsign] = t1
 
            elif "EchoLink QSO state changed to DISCONNECTED" in line:
                    try:
                        callsign =  det_channel(line, l) 
                        echol_duration= t1 - timer_echolink[callsign]
 
                        insert_ifnotexist('echolink_activity',t1,echol_duration.total_seconds(),callsign)
 
                        del timer_echolink[callsign]
 
                    except Exception, e:
                        #print "No echolink up / timestamp "
                        True=True  
 
 
            '''
            elif StrRepeater+": Activating module " + StrParrot  in line:
                    print StrParrot+ " module activated on "+StrRepeater
                    rep_mod_parrot = 1    
            elif StrRepeater+": Deactivating module " + StrParrot  in line:
                    print StrParrot+" module desactivated on "+StrRepeater
                    rep_mod_parrot = 0
            elif StrRepeater+": Activating module " + StrHelp  in line:
                    print StrHelp+" module activated on "+StrRepeater
                    rep_mod_help = 1    
            elif StrRepeater+": Deactivating module " + StrHelp  in line:
                    print StrHelp+" module desactivated on "+StrRepeater
                    rep_mod_help = 0
            elif StrRepeater+": Activating module " + StrEcholink  in line:
                    print StrEcholink+" module activated on "+StrRepeater
                    rep_mod_echolink = 1    
            elif StrRepeater+": Deactivating module " + StrEcholink in line:
                    print StrEcholink+" module desactivated on "+StrRepeater
                    rep_mod_echolink = 0                
            elif StrRepeater+": Activating module " + StrMetarinfo  in line:
                    print StrMetarinfo+" module activated on "+StrRepeater
                    rep_mod_metar = 1    
            elif StrRepeater+": Deactivating module " + StrMetarinfo in line:
                    print StrMetarinfo+" module desactivated on "+StrRepeater
                    rep_mod_metar = 0     
            elif StrRepeater+": Activating module " + StrVoicemail  in line:
                    print StrVoicemail+" module activated on "+StrRepeater
                    rep_mod_voicemail = 1    
            elif StrRepeater+": Deactivating module " + StrVoicemail in line:
                    print StrVoicemail+" module desactivated on "+StrRepeater
                    rep_mod_voicemail = 0         
            elif StrRepeater+": Activating module " + StrDtmfrepeater  in line:
                    print StrDtmfrepeater+" module activated on "+StrRepeater
                    rep_mod_dtmf = 1    
            elif StrRepeater+": Deactivating module " + StrDtmfrepeater in line:
                    print StrDtmfrepeater+" module desactivated on "+StrRepeater
                    rep_mod_dtmf = 0    
            elif StrRepeater+": Activating module " + StrSelcallenc in line:
                    print StrSelcallenc+" module activated on "+StrRepeater
                    rep_mod_selcall= 1    
            elif StrRepeater+": Deactivating module " + StrSelcallenc in line:
                    print StrSelcallenc+" module desactivated on "+StrRepeater
                    rep_mod_selcall = 0    
            elif StrRepeater+": Activating module " + StrPropagation in line:
                    print StrPropagation+" module activated on "+StrRepeater
                    rep_mod_propag= 1    
            elif StrRepeater+": Deactivating module " + StrPropagation in line:
                    print StrPropagation+" module desactivated on "+StrRepeater
                    rep_recoder = 0    
            elif StrRepeater+": Activating QSO recorder" in line:
                    print "QSO recorder activated on "+StrRepeater
                    rep_recoder= 1    
            elif StrRepeater+": Deactivating QSO recorder" in line:
                    print "QSO recorder module desactivated on "+StrRepeater
                    rep_mod_propag = 0    
            elif StrSimplex+": Activating module " + StrParrot  in line:
                    print StrParrot+ " module activated on "+StrSimplex
                    sim_mod_parrot = 1    
            elif StrSimplex+": Deactivating module " + StrParrot  in line:
                    print StrParrot+" module desactivated on "+StrSimplex
                    sim_mod_parrot = 0
            elif StrSimplex+": Activating module " + StrHelp  in line:
                    print StrHelp+" module activated on "+StrSimplex
                    sim_mod_help = 1    
            elif StrSimplex+": Deactivating module " + StrHelp  in line:
                    print StrHelp+" module desactivated on "+StrSimplex
                    sim_mod_help = 0
            elif StrSimplex+": Activating module " + StrEcholink  in line:
                    print StrEcholink+" module activated on "+StrSimplex
                    sim_mod_echolink = 1    
            elif StrSimplex+": Deactivating module " + StrEcholink in line:
                    print StrEcholink+" module desactivated on "+StrSimplex
                    sim_mod_echolink = 0                
            elif StrSimplex+": Activating module " + StrMetarinfo  in line:
                    print StrMetarinfo+" module activated on "+StrSimplex
                    sim_mod_metar = 1    
            elif StrSimplex+": Deactivating module " + StrMetarinfo in line:
                    print StrMetarinfo+" module desactivated on "+StrSimplex
                    sim_mod_metar = 0     
            elif StrSimplex+": Activating module " + StrVoicemail  in line:
                    print StrVoicemail+" module activated on "+StrSimplex
                    sim_mod_voicemail = 1    
            elif StrSimplex+": Deactivating module " + StrVoicemail in line:
                    print StrVoicemail+" module desactivated on "+StrSimplex
                    sim_mod_voicemail = 0         
            elif StrSimplex+": Activating module " + StrDtmfrepeater  in line:
                    print StrDtmfrepeater+" module activated on "+StrSimplex
                    sim_mod_dtmf = 1    
            elif StrSimplex+": Deactivating module " + StrDtmfrepeater in line:
                    print StrDtmfrepeater+" module desactivated on "+StrSimplex
                    sim_mod_dtmf = 0    
            elif StrSimplex+": Activating module " + StrSelcallenc in line:
                    print StrSelcallenc+" module activated on "+StrSimplex
                    sim_mod_selcall= 1    
            elif StrSimplex+": Deactivating module " + StrSelcallenc in line:
                    print StrSelcallenc+" module desactivated on "+StrSimplex
                    sim_mod_selcall = 0    
            elif StrSimplex+": Activating module " + StrPropagation in line:
                    print StrPropagation+" module activated on "+StrSimplex
                    sim_mod_propag= 1    
            elif StrSimplex+": Deactivating module " + StrPropagation in line:
                    print StrPropagation+" module desactivated on "+StrSimplex
                    sim_mod_propag = 0    
            elif StrSimplex+": Activating QSO recorder" in line:
                    print "QSO recorder activated on "+StrSimplex
                    sim_recoder= 1    
            elif StrSimplex+": Deactivating QSO recorder" in line:
                    print "QSO recorder module desactivated on "+StrSimplex
                    sim_recoder = 0    
 
            elif "EchoLink QSO state changed to CONNECTED" in line:
                ch =  line.split(':')
                Last_Echolink_station = ch[0]
                Echok_Station_conn = 1
                print "ECHOLINK STATION CONNECTED"
            elif "EchoLink QSO state changed to BYE_RECEIVED" in line:
                Echok_Station_conn = 0
                print "ECHOLINK STATION DISCONNECTED"
 
 
 
            writeconf()  # Write the status file on disk
            '''     
conn.commit()      
conn = sqlite3.connect(db)
print "fin"
print sqllist