As an expansion of the San Graphing Excel document I created. I engineered a module for drupal which dynamically acquires a structure to graph.
The data must:
- Be in a database
- Be structured logically
- Have control values (Can be Multiple)
- They will be used as “Where” in the SQL Query.
- Singular or multiple control values are accepted.
Example:
In a SAN Array, you may have multiple shelves of drives (control value: Shelf). You will have multiple drives (control value: HDU). And you have the time(s) that you wish to view (control value: Time Stamp).
After these control values which limit the results, you need to select the information you actually wish to graph. In the further San Example, we could graph IOPS, MB/s, etc.
Each graph is a piece of content in drupal. Its a node, which can be acted upon with other modules, like Panels. Panels can then group the nodes and display a dashboard at a path like so:
Its possible in the future to use AJAX to dynamically reload each individual graph, but I have yet to implement that, currently the page is refreshed every X seconds.
Because the content creation form is dynamically created, its never out of date. If new luns/raidgroups/shelves are added, it is automatically displayed at the next content creation.
For the AMS500 the San can produce performance logs. These logs were used in Excel, they have been modified a bit more and cleaned up using Sed and now python can import them directly into a Mysql database using the following script.
#!/usr/bin/python
'''
Created on Apr 20, 2010
@author: ghernandez
'''
import time
import datetime
import sys
import MySQLdb
#Variables
MySQLHost = "HOSTNAME"
MySQLUser = "USERNAME"
MySQLPasswd = "PASSWORD"
Database = "DATABASE"
Table_Lines = []
ImportDate= "Unknown!"
SearchArray= [
("Port Information", 2),
("RG Information", 2),
("LU Information", 2),
("Cache Information", 1),
("Partition Information", 2),
("Processor Information", 1),
("Drive Information", 3),
("Backend Information", 3)
]
file = open(sys.argv[1], "r")
#file = open("/Storage-Logs/05.14/pfm00000-05.14_07.10.pfm", "r")
TopofFile = file.tell()
def AddEntry(databaseName, TableName, RowName, ListofLists_Entries):
conn = MySQLdb.connect (host = "10.20.15.240",
user = "phpMyAdmin",
passwd = "phpMyAdmin",
db = "phpmyadmin")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
def OpenFile_Count_Entries():
global ImportDate
linenum=1
while 1:
line = file.readline()
'''Make sure the file is correct'''
if linenum == 1 and "No." not in line:
break
''' Get Import Date '''
if linenum == 2:
ImportDate = line.split('+')[1].replace('/', "-")[:-2]
'''If the line is empty and does not have a next line, quit.'''
if line == '':
break
''' Catch all header lines and mark line numbers'''
if "---- " in line:
Table_Lines.append(line[5:-7])
linenum += 1
'''
@note: Entries are number of fields to remove on the subsequent lines.
The format of the perf files breaks a table down and the fields
are in multiple sections. But X number of "Entries" will be repeated
on the subsequent fields.
'''
def Add_Info(SearchString, Entries): #Returns a tuple or two varibles, entries, and
file.seek(TopofFile)
line = file.readline()
'''Advance to SearchString'''
while True:
line = file.readline()
if SearchString in line:
line = file.readline() #Advance to next line.
break
if line == '': break
Titles = [ '' ]
InfoDict = {}
while "----" not in line and not line == '':
#print line
if '\n' in line: line = line[:-1]
if '\r' in line: line = line[:-1]
array = line.split('+')
'''The first two entries in this perf log will always be CTL and Port,
we only need them once. We delete the first two elements in the array'''
if "CTL" in line and not "----" in line:
if Titles == [ '' ]:
Titles = array
else:
for i in range(Entries):
del array[0]
Titles += array
else: #If its not a title, its a datum.
if tuple(array[:Entries]) not in InfoDict.keys():
InfoDict[tuple(array[:Entries])] = list(array[Entries:])
else:
currange = tuple(array[:Entries])
for i in xrange(Entries):
del array[0]
InfoDict[currange] += array
line = file.readline()
'''
#Check that the two numbers are the same.
print "Number of titles ", len(Titles)
counter = 0
iterator = 0
for name in InfoDict.keys():
iterator +=1
counter += len(list(name) + InfoDict[name])
a = counter / iterator
print "Number of entries captured",a
'''
return Titles, InfoDict
def SortedDictionaryValues(adict):
keys = adict.keys()
keys.sort()
return keys, map(adict.get, keys)
def main():
conn = MySQLdb.connect(host = MySQLHost,
user = MySQLUser,
passwd = MySQLPasswd,
db = Database
)
c = conn.cursor ()
OpenFile_Count_Entries()
for arg in SearchArray:
#See if we have an entry for this date time already
c.execute('SELECT TimeStamp FROM `' + Database + "`.`" + arg[0] + '` WHERE TimeStamp = "' + ImportDate + '" LIMIT 0, 1')
#Skip inserts if the timestamp already exists
if str(c.fetchone()) == "None":
Titles, EntriesDict = Add_Info(arg[0], arg[1])
InsertString = "INSERT INTO `" + Database + "`.`" + arg[0] + "` ( \n`Entries`, \n"
#Build insert query string.
for name in Titles:
InsertString += "`" + name + "`,\n"
#Remove last "," and add closing ")"
InsertString = InsertString[:-1] + "\n`TimeStamp`\n)"
#Build Value strings
#First entry is null as it should be auto incrementing.
ValueString = "VALUES "
SortedKeys, SortedOutputs = SortedDictionaryValues (EntriesDict)
counter = 0
for arg in SortedKeys:
Valuetuple = ("0", ) + arg + tuple(SortedOutputs[counter]) + (ImportDate, )
ValueString += str(Valuetuple) + ",\n"
counter += 1
#Append ";" and remove ","
ValueString = ValueString[:-2] +";"
#print InsertString
#print ValueString
c.execute(InsertString + ValueString)
else:
print "Duplicate Data!"
break
c.close();
conn.close()
# c.execute("SELECT * FROM `" + SearchArray[7][0] + "`")
# x = c.fetchone()
'''---------------------------------------------
Start!
---------------------------------------------'''
# @todo: Add a check to see if an entry for this timestamp has already been added.
if __name__ == '__main__':
main()
Im pretty happy at this point with it. The next step with this module would be to create an interface in python which would query cisco routers and create the same structured database. Then we could create graphs of performance statistics from across our network and get rid of solar winds.
