Scheduled BigIPReport CSV exports via mail

Today I got a feature request over at Devcentral from a BigIPReport admin to add the possibility to add scheduled exports of BigIPReport via mail. While it does not really fit into the project itself actually doing it is actually simpler than you might think!

Using a mix of Powershell and .Net we can download the Json files, parse them and generate a CSV file that can be sent to anyone in the organisation.

Please note that as usual there’s a thousand ways to skin a cat (funny expression right there) and this script could be improved quite a bit. Some potential examples:

• Creating the attachment from memory instead of a temporary file
• Changing the mail format to HTML and adding some useful statistics like virtual server count, pool count, node count etc.
• Adding a database, or using a flat file could also give out trends.

If anyone is up to the task and wants to share the result I’d be happy to post it here along with your name. 🙂

Anyways, here’s the script!

$BigIPReportURL = "https://bigipreport.domain.local"
 
#SMTP Configuration
$User = "user"
$Password = "password"
$SmtpServer = "mail.domain.com"
$SmtpServerPort = "2525" 
$From = "user@domain.com"
$Recipients = @("user1@domain.com", "user2@domain.com")
 
#Full path to where you want to store the csv temporary csv file
$CSVFile = "C:\Users\Patrik\Documents\t.csv"
 
If(Test-Path $CSVFile){
 Write-Host "CSV file exists, exiting script in order not to overwrite it"
 Exit
}
 
#Create new webclient object
$WebClient = New-Object System.Net.WebClient
#Enable integrated authentication
$WebClient.UseDefaultCredentials = $true
#Get the json objects
$Virtualservers = ($WebClient.DownloadString("$BigIPReportURL/json/virtualservers.json")) | ConvertFrom-Json
$Pools = ($WebClient.DownloadString("$BigIPReportURL/json/pools.json")) | ConvertFrom-Json
 
$CSVHeader = "name;description;ip;port;sslprofile;compressionprofile;persistenceprofile;availability;enabled;currentconnections;cpuavg5sec;cpuavg1min;cpuavg5min;defaultpool;associated-pools;loadbalancer"
Function Get-PoolDetails {
 Param([array]$VSPools, [string]$Loadbalancer)
 
 $ReturnData = @()
 
 Foreach($Pool in $VSPools){
 $ObjPool = $Pools | Where-Object { $_.name -eq $Pool -and $_.loadbalancer -eq $Loadbalancer }
 $ReturnData += ($ObjPool.members | ForEach-Object { $_.name + " (" + $_.ip + ")" }) -Join ", "
 }
 
 $ReturnData -Join "|"
}
 
$CSVHeader | Out-File $CSVFile
 
Foreach($VS in $VirtualServers){
 $PoolDetails = Get-PoolDetails -VSPools $VS.pools -Loadbalancer $VS.Loadbalancer
 @($Vs.name, $Vs.description, $Vs.ip, $Vs.port, $vs.sslprofile, $vs.compressionprofile, $vs.persistenceprofile, $vs.availability, $vs.enabled, $vs.currentconnections, $vs.cpuavg5sec, $vs.cpuavg1min, $vs.cpuavg5min, $vs.defaultpool, $PoolDetails, $vs.loadbalancer) -Join ";" | Out-File -Append $CSVFile
}
 
$MailDate = $(Get-Date -format d)
 
$Email = New-Object System.Net.Mail.MailMessage
 
$Email.From = $From
Foreach($Recipient in $Recipients){
 $Email.to.Add($Recipient)
}
 
$Email.Subject = "$MailDate F5 CSV"
$Email.Body = "Here's the monthly CSV export"
 
$Attachment = New-Object System.Net.Mail.Attachment($CSVFile, 'text/plain')
$Email.Attachments.Add($Attachment)
 
$SMTPClient = New-Object System.Net.Mail.SmtpClient( $SmtpServer , $SmtpServerPort )
$SMTPClient.EnableSsl = $True
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( $User , $Password );
$SMTPClient.Send($Email)
 
$Attachment.Dispose()
Remove-Item $CSVFile