breathalize

As a vRanger user, version 4 brought lots of welcome improvements. Apart from the overall performance achieved by no longer requiring the vRanger server to proxy the backups, the main improvement for me was the database moving to SQL.

As a result of this change, gaining access to and manipulating the backup data is now really easy. The company I work for, like many others have various checks that run each morning – the next logical step was to include an automated vRanger report. Now while vRanger does have a report system built in, it didn’t do quite what I needed and cannot email, so I wanted to be able to reproduce the report in a way I could stick into email, webpages etc.

To work out how to form the query, I began a trace on the vRangerPro database in SQL Profiler – once I had run the report in the vRanger GUI, I could work out which query was being executed, load it back into SQL Query Analyser and start tinkering. Now all of my VM backups run overnight so I wanted to be able to run the query to only look at jobs starting within the last  24 hours so when executed in the morning checks it would cover the previous nights run.

The eventual output was as follows:

SELECT DISTINCT t.VmName AS [VM Name], dbo.TaskStatus.Name AS Status, dbo.TaskState.Name AS [Task Status], dbo.SpaceSavingType.Name AS [Space Saving], isc.Address AS ESX, r.DisplayName AS Repository, dbo.RepositoryCIFS.ShareName AS [Share Name], t.StartTime, t.EndTime, DATEDIFF(minute, t.StartTime, t.EndTime) AS [Duration in Minutes], sp.SizeInMbOriginal AS [VM Size], sp.SizeInMbStored AS [Backup Size], t.Error FROM dbo.SpaceSavingType INNER JOIN dbo.SavePoint AS sp ON dbo.SpaceSavingType.SpaceSavingTypeId = sp.SpaceSavingTypeId RIGHT OUTER JOIN dbo.Task AS t INNER JOIN dbo.Job AS j ON t.JobId = j.JobId INNER JOIN dbo.BackupTask AS bt ON bt.TaskId = t.TaskId INNER JOIN dbo.Repository AS r ON bt.RepositoryId = r.RepositoryId INNER JOIN dbo.RepositoryCIFS ON r.RepositoryId = dbo.RepositoryCIFS.RepositoryId INNER JOIN dbo.TaskState ON t.TaskStateId = dbo.TaskState.TaskStateId INNER JOIN dbo.TaskStatus ON t.TaskStatusId = dbo.TaskStatus.TaskStatusId LEFT OUTER JOIN dbo.InventorySourceConnection AS isc ON bt.InventorySourceConnectionId = isc.InventorySourceConnectionId ON sp.SavePointId = bt.SavePointId WHERE     (j.JobTypeId = 0) AND (t.StartTime >= DATEADD(day, – 1, GETDATE())) AND (DATEDIFF(minute, t.StartTime, t.EndTime) >= 0) AND (j.JobStatusId IN (0, 1, 3, 2)) AND (t.TaskStatusId IN (0, 1, 3, 2))

The query links various tables in order to produce a meaningful output that can be used easily in reports and web pages – the important factor is what time period you want to report against. In this case, the filter is the last day based on the Start Time of each job.

The query should produce the following type of result:

Example output of the SQL query in Excel 2007

• VM Name – unsurprisingly is the name of the VM being backed up
• Status – displays either Success or Failed
• Task Status – again self evident!
• Space Saving – None (Full Backup), Incremental or Differential
• ESX – reports which ESX Host the VM was on at the time of the backup
• Repository -  shows the name under which the CIFS repository is labelled in vRanger
• Share Name – the specific CIFS share
• Start Time and End Time are obvious but useful to record for tracking backup performance
• Duration in Minutes – created by SQL using a DATEDIFF method
• VM Size – the recorded size of the actually VM size i.e. VMDKs
• Backup Size – the actual end result of the backup – again useful for seeing how much space is saved due to compression
• Error – If the job failed, the field will display the information relating to the failure (Disk Space, Permissions etc)

I used this SQL method because I am more familiar with it and how to get it into other formats for distribution – there are Powershell cmdlets for vRanger which may well produce similar results but for now this works well and is easy to tweak. Once the Powerpack for PowerGUI / VESI is released for vRanger I am sure this will be the preferred way of interrogating and controlling the vRanger application.

Hope this is proves useful for fellow vRanger users …