Category Archives: Scripting

Streaming Datasets – PowerShell | PowerCLI | Power BI

A large part of my day is spent scripting in PowerShell, specifically with PowerCLI.  One of the strongest areas of PowerCLI, obviously, is being able to retrieve information.  It’s one of the key use cases, in my opinion, for using PowerCLI in a VMware environment, it’s ability to retrieve information for Capacity planning and reporting.

Recently I’ve been looking at how to consume all that information.  You can obviously export it to a CSV, push it into a database, or something that I’ve been playing around with recently, stream it into Power BI.  Now if you haven’t tried it out yet, PowerBI is an analytics service from Microsoft.  At its core it’s a data warehouse for business intelligence.  But putting all those fancy words aside, I use it to create fancy reports.

Exporting information out of a vCenter environment with PowerCLI is dead simple.  I have dozens of scheduled tasks running all the time doing this.  Where I’ve fallen down, is taking that information and trending it over time.  This is where the Streaming Datasets functionality of Power BI comes in.  Using PowerCLI I can get an Object and Value from vCenter and then Post that directly into Power BI, using their API, and have it instantly graphed in a report.  I can then share that report out to anyone I want.  Power BI lets me do this over and over, almost as fast as I can pull the information out of vCenter.

In the example below I show how to create a trend report over time that displays Total and Available Storage of a vCenter Cluster.  Rather simple, I know, but can easily be adapted to show things like number of running VMs running, reserved resources used, etc, etc.  The skies the limit really.

Before we do any scripting the first thing we do is log into Power BI.  If you don’t have an account, don’t worry, the basic version is free.  Hit the Sign Up link and make sure you select Power BI and not Power BI Desktop for Windows, we want the cloud version.

Once logged in we click on Streaming Datasets in the bottom left under the Datasets category.  This is where we create our initial dataset schema so that it can accept streaming input.  We click on ‘Add streaminig dataset’ in the top right.

Then select the source of data, which will be API and click next.

We give our New Streaming Dataset a name and define a few values.  In this example we will define a Date, Total Storage, and Available Storage value, turn on Historic Data Analysis and click Create.  Make note of your data type to the right of the value.  Date is DateTime and the other two are Numbers.

We’ve now created our schema and are provided with a Push URL address and sample code in a few different formats (we want PowerShell).  If you look carefully we are using an Invoke-RestMethod to Post to Power BI.  This sample code has just provided us the template and hardest part of our PowerShell / PowerCLI script.  Click over the code and copy / pasta it out to use in our script (Paste it at the bottom of the script as it will be the last thing that runs).

Now we actually start on the PowerShell / PowerCLI script.  To keep it as concise as possible.  I’ve skip the process I use to actually connect to the vCenter and retrieve the information out using PowerCLI in the code below.  The real goal here is just to retrieve some values and get that into Power BI.  Line 6 is basically retrieving all shared VMFS datastores in Cluster1.  The important lines to note, though, are 4, 8, and 9 where I store my key values in three variables.  One for Date, one for TotalStorage, and one for AvailableStorage.

Import-Module VMware.VimAutomation.Core
Connect-VIServer -Server host.mydomain.local

$date = Get-Date

$datastore = Get-Cluster -Name Cluster1 | Get-Datastore | Where-Object {$_.Type -eq 'VMFS' -and $_.Extensiondata.Summary.MultipleHostAccess}

$TotalStorage = ($datastore | Measure-Object -Property CapacityMB -Sum).Sum / 1024
$AvailableStorage = ($datastore | Measure-Object -Property FreeSpaceMB -Sum).Sum / 1024 

The additional lines below from 11 onward is the important code.  This is our pasted sample code from Power BI that we will slightly modify to push our values up to Power BI.  Don’t copy mine, as your URL and key will be different.  On lines 13, 14, and 15 we will remove the example values and replace it with our three variables, $Date, $TotalStorage, and $AvailableStorage.

Import-Module VMware.VimAutomation.Core
Connect-VIServer -Server 10.1.1.201 -user "mydomain\username"

$date = Get-Date

$datastore = Get-Cluster -Name Cluster1 | Get-Datastore | Where-Object {$_.Type -eq 'VMFS' -and $_.Extensiondata.Summary.MultipleHostAccess}

