def compareChosenDevicesByAlarmPriority(fileName, priority, device_filtering, cursor):
d = fileName
l = priority
fw = File_writer(d, priority, "column", "analysis")
fw.create_txt("../../output/columnAnalysis/")
fw.write_txt('\nDEVICE '+ str(d) + ': ')
fw.write_txt('\n\tPRIORITY ' + str(l) + ':')
query = ("select Device, Time, State, Tag, Description from electric where device=%s and livellopriorita=%s and action='Alarm CAME'")
cursor.execute(query, (d,l))
events = cursor.fetchall()
allSeenEvents = []
devicesDict = dict() # key = device, value = an object of the class "columnStats"
for e in events:
if e[0] not in devicesDict:
devicesDict[e[0]] = ColumnStats()
allSeenEvents.append(e)
query = ("select Device, Time, State, Tag, Description from electric where time>=(%s) and time <= (%s + interval %s minute) and action='Alarm CAME' order by time;")
cursor.execute(query, (e[1], e[1], 5))
eventsAfter = cursor.fetchall()
if e not in allSeenEvents:
allSeenEvents.append(e)
devicesDict[e[0]].updateState(e[2])
devicesDict[e[0]].updateTag(e[3])
devicesDict[e[0]].updateDescr(e[4])
#else:
# devicesDict[e[0]].addDuplicate()
for ea in eventsAfter:
if ea[0] in device_filtering or ea[0] == d:
if ea[0] not in devicesDict:
devicesDict[ea[0]] = ColumnStats()
if ea not in allSeenEvents:
allSeenEvents.append(ea)
devicesDict[ea[0]].updateState(ea[2])
devicesDict[ea[0]].updateTag(ea[3])
devicesDict[ea[0]].updateDescr(ea[4])
devicesDict[ea[0]].addTimestamp(e[1], ea[1])
#else:
# devicesDict[ea[0]].addDuplicate()
# PUT EVERYTHING IN THE TXT FILE
for k in devicesDict:
fw.write_txt("DEVICE " + k + ":", newline=True)
devicesDict[k].writeState(fw)
devicesDict[k].writeTag(fw)
devicesDict[k].writeDescr(fw)
devicesDict[k].writeTemporalPosition(fw)
def process_files(self, select_priority, file_selection, file_suffix, log = True):
''' (1)
Method that extracts data from the text files.
-----------------
Parameters:
select_priority -- A string with the priority level to be considered
files_used -- The number of the file to be used
log -- "True" if you want to print debug information in the console
'''
if not select_priority or not file_selection:
raise DataError("Priority or file not chosen. Exiting now.")
num = file_selection
self.extractor.extract(self.file_names[num-1], self.true_device_names[num-1], select_priority, file_suffix)
self.device_considered = self.file_names[num-1]
self.device_considered_realName = self.true_device_names[num-1]
self.priority_considered = select_priority
self.file_suffix = file_suffix
self.file_writer = File_writer(self.device_considered, self.priority_considered)
self.log("Priority level considered: " + select_priority, log)
self.log("File considered: " + str(self.file_names[num-1]), log)
self.log("Text files data extraction completed.", log)
def compareChosenDevicesByAlarmPriority(cursor):
#chosenDevices = ['EHS60/BE', 'EXS4/8X', 'EMC001*9', 'EXS106/2X', 'ESS1*84',
# 'ESS11/5H', 'ESS406/E91', 'ESS407/E91', 'ESS520/E91', 'ESS11*84']
# our devices: ['EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X']
chosenDevices = ['EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X']
levelsOfPriority = ['L0', 'L1', 'L2', 'L3']
for d in chosenDevices:
fw = File_writer(d, "afterDescrNoDup")
fw.create_txt("../../res/newres/")
markedEvents = []
print '\nDEVICE '+ str(d) + ': '
fw.write_txt('\nDEVICE '+ str(d) + ': ')
for l in levelsOfPriority:
print '\n\tPRIORITY ' + str(l) + ':'
fw.write_txt('\n\tPRIORITY ' + str(l) + ':')
query = ("select * from electric where device=%s and livellopriorita=%s and action='Alarm CAME'")
cursor.execute(query, (d,l))
events = cursor.fetchall()
afterSeq = [] # Contiene le liste dei device che vediamo in ogni riga nei file di testo
afterSequence = [] # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
for e in events:
query = ("select * from electric where time>=(%s) and time <= (%s + interval %s minute) and action='Alarm CAME' order by time;")
cursor.execute(query, (e[0], e[0], 5))
eventsAfter = cursor.fetchall()
devicesAfter = [] # all events that happened 5 min after the event "e"
for ea in eventsAfter:
if ea not in markedEvents: #CONDIZIONE per rimuovere i DUPLICATI
markedEvents.append(ea)
if ea[4] != d: #CONDIZIONE per evitare problemi con il device di riferimento e l'aggiunta di stati, tag o descr.
extraColumn = ea[6].encode('ascii', 'ignore').decode('ascii')
extraColumn.replace("'", "")
extraColumn = re.escape(extraColumn)
devicesAfter.append(ea[4] + "--" + extraColumn)
if devicesAfter != []:
afterSequence.append(devicesAfter) # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
devicesAfter=list(set(devicesAfter)) #Lista non ordinata di distinct devices
afterSeq.append(devicesAfter) #Lista di liste (i.e. tutto quello dopo "distinct device after 5 min")
#CONSOLE
print '\n\t\tDistinct devices after 5 minutes: [ '
for xx in afterSeq:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "', ",
print '], '
print ']'
#TEXT FILE
fw.write_txt('\n\t\tDistinct devices after 5 minutes: [ ')
for xx in afterSeq:
fw.write_inline( '\t\t[ ', )
for yy in xx:
fw.write_inline( "'" + str(yy) + "', " )
fw.write_txt('], ')
fw.write_txt(']')
print("==>")
fw.write_txt('==>', newline = True) #KEEP THIS!
def compareChosenDevicesByAlarmPriority(cursor):
#chosenDevices = ['EHS60/BE', 'EXS4/8X', 'EMC001*9', 'EXS106/2X', 'ESS1*84',
# 'ESS11/5H', 'ESS406/E91', 'ESS407/E91', 'ESS520/E91', 'ESS11*84']
# our devices: ['EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X']
chosenDevices = [
"AUTO-TRANSFERT", "ECC01/5DX", "EMD1A*9", "EMD2A*9", "EMD3A*9",
"EMC700/1E", "EMC001*9"
]
levelsOfPriority = ['L0', 'L1', 'L2', 'L3']
fw = File_writer("CUSTOM_7net-nooverlaps-yesallsingledup-emc001"
) #always leave "CUSTOM_" in front
fw2 = File_writer("last_SQL_query_results")
fw.create_txt("../res/newres/")
fw2.create_txt("../res/newres/")
print '\nDEVICE ' + str(chosenDevices) + ': '
fw.write_txt('\nDEVICE ' + str(chosenDevices) + ': ')
#print '\n\tPRIORITY ' + str(l) + ':'
fw.write_txt('\n\tPRIORITY L0 :')
strList = "%s"
for i in range(1, len(chosenDevices)):
strList = strList + " OR device=" + "%s"
textQuery = "select * from electric where (device=" + strList + ") and action='Alarm CAME' order by time"
print(textQuery)
query = (textQuery)
tpl = tuple(chosenDevices)
cursor.execute(query, tpl)
events = cursor.fetchall()
afterSeq = [
] # Contiene le liste dei device che vediamo in ogni riga nei file di testo
beforeSeq = []
afterSequence = [
] # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
beforeSequence = []
intersectionDevicesBeforeAndAfter = []
stateOfDevices = StateHandler()
totalEvents = 0
totalRelevantEvents = 0
for e in events:
#print '\n' + str(e)
totalEvents = totalEvents + 1
totalRelevantEvents += 1
'''
query = ("select * from electric where time>=(%s) and time <= (%s + interval %s minute) and action='Alarm CAME' order by time;")
cursor.execute(query, (e[0], e[0], 5))
eventsAfter = cursor.fetchall()
'''
devicesAfter = [] # all events that happened 5 min after the event "e"
if not stateOfDevices.addActivatedDevice(
e[4], e[0]): #i.e. sequence is ready
sequence = stateOfDevices.getSequence()
afterSeq.append(sequence)
stateOfDevices = StateHandler() #create new StateHandler
stateOfDevices.addActivatedDevice(e[4], e[0])
elif stateOfDevices.check_device_ready(
): #if we have a single device sequence ready..
single_sequence = stateOfDevices.get_device_ready()
afterSeq.append(single_sequence)
'''
#totalEvents = totalEvents + len(eventsAfter)
for ea in eventsAfter:
totalEvents = totalEvents + 1
if ea[4] in chosenDevices:
totalRelevantEvents += 1
devicesAfter.append(ea[4]) #ea[4] = nome del device
if not stateOfDevices.addActivatedDevice(ea[4], ea[0]):
sequence = stateOfDevices.getSequence()
afterSeq.append(sequence)
stateOfDevices = StateHandler() #create new StateHandler
stateOfDevices.addActivatedDevice(ea[4], ea[0])
elif stateOfDevices.check_device_ready(): #if we have a single device sequence ready..
single_sequence = stateOfDevices.get_device_ready()
afterSeq.append(single_sequence)
'''
#if devicesAfter != []:
# devicesAfter.append(e[4])
#afterSequence.append(devicesAfter) # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
#devicesAfter=list(set(devicesAfter)) #Lista non ordinata di distinct devices
#afterSeq.append(devicesAfter) #Lista di liste (i.e. tutti quello dopo "distinct device after 5 min")
if e[4] in ["ECC01/5DX"]:
print(e[4] + " - Timestamp " + str(e[0]))
fw2.write_txt(e[4] + " - Timestamp " + str(e[0]))
#add the last sequence
sequence = stateOfDevices.getSequence()
afterSeq.append(sequence)
'''
# CONSOLE
print '========= AFTER ========='
print '\t\tSequences After: [ '
for xx in afterSequence:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "',",
print '], '
print ']'
ghjk
# TXT FILE
fw.write_txt( '========= AFTER =========')
fw.write_txt( '\t\tSequences After: [ ' )
for xx in afterSequence:
fw.write_inline( '\t\t[ ' )
for yy in xx:
fw.write_inline( "'" + str(yy) + "'," )
fw.write_txt( '], ' )
fw.write_txt( ']' )
'''
#CONSOLE
print '\n\t\tDistinct devices after 5 minutes: [ '
for xx in afterSeq:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "', ",
print '], '
print ']'
#TEXT FILE
fw.write_txt('\n\t\tDistinct devices after 5 minutes: [ ')
for xx in afterSeq:
fw.write_inline('\t\t[ ', )
for yy in xx:
fw.write_inline("'" + str(yy) + "', ")
fw.write_txt('], ')
fw.write_txt(']')
'''
relim_input = itemmining.get_relim_input(afterSeq)
report = itemmining.relim(relim_input, min_support=int(afterSeq.__len__() * support))
print "\n\t\t ===> FREQUENT ITEMSETS in Distinct devices after 5 minutes: (with support=0.5 the threshold is " + str(afterSeq.__len__() * support) + ")"
for key, values in report.items():
print '\t\t\t',
print key,
print ': ',
print values
'''
'''
#CONSOLE
print '========= BEFORE ========='
print '\t\tSequences Before: [ '
for xx in beforeSequence:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "', ",
print '], '
print ']'
#TXT FILE
fw.write_txt( '========= BEFORE =========' )
fw.write_txt( '\t\tSequences Before: [ ' )
for xx in beforeSequence:
fw.write_inline( '\t\t[ ' )
for yy in xx:
fw.write_inline( "'" + str(yy) + "', " )
fw.write_txt( '], ' )
fw.write_txt( ']' )
'''
'''
#CONSOLE
print '\t\tDistinct devices before: [ '
for xx in beforeSeq:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "', ",
print '], '
print ']'
#TXT FILE
fw.write_txt( '\t\tDistinct devices before: [ ' )
for xx in beforeSeq:
fw.write_inline( '\t\t[ ' )
for yy in xx:
fw.write_inline( "'" + str(yy) + "', " )
fw.write_txt( '], ' )
fw.write_txt( ']' )
'''
print("EVENTI TOTALI NEL DOPPIO CICLO DI QUERY: " + str(totalEvents))
print(
"EVENTI TOTALI UNICAMENTE RELATIVI AI DEVICE PRESCELTI NEL DOPPIO CICLO DI QUERY: "
+ str(totalRelevantEvents))
counterEvents = 0
counterItemsets = 0
for lst in afterSeq:
counterEvents = counterEvents + len(lst)
counterItemsets = counterItemsets + 1
print("NUMERO DI ITEMSET TOTALI GENERATI: " + str(counterItemsets))
print("EVENTI TOTALI IN TUTTI GLI ITEMSET GENERATI: " + str(counterEvents))
stateOfDevices.debugFile()
def compareChosenDevicesByAlarmPriority(cursor):
#chosenDevices = ['EHS60/BE', 'EXS4/8X', 'EMC001*9', 'EXS106/2X', 'ESS1*84',
# 'ESS11/5H', 'ESS406/E91', 'ESS407/E91', 'ESS520/E91', 'ESS11*84']
# our devices: ['EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X']
chosenDevices = [
"AUTO-TRANSFERT", "ECC01/5DX", "EMD1A*9", "EMD2A*9", "EMD3A*9",
"EMC700/1E"
]
levelsOfPriority = ['L0', 'L1', 'L2', 'L3']
fw = File_writer("CUSTOM_7net-overlaps-emc001")
fw.create_txt("../../res/newres/")
print '\nDEVICE ' + str(chosenDevices) + ': '
fw.write_txt('\nDEVICE ' + str(chosenDevices) + ': ')
#print '\n\tPRIORITY ' + str(l) + ':'
fw.write_txt('\n\tPRIORITY L0 :')
strList = "%s"
for i in range(1, len(chosenDevices)):
strList = strList + " OR device=" + "%s"
textQuery = "select * from electric where device=" + strList + " and action='Alarm CAME'"
print(textQuery)
query = (textQuery)
tpl = tuple(chosenDevices)
cursor.execute(query, tpl)
events = cursor.fetchall()
afterSeq = [
] # Contiene le liste dei device che vediamo in ogni riga nei file di testo
beforeSeq = []
afterSequence = [
] # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
beforeSequence = []
intersectionDevicesBeforeAndAfter = []
for e in events:
#print '\n' + str(e)
query = (
"select * from electric where time>=(%s) and time <= (%s + interval %s minute) and action='Alarm CAME' order by time;"
)
cursor.execute(query, (e[0], e[0], 5))
eventsAfter = cursor.fetchall()
devicesAfter = [] # all events that happened 5 min after the event "e"
for ea in eventsAfter:
if ea[4] in chosenDevices:
devicesAfter.append(ea[4]) #ea[4] = nome del device
#if devicesAfter != []:
devicesAfter.append(e[4])
afterSequence.append(
devicesAfter
) # Contiene tutte le liste di deviceAfter (con duplicati). E' una lista di liste
devicesAfter = list(
set(devicesAfter)) #Lista non ordinata di distinct devices
afterSeq.append(
devicesAfter
) #Lista di liste (i.e. tutti quello dopo "distinct device after 5 min")
print(e[4] + str(devicesAfter) + " - ID " + str(e[21]))
#CONSOLE
print '\n\t\tDistinct devices after 5 minutes: [ '
for xx in afterSeq:
print '\t\t[ ',
for yy in xx:
print "'" + str(yy) + "', ",
print '], '
print ']'
#TEXT FILE
fw.write_txt('\n\t\tDistinct devices after 5 minutes: [ ')
for xx in afterSeq:
fw.write_inline('\t\t[ ', )
for yy in xx:
fw.write_inline("'" + str(yy) + "', ")
fw.write_txt('], ')
fw.write_txt(']')
class StateHandler(object):
'''
Creates not duplicated sequences. Can also create non overlapping sequences, or use an "overtime"
to extend a sequence time lenght. Can also use an "undertime" to cut sequences too sparsely distributed
over the time period.
'''
fwDebug = File_writer(
"last_execution_state_debug") #logging and debuggin purposes
fwDebug.create_txt("../../res/newres/")
eventiRicevuti = 0
eventiScartatiDuplicati = 0
eventiOltre5Minuti = 0
eventiScartatiSovrapposti = 0
specialSingleSequences = 0
def __init__(self, undertime=0, overtime=0, overlaps=False):
''' Put overlaps = True if you you want to have overlapping sequences '''
self.counter = None
self.undertime = undertime
self.overtime = overtime
self.overlaps = overlaps
self.devicesStates = dict()
self.singleDeviceReady = False # If True, it signals that there is a device ready to be saved as a single sequence [dev1]
def addActivatedDevice(self, device, timestamp):
''' Adds a devices that has been found to be activated on a certain timestamp.
Does nothing and return False if the event to be added is more then 5 minutes old, True otherwise.'''
StateHandler.eventiRicevuti += 1
if self.minutesElapsed(
5, timestamp): # 5 minutes are elapsed since the counter
StateHandler.eventiRicevuti -= 1
StateHandler.eventiOltre5Minuti += 1
return False
else:
if device not in self.devicesStates: # the device is new...
if len(self.devicesStates
) == 0: # ...and it's the first to be added
newState = DeviceState(timestamp, True)
self.devicesStates[device] = newState
self.counter = newState.getTimestamp()
else: #... and it's not the first
newState = DeviceState(timestamp, True)
self.devicesStates[device] = newState
#if self.overlaps:
else: #the device is not new
if len(self.devicesStates
) == 1: #there is only 1 device in the dictionary,
#and it is the same device that we are adding now
StateHandler.eventiScartatiDuplicati += 1
newState = DeviceState(timestamp, True)
self.counter = newState.getTimestamp(
) #reset the 5 minutes counter
self.singleDeviceReady = True
self.singleDevice = device
#else do nothing
else:
self.singleDeviceReady = True
self.singleDevice = device
StateHandler.eventiScartatiSovrapposti += 1
return True
def minutesElapsed(self, mins, ts):
''' Returns True if "mins" minutes are elapsed since the last counter '''
deltaMin = timedelta(minutes=mins)
timestamp = datetime.strptime(ts, '%Y-%m-%d %H:%M:%S.%f')
if self.counter == None:
self.counter = timestamp
if (timestamp - self.counter) > deltaMin:
return True
else:
return False
def getSequence(self):
#ritorna i devices in devicesStates
sequence = []
for key in self.devicesStates:
sequence.append(key)
return sequence
def check_device_ready(self):
if self.singleDeviceReady:
self.singleDeviceReady = False
return True
else:
return False
def get_device_ready(self):
dev = self.singleDevice
StateHandler.specialSingleSequences += 1
#self.get_device_ready = ""
return [dev]
def debugFile(self):
StateHandler.fwDebug.write_txt("EVENTI RICEVUTI: " +
str(StateHandler.eventiRicevuti))
StateHandler.fwDebug.write_txt(
"EVENTI DUPLICATI SCARTATI: " +
str(StateHandler.eventiScartatiDuplicati))
StateHandler.fwDebug.write_txt("EVENTI FUORI DAI 5 MINUTI: " +
str(StateHandler.eventiOltre5Minuti))
print("--- StateHandler ---")
print("EVENTI RICEVUTI: " + str(StateHandler.eventiRicevuti))
print("EVENTI DUPLICATI SCARTATI tipo [dev1dev1dev1]: " +
str(StateHandler.eventiScartatiDuplicati))
print("EVENTI SOVRAPPOSTI SCARTATI tipo [dev1dev2dev1dev2]: " +
str(StateHandler.eventiScartatiSovrapposti))
print("SEQUENZE SINGOLE SPECIALI CREATE tipo [dev1] da [dev1dev1]: " +
str(StateHandler.specialSingleSequences))
print("EVENTI MESSI IN SEQUENZE: " +
(str(StateHandler.eventiRicevuti -
StateHandler.eventiScartatiDuplicati -
StateHandler.eventiScartatiSovrapposti)))
print("EVENTI FUORI DAI 5 MINUTI (#itemsets): " +
str(StateHandler.eventiOltre5Minuti))
def compareChosenDevicesByAlarmPriority(cursor):
#chosenDevices = ['EHS60/BE', 'EXS4/8X', 'EMC001*9', 'EXS106/2X', 'ESS1*84',
# 'ESS11/5H', 'ESS406/E91', 'ESS407/E91', 'ESS520/E91', 'ESS11*84']
# our devices: ['EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X']
chosenDevices = [
'EMC001*9', 'EHS60/BE', 'ESS11/5H', 'ESS1*84', 'EXS4/8X', 'EXS106/2X'
]
levelsOfPriority = ['L0', 'L1', 'L2', 'L3']
for d in chosenDevices:
# clusters-offlineAverage
# clusters-staticDistance
# clusters-dbscan
fw = File_writer(d, "clusters-meanShift")
fw.create_txt("../../res/newres/")
fw2 = File_writer(d, "DEBUG-clusters-meanShift")
fw2.create_txt("../../res/newres/")
print '\nDEVICE ' + str(d) + ': '
fw.write_txt('\nDEVICE ' + str(d) + ': ')
for l in levelsOfPriority:
print '\n\tPRIORITY ' + str(l) + ':'
fw.write_txt('\n\tPRIORITY ' + str(l) + ':')
query = (
"select * from electric where device=%s and livellopriorita=%s and action='Alarm CAME'"
)
cursor.execute(query, (d, l))
events = cursor.fetchall()
clusterList = []
for e in events:
query = (
"select * from electric where time>=(%s) and time <= (%s + interval %s minute) and action='Alarm CAME' order by time;"
)
cursor.execute(query, (e[0], e[0], 5))
clusterHandler = ClusterHandler(e)
eventsAfter = cursor.fetchall()
for ea in eventsAfter:
clusterHandler.addEvent(ea)
try:
# 1) OFFLINE AVERAGE
#clusterHandler.findClustersOfflineAverage(fw2, debug=True)
# 2) STATIC DISTANCE
#timeDelta = timedelta(minutes = 5)
#clusterHandler.findClustersStaticDistance(fw2, timeDelta, debug=True)
# 3) DBSCAN
#clusterHandler.findClustersDBSCAN(fw2, debug=True)
# 4) MEAN SHIFT
clusterHandler.findClustersMeanShift(fw2, debug=True)
except DataError as e:
pass
newClusters = clusterHandler.getClusters()
#Create the list of itemsets
for stateList in newClusters:
devices = []
for evstate in stateList:
devices.append(evstate.getDevice())
clusterList.append(list(set(devices)))
#clusterList = a list of clusters, where each cluster is a list of devices
#stateList = a list of objects "EventState"
#evstate = a single object "EventState"
#uncomment this if you want to see the itemsets printed to the console
#CONSOLE
print '\n\t\tDistinct devices after 5 minutes: [ '
for devList in clusterList:
print '\t\t[ ',
for dev in devList:
print "'" + dev + "', ",
print '], '
print ']'
#TEXT FILE
fw.write_txt('\n\t\tDistinct devices after 5 minutes: [ ')
for devList in clusterList:
fw.write_inline('\t\t[ ', )
for dev in devList:
fw.write_inline("'" + dev + "', ")
fw.write_txt('], ')
fw.write_txt(']')
print("==>")
fw.write_txt('==>', newline=True)
def save_to_file(self):
''' Saves the list of all the selected variables in a txt file '''
fw = File_writer("Unique_devices", "List")
fw.write_list(self.unique_devices)