The aim of the dashboard_writer() function is to provide the analyst or reviewer with some graphics that summarize our UserAssist data. We chose to present the top 10, bottom 10, and most recent 10 executables to the user. This function is our longest and requires the most logic.
On line 81, we add our dashboard worksheet object to the workbook. Next, we merge the first row from the A to Q columns and write our company name, XYZ Corp, using our title format created in the excelWriter() function. Similarly, we create a subtitle row to identify this worksheet as our dashboard on line 83, as follows:
071 def dashboard_writer(workbook, data, ua_format):
072 """
073 the dashboard_writer function creates the 'Dashboard'
074 worksheet, table, and graphs
075 :param workbook: the excel workbook object
076 :param data: the list of lists containing parsed UA data
077 :param ua_format: the format object for the title and
078 subtitle row
079 :return: Nothing
080 """
081 dashboard = workbook.add_worksheet('Dashboard')
082 dashboard.merge_range('A1:Q1', 'XYZ Corp', ua_format)
083 dashboard.merge_range('A2:Q2', 'Dashboard', ua_format)
On line 87, we create and add a date_format to the workbook in order to properly format our dates. On lines 92 and 93, we make function calls to the two sorting functions. We use list slicing to carve the sorted data to create our sublists: topten, leastten, and lastten. For the topten executables used by count, we grab the last 10 elements in the sorted list. For the leastten, we simply perform the inverse. For the lastten, we grab the first 10 results in the sorted dates list, as follows:
085 # The format to use to convert datetime object into a human
086 # readable value
087 date_format = workbook.add_format({
088 'num_format': 'mm/dd/yy h:mm:ss'})
089
090 # Sort our original input by count and date to assist with
091 # creating charts.
092 sorted_count = sort_by_count(data)
093 sorted_date = sort_by_date(data)
094
095 # Use list slicing to obtain the most and least frequently
096 # used UA apps and the most recently used UA apps
097 topten = sorted_count[-10:]
098 leastten = sorted_count[:10]
099 lastten = sorted_date[:10]
On line 103, we iterate over the elements in the lastten list. We must convert each timestamp into a datetime object. The datetime object is stored in the first index of the UserAssist list we created and is converted by the file_time() function:
101 # For the most recently used UA apps, convert the FILETIME
102 # value to datetime format
103 for element in lastten:
104 element[1] = file_time(element[1])
On lines 108 through 116, we create our three tables for our top, bottom, and most recent data points. Note how these tables start on row 100. We chose to place them far away from the top of the spreadsheet so the user sees the tables we will add instead of the raw data. As we saw when describing tables in the xlsxwriter section, the second argument of the add_table() function is a dictionary containing keywords for header names and formats. There are other keywords that could be provided for additional functionality. For example, we use the format keyword to ensure that our datetime objects are displayed as desired using our date_format variable. We have the following code:
106 # Create a table for each of the three categories, specifying
107 # the data, column headers, and formats for specific columns
108 dashboard.add_table('A100:B110',
109 {'data': topten, 'columns': [{'header': 'App'},
110 {'header': 'Count'}]})
111 dashboard.add_table('D100:E110',
112 {'data': leastten, 'columns': [{'header': 'App'},
113 {'header': 'Count'}]})
114 dashboard.add_table('G100:H110',
115 {'data': lastten, 'columns': [{'header': 'App'},
116 {'header': 'Date (UTC)', 'format': date_format}]})
On lines 118 to 153, we create our charts for the three tables. After instantiating top_chart as a pie chart, we set the title and the scale in the X and Y direction. During testing, we realized that the figure would be too small to adequately display all of the information, and so we used a larger scale:
118 # Create the most used UA apps chart
119 top_chart = workbook.add_chart({'type': 'pie'})
120 top_chart.set_title({'name': 'Top Ten Apps'})
121 # Set the relative size to fit the labels and pie chart within
122 # chart area
123 top_chart.set_size({'x_scale': 1, 'y_scale': 2})
On line 127, we add the series for our pie chart; identifying the categories and values is straightforward. All we need to do is define the rows and columns we want to plot. The data_labels key is an additional option that can be used to specify the value's format of the plotted data. In this case, we chose the 'percentage' option as seen on line 130, as follows:
125 # Add the data as a series by specifying the categories and
126 # values
127 top_chart.add_series(
128 {'categories': '=Dashboard!$A$101:$A$110',
129 'values': '=Dashboard!$B$101:$B$110',
130 'data_labels': {'percentage': True}})
131 # Add the chart to the 'Dashboard' worksheet
132 dashboard.insert_chart('A4', top_chart)
With this setup, our pie chart will be split based on usage count, the legend will contain the name of the executable, and the percentage will show the relative execution in comparison to the other nine executables. After creating the chart, we call insert_chart() to add it to the dashboard worksheet. The least_chart is created in the same manner, as follows:
134 # Create the least used UA apps chart
135 least_chart = workbook.add_chart({'type': 'pie'})
136 least_chart.set_title({'name': 'Least Used Apps'})
137 least_chart.set_size({'x_scale': 1, 'y_scale': 2})
138
139 least_chart.add_series(
140 {'categories': '=Dashboard!$D$101:$D$110',
141 'values': '=Dashboard!$E$101:$E$110',
142 'data_labels': {'percentage': True}})
143 dashboard.insert_chart('J4', least_chart)
Finally, we create and add the last_chart to our spreadsheet. In an effort to save trees, this is handled in the same fashion as we previously discussed. This time, however, our chart is a column chart and we've modified the scale to be appropriate for the type of chart:
145 # Create the most recently used UA apps chart
146 last_chart = workbook.add_chart({'type': 'column'})
147 last_chart.set_title({'name': 'Last Used Apps'})
148 last_chart.set_size({'x_scale': 1.5, 'y_scale': 1})
149
150 last_chart.add_series(
151 {'categories': '=Dashboard!$G$101:$G$110',
152 'values': '=Dashboard!$H$101:$H$110'})
153 dashboard.insert_chart('D35', last_chart)