Project Chargeback Reports

The following is a typical scenario that you are bound to see in your environment. Your organization has several projects running, and each of these projects uses a number of virtual machines in your VMware vSphere environment. Management wants you to send a daily informational email to each project leader, with an overview consumed resources for his project(s).

PowerShell to the rescue!

Getting the stats

The PowerCLI snap-in provides a cmdlet called Get-Stat that will provide us with the statistical data from the vCenter database. Based on a Custom Attribute, called Project, that is present on each machine that belong to a specific project, the script will first select all the Virtual Machines.

Once the selection of the Virtual Machines is made, the script will retrieve the statistical data. To limit the load on the vCenter, we only use one Get-Stat call in the script. With some nested Group-Object calls, the script produces a report per project owner, detailing all virtual machines in each of his projects.

To keep the example script simple, we only retrieve two metrics. But it should be obvious that you can easily expand the script to retrieve and calculate numerous other metrics.

Who to send it to?

The ProjectOwner custom attribute contains an Active Directory Display Name value. With an AdsiSearcher call, the script can easily find the email address of the user and send him the report by email.

1
2
3
4
5
6
7
$dcName = "TEST"
$displayName = $projectOwner
$adDomain = [adsi]("LDAP://" + $dcName + ":389/dc=mycorp,dc=com")
$adSearch = [adsisearcher]$adDomain
$adSearch.Filter = '(&(objectClass=User)(name=' + $displayName + '))'
$result = $adSearch.FindOne()
$mailAddress = $result.Properties["mail"][0]

With the Quest’s AD snapin the code required to find the email address becomes a lot easier.

1
2
$user = Get-QADUser -DisplayName $projectOwner
$emailAddress = $user.Email

Management wants the report , that is send to the Project Owner, to look something like this

To avoid cluttering the script, the report shows the data in its simplest form. You can of course go out of your way and send the report as an HTML mail, using style sheets and all.

Find the complete reporting script as file vcProjectStats.ps1.

Schedule the task

We want to schedule the report task to run every day at 02:00 AM. This can be accomplished quite easily with the TaskScheduler module that comes with the PowerShellPack .

Before installing the PowerShellPack make sure to “Unblock” the MSI file.  And make sure to correct the typo in the Register-ScheduleTask code before you use the module.

The available functions in the TaskScheduler module give access to most of the Task Scheduler 2.0 features. But for our purposes, one important feature was missing, the ability to run the scheduled task under a well-defined account.

To solve that problem I created a function, called Set-TaskPrincipal. This function will fill in the required Principal  properties. The password for the account is passed to the Task Scheduler during the registration of the task, in the Register-ScheduledTask function.

The script to create the Scheduled Task can be found as attachment Create-ScheduledTask.ps1

Conclusion

For those that are not yet convinced, I think that these small scripts show, once again, how easy it is to use PowerShell to solve your day-to-day task as an administrator.

The scripts demonstrate

  • the flexibility to use 3-party snapins in your scripts
  • the availability of numerous modules to solve your problems
  • the ease to expand on existing modules
  • the richness of the core PowerShell language

And as always, there is no single, correct script when using PowerShell. Feel  free to send in your improvement suggestions or different methods to tackle the problem.

Happy scripting !

Share on: