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

 

Related Posts

7 thoughts on “Scheduled BigIPReport CSV exports via mail

  1. Hi

    The CSV is only with one line , the description line .. maybe something i configured wrong . … but the bigipreport is working fine which is weird why the csv is not good.

    And I would advise that the CSV be in culumn not hierarchy 🙂

    1. Thanks for the feedback. The script worked for me when testing with SMTP2Go. Perhaps there’s GPO’s that hinders you from sending some of the content?
      What do you mean that the csv should be in column and not hierarchy?

      /Patrik

      1. yes we do have ironport
        but we whitelisted the sender but still sending the csv without the content ..
        the wierd part is that the content has only the CSVHeader .. thats all
        in the server temp file is ok ..only when it has been sent via the mail
        i dont this the ironport has to do something here … maximum is will block the file .. not deleting some of its content .. anyway we whitelisted the sender

          1. Great! 🙂 It’s working fine now ! Thanks !
            one last thing (recommendation) . I think csv file in column way , I mean virtical view, would be more clear than horizontal view.

          2. How would you accomplish that when the data is multidimensional? Do you want to remove the pool information?

            If you can publish an example of how you want it to be I can most likely fix it.

  2. I meant that when the file is created on the folder it is ok
    But when it sent over the mail it is not ok .. it received empty without information and just the first line

Leave a Reply

Your email address will not be published. Required fields are marked *