A SysAdmin needs a graphical report displaying when all the backup and replication jobs run, and for how long, in order to identify if there is any runtime overlaps within the current job setup, and recommend if any changes are needed to the current schedules to optimize the backup process.
Fig. 1: Example report showing what we are setting out to achieve
Part 1: Obtaining a CSV Report from SQL
1.1 From a machine with a suitable version of SQL Server Management Studio installed (here using 'Microsoft SQL Server 2008 R2 – Management Studio Express',) and using a suitable set of credentials; connect to the SQL Server homing the VeeamBackup Database.
1.2 Click on the 'New Query' button, and in the SQLQuery pane, paste the following:
SELECT TOP 1000 [job_name]
where [creation_time] > '2012-04-04 17:00:00.000' and [creation_time] < '2012-04-11 17:00:00.000'
order by [creation_time] desc;
1.3 Edit the creation_time start time and end time as per requirements, then click on the '! Execute' button.
1.4 By default, this will display the results in a grid. Click on the top left corner of the grid to select all –
– then from the File menu > Save Results As... > and save the file as a CSV (Comma delimited) file.
Part 2: Creating the Gantt Chart using Excel 2010
The steps to create the Gantt chart only need going through once, after setup can simply import new data.
2.1 Open the CSV file from Part 1 using Excel 2010.
2.2 In D1, input the formula =c1-b1 and use the Fill button on the Home Ribbon to complete column D
2.3 Format column B as dd/mm/yy hh:mm, column D as hh:mm:ss, and we can hide column C
2.4 Select the range A1:B? (where ? is the last row you want to include in your data) and from the Insert Ribbon select Bar > 2-D Bar > Stacked Bar
In the example below we are using A1:B14
2.5 From Chart Tools > Layout Ribbon > Legend > Select None : Turn off Legend
2.6 On the Design Ribbon click 'Switch Row/Column'
2.7 Right-click the horizontal axis, and select 'Format Axis...' Tick the box for 'Categories in reverse order', select the radio button for 'Horizontal axis crosses: At maximum category', and Close the 'Format Axis' dialog box
2.8 Right-click on any bar and select 'Format Data Series...'
Check that the 'Series Options: Series Overlap' is set to 100% Overlapped
Set the Fill radio button to 'No fill'
Set the Border Color radio button to 'No line'
Close the 'Format Data Series' dialog box.
2.9 On the Chart Tools > Design Ribbon, click on 'Select Data'
Click on Add
Give the Series a name such as 'Duration'
Click the button to the right of the 'Series values' field, then select the range D1:D? (where ? is the last row you want to include in your data) on the spreadsheet.
In our example below we are using D1:D14
Finally, click the button to the right of the Edit Series dialogue box, click OK to close the 'Edit Series' dialog box, click OK to close the 'Select Data Source' box, and voila!
To see the complete list of ReportSessionsView column titles:
SQL Server > Databases > VeeamBackup > Views > dbo.ReportSessionsView > Columns