PowerShell PowerCLI Get-VMHost
Gather all datastore cluters, loop through them, get datastore (LUN) info, insert to SQL
I wrote a ASP.net web application for searching and displaying my information I gather below.
I wanted a daily view of each of the datastores, to review free space. This runs as a scheduled task.
& 'C:\Program Files (x86)\VMware\Infrastructure\PowerCLI\Scripts\Initialize-PowerCLIEnvironment.ps1'
Connect-VIServer -Server VCenter -User Domain\Username -Password "xxx"
#// --------------------------------------------------------SQL Connection & Auth----------------------------------------------------------- \\
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ServerName; Initial Catalog=DBNameHere; User ID=sa; Password=xxxx")
$conn.Open()
$cmd = $conn.CreateCommand()
#// -- TimeStamp \\
$Dates= Get-Date
$Dates=$Dates.ToShortDateString()
#// --Gather full list of datastore clusters-- \\
$MyDataCluster=Get-DatastoreCluster | select -ExpandProperty Name
#// A Loop through list of clusters \\
Foreach($Cluster in $MyDataCluster){
# // Try Catch to gather all datastores within each cluster \\
Try{
$MyDatastoreInfo=Get-DatastoreCluster $Cluster -ErrorAction Stop | get-datastore | Select Name, @{l='FreeSpaceGB';e={($_.FreeSpaceGB).tostring("##.##")}}, @{l='CapacityGB';e={($_.CapacityGB).tostring("##.##")}} -ErrorAction Stop
#// B Loop through Infromation Insert to SQL \\
ForEach($IN in $MyDatastoreInfo){
#// C Try Catch Insert to SQL \\
Try{
$Capacity=$IN.Capacitygb
$FreeSpace=$IN.FreeSpaceGB
$LUNNames=$IN.Name
$cmd.CommandText ="INSERT INTO [DOCUMENT].[dbo].[DATASTORES]([LUN_Total_Size], [LUN_Free_Sapce], [ClusterName], [LUNName], [EnteredDate], [vCenter]) VALUES ($Capacity,$FreeSpace,'$Cluster','$LUNNames', '$Dates', 'VPVC01')"
$cmd.ExecuteNonQuery()
$cmd.CommandText
#// Catch Statement \\
}Catch [Exception]{
$_.Exception.Message
} #// End C \\
} #// End B \\
}Catch [Exception]{
$_.Exception.Message
}
}#// End A \\
Clear-Variable MyDataCluster
Clear-Variable Cluster
Clear-Variable MyDatastoreInfo
Clear-Variable IN
Clear-Variable Capacity
Clear-Variable FreeSpace
Clear-Variable LUNNames
Disconnect-VIServer -Server VCenter -confirm:$false
#End PowerShell
​
​
​
Corresponding SQL table
​
SQL Table
USE [Document]
GO
/****** Object: Table [dbo].[DATASTORES] Script Date: 11/17/2016 13:41:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DATASTORES](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LUN_Total_Size] [decimal](8, 2) NOT NULL,
[LUN_Free_Sapce] [decimal](8, 2) NOT NULL,
[ClusterName] [nvarchar](50) NOT NULL,
[LUNName] [nvarchar](50) NOT NULL,
[EnteredDate] [date] NOT NULL,
[vCenter] [nvarchar](50) NULL,
CONSTRAINT [PK_DATASTORES] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
​
​
One of the Corresponding ASP.net Page
​
<asp:GridView ID="GridView1" runat="server"
AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" EmptyDataText="Your Query Returned 0 Results"
DataKeyNames="ID" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Width="1150px">
<RowStyle BackColor="#EFF3FB" />
<Columns>
<asp:BoundField DataField="LUNName" HeaderText="LUN Name" SortExpression="LUNName" />
<asp:BoundField DataField="ClusterName" HeaderText="Cluster Name" SortExpression="ClusterName" />
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" Visible="false" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="LUN_Total_Size" HeaderText="Total Size" SortExpression="LUN_Total_Size" />
<asp:BoundField DataField="LUN_Free_Sapce" HeaderText="Free Space" SortExpression="LUN_Free_Sapce" />
<asp:BoundField DataField="vCenter" HeaderText="vCenter" SortExpression="vCenter" />
<asp:BoundField DataField="EnteredDate" HeaderText="EnteredDate" SortExpression="EnteredDate" />
</Columns>
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle HorizontalAlign="Left" BackColor="#507CD1" Font-Bold="True"
ForeColor="White" />
<EditRowStyle BackColor="#2461BF" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:YourWebConfigConStringHere %>"
SelectCommand="SELECT * FROM [DATASTORES] WHERE ([EnteredDate] = @EnteredDate) ORDER BY [vCenter], [ClusterName], [LUNName]">
<SelectParameters>
<asp:QueryStringParameter DbType="Date" Name="EnteredDate"
QueryStringField="Field" />
</SelectParameters>
</asp:SqlDataSource>
​
​
​
​
​
​
​