2020 | Scott W. Head  |  Sr. Systems Admin | Certified Microsoft Windows Server Administrator

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>