""" These are the pre-configured SQL queries and graphs which are run by the code in routes.py """ graphs = [{'title': 'Cmds per minute', 'id': 'cmds_per_minute', 'graph_type': 'bar', 'explanation': 'Maximum number of concurrent commands each minute', 'x': {'title': 'Minute', 'field': 'Minute'}, 'y': {'title': 'Num Cmds', 'field': 'MaxCommands'}, 'sql': """ SELECT SUBSTR(startTime, 0, 17) as 'Minute', MAX(running) as 'MaxCommands' FROM process GROUP BY Minute; """}, {'title': 'Cmds started per Ten minutes', 'id': 'cmds_per_10_minute', 'graph_type': 'bar', 'explanation': 'Commands started in each ten minute bucket', 'x': {'title': 'Ten Minute', 'field': 'TenMinute'}, 'y': {'title': 'Num Cmds', 'field': 'NumCommands'}, 'sql': """ SELECT SUBSTR(startTime, 0, 16) as 'TenMinute', COUNT(cmd) as 'NumCommands' FROM process GROUP BY TenMinute; """}, {'title': 'Cmd Totals', 'id': 'cmd_totals', 'graph_type': 'bar', 'explanation': 'How many commands of each type', 'x': {'title': 'Cmd', 'field': 'Cmd'}, 'y': {'title': 'Num Cmds', 'field': 'NumCmds'}, 'sql': """ select cmd as 'Cmd', count(cmd) as 'NumCmds' from process group by cmd order by NumCmds desc limit 15; """}, {'title': 'Cmds per user', 'id': 'cmds_per_user', 'graph_type': 'bar', 'explanation': 'How many commands of each type per user', 'x': {'title': 'User', 'field': 'User'}, 'y': {'title': 'Num Cmds', 'field': 'NumCmds'}, 'sql': """ select user as 'User', count(user) as 'NumCmds' from process group by user order by NumCmds desc limit 15; """} ] queries = [{'title': 'Time Frame', 'explanation': 'Start and end time for this log', 'sql': """ select MIN(starttime) as Start, MAX(starttime) as End from process; """}, {'title': 'Cmd Totals', 'explanation': 'How many commands of each type', 'sql': """ select cmd, count(cmd) as CountCmds from process group by cmd order by CountCmds desc limit 20; """}, {'title': 'Cmd Totals by User', 'explanation': 'How many commands of each type per user', 'sql': """ select cmd, count(cmd) as CountCmds, user from process group by cmd, user order by CountCmds desc limit 30; """}, {'title': 'Average wait time', 'explanation': 'Average wait time', 'sql': """ SELECT AVG(totalreadWait+totalwriteWait) as wait FROM tableUse; """}, {'title': 'Worst lock offenders', 'explanation': 'Users whose commands hold locks', 'sql': """ SELECT user, SUM(maxreadHeld+maxwriteHeld) as held FROM tableUse JOIN process USING (processKey) GROUP BY user ORDER BY held DESC LIMIT 25; """}, {'title': 'Blocking Commands', 'explanation': 'Commands that blocked others', 'sql': """ SELECT startTime, endTime, running, user, cmd, pid, tablename, maxReadHeld, maxWriteHeld, totalReadWait, totalWriteWait FROM tableUse JOIN process USING (processKey) WHERE (totalReadHeld > 10000 or totalWriteHeld > 10000) AND tablename not like 'meta%' AND tablename not like 'clients%' AND tablename not like 'changes%' ORDER BY startTime, endTime limit 30; """}, {'title': 'Blocking Commands including meta tables', 'explanation': 'Commands that blocked others including meta_db and clients locks', 'sql': """ SELECT startTime, endTime, running, user, cmd, pid, tablename, maxReadHeld, maxWriteHeld, totalReadWait, totalWriteWait FROM tableUse JOIN process USING (processKey) WHERE (totalReadHeld > 10000 or totalWriteHeld > 10000) ORDER BY startTime, endTime limit 30; """}, {'title': 'Block commands - victims of the above', 'explanation': '', 'sql': """ SELECT startTime, endTime, computedLapse, running, user, cmd, pid, tablename, maxReadHeld, maxWriteHeld,totalReadWait, totalWriteWait FROM tableUse JOIN process USING (processKey) WHERE (totalReadWait > 10000) or (totalWriteWait > 10000) ORDER BY startTime, endTime limit 30; """}, {'title': 'Longest Compute Phases', 'explanation': 'Longest Compute Phases', 'sql': """ SELECT process.processKey, user, cmd, args, startTime, CASE WHEN MAX(totalreadHeld + totalwriteHeld) > MAX(totalreadWait + totalwriteWait) THEN MAX(totalreadHeld + totalwriteHeld) - MAX(totalreadWait + totalwriteWait) ELSE MAX(totalreadHeld + totalwriteHeld) END AS compute FROM tableUse JOIN process USING (processKey) GROUP BY tableUse.processKey ORDER BY compute DESC LIMIT 25 """}, {'title': 'Consumed Most IO', 'explanation': 'Consumed Most IO', 'sql': """ SELECT user, cmd, SUM(pagesIn+pagesOut) as io, process.processKey, process.args FROM tableUse JOIN process USING (processKey) GROUP BY tableUse.processKey ORDER BY io DESC LIMIT 25 """}, {'title': 'Read / Write Percentage', 'explanation': 'Percentage of pages read and pages written', 'sql': """ SELECT TOTAL(pagesIn) * 100.0 / (TOTAL(pagesIn)+TOTAL(pagesOut)) as readPct, TOTAL(pagesOut) * 100.0 / (TOTAL(pagesIn)+TOTAL(pagesOut)) as writePct FROM tableUse """}, {'title': 'System CPU', 'explanation': 'Top 25 commands by system CPU', 'sql': """ select pid, user, cmd, completedLapse, rpcRcv, rpcSnd, uCpu, sCpu, startTime, endTime from process order by sCpu desc limit 25 """}, {'title': 'User CPU', 'explanation': 'Top 25 commands by user CPU', 'sql': """ select pid, user, cmd, completedLapse, rpcRcv, rpcSnd, uCpu, sCpu, startTime, endTime from process order by uCpu desc limit 25 """}, # {'title': '', # 'explanation': '', # 'sql': """ # """}, ]