$TotalStorage = ($datastore | Measure-Object -Property CapacityMB -Sum).Sum / 1024
$AvailableStorage = ($datastore | Measure-Object -Property FreeSpaceMB -Sum).Sum / 1024 

$endpoint = "https://api.powerbi.com/beta/83fe1fa2-fa52-4376-b7f0-cb645a5fcfced/datasets/d57970bc-60b3-46e6-b23b-d782431a72be/rows?key=2zEhgN9mu%2BEH%2FI2Cbk9hd2Kw4b5c84YaO6W8gzFcZbBnO6rti3N631Gjw%2FveNXSBxwR84VcWPGOSrheNwQnCbw%3D%3D"
$payload = @{
"Date" = $Date
"Total Storage" = $TotalStorage
"Available Storage" = $AvailableStorage
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

Disconnect-VIServer * -Confirm:$false

On the last line I disconnect  from my vCenter and close any sessions.  This helps if running as a scheduled task.  Finally save the script.

And that’s it for the scripting part.  Assuming everything is correct, no connection issues, correct values being retrieved.  All we have to do is run the script and it will send a POST request using Invoke-RestMethod with our three values.  We can now run this script as many times as we want and it will continue to post the current date and time along with Total Storage and Available Storage.  At this point, if we wish, we can turn the script into a scheduled task or just continue to run manually to suit our needs.

We now go back to Power BI and report on what we have.  Back on our Streaming Datasets browser window we click the Create Report icon under actions.  Now this part is going to be very subjective to the end user who wants the report.  But the key data we want is under RealTimeData on the far right.  Select all three values and we get presented with a default view of our data.  Under Visualizations select line chart and now we start to see a more visual representation of our capacity over time.  Under the Analytics section add a trend line and see a basic view of available capacity over time.  Finally hit save and you have a self updating report from streaming data.

For the report to start to look anything like below it will take time and a few sample datasets.  In the below image I’ve mocked up some numbers over time as an example.

Once you have a working script and it’s streaming data to PowerBI it’s really up to you on how to report on it.  The above example, as simple as it is, lays the ground work to more customized and complex reporting that you might not be able to get out of traditional monitoring and reporting software.  The ability is there to even share out the report.

Streaming datasets, as you might have noticed in the UR, is still in beta.  As great as I have found it to be it does have some quirks.  For one you can’t easily modify data you have already streamed up to Power BI.  So if you send incorrect data / values up to Power BI in a streaming dataset it will remain their.  At which point you will have to consider Filters to exclude it in reports.

In summary I think Power BI is a very underrated free tool from Microsoft.  I’ve only just started to scratch the surface of what’s possible with it.  The simplicity of capturing data with PowerShell and sending it to Power BI is well worth the time and effort to try at least once.  So what are you waiting for?

PowerCLI A Syslog Server To All ESXi Hosts In vCenter

I was recently tasked with configuring all ESXi hosts within a number of vCenter environments to use a Syslog Server.  Each of these environments contained numerous clusters and ESXi hosts.  Too many to manually want to configure a Syslog Server by hand.  Using our lab environment I played around with a few different ways of quickly pushing out some Syslog settings via PowerCLI.

I came across two different PowerCLI cmdlets that would do the job.  The  first was Set-AdvancedSetting and the second was Set-VMHostAdvancedConfiguration.  The latter being a deprecated cmdlet.  Both cmlets do the job equally well.  Ideally you would want to be using the newer Set-AdvancedSetting cmdlet rather than the deprecate one.

Where I didn’t like the newer Set-AdvancedSetting was that it would ask for confirmation before making a change.   I didn’t fully realise how annoying this would be till after I had wrote my script.   Once a connection to a vCenter is made in PowerCLI the script I created prompts for a Syslog Server then gets all ESXi hosts in the vCenter, applies the Syslog Server value, reloads syslog, and finally opens the syslog ports.  All simple and basic except that you will be prompted to apply the syslog value to each host.  Fine if you’re very cautious or if you want to omit specific hosts.

Perform operation?
Modifying advanced setting ‘Syslog.global.logHost’.
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is “Y”):y

