forked from stfc/oncall
-
Notifications
You must be signed in to change notification settings - Fork 1
/
AppendSpreadsheet.py
168 lines (147 loc) · 7.15 KB
/
AppendSpreadsheet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
import csv, sys, time, os
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Side, Border
from datetime import datetime
NEW_TICKETS_FILE_NAME = "Results.tsv"
SPREADSHEET_NAME = "Callouts.xlsx"
SHEET_NAME = "Callouts 2018"
try:
with open(NEW_TICKETS_FILE_NAME) as tsv:
try:
wb = load_workbook(SPREADSHEET_NAME)
except FileNotFoundError:
print(SPREADSHEET_NAME, 'doesn\'t exist (weekly spreadsheet), please add this file to the directory '
'of this script')
print('Directory of script:', os.path.dirname(os.path.realpath(__file__)))
time.sleep(4)
sys.exit()
try:
currentSheet = wb[SHEET_NAME]
except KeyError:
print('Sheet named \'' + SHEET_NAME + '\' doesn\'t exist')
print('Either create a sheet with this name in', SPREADSHEET_NAME, 'or edit script code so it finds a sheet '
'that does exist')
time.sleep(4)
sys.exit()
# Find which row to start appending spreadsheet
alarmColumn = currentSheet['A']
print('Calculating where to append spreadsheet')
startAppending = False
i = 2 # Start at 2 as first result will always be 'column header'
while not startAppending:
if alarmColumn[i].value is None:
startAppending = True
startingRowNumber = i + 1
i += 1
print('Will append spreadsheet from row #' + str(startingRowNumber))
# Skips first row of TSV file due to column headers
iterResults = iter(csv.reader(tsv, dialect="excel-tab"))
next(iterResults)
# Extracting data from TSV file
i = 0
nagiosFiller = "Nagios issued"
hostFiller = "on_host_"
for row in iterResults:
hostStart = None
service = None
hostCheck = True
workingHours = False
ticketID = int(row[0])
ticketCreated = datetime.strptime(row[15], '%Y-%m-%d %H:%M:%S')
dateCreated = ticketCreated.strftime('%d/%m/%Y')
timeCreated = ticketCreated.strftime('%H:%M:%S')
# Is callout in work hours?
weekday = ticketCreated.isoweekday()
if '08:30:00' < timeCreated < '17:00:00' and weekday < 6:
workingHours = True
# Get Nagios alarm (or subject if not from Nagios)
alarm = row[2]
if nagiosFiller in alarm:
for k in range(len(nagiosFiller), len(alarm)):
if 'T' in alarm[k]:
alarm = alarm[k:]
break
# Service
if 'ceph' in alarm.lower():
service = 'CEPH'
elif 'arc-ce' in alarm.lower():
service = 'CE'
elif 'gdss' in alarm.lower():
service = 'DISK Server'
# Get hostname from Nagios alarm
if hostFiller in alarm:
j = len(alarm) - 1
while hostCheck:
if alarm[j] == '_':
hostStart = j
hostCheck = False
j -= 1
hostname = alarm[hostStart + 1:]
# Putting data into spreadsheet
currentRow = startingRowNumber + i
currentSheet.cell(row=currentRow, column=1, value=alarm)
if hostStart is not None:
currentSheet.cell(row=currentRow, column=2, value=hostname)
currentSheet.cell(row=currentRow, column=3, value=dateCreated)
currentSheet.cell(row=currentRow, column=4, value=timeCreated)
if service is not None:
currentSheet.cell(row=currentRow, column=6, value=service)
# RT query
currentSheet.cell(row=currentRow, column=5, value=ticketID)
currentSheet.cell(row=currentRow, column=5).hyperlink = 'https://helpdesk.gridpp.rl.ac.uk/Ticket/Display' \
'.html?id=' + str(ticketID)
if workingHours:
currentSheet.cell(row=currentRow, column=8, value='N/A')
currentSheet.cell(row=currentRow, column=10, value='Work hours')
i += 1
# Merge cells
currentDate = datetime.now().strftime('%d-%b')
currentSheet.cell(row=startingRowNumber, column=12, value=currentDate)
currentSheet.merge_cells(start_row=startingRowNumber, start_column=12, end_row=currentRow, end_column=12)
# Setting inner borders and cell alignment
rows = currentSheet.iter_rows(min_row=startingRowNumber, min_col=1, max_row=currentRow, max_col=17)
innerBorderStyle = Side(border_style='thin', color='FF000000')
innerBorderFormat = Border(left=innerBorderStyle, right=innerBorderStyle, top=innerBorderStyle,
bottom=innerBorderStyle)
for row in rows:
for cell in row:
if cell.column == 'A' or cell.column == 'K':
# These columns need to be horizontally left aligned (alarm and comment columns)
cell.alignment = Alignment(vertical='center', wrap_text=True)
else:
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
cell.border = innerBorderFormat
# Setting outer border
# Code found at: https://stackoverflow.com/questions/34520764/apply-border-to-range-of-cells-using-openpyxl
# Written by Yaroslav Admin, edited by Adam Stewart
outerRows = currentSheet.iter_rows(min_row=startingRowNumber, min_col=1, max_row=currentRow, max_col=17)
outerBorderStyle = Side(border_style='medium', color='FF000000')
outerRows = list(outerRows)
max_y = len(outerRows) - 1
for pos_y, cells in enumerate(outerRows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
# Checking if an edge cell
if pos_x == 0:
border.left = outerBorderStyle
if pos_x == max_x:
border.right = outerBorderStyle
if pos_y == 0:
border.top = outerBorderStyle
if pos_y == max_y:
border.bottom = outerBorderStyle
# Set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
wb.save(SPREADSHEET_NAME)
print('Spreadsheet changes saved')
except FileNotFoundError:
print(NEW_TICKETS_FILE_NAME, '(this week\'s tickets) not found, please add to the same directory of the script')
print('Directory of script:', os.path.dirname(os.path.realpath(__file__)))
time.sleep(4)
sys.exit()