I then decided to test out Set-VMHostAdvancedConfiguration and found I preferred this cmdlet over Set-AdvancedSetting.  Each time it set a syslog value on a host it would flash up a friendly yellow little warning and go ahead with the change.  Much more convenient for large changes.  At some point in a future PowerCLI version I assume this cmdlet won’t work but until then it works a treat.

WARNING: Set-VMHostAdvancedConfiguration cmdlet is deprecated. Use
Set-AdvancedSetting cmdlet instead.

The below code block uses the newer Set-AdvancedSetting cmdlet.

# This script will set a Syslog Server all all ESXi hosts within a vCenter once connected.
# Uses the newer Get / Set-AdvancedSetting cmdlet.
# This cmdlet will require confirmation for each host being modified.
# Created by Mark Ukotic
# 04/04/2015

Write-Host "This script will change the Syslog Server on all hosts within a vCenter, restart Syslog, and open any required ports."

Write-Host

$mySyslog = Read-Host "Enter new Syslog Server. e.g. udp://10.0.0.1:514"

Write-Host

foreach ($myHost in get-VMHost)
{
#Display the ESXi Host being modified
Write-Host '$myHost = ' $myHost

#Set the Syslog Server
$myHost | Get-AdvancedSetting -Name Syslog.global.logHost | Set-AdvancedSetting -Value $mySyslog

#Restart the syslog service
$esxcli = Get-EsxCli -VMHost $myHost
$esxcli.system.syslog.reload()

#Open firewall ports
Get-VMHostFirewallException -Name "syslog" -VMHost $myHost | set-VMHostFirewallException -Enabled:$true
}

This second code block uses the deprecated Set-VMHostAdvancedConfiguration, which I prefer.

# This script will set a Syslog Server all all ESXi hosts within a vCenter once connected.
# The deprecated Set-VMHostAdvancedConfiguration cmdlet is used.
# Seems to run a little more cleaner with this cmdlet and doesn't ask for confirmation
# Created by Mark Ukotic
# 04/04/2015

Write-Host "This script will change the Syslog Server on all hosts within a vCenter, restart Syslog, and open any required ports."

Write-Host

$mySyslog = Read-Host "Enter new Syslog Server. e.g. udp://10.0.0.1:514"

Write-Host

foreach ($myHost in get-VMHost)
{
#Display the ESXi Host being modified
Write-Host '$myHost = ' $myHost

#Set the Syslog Server
Set-VMHostAdvancedConfiguration -Name Syslog.global.logHost -Value $mySyslog -VMHost $myHost

#Restart the syslog service
$esxcli = Get-EsxCli -VMHost $myHost
$esxcli.system.syslog.reload()

#Open firewall ports
Get-VMHostFirewallException -Name "syslog" -VMHost $myHost | set-VMHostFirewallException -Enabled:$true
}

Dude, Where’s my mail statistics?

One thing that really bugged me when Exchange 2007 was released was the removal in the GUI to view mailbox size and total items.  I can’t figure out why?  Though I’m sure there is something written in 3pt text in an obscure TechNet article somewhere?!?!

In Exchange 2000/3 you would drilled down to your Storage Group / Mailbox Store / Mailboxes.  In Exchange 2007/10 it just doesn’t exist.  So how to you get this information?  PowerShell to the rescue… again.  Just another clear indicator that Microsoft wants us using PowerShell more and more.  Using Get-MailboxStatistics we can retrieve this basic information and so much more.

Get-MailboxStatistics

Running the Cmdlet by itself without any parameters will list DisplayName, ItemCount, and StorageLimitStatus for all mailboxes.

We can narrow it down to one user by using the following script.

Get-MailboxStatistics –identity “test.user”

If we want to replicate something similar to what we got in Exchange 2000/3 we can use the following script

Get-MailboxStatistics | ft DisplayName, LastLoggedOnUserAccount, TotalItemSize, ItemCount, LastLogonTime

Using the Format-Table cmdlet we can add a number of different columns in addition to the five above.  Below are columns we can display and filter on.

AssociatedItemCount
Database
DatabaseName
DeletedItemCount
DisconnectDate
DisplayName
Identity
ItemCount
LastLoggedOnUserAccount
LastLogoffTime
LastLogonTime
LegacyDN
MailboxGuid
ServerName
StorageGroupName
StorageLimitStatus
TotalDeletedItemSize
TotalItemSize

There are a few things we can now do to clean up our report.  TotalItemSize is returned to us in Bytes –not very user friendly.  So we can convert this to MB.  We can also sort our results by this column as well.

Get-MailboxStatistics | sort totalitemsize –Descending | ft DisplayName, LastLoggedOnUserAccount,@{n="Total Size (MB)";e={$_.totalitemsize.value.toMB()}}, ItemCount, LastLogonTime

So now we have something very similar like the old Exchange System Manager would have provided us on the screen.  In those days we would simply right click on our Mailbox container and select Export.  We can easily achieve the same thing with PowerShell.  Many people just redirect this output to a text file –which is fine.  The better way is to pipe it to a CSV file

Get-MailboxStatistics | sort totalitemsize –Descending | select-object DisplayName, LastLoggedOnUserAccount,@{n="Total Size (MB)";e={$_.totalitemsize.value.toMB()}}, ItemCount, LastLogonTime | Export-CSV –path ‘c:tempMailboxstats.csv’

Updated: The above line has been modified to include select-object instead of format-table due to using the Export-CSV cmdlet.

We now have a nice comma delimitated output file already sorted by Mailbox size.

Get-MailboxStatistics is a rather simple and easy to use cmdlet.  Once you become comfortable using the cmdlet, you can have a number of pre-defined scripts ready to run just how you like.

If you have a lot of mailboxes and want to disregard mailboxes below a certain size you could filter on TotalItemSize.  The below example will only return mailboxes greater than 100MB.

Get-MailboxStatistics |where {$_.TotalItemSize -gt 100MB} | sort totalitemsize | select DisplayName,ItemCount,@{n="Total Size (MB)";e={$_.totalitemsize.value.toMB()}} |ft -AutoSize

At a later stage I’ll touch on how to get this report to run to a schedule and email the output to yourself or a manager.

Appendix.

Exchange 2003 Mailbox view 

EMC Message Tracking vs PowerShell Message Tracking

Okay so technically it’s all PowerShell right?!?  Well yes and no… more yes.  Sure, when you use the EMC Message Tracking tool, it creates the PowerShell command at the bottom of the window for you to copy and modify as you see fit.  For simple reports for one user on one Exchange server this is fine.  As soon as you need to run more complex reports for multiple user and servers you’ll quick see that doing it directly from PowerShell is much more effective.

Recently I was asked to run a number of reports based off 100+ users on multiple Exchange servers.  The EMC Message Tracking GUI is limited to running reports on one user and one server at a time so this just wouldn’t cut it for my situation.

So how do you make get-messagetrackinglog retrieve reports on multiple users and servers in one script?  Unfortunately you can’t just put multiple email addresses or servers after a parameter.

The trick here is to use the foreach-object cmdlet.  We can place all our users and servers into a variable and then use foreach (alias to foreach-object) to loop through all of our arguments.

In the below example I also use a variable for an output file and starting / ending date ranges.  In my situation it was about recycling the script for different reports as quickly as possible.

$servers = "server1","server2"
$senders = "Email1@domain.com","Email2@domain.com"
$outputfile = "C:tempoutput.csv

$start = “DD/MM/YYYY HH:MM:SS”
$end = “DD/MM/YYYY HH:MM:SS”

$(foreach ($sender in $senders) {
$(foreach ($server in $servers) {

Get-MessageTrackingLog -Sender $sender -Start $start -End $end -EventID “SEND” -Server $server -resultsize unlimited | select Timestamp, @{Name=”Recipients”;expression={$_.Recipients}}, MessageSubject, Sender

})}) | sort-object -property timestamp | Export-CSV -path $outputfile

We’ve now turned a basic command into quite a flexible script for receiving message logs out of exchange.

If you’re going to work out of Excel (or something similar) the sort-object is probably superfluous.  Where it’s useful is if you choose to output to the screen to quickly check what’s being returned.

By replacing

Export-CSV -path $outputfile

With

Format-Table –autosize

You can quickly see what results are returned.  Just remember to increase your screen buffer through the Powershell Window properties, else some columns might not be returned.

WARNING: 2 columns do not fit into the display and were removed.

Finally a note on the -resultsize parameter.  Without it your results would be limited to 1